8

The DISTINCT keyword does not work in the mysql query

 3 years ago
source link: https://www.codesd.com/item/the-distinct-keyword-does-not-work-in-the-mysql-query.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

The DISTINCT keyword does not work in the mysql query

advertisements

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.

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

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK