Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Unanswered: Conditional Join

    I have three tables ...

    Code:
    tblWine                  tblSpecialOfferWine             tblSpecialOffer
    ID  Name                 ID  WineID  SpecialOfferID      ID  Name            IsLive
    =====================	 ============================    ==========================
    1   Mouton Rothschild    1   1       1                   1   February Offer  0
    2   Lafite Rothschild    2   1       2                   2   March Offer     1
    3   Chateau Teyssier     3   2       1
    ... and the current query I am using is the following along with it's result set ...

    Code:
    SELECT
    	tblWine.ID AS WineID,
    	tblWine.Name AS WineName,
    	tblSpecialOffer.ID AS SpecialOfferID,
    	tblSpecialOffer.Name AS SpecialOfferName
    
    FROM
    	tblWine
    	LEFT OUTER JOIN tblSpecialOfferWine ON tblSpecialOfferWine.WineID = tblWine.ID
    	LEFT OUTER JOIN tblSpecialOffer ON tblSpecialOfferWine.SpecialOfferID = tblSpecialOffer.ID
    
    Results
    WineID  WineName           SpecialOfferID  SpecialOfferName
    ===========================================================
    1       Mouton Rothschild  1               February Offer
    1       Mouton Rothschild  2               March Offer
    2       Lafite Rothschild  1               February Offer
    3       Chateau Teyssier   NULL            NULL
    ... but the result set I want is All wines and their associated specials offers but only show details of the offer if the offer is live like so ...

    Code:
    Results
    WineID  WineName           SpecialOfferID  SpecialOfferName
    ===========================================================
    1       Mouton Rothschild  2               March Offer
    2       Lafite Rothschild  NULL            NULL
    3       Chateau Teyssier   NULL            NULL
    ... I've tried putting where clauses like ...

    Code:
    WHERE tblSpecialOffer.IsLive = 1 OR tblSpecialOffer.IsLive IS NULL
    ... but then that hides the wines that were on a previously associated on a special offer but is no longer live (see Wine #2).

    Any ideas on the query I should be using?

    Note: The queries and data above were made off the top of my head so may contain mistakes.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are very close:
    Code:
    SELECT
    	tblWine.ID AS WineID,
    	tblWine.Name AS WineName,
    	tblSpecialOffer.ID AS SpecialOfferID,
    	tblSpecialOffer.Name AS SpecialOfferName
    
    FROM
    	tblWine
    	LEFT OUTER JOIN tblSpecialOfferWine ON tblSpecialOfferWine.WineID = tblWine.ID
    	LEFT OUTER JOIN tblSpecialOffer
    		ON tblSpecialOfferWine.SpecialOfferID = tblSpecialOffer.ID
    		AND tblSpecialOffer.IsLive = 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    Thanks, I did and it worked ... kind of. Here are the results ...

    Code:
    Results
    WineID  WineName           SpecialOfferID  SpecialOfferName
    ===========================================================
    1       Mouton Rothschild  NULL            NULL
    1       Mouton Rothschild  2               March Offer
    2       Lafite Rothschild  NULL            NULL
    3       Chateau Teyssier   NULL            NULL
    ... I'd want it so it would only show Wine #1 once. So it should join rows from tblSpecialOfferWine if the associated special offer isn't live.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then you will nee a subquery:
    Code:
    SELECT
    	tblWine.ID AS WineID,
    	tblWine.Name AS WineName,
    	SpecialOffers.SpecialOfferID,
    	SpecialOffers.SpecialOfferName
    
    FROM
    	tblWine
    	LEFT OUTER JOIN --SpecialOffers
    		(SELECT	tblSpecialOfferWine.WineID
    			tblSpecialOffer.ID AS SpecialOfferID,
    			tblSpecialOffer.Name AS SpecialOfferName
    		FROM	tblSpecialOfferWine ON tblSpecialOfferWine.WineID = tblWine.ID
    			INNER JOIN tblSpecialOffer
    		ON tblSpecialOfferWine.SpecialOfferID = tblSpecialOffer.ID
    		WHERE	tblSpecialOffer.IsLive = 1) SpecialOffers
    		ON tblWine.WineID = SpecialOffers.WineID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39

    Thumbs up

    Works a treat! Thanks a lot

Posting Permissions

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