• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3537
  • Last Modified:

MySQL Select Distinct on Left Join Problem

I have 2 tables, one for groups, the other for items.  Multiple items are assigned to each group.

I am attempting to join both tables and select only unique/distinct groups based on the group ID.

SELECT DISTINCT DISTINCT GRP.id, GRP.description, ITM.gid, ITM.mfgnum
FROM products_groups AS GRP
LEFT JOIN products_items AS ITM ON GRP.id = ITM.gid
WHERE GRP.description NOT LIKE '%-%'
AND (
GRP.description LIKE '%engagement%'
OR GRP.description LIKE '%wedding%'
)
AND GRP.orderable = 'Y'
ORDER BY GRP.id DESC


Unfortunately, this still returns duplicates:

id | description | gid | mfgnum

12480 | 14k 8mm Tapered Wedding Band | 12480 | HRT080-10
12480 | 14k 8mm Tapered Wedding Band | 12480 | HRT080-6
12479 | 14k 6mm Tapered Wedding Band | 12479 | HRT060-10
12479 | 14k 6mm Tapered Wedding Band | 12479 | HRT060-6
12478 | 14k 5mm Tapered Wedding Band | 12478 | HRT050-10
12478 | 14k 5mm Tapered Wedding Band | 12478 | HRT050-6
12477 | 14k 4mm Tapered Wedding Band | 12477 | HRT040-10

This SHOULD only be returning:

12480 | 14k 8mm Tapered Wedding Band | 12480 | HRT080-10
12479 | 14k 6mm Tapered Wedding Band | 12479 | HRT060-10
12478 | 14k 5mm Tapered Wedding Band | 12478 | HRT050-10
12477 | 14k 4mm Tapered Wedding Band | 12477 | HRT040-10


Where am I going wrong?
0
purpleonyx
Asked:
purpleonyx
  • 4
  • 2
2 Solutions
 
Raynard7Commented:
The only difference seems to be the -10 and the -6

what logic do you want to use to find each?
0
 
purpleonyxAuthor Commented:
I only need to base this on the Group ID and Item GID (which are the same number).  The mfgnum is the same as you mentioned, with the exception of the -*.  Anything after the "-" I dont even care about.  The main item number preceeds the dash.
0
 
Raynard7Commented:
the above are not duplicates as the data is not identical

If you only wanted one mfgnum for each grp id you could do

Select
    x.*, (select i.mfgnum from products_items i where i.gid = x.gid limit 1)
from
(
SELECT DISTINCT DISTINCT GRP.id, GRP.description, ITM.gid
FROM products_groups AS GRP
LEFT JOIN products_items AS ITM ON GRP.id = ITM.gid
WHERE GRP.description NOT LIKE '%-%'
AND (
GRP.description LIKE '%engagement%'
OR GRP.description LIKE '%wedding%'
)
AND GRP.orderable = 'Y'
ORDER BY GRP.id DESC
) as x
order by
 x.id desc
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

SELECT GRP.id, GRP.description, ITM.gid, ITM.mfgnum
FROM products_groups AS GRP
LEFT JOIN products_items AS ITM ON GRP.id = ITM.gid
WHERE GRP.description NOT LIKE '%-%'
AND (
GRP.description LIKE '%engagement%'
OR GRP.description LIKE '%wedding%'
)
AND GRP.orderable = 'Y'
GROUP BY GRP.id
ORDER BY GRP.id DESC
0
 
Raynard7Commented:
Below would remove everything before the -

SELECT DISTINCT DISTINCT GRP.id, GRP.description, ITM.gid,  SUBSTRING_INDEX(ITM.mfgnum, "-", 1) as mfgnum
FROM products_groups AS GRP
LEFT JOIN products_items AS ITM ON GRP.id = ITM.gid
WHERE GRP.description NOT LIKE '%-%'
AND (
GRP.description LIKE '%engagement%'
OR GRP.description LIKE '%wedding%'
)
AND GRP.orderable = 'Y'
ORDER BY GRP.id DESC
0
 
purpleonyxAuthor Commented:
Both examples from AngelIII and Raynard7 work perfectly!

AngelIII, thanks for reminding me of the GROUP BY command, did not even consider that!

Raynard7, your usage of the substring call is something I never even knew existed, and it seems to work the best in this situation.  Much appreciated!

Split points between the both of you.

Raynard7 -- can you explain though why we are using DISTINCT twice?

Thanks!
0
 
Raynard7Commented:
typing error
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now