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

    Unanswered: Query table for cheapest items (but a little more complicated than that)

    I'm not sure if this is even possible but can i pull out rows from two tables (that have a one-to-many relationship) but only if they satisfy a few conditions.

    Code:
    tblWine              tblSources
    ID  Name             ID  WineID  Source   Price    Status
    -----------------    -----------------------------------------
    1   Le Dome          1   1       Smith    100.00   IB
    2   Teyssier         2   1       Jones    110.00   IB
    3   Muscat           3   1       Hill     100.00   DP
                         4   2       Smith    135.00   DP
                         5   2       Hill     125.00   DP
    I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.

    Code:
    tblWineSources
    WineID  Name         SourceID  Source   Price    Status
    -------------------------------------------------------------
    1       Le Dome      1         Smith    110.00   IB
    1       Le Dome      3         Hill     100.00   DP
    2       Teyssier     5         Hill     125.00   DP

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Please check your Resultsets they do not represent the requirement you stated....
    BTW, for your help ...
    Code:
    CREATE TABLE tblWine(
    ID VARCHAR(10)
    ,NAME VARCHAR(20))
    Go
    CREATE TABLE tblSources(
    ID VARCHAR(10)
    ,WINEID VARCHAR(10)
    ,SOURCE VARCHAR(20)
    ,PRICE INT
    ,STATUS VARCHAR(10))
    Go
    INSERT INTO tblWine VALUES('1','Le Dome')
    INSERT INTO tblWine VALUES('2','Teyssier')
    INSERT INTO tblWine VALUES('3','Muscat')
    
    INSERT INTO tblSources VALUES('1','1','Smith',100.00,'IB')
    INSERT INTO tblSources VALUES('2','1','Jones',110.00,'IB')
    INSERT INTO tblSources VALUES('3','1','Hill',100.00,'DP')
    INSERT INTO tblSources VALUES('4','2','Smith',135.00,'DP')
    INSERT INTO tblSources VALUES('5','2','Hill',125.00,'DP')
    GO
    ---The required Query Place----
    SELECT tblSources.WINEID
           ,tblWine.Name
           ,tblSources.ID as SourceID
           ,tblSources.Source
           ,tblSources.Status
           ,tblSources.Price as Maxprice
    FROM tblWine
    INNER JOIN tblSources
    ON tblWine.ID=tblSources.WINEID
    
    GROUP BY tblSources.WINEID
            ,tblWine.Name
            ,tblSources.ID 
            ,tblSources.Source
            ,tblSources.Status
            ,tblSources.Price
    ------------------------------
    
    Go
    DROP TABLE tblWine
    DROP TABLE tblSources
    Last edited by rudra; 10-18-06 at 13:37.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    I realise that. That's because I don't want to return all the rows in the join. I only need the rows that have the cheapest price for their status. Since my first post I've managed to solve part of the problem.

    Code:
    SELECT
    	tblWine.ID AS WineID,
    	tblWine.Name,
    	tblSources.ID AS SourceID,
    	tblSources.Source,
    	tblSources.Status,
    	tblSources.Price
    FROM
    	tblSources
    	LEFT OUTER JOIN tblWine ON tblSources.WineID = tblWine.ID
    GROUP BY
    	tblWine.ID,
    	tblWine.Name,
    	tblSources.ID,
    	tblSources.Source,
    	tblSources.Status,
    	tblSources.Price
    HAVING
    	tblSources.ID IN
    	(
    		SELECT 
    			TOP 1
    			subSources.ID
    		FROM 
    			tblSources subSources
    		WHERE
    			subSources.WineID = tblWine.ID
    			AND subSources.Status = 'IB'
    		ORDER BY
    			subSources.Price ASC
    	)
    	OR tblSources.ID IN
    	(
    		SELECT 
    			TOP 1
    			subSources.ID
    		FROM 
    			tblSources subSources
    		WHERE
    			subSources.WineID = tblWine.ID
    			AND subSources.Status = 'DP'
    		ORDER BY
    			subSources.Price ASC
    	)
    ORDER BY
    	tblWine.ID ASC,
    	tblSources.ID ASC

    Thanks for you help though.

    The problem now is that I've made these tables simpler for the purpose of this post so not to confuse the issue. There is actually another column in tblSources which I need to check is unique just like status. This additional column can have 1 of 8 different values, and the only way I currently know how to do it is so expand the query above to accommodate 16 subqueries in the HAVING clause, which doesn't seem ideal, or is it?

    So ...

    Code:
    tblWine              tblSources
    ID  Name             ID  WineID  Source   Price    Status  Format
    -----------------    -----------------------------------------
    1   Le Dome          1   1       Smith    100.00   IB      Bottle
    2   Teyssier         2   1       Jones    110.00   IB      Bottle
    3   Muscat           3   1       Hill     100.00   DP      Bottle
                         4   2       Smith    135.00   DP      Bottle
                         5   2       Hill     125.00   DP      Magnum
    ... would return ...

    Code:
    tblWineSources
    WineID  Name         SourceID  Source   Price    Status  Format
    -------------------------------------------------------------
    1       Le Dome      1         Smith    110.00   IB      Bottle
    1       Le Dome      3         Hill     100.00   DP      Bottle
    2       Teyssier     5         Smith    135.00   DP      Bottle
    2       Teyssier     5         Hill     125.00   DP      Magnum
    So because there are two sources for Le Dome / IB / Bottle, only the cheapest record would be shown.

  4. #4
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    If no one can help with the above, could someone tell me if the following is even possible?

    How do I find the IDs of the cheapest Format/Status pair?

    So ...

    Code:
    tblSources
    ID     Price    Format    Status
    -----------------------------------
    1540   100.00   Bottles   IB
    1541   110.00   Bottles   DP
    1542   105.00   Bottles   IB
    1543   105.00   Bottles   DP
    1544   115.00   Magnums   IB
    1545   110.00   Magnums   IB
    ... would result in ...

    Code:
    Results
    ID
    ----
    1540
    1543
    1545
    I could use a query for each combonation but the actual data can contain 1 of 2 different statuses or 1 of 8 different formats.
    Last edited by GoMo; 10-19-06 at 07:59.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this as the solution to your problem in post #1
    Code:
    select W.ID as WineID
         , W.Name
         , S.ID as SourceID
         , S.Source
         , S.Price
         , S.Status
      from tblWine as W
    inner
      join tblSources as S
        on S.WineID = W.ID
       and S.Price =
           ( select min(Price)
               from tblSources
              where WineID = S.WineID
                and Status = S.Status )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by GoMo
    But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included?
    yep, that is correct

    Quote Originally Posted by GoMo
    One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.
    which source?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    any source, doesn't really matter. but as long as only one is shown.

  9. #9
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    39
    thanks to you, i've sussed it!

    here's an example of what i did (i used a specific wine with many sources, in this case it had an ID of 11)

    Code:
    SELECT
    	tblSources.*
    FROM
    	tblSources
    WHERE
    	tblSources.WineID = 11
    	AND tblSources.ID =
    	(
    		SELECT
    			TOP 1
    			subSources.ID
    		FROM
    			tblSources AS subSources
    		WHERE
    			subSources.WineID = tblSources.WineID
    			AND subSources.Format = tblSources.Format
    			AND subSources.Status = tblSources.Status
    			AND subSources.CasePrice =
    			(
    				SELECT
    					MIN(sub2Sources.CasePrice)
    				FROM
    					tblSourcesAS sub2Sources
    				WHERE
    					sub2Sources.WineID = subSources.WineID
    					AND sub2Sources.Format = subSources.Format
    					AND sub2Sources.Status = subSources.Status
    			)
    	)

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select W.ID as WineID
         , W.Name
         , S.ID as SourceID
         , S.Source
         , S.Price
         , S.Status
      from tblWine as W
    inner
      join tblSources as S
        on S.WineID = W.ID
       and S.Price =
           ( select min(Price)
               from tblSources
              where WineID = S.WineID
                and Status = S.Status ) 
       and S.Source =
           ( select min(Source)
               from tblSources
              where WineID = S.WineID
                and Status = S.Status 
                and Price =
                    ( select min(Price)
                        from tblSources
                       where WineID = S.WineID
                         and Status = S.Status ) )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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