B3rgi
B3rgi

Reputation: 3

One-to-many relationship only one entry of the many needed

At first, thank you for taking the time to read this

Problem:

Example

SELECT Article.ID, Article.Name, Article.Price, EV.EV, Pic.Upload_Datum 
FROM Article
LEFT JOIN Pic ON Articel.ID = Pic.Artikle_ID 
LEFT JOIN Stock ON Stock.ID = Article.Lager_ID
LEFT JOIN EV ON EV.ID = Article.EV_ID 
WHERE Article.Activ = "1" 
AND Stock.trader_ID = "2" 
AND Article.stock_ID = "1"
AND pic.Uploaddate IS NOT BETWEEN 1510903702 AND 1503127702
LIMIT 10

Example resutl

ID Upload

2624919 1489736525

2624919 1489736528

2624920 1507295386

2624920 1507295389

2624920 1507295397

thats one of the results i get but i only need this two:

2624919 1489736525

2624920 1507295386

Now i get foreach Article.ID more than one record, because in the "pic" table there are more than one pictures of any article. The problem is, that i only need the first picture that the sql finds.

I hope its the right way to ask.

Greetings B3rgi

Upvotes: 0

Views: 43

Answers (1)

i3lai3la
i3lai3la

Reputation: 980

If you dont really care of which data to take for your pic, see below sample, it will take the first pic of your Article.

SELECT Article.ID, Article.Name, Article.Price, EV.EV, 

         SELECT  Pic.Upload_Datum 
         FROM    Pic 
         WHERE   Articel.ID = Pic.Artikle_ID 
         LIMIT 1) AS Upload_Datum 
FROM Article
LEFT JOIN Stock ON Stock.ID = Article.Lager_ID
LEFT JOIN EV ON EV.ID = Article.EV_ID 
WHERE Article.Activ = "1" 
  AND Stock.trader_ID = "2" 
  AND Article.stock_ID = "1"
LIMIT 10

Upvotes: 1

Related Questions