CODEWITHSUNDEEP
mysqlselectjoindistinct
George
George

Reputation: 3321

Select distinct records on a join

I have two mysql tables - a sales table: 

+----------------+------------------------------+------+-----+---------+-------+
| Field          | Type                         | Null | Key | Default | Extra |
+----------------+------------------------------+------+-----+---------+-------+
| StoreId        | bigint(20) unsigned          | NO   | PRI | NULL    |       |
| ItemId         | bigint(20) unsigned          | NO   |     | NULL    |       |
| SaleWeek       | int(10) unsigned             | NO   | PRI | NULL    |       |
+----------------+------------------------------+------+-----+---------+-------+

and an items table:

+--------------------+------------------------------+------+-----+---------+-------+
| Field              | Type                         | Null | Key | Default | Extra |
+--------------------+------------------------------+------+-----+---------+-------+
| ItemId             | bigint(20) unsigned          | NO   | PRI | NULL    |       |
| ItemName           | varchar(100)                 | NO   |     | NULL    |       |
+--------------------+------------------------------+------+-----+---------+-------+

The sales table contains multiple records for each ItemID - one for each SaleWeek. I want to select all items sold by joining the two tables like so:

SELECT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId 
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;

However, this is returning multiple ItemId values based on the multiple entries for each SaleWeek. Can I do a distinct select to only return one ItemID - I don't want to have to query for the latest SaleWeek because some items may not have an entry for the latest SaleWeek so I need to get the last sale. Do I need to specify DISTINCT or use a LEFT OUTER JOIN or something?

Upvotes: 21

Views: 125781

Answers (4)

DRapp
DRapp

Reputation: 48139

You had comment about the sales week too. And wanting the most recent week, you may want to try using a GROUP BY

SELECT 
      items.ItemName, 
      items.ItemId,
      max( Sales.SaleWeek ) MostRecentSaleWeek
   FROM 
      items JOIN sales ON items.ItemId = sales.ItemId
   WHERE 
      sales.StoreID = ? 
   GROUP BY
      items.ItemID,
      items.ItemName
   ORDER BY
      MostRecentSaleWeek,   -- ordinal column number 3 via the MAX() call
      items.ItemName

You may have to change the ORDER BY to the ordinal 3rd column reference if you so want based on that column.. This query will give you each distinct item AND the most recent week it was sold.

Upvotes: 13

sivaprakash
sivaprakash

Reputation: 65

We can use this:

INSERT INTO `test_table` (`id`, `name`) SELECT DISTINCT 
    a.`employee_id`,b.`first_name` FROM `employee_leave_details`as a INNER JOIN 
    `employee_register` as b ON a.`employee_id` = b.`employee_id`

Upvotes: 1

Suresh Kumar Amrani
Suresh Kumar Amrani

Reputation: 935

    SELECT  u.user_name,u.user_id, u.user_country,u.user_phone_no,ind.Industry_name,inv.id,u.user_email
    FROM invitations inv
    LEFT JOIN users u
    ON inv.sender_id = u.user_id
    LEFT JOIN employee_info ei
    ON inv.sender_id=ei.employee_fb_id   
    LEFT JOIN industries ind
    ON ei.industry_id=ind.id
    WHERE inv.receiver_id='XXX'
    AND inv.invitation_status='0'
    AND inv.invitati

on_status_desc='PENDING'
GROUP BY (user_id)

Upvotes: 2

Kaleb Brasee
Kaleb Brasee

Reputation: 51925

A DISTINCT should do what you're looking for:

SELECT DISTINCT items.ItemName, items.ItemId FROM items
JOIN sales ON items.ItemId = sales.ItemId 
WHERE sales.StoreID = ? ORDER BY sales.SaleWeek DESC;

That would return only distinct items.ItemName, items.ItemId tuples.

Upvotes: 34

Related Questions