ListingLink
ID1 ID2 Active Date
9 2 1 2007-10-22 17:47:31.230
9 7 1 2007-10-22 17:47:32.137
9 6 1 2007-10-22 17:47:32.540
9 8 1 2007-10-22 17:47:33.010
3 9 1 2007-10-22 18:31:15.980
I have this table of Data which will Link to topics together, Problem is I need a Query that will produce 5 Rows if the Input is ID 9. Or One Row if the ID was 2.
Not Conditional just example inputs and outputs.
This will only give one side of the Results
SELECT Listing.sTitle,Listing.iListingID
FROM Listing
INNER JOIN ListingLink
ON listingLink.ID2 = listing.ilistingID
WHERE ListingLink.bIsActive = 1 AND ListingLink.ID1 =9 OR ListingLink.ID2 = 9hi nate Try this one::::
SELECT Listing.sTitle,Listing.iListingID
FROM Listing
INNER JOIN Listinglink
ON listing.ilistingID = listinglink.id1 or listing.ilistingid=listinglink.id2
WHERE ListingLink.bIsActive = 1 and ListingLink.ID1 =9 OR ListingLink.ID2 = 9|||Thanks that works a treat,
SELECT Distinct(Listing.sTitle),Listing.iListingID
FROM Listing
INNER JOIN Listinglink
ON listing.ilistingID = listinglink.iLIstingOneID or listing.ilistingid=listinglink.iLIstingtwoID
WHERE ListingLink.bIsActive = 1 and ListingLink.iLIstingOneID =@.ListingID OR ListingLink.iLIstingtwoID = @.LisitngID
I wrote another query which retrieves the Values except a particular ListingID (9) Turns up as Null so when I print it, the set is correct and requires no application Logic, just got to check which query is faster, will put the reply down.
Though I have a Feeling My Query is Slower
SELECT Distinct(Listing.sTitle),Listing.iListingID FROM Listing,ListingLInk Where Listing.iListingID = ListingLink.iLIstingtwoID AND
iLIstingOneID = 9
Union
SELECT Distinct(Listing.sTitle),Listing.iListingID FROM Listing Right JOIN ListingLink ON Listing.iListingID = ListingLink.iLIstingOneID AND
iLIstingTwoID = 9|||SELECT Distinct(Listing.sTitle) ... please don't do that
DISTINCT is not a function
DISTINCT applies to all columns in the SELECT, not just the one with parentheses around it|||On the back of Rudy's post; this article (http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx) is well worth a read.|||Always makes a lot more sense when you step back and look at it, thanks won't make that mistake again.|||Double sided?
Swedish Airline stewardesses?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment