CODEWITHSUNDEEP
mysqljoin
Pete
Pete

Reputation: 53

mysql querying 2 columns that refer to 1 column in different table

Sorry if question has been asked but I was not able to relate any answers I found to my situation.

I have created a temp table for this problem:

describe temp;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | NO   |     | 0       |       |
| artist    | varchar(255) | NO   |     | NULL    |       |
| title     | varchar(255) | NO   |     | NULL    |       |
| id_genre  | int(11)      | NO   |     | NULL    |       |
| id_genre2 | int(11)      | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

The other relevent table is a list of genres.

I want to create a query that results in a combination of these 2 queries.

select temp.title,genre.name
from temp
join genre on genre.id = temp.id_genre;

+------------------------+--------------+
| title                  | genre1       |
+------------------------+--------------+
| Tight Capris - 1958-B  | Rockabilly   |
| Endless Sleep - 1958-5 | MyFavourites |
| Daisy Mae - 1966       | Rockabilly   |
| Fire of love - 1966-58 | Rockabilly   |
| Stormy - 1963          | Pop          |
+------------------------+--------------+
5 rows in set (0.00 sec)

select temp.title,genre.name as genre2
from temp
join genre on genre.id = temp.id_genre2;

+------------------------+------------+
| title                  | genre2     |
+------------------------+------------+
| Tight Capris - 1958-B  | Rockabilly |
| Endless Sleep - 1958-5 | Rockabilly |
| Daisy Mae - 1966       | Rockabilly |
| Fire of love - 1966-58 | Rockabilly |
| Stormy - 1963          | Pop        |
+------------------------+------------+
5 rows in set (0.00 sec)

Notice the genre difference in the 1 title: Endless Sleep

I would like to create a result that looks like this except to replace the genre.id with the name of the genre:

+------------------------+----------+-----------+
| title                  | id_genre | id_genre2 |
+------------------------+----------+-----------+
| Tight Capris - 1958-B  |      163 |       163 |
| Endless Sleep - 1958-5 |      161 |       163 |
| Daisy Mae - 1966       |      163 |       163 |
| Fire of love - 1966-58 |      163 |       163 |
| Stormy - 1963          |       99 |        99 |
+------------------------+----------+-----------+
5 rows in set (0.00 sec)

Thanks

Upvotes: 0

Views: 168

Answers (3)

Marjeta
Marjeta

Reputation: 1141

I agree with Stephan that you need to JOIN genre table twice. But it might be a good idea to use LEFT JOIN instead of INNER JOIN:

SELECT  temp.title,
        a.name GenreA,
        b.Name GenreB
FROM    temp
        LEFT OUTER JOIN genre a 
            ON a.id = temp.id_genre
        LEFT OUTER JOIN genre b 
            ON b.id = temp.id_genre2

This will help if you have records in temp that have NULL in one of the id_genre columns, or have an id that doesn't match a row in genre table.

Added:

In reference to "How could I filter that further with particular genres. For example where genre1 = 'rockabilly' (which doesn't work).":

At the and add

HAVING genre1 = 'rockabilly'

Upvotes: 0

John Woo
John Woo

Reputation: 263723

You need to join table Genre twice because there are two columns on table temp that is dependent on it.

SELECT  temp.title,
        a.name GenreA,
        b.Name GenreB
FROM    temp
        INNER JOIN genre a 
            ON a.id = temp.id_genre
        INNER JOIN genre b 
            ON b.id = temp.id_genre2

OUTPUT

╔════════════════════════╦══════════════╦════════════╗
║         TITLE          ║    GENREA    ║   GENREB   ║
╠════════════════════════╬══════════════╬════════════╣
║ Tight Capris - 1958-B  ║ Rockabilly   ║ Rockabilly ║
║ Endless Sleep - 1958-5 ║ MyFavourites ║ Rockabilly ║
║ Daisy Mae - 1966       ║ Rockabilly   ║ Rockabilly ║
║ Fire of love - 1966-58 ║ Rockabilly   ║ Rockabilly ║
║ Stormy - 1963          ║ Pop          ║ Pop        ║
╚════════════════════════╩══════════════╩════════════╝

Upvotes: 1

Stephan
Stephan

Reputation: 8090

So this is what you need?

SELECT
    title,
    genre1.name as genre1,
    genre2.name as genre2
FROM 
    temp
INNER JOIN genre as genre1
    ON genre1.id = id_genre
INNER JOIN genre as genre2
    ON genre2.id = id_genre2

Upvotes: 1

Related Questions