Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Double Sided Query

    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 = 9

  2. #2
    Join Date
    Aug 2007
    Posts
    17
    hi 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

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Nate1
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    On the back of Rudy's post; this article is well worth a read.
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Always makes a lot more sense when you step back and look at it, thanks won't make that mistake again.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Double sided?


    Swedish Airline stewardesses?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •