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

Select rows with inner join and max id

100+
P: 137
Hello,
I have wrote this query but it's not completely right.
In my database are more tables and 1 of those tables contains more rows about a product (prices).
So when i want to select the records from a invoice with for example 3 products from the same id en 2 products from another id, it has to show all rows in that invoice but with the last added price.

This is my query so far:
Expand|Select|Wrap|Line Numbers
  1. SELECT verkoop_order.sub_totaal, verkoop_order.btw_percentage, verkoop_order.btw_bedrag, verkoop_order.verzendkosten,
  2.                 verkoop_order.datum_verkoop, verkoop.artikel_groep, verkoop.Omschrijving,
  3.                 verwijderings_bijdrage.bedrag AS verwijderingsbijdrage, verkoop.Aantal, verkoop.prijs, verkoop.totaal, verkoop.btw
  4.                 FROM(verkoop_order)
  5.                 INNER JOIN verkoop ON verkoop_order.id = verkoop.verkoop_id 
  6.                 LEFT OUTER JOIN producten ON verkoop.product_id = producten.id
  7.                 LEFT OUTER JOIN producten_prijzen ON producten.id = producten_prijzen.producten_id 
  8.                 LEFT OUTER JOIN verwijderings_bijdrage ON producten_prijzen.verwijderings_bijdrage_id = verwijderings_bijdrage.id
  9.                 WHERE verkoop_order.id = 212 ORDER BY verkoop.id
  10.                 LIMIT 0, 60
So, 1 product has for example 3 prices (to calculate the average purchase price)
But the rows that i want from that invoice can have multiple rows from that specified product, but the latest (or highest id) price

Hope it's clear enough... :)

Thanks in advance!
Paul
Ps, sorry, the table is in dutch, but i hope this will not bother you... :D
Jul 8 '11 #1
Share this Question
Share on Google+
12 Replies


Rabbit
Expert Mod 10K+
P: 12,347
It would if you posted some sample data and expected results.
Jul 8 '11 #2

100+
P: 137
Let's try:
Last week i sold 1 laser and 1 light effect.
But the laser had 2 prices in his table (producten_prijzen)
When i roll it out this came up:
Laser American Dj micro galaxian 0,00 1 95,00 95,00
Laser American Dj micro galaxian 0,00 1 95,00 95,00
Led effect American Dj Jewel led 0,00 1 69,00 69,00

So i changed it at the end with a GROUP BY
So i thought it was good...
But today i made a invoice for services.
In there was 1 product called services with 1 price.
So, when i made the invoice, it looks like this:

Services Labour hour 29-6-2011 0,00 5 30,00 150,00
Services Labour hour 27-6-2011 0,00 3 30,00 90,00
Services Labour hour 28-6-2011 0,00 4 30,00 120,00

But now the query returned this...
Services Labour hour 29-6-2011 0,00 5 30,00 150,00

And that's it...

Hope this is more clear?
Regards,
Paul
Jul 8 '11 #3

Rabbit
Expert Mod 10K+
P: 12,347
I still don't understand.

I don't see what the laser and led products have to do with your example. You mention them but then don't use them any further in your example.

Plus, you don't say what is wrong with the results you got and you don't say what results you're expecting.
Jul 8 '11 #4

100+
P: 137
Well i know, but it's hard to explain...
Simple said.. when there are 2 or more records in the price table of that product, it is shown also that many times.
But when i use another product (with 1 price record) more then once it now shows 1 record.
But before the change i made (without the GROUP BY) it shows the correct rows but if that product has 2 or more prices it shows for every price a row.
In the above example i did use 2 different things but they are all in the product list.
Uuuhhmmm....Copy of the example but with more explanation:

description | product | disposal contribution | amount | price | sum price
Laser American Dj micro galaxian 0,00 1 95,00 95,00
Laser American Dj micro galaxian 0,00 1 95,00 95,00
Led effect American Dj Jewel led 0,00 1 69,00 69,00

But i want to have:
Description | product | disposal contribution | amount | price | sum price
Laser American Dj micro galaxian 0,00 1 95,00 95,00
Led effect American Dj Jewel led 0,00 1 69,00 69,00

description | product | disposal contribution | amount | price | sum price
Services Labour hour 29-6-2011 0,00 5 30,00 150,00
Services Labour hour 27-6-2011 0,00 3 30,00 90,00
Services Labour hour 28-6-2011 0,00 4 30,00 120,00

But now the query returned this...
Services Labour hour 29-6-2011 0,00 5 30,00 150,00

But i want (and this one is with 1 price in the price table):
Services Labour hour 29-6-2011 0,00 5 30,00 150,00
Services Labour hour 27-6-2011 0,00 3 30,00 90,00
Services Labour hour 28-6-2011 0,00 4 30,00 120,00

i know these are 2 different products but when i sell 2 lasers, i set the amount on 2.
But with the services i did at the customer, i spend a few days.
So i specified that with multiple rows of 1 product instead of setting 1 row with more amount (as you can see)
Maybe i can send you an example in pdf?

The table structe looks like this:
producten (products)
producten_gereserveerd (products_reserverd)
producten_historie(products_history)
producten_prijzen(products_prices)
producten_reparatie(products_repair)
producten_statestieken(products_statistics)
producten_uitgeleend(products_borought)
merk(make)
type(type)
artikel_groep(article_group)

The main table is the products table.
In there the id's of the make, type, article group are set.
The other tables (everything with products_ at the beginning) has a row with the products_id in it the to join it with the products table.

So, i need to select the latest price, in the price table, and show this price for every added line.
Jul 8 '11 #5

Rabbit
Expert Mod 10K+
P: 12,347
I'm going to need a complete example to know what you're trying to do. I can't understand what you're saying.

What I need is something like this:

Here are my tables and sample data
Expand|Select|Wrap|Line Numbers
  1. tblOrders
  2. Order#
  3. 123456
  4. 123457
Expand|Select|Wrap|Line Numbers
  1. tblOrderItems
  2. OrderItem# Order# PID# Quantity Price
  3. 1          123456 1    2        $4.50
  4. 2          123456 2    5        $1.00
  5. 3          123457 1    1        $4.50
  6. 4          123457 3    3        $12.00
The results I'm looking for is this.
Expand|Select|Wrap|Line Numbers
  1. ...
Jul 9 '11 #6

100+
P: 137
table price
Expand|Select|Wrap|Line Numbers
  1. ID  product_id   price
  2. 1       1        99,-
  3. 2       1        79,-
  4. 3       1        89,-
  5. 4       2        40,-
  6.  
Product list:
Expand|Select|Wrap|Line Numbers
  1. product_id     make_id     type_id      category_id
  2. 1              1           3            1
  3. 2              2           4            7
  4.  
The results must be:
Expand|Select|Wrap|Line Numbers
  1. Make              quantity      price
  2. Amercian DJ       2             89,-
  3. Service monday    1             40,-
  4. Service tuesday   4             160,-
  5. Service wednesday 3             120,-
  6.  
Hope it's now clear :D
Jul 12 '11 #7

Rabbit
Expert Mod 10K+
P: 12,347
Even thought I don't see one in the results. I assume there's a product id.

What you need to do is something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT *, (
  2.    SELECT price
  3.    FROM tblPrice AS p
  4.    WHERE p.product_id = o.product_id
  5.       AND ID = (
  6.          SELECT MAX(ID)
  7.          FROM tblPrice
  8.          WHERE product_id = o.product_id
  9.       )
  10.    ) AS extended_price
  11. FROM tblOrder AS o
Jul 12 '11 #8

100+
P: 137
Sadly it doesn't work..
Uuuhhmmm maybe this will help you more to understand it.

In the invoice i sold 2 devices.
1 laser and 1 led effect.
The laser has 2 prices
So i get this result
Expand|Select|Wrap|Line Numbers
  1. quantity    description    price
  2. 1           Laser          99,-
  3. 1           Laser          99,-
  4. 1           Led effect     79,-
  5.  
But i want:
Expand|Select|Wrap|Line Numbers
  1. quantity    description    price
  2. 1           Laser          99,-
  3. 1           Led effect     79,-
  4.  
Next example:
I sold 3 light bulbs but now i added them sepperatly, instead of using the quantity.
But these lights have 1 price.
I get this (correct) result:
Expand|Select|Wrap|Line Numbers
  1. quantity    description    price
  2. 1           Light bulb     9,-
  3. 1           Light bulb     9,-
  4. 1           Light bulb     9,-
  5.  
But when i add a new price to this product i get this:
Expand|Select|Wrap|Line Numbers
  1. quantity    description    price
  2. 1           Light bulb     9,-
  3. 1           Light bulb     9,-
  4. 1           Light bulb     9,-
  5. 1           Light bulb     9,-
  6. 1           Light bulb     9,-
  7. 1           Light bulb     9,-
  8.  
If i add DISTINCT at the end in the WHERE clause it works well.
But in the last case (with the 6 light bulbs) i get this:
Expand|Select|Wrap|Line Numbers
  1. quantity    description    price
  2. 1           Light bulb     9,-
  3.  
Thanks for the effort!
Paul
Jul 13 '11 #9

Rabbit
Expert Mod 10K+
P: 12,347
You say invoice but you didn't have an invoice table with data in the example you showed me.

Also, if you're saying the query doesn't work, you have to show me your adaptation of the query. Because I don't know your table names and field names, you can't just copy my query and expect it to work. You have to adapt it to your data. And if you adapted it, I need to see how you adapted it.
Jul 13 '11 #10

100+
P: 137
Well, the query did work but with the same (wrong) results.
This is what i changed:
Expand|Select|Wrap|Line Numbers
  1.     SELECT verkoop_order.sub_totaal, verkoop_order.btw_percentage, verkoop_order.btw_bedrag, verkoop_order.verzendkosten,
  2.                     verkoop_order.datum_verkoop, verkoop.artikel_groep, verkoop.Omschrijving,
  3.                     verwijderings_bijdrage.bedrag AS verwijderingsbijdrage, verkoop.Aantal, (
  4.    SELECT prijs
  5.    FROM producten_prijzen AS p
  6.    WHERE p.producten_id = producten.id
  7.       AND ID = (
  8.          SELECT MAX(ID)
  9.          FROM producten_prijzen
  10.          WHERE product_id = producten.id
  11.       )
  12.    ) AS extended_price, verkoop.totaal, verkoop.btw
  13.                     FROM(verkoop_order)
  14.                     INNER JOIN verkoop ON verkoop_order.id = verkoop.verkoop_id 
  15.                     LEFT OUTER JOIN producten ON verkoop.product_id = producten.id
  16.                     LEFT OUTER JOIN producten_prijzen ON producten.id = producten_prijzen.producten_id 
  17.                     LEFT OUTER JOIN verwijderings_bijdrage ON producten_prijzen.verwijderings_bijdrage_id = verwijderings_bijdrage.id
  18.                     WHERE verkoop_order.id = 212 ORDER BY verkoop.id
  19.                     LIMIT 0, 60
My table structure:
Expand|Select|Wrap|Line Numbers
  1. Table make:
  2. id (int)
  3. make (varchar(45))
  4.  
  5. ------
  6. Table type
  7. id (int)
  8. makeID (int)
  9. article_id (int)
  10. type (varchar(55))
  11.  
  12. ------
  13. Table article
  14. ID (int)
  15. article (varchar(45))
  16.  
  17. ------
  18. Table Products
  19. ID (in)
  20. description (varchar(100))
  21. value (varchar(50)
  22. location (varchar(40)
  23. make_id (int)
  24. type_id (int)
  25. article_id (int)
  26. creditor_id (int)
  27. lent (int)
  28. repair (int)
  29. carton (int)
  30. barcode1 (varchar(20))
  31. barcode2 (varchar(20))
  32. article_number_supplier (varchar(45))
  33. garantuee (int)
  34. picture (varchar(85))
  35.  
  36. ------
  37. Table products_reserved
  38. id (int)
  39. products_id (int)
  40. order_id (int)
  41. date (date)
  42. finished (int)
  43.  
  44. -----
  45. Table products_historie
  46. id (int)
  47. products_id (int)
  48. quantity_purchased (int)
  49. quantity_sold (int)
  50. last_purchase (date)
  51. last_sell (date)
  52. last_edit (datetime)
  53.  
  54. -----
  55. Table products_prices
  56. id (int)
  57. products_id (int)
  58. exclusive_price (decimal(8,2))
  59. disposal_id (int)
  60. tax_tarrif (decimal(4,2))
  61. inclusif_price (decimal(8,2))
  62. gross_profit (decimal(8,2))
  63. margin (decimal(4,2))
  64. advice_price(decimal(8,2))
  65. our_price(decimal(8,2))
  66. send_costs(decimal(5,2))
  67. date(datetime)
  68.  
  69. ----- 
  70. Table products_repair
  71. id (int)
  72. products_id (int)
  73. workorder_id (int)
  74. date (date)
  75.  
  76. ------
  77. Table products_statistics
  78. id (int)
  79. products_id (int)
  80. date (datetime)
  81. quantity (int)
  82. purchase_sell (varchar(7))
  83.  
  84. ------
  85. Table products_lent
  86. id (int)
  87. products_id (int)
  88. order_id (int)
  89. date (date)
  90.  
  91. -----
  92. Table disposal_costs
  93. id (int)
  94. price (decimal(5,2))
  95.  
  96.  
If a invoice is made, it goes into this table structure:

Expand|Select|Wrap|Line Numbers
  1. Table sale_order
  2. id (int)
  3. customer_id (int)
  4. sub_total (decimal(10,2))
  5. tax_tarrif (decimal(5,2))
  6. tax_price (decimal(10,2))
  7. sendcosts (decimal(5,2))
  8. payment_method (varchar(20))
  9. sell_date (datetime)
  10. remember(datetime)
  11. last_remember(datetime)
  12. status(varchar(20))
  13. form(varchar(45))
  14. deposit(decimal(10,2))
  15.  
  16.  
  17. -----
  18. Table sale
  19. ID (int)
  20. sale_order_id (int)
  21. products_id (int)
  22. article_group (varchar(45))
  23. description(varchar(70))
  24. quantity (varchar (7))
  25. serialnumber(varchar(50))
  26. tax(decimal(5,2))
  27. price (decimal(8,2))
  28. total (decimal(8,2))
  29. disposal_id (varchar(20))
  30.  
  31.  
The invoice is made in the sale_order table with the total cost and tax and.....
Then each product in the invoice is placed into the table sale.

That's the most important part of the datatbase structure.
Everything is linked trough the products table, but the make, type and articles are also linked underneath each other


Regards,
Paul
Jul 13 '11 #11

100+
P: 137
Well, i finally found the answer.
A lot of testing, but it's working fine now.
Thanks for your support!
Jul 25 '11 #12

Rabbit
Expert Mod 10K+
P: 12,347
Oops, sorry, I totally forgot about this one. Glad you found your answer though.
Jul 25 '11 #13

Post your reply

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