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.

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.

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 DPPlease check your Resultsets they do not represent the requirement you stated...
BTW, for your help ...

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|||:) 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.

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 ...

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 ...

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.|||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 ...

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 ...

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.|||try this as the solution to your problem in post #1select 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 )|||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.|||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

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?|||any source, doesn't really matter. but as long as only one is shown.|||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)

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
)
)|||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 ) )