Subqueries with more results

[reentry]

Hi,

I Have some troubles with a SQL Statment. The result should be a list of the same Articles with current price and calculated price per kg.

Each article has more prices.

The querie works fine with 1 article:

SELECT Preis.ArtikelID, Preis.BruttoPreis, Artikel.Bezeichnung, Artikel.Einheit, Artikel.Liefereinheit, Artikel.Gewicht, Artikel.ArtikelNr, round((Preis.BruttoPreis / Artikel.Gewicht *1 ),2) as KiloPreis
FROM Preis INNER JOIN Artikel ON Preis.ArtikelID = Artikel.ArtikelID
WHERE (((Preis.Preisid)=
                                 (SELECT Max(Preis.PreisID)
                                  FROM Preis WHERE Artikelid = 10113)));

My problem is if i try to search for all Artikel which contains for example orange...

I thought this would work:

SELECT Preis.ArtikelID, Preis.BruttoPreis, Artikel.Bezeichnung, Artikel.Einheit, Artikel.Liefereinheit, Artikel.Gewicht, Artikel.ArtikelNr, Round((Preis.BruttoPreis/Artikel.Gewicht*1),2) AS KiloPreis, Artikel.Bezeichnung
FROM Preis INNER JOIN Artikel ON Preis.ArtikelID = Artikel.ArtikelID
WHERE (((Preis.Preisid) = 
                                   (SELECT Max(Preis.PreisID) 
                                    FROM Preis WHERE Artikelid IN 
                                                                             (SELECT ArtikelID 
                                                                               FROM Artikel WHERE Bezeichnung like '*Orange*'))));

I get 1 Article instead of all articles which contain Orange... I think the problem is the MAX function but i have no idea how i can solve it.

Does anyone have an idea ? or solution ?

Best Regards Matthias

[June7]

If you want the most recent price record for each article, consider TOP N query: http://allenbrowne.com/subquery-01.html#TopN

[reentry]

I considered your hint ( i created the below statement) and i get a better result, but now i get lots of duplicates Seen in Table below. There are exactly 31 Articles which have orange in their name, thats the reason why i chose TOP 31. Dont be irretated by the empty cells, i am working with some test data therefore there are lots of data missing.

How can i reduce these Duplicates because he chose not only the current price id? Group by doesnt work and if i use DISTINCT the sql staments seems to be in an endless Loob ..

SELECT Preis.ArtikelID, Preis.BruttoPreis, Artikel.Bezeichnung, Artikel.Einheit, Artikel.Liefereinheit, Artikel.Gewicht, Artikel.ArtikelNr, Round((Preis.BruttoPreis/Artikel.Gewicht*1),2) AS KiloPreis, Artikel.Bezeichnung
FROM Preis INNER JOIN Artikel ON Preis.ArtikelID = Artikel.ArtikelID
WHERE (((Preis.Preisid) in (Select TOP 31 Preis.PreisID From Preis Where Artikelid IN (Select ArtikelID from Artikel Where Bezeichnung like '*Orange*'))));

PreisID	Artikelid	ArtikelNr	Bezeichnung	Einheit	Liefereinheit	Gewicht	BruttoPreis	KiloPreis
28452	10316	kof0541	Orangen 15 kg Übersee		0	0	19,5
28302	10316	kof0541	Orangen 15 kg Übersee		0	0	19,5
30969	10316	kof0541	Orangen 15 kg Übersee		0	0	19,5
28453	10416	kof0546	Orangen XXL 8,50 kg		0	0	26
30970	10416	kof0546	Orangen XXL 8,50 kg		0	0	26
28454	10417	kof0547	Kleine Orangen Kiste I 6 kg		0	0	9,8
30971	10417	kof0547	Kleine Orangen Kiste I 6 kg		0	0	9,8
28659	10580	kof1328	Struwelpeter Orangen 8 kg		0	0	18
31163	10580	kof1328	Struwelpeter Orangen 8 kg		0	0	18
28737	10653	kof1431	VB Kbl-Orangen FILET 5 Liter		0	0	38
31240	10653	kof1431	VB Kbl-Orangen FILET 5 Liter		0	0	38
28974	10184	kof1800	6 x 2 Liter Orangenjuice Fris		0	0	6,7
31474	10184	kof1800	6 x 2 Liter Orangenjuice Fris		0	0	6,7
28170	10184	kof1800	6 x 2 Liter Orangenjuice Fris		0	0	6,1
31475	10875	kof1801	FR Blutorangensaft Frisch 6 x 2 Lt		0	0	6,85
28975	10875	kof1801	FR Blutorangensaft Frisch 6 x 2 Lt		0	0	6,85
29036	10936	kof1875	Orangenkonfitüre 3 kg		0	0	13,5
31535	10936	kof1875	Orangenkonfitüre 3 kg		0	0	13,5
29227	11107	kof2324	Orange Roughy 10kg 120/170gr		0	0	20,5
29242	11119	kof2339	Orange Roughy 5 kg 170/230gr		0	0	22,9
28306	10320	kof2830	Blutorangeneis 1 x 4 Liter		0	0	14
29522	10320	kof2830	Blutorangeneis 1 x 4 Liter		0	0	19,8
29581	11430	kof2947	Coulis-Blutorangenpüree 12x480gr		0	0	3,8
29622	11470	kof3006	TK-Orangesanguinepüree 6x1kg (Blutorang)		0	0	6,1
30161	11982	kof4212	Orange Schokolade 4 x 2,50 kg		0	0	29,75
30209	12029	kof4272	Orange Turitella 117 Stk		0	0	17,5
30307	12127	kof4522	Turitella Orange 1 Pkg		0	0	15,5
30385	12205	kof4703	Fruchtw Orangenessig 12x250ml		0	0	5,3
30444	12264	kof4924	Orangenöl 6x500ml		0	0	7,9
30586	12406	kof5673	Unbehandelte Orangen 5 kg		0	0	12
30778	12598	kof6540	Orangen per Stk		0	0	0,3

[June7]

The PreisID values are unique, therefore the records are not duplicates.

Apparently these tables have a 1-to-many relationship. One of the tables has multiple records with the same ArtikelID. Therefore, joining these tables appears to 'duplicate' data from the other table.

[reentry]

U are right they arent dublicates wrong term, but i need only the data for the highest Price ID for each article ( article to price is a 1 to n relationship).

[orange]

Please show us more of your table designs.
As June says, these are not duplicates.

And after seeing your latest post ( I was writing) which of these do you want since the Price is same in all 3 records???

28452	10316	kof0541	Orangen 15 kg Übersee		0	0	19,5
28302	10316	kof0541	Orangen 15 kg Übersee		0	0	19,5
30969	10316	kof0541	Orangen 15 kg Übersee		0	0	19,5

[June7]

The link I posted shows how to get the TOP n for each group - in your case the groups are ArtikelID.

If you eliminate the PreisID field, could do a GROUP BY or DISTINCT query.

[reentry]

@
Always the one with the highest PreisID in this case this one

30969

10316

kof0541

Orangen 15 kg Übersee

0

0

19,5

Because all other arnt calculated with the current price.