CODEWITHSUNDEEP
mysqlsqljoinview
Richard-MX
Richard-MX

Reputation: 484

What query to obtain the following on MySQL?

Users

+--------+--------------+----------+--------------+
| userID | userUsername | userName | userLastName |
+--------+--------------+----------+--------------+
|      6 | richard      | Ricardo  | Vega         |
|     10 | jason        | Jason    | Bourne       |
+--------+--------------+----------+--------------+

Restocks

+-----------+-------------+--------+--------+-----------------+
| restockID | restockDate | itemID | userID | restockQuantity |
+-----------+-------------+--------+--------+-----------------+
|         1 | 2012-02-29  |      1 |      6 |              48 |
|         2 | 2012-02-29  |      1 |     10 |             100 |
|         3 | 2012-02-29  |      2 |     10 |              50 |
|         4 | 2012-02-29  |      2 |      6 |             100 |
|         5 | 2012-02-29  |      2 |      6 |             200 |
|         6 | 2012-02-29  |      2 |     10 |            2000 |
|         7 | 2012-02-29  |      1 |     10 |            2000 |
+-----------+-------------+--------+--------+-----------------+

Items

+--------+--------------------+
| itemID | itemName           |
+--------+--------------------+
|      1 | Coca Cola (lata)   |
|      2 | Cerveza Sol (lata) |
+--------+--------------------+

Ok guys, i have supplied some sample data as requested. I need to get this table:

+--------+--------------------+---------------+-------------+----------+--------------+--------------+
| itemID | itemName           | itemExistence | restockDate | userName | userLastName | userUsername |
+--------+--------------------+---------------+-------------+----------+--------------+--------------+
|      2 | Cerveza Sol (lata) |          2350 | 2012-02-29  | Jason    | Bourne       | jason        |
|      1 | Coca Cola (lata)   |          2148 | 2012-02-29  | Ricardo  | Vega         | richard      |
+--------+--------------------+---------------+-------------+----------+--------------+--------------+

But, i need restockDate to be THE LATEST ONE for each itemName. In the example, it shows the first restock and not the latest one. I just need to show what's the existence for the item and when was restocked for last time, not first time.

If my tables are not good or so, please suggest a new schema.

I know maybe this is a lot so i will tip 5 USD (Paypal) to the one how can help me with this. Promise.

Upvotes: 2

Views: 160

Answers (5)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

As discussed in comments, many restocks can be performed on the same day so it is not possible to compare dates in this case. Two options are presented here: Use the incremental PK from restocks table or restructure the table. For the first case, this is the solution:

select i.itemID, i.itemName, i.itemExistence, r.restockDate, u.userName,
  u.userLastName, u.userUsername
from items i
left join (
    select r1.restockDate, r1.itemID, r1.userID from restocks r1
    left join restocks r2
    on r1.itemId = r2.itemId and r1.restockId < r2.restockId
    where r2.restockDate is null
) as r on i.itemID = r.itemID
inner join users u on r.userID = u.userID

For the second case, the restructre would imply changing the date field to a unique datetime that would uniquely identify a record. That is the best solution, however, it does require to also update any previous data present in the table. That means, to update all the records that have the same date for a single product restock and set different date times to them.

The lazy one (like me), would go for the first option :) Let me know if you have any doubt about this.

Upvotes: 2

Marcus Adams
Marcus Adams

Reputation: 53870

You don't mention what itemExistence is, so I'm hoping it's a column in the Items table.

Here's an easy way to do it with a self-join:

SELECT i.itemID, i.itemName, i.itemExistence, r1.restockDate,
  u.userName, u.userLastName, u.userUsername
FROM Items i
JOIN Restocks r1
  ON r1.itemID = i.itemID
JOIN Users u
  ON u.userID = r1.userID
LEFT JOIN Restocks r2
  ON r2.itemID = i.itemID
  AND r2.restockDate > r1.restockDate
WHERE r2.itemID IS NULL

The LEFT JOIN with the WHERE clause ensures that we only pull the row with the latest restockDate.

The advantage of this approach is that it avoids subqueries, which often negate the use of indexes.

You can get duplicate records for a particular item if it was restocked more than once on the same date.

Upvotes: 0

Onkar Janwa
Onkar Janwa

Reputation: 3950

select    
items.itemID, items.itemName, items.itemExistence, 

(select A.restockDate from restocks A where A.itemId = items.itemID limit 0, 1),
(select B.userID from restocks B where B.itemId = items.itemID limit 0, 1),

users.userName, users.userLastName, users.userUsername

from items 

left join users on B.userID = users.userID

Please try this.

Upvotes: 0

Junaid
Junaid

Reputation: 2094

first get the distinct from items table and then use it to join others

SELECT items.*, restocks.restockDate, users.userName, users.userLastName, users.userUsername
FROM (SELECT DISTINCT items.itemID, items.itemName, items.itemExistence FROM items) AS items 
LEFT JOIN restocks on items.itemID = restocks.itemID
LEFT JOIN users on restocks.userID = users.userID
GROUP BY items.itemName

Not Tested

Upvotes: 1

sam_13
sam_13

Reputation: 532

UPDATED

select items.itemID, items.itemName, items.itemExistence, restocks.restockDate, users.userName, users.userLastName, users.userUsername
from items 
inner join restocks on items.itemID = restocks.itemID
inner join users on restocks.userID = users.userID
GROUP BY items.itemName

Upvotes: 0

Related Questions