By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,067 Members | 1,818 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,067 IT Pros & Developers. It's quick & easy.

Select right price by products..

100+
P: 137
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?
Expand|Select|Wrap|Line Numbers
  1. Table structure:
  2. products:
  3. ID               int(3) PK
  4. Description     text
  5. value           varchar(250)
  6. lokation          varchar(40)
  7. brand_id          int(11)
  8. type_id          int(11)
  9. article_id       int(11)
  10. crediteur_id     int(11)
  11. loaned       int(11)
  12. repair       int(11)
  13. Boxed         int(11)
  14. barcode1         varchar(20)
  15. barcode2         varchar(20)
  16. artikelnr_supplier varchar(45)
  17. garantuee         int(11)
  18. picture            varchar(85)
  19.  
Expand|Select|Wrap|Line Numbers
  1. Table price:
  2. id               int(11) PK
  3. producten_id     int(11)
  4. exclusive_price  decimal(8,2)
  5. verwijderings_bijdrage_id int(11)
  6. tax_tarrif      decimal(4,2)
  7. inclusive_price  decimal(8,2)
  8. bruto_profit      decimal(8,2)
  9. marge            decimal(4,2)
  10. advice_price     decimal(8,2)
  11. our_price      decimal(8,2)
  12. porto_costs     decimal(5,2)
  13. date            datetime
  14.  
Thanks!
Regards,
Paul
Oct 17 '11 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,359
The best way is as you described it in your post,
select a product and inner join the price table
Oct 17 '11 #2

100+
P: 137
Well, i have this query but i have 2 prices for this product.
And it returns 2 records.

Expand|Select|Wrap|Line Numbers
  1. SELECT p.id, artikel, merk, type, onze_prijs, garantie, verwijderings_bijdrage.bedrag,
  2.                             (producten_historie.aantal_ingekocht - (p.uitgeleend + p.reparatie) - producten_historie.aantal_verkocht) AS Stock
  3.                             FROM producten as p 
  4.                             INNER JOIN merk ON p.merk_id = merk.id 
  5.                             INNER JOIN type ON p.type_id = type.id 
  6.                             INNER JOIN producten_historie ON p.id = producten_historie.producten_id 
  7.                             INNER JOIN artikel ON p.artikel_id = artikel.id 
  8.                             LEFT OUTER JOIN 
  9.                             (SELECT id, onze_prijs, producten_id, verwijderings_bijdrage_id
  10.                             FROM producten_prijzen 
  11.                             ORDER BY id DESC
  12.                             ) T                    
  13.                             ON p.id = T.producten_id
  14.                     LEFT OUTER JOIN verwijderings_bijdrage ON T.verwijderings_bijdrage_id = verwijderings_bijdrage.id 
  15.                     WHERE (merk.merk LIKE '%hp550%') OR (type.type LIKE '%hp550%') OR (barcode1 LIKE '%hp550%') OR
  16.                     (barcode2 LIKE '%hp550%') OR (artikel.artikel LIKE '%hp550%') OR p.omschrijving LIKE '%hp550%'
  17.  
Oct 17 '11 #3

Rabbit
Expert Mod 10K+
P: 12,359
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.
Oct 17 '11 #4

100+
P: 137
Well, done that and it''s working!
Jees, what a query was that!

Added this:
Expand|Select|Wrap|Line Numbers
  1. ...LEFT OUTER JOIN
  2.   (SELECT id, onze_prijs, producten_id, verwijderings_bijdrage_id
  3.                             FROM(SELECT MAX(id) as maxid
  4.                             FROM(producten_prijzen)
  5.                                   GROUP BY producten_id)
  6.                              AS x
  7.      INNER JOIN producten_prijzen AS p ON p.id = x.maxid 
  8.      ) T
  9.    ON....... 
  10.  
Thanks!
Oct 17 '11 #5

Rabbit
Expert Mod 10K+
P: 12,359
Not a problem, you should mark your post as the answer for others that may come upon this thread.
Oct 17 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.