CODEWITHSUNDEEP
mysqljoin
Jithin Varghese
Jithin Varghese

Reputation: 2228

DISTINCT keyword not working in mysql query

I have ran into a problem that my mysql query is not working as expected. I am joining 3 tables using union and from the result i want to ignore the repeating words. For that I have used DISTINCT keyword. But it is not working as expected.

My query is,

SELECT DISTINCT(catnam), sub2id 
FROM tbl_first_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men' 
UNION 
SELECT DISTINCT(catnam), sub2id 
FROM tbl_third_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men' 
UNION 
SELECT DISTINCT(catnam), sub2id 
FROM tbl_fourth_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men'

And my result is,

catnam    sub2id
------    ------
Levi's      4
United      1
Reebok     130
Jack       18
Proline    77
Levi's     161
Arrow      284

In the above result Levi's is repeating. How to ignore this. I need only one Levi's. Is there any way to avoid this. I am stuck in here.

Upvotes: 3

Views: 850

Answers (2)

user5757782
user5757782

Reputation:

I'd try:

SELECT n, GROUP_CONCAT(sub2id )
FROM
(
SELECT catnam n, sub2id 
...
UNION
SELECT catnam, sub2id
...
)
GROUP BY n

Upvotes: 2

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

MySQL DISTINCT with multiple columns

You can use the DISTINCT clause with more than one column. In this case, MySQL uses the combination of all columns to determine the uniqueness of the row in the result set.

http://www.mysqltutorial.org/mysql-distinct.aspx

if you want solve your problem, you can use group_concat() function then you group by catnam for all union queries :

SELECT name ,GROUP_CONCAT(id)  
FROM your_table
GROUP BY name ;

so your query can be:

SELECT distinct(catnam) as catnam, GROUP_CONCAT(sub2id )  
FROM tbl_first_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men'
UNION 
SELECT distinct(catnam) as catnam, GROUP_CONCAT(sub2id )
FROM tbl_third_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men' 
UNION 
SELECT distinct(catnam) as catnam, GROUP_CONCAT(sub2id ) 
FROM tbl_fourth_page_products f INNER JOIN subcategory_level2 s 
ON f.brand_id = s.sub2id 
WHERE f.title_cat = 'men'
group by catnam

Upvotes: 3

Related Questions