CODEWITHSUNDEEP
mysqljoin
Andres SK
Andres SK

Reputation: 10982

MySQL joining 2 columns with the same table

I have 3 tables:

I need to get the cat slug, and the unit slugs for the stats table, joined with the unit_cat table and the unit_list. The problem is that I'm joining both unit_stats.id_unit_1 and unit_stats.id_unit_2

Here's the sqlfiddle of the structure and my current query (which is not getting the desired result):

SELECT
unit_stats.hits AS hits,
unit_cat.slug AS cat,
unit_list.slug AS slug_1,
unit_list.slug AS slug_2
FROM unit_stats
JOIN unit_cat ON unit_cat.id_cat = unit_stats.id_cat
JOIN unit_list ON unit_list.id_unit = unit_stats.id_unit_1 AND unit_list.id_unit = unit_stats.id_unit_2
ORDER BY hits DESC

The desired result should be in this format:

temperature, celsius, fahrenheit

Thanks for your help.

Upvotes: 0

Views: 25

Answers (1)

Nick
Nick

Reputation: 147216

Your query doesn't work because you have conflicting conditions in your JOIN condition to unit_list. You actually need to JOIN to unit_list twice, once for each unit, to get the different slugs. Note that you also need to include the id_cat in the JOIN condition so that you don't get units from other categories.

SELECT
    us.hits AS hits,
    uc.slug AS cat,
    ul1.slug AS slug_1,
    ul2.slug AS slug_2
FROM unit_stats us
JOIN unit_cat uc ON uc.id_cat = us.id_cat
JOIN unit_list ul1 ON ul1.id_unit = us.id_unit_1 AND ul1.id_cat = uc.id_cat
JOIN unit_list ul2 ON ul2.id_unit = us.id_unit_2 AND ul2.id_cat = uc.id_cat
ORDER BY hits DESC

Output (for your demo data)

hits    cat             slug_1          slug_2
16      weight          kilogram        gram
6       area            square-meter    square-kilometer
4       temperature     celsius         fahrenheit
2       distance        meter           exameter

Demo on SQLFiddle

Upvotes: 1

Related Questions