Select right price by products..

[djpaul]

Hello,
I have a database for my products.
In there is a table with all the products and there is a table with te actual prices.
I need to select a product and inner join the price table an get the latest (read latest id) from the price table.

What is the best way?

Table structure:
products:
ID               int(3) PK
Description     text
value           varchar(250)
lokation          varchar(40)
brand_id          int(11)
type_id          int(11)
article_id       int(11)
crediteur_id     int(11)
loaned       int(11)
repair       int(11)
Boxed         int(11)
barcode1         varchar(20)
barcode2         varchar(20)
artikelnr_supplier varchar(45)
garantuee         int(11)
picture            varchar(85)

Table price:
id               int(11) PK
producten_id     int(11)
exclusive_price  decimal(8,2)
verwijderings_bijdrage_id int(11)
tax_tarrif      decimal(4,2)
inclusive_price  decimal(8,2)
bruto_profit      decimal(8,2)
marge            decimal(4,2)
advice_price     decimal(8,2)
our_price      decimal(8,2)
porto_costs     decimal(5,2)
date            datetime

Thanks!
Regards,
Paul

[Rabbit]

The best way is as you described it in your post,

select a product and inner join the price table

[djpaul]

Well, i have this query but i have 2 prices for this product.
And it returns 2 records.

SELECT p.id, artikel, merk, type, onze_prijs, garantie, verwijderings_bijdrage.bedrag,
                            (producten_historie.aantal_ingekocht - (p.uitgeleend + p.reparatie) - producten_historie.aantal_verkocht) AS Stock
                            FROM producten as p 
                            INNER JOIN merk ON p.merk_id = merk.id 
                            INNER JOIN type ON p.type_id = type.id 
                            INNER JOIN producten_historie ON p.id = producten_historie.producten_id 
                            INNER JOIN artikel ON p.artikel_id = artikel.id 
                            LEFT OUTER JOIN 
                            (SELECT id, onze_prijs, producten_id, verwijderings_bijdrage_id
                            FROM producten_prijzen 
                            ORDER BY id DESC
                            ) T                    
                            ON p.id = T.producten_id
                    LEFT OUTER JOIN verwijderings_bijdrage ON T.verwijderings_bijdrage_id = verwijderings_bijdrage.id 
                    WHERE (merk.merk LIKE '%hp550%') OR (type.type LIKE '%hp550%') OR (barcode1 LIKE '%hp550%') OR
                    (barcode2 LIKE '%hp550%') OR (artikel.artikel LIKE '%hp550%') OR p.omschrijving LIKE '%hp550%'

[Rabbit]

In your subquery where you select from the price table, use a subquery to filter for the max id where you group by the product id.

[djpaul]

Well, done that and it''s working!
Jees, what a query was that!

Added this:

...LEFT OUTER JOIN
  (SELECT id, onze_prijs, producten_id, verwijderings_bijdrage_id
                            FROM(SELECT MAX(id) as maxid
                            FROM(producten_prijzen)
                                  GROUP BY producten_id)
                             AS x
     INNER JOIN producten_prijzen AS p ON p.id = x.maxid 
     ) T
   ON.......

Thanks!

[Rabbit]

Not a problem, you should mark your post as the answer for others that may come upon this thread.