I'm trying to do a lookup from a table where an ID number equals a
name. But in selecting from three tables, I can't seem to figure out
how to display the data in the fashion I need.
In the first table (user_contact), I have contact info for each user
and the user ID. In the second table (user_info), each user ID will
have a primary genre, and an alternate genre (as an ID number). The
third table holds the lookup information for genre_id, and
genre_label.
Table user_contact:
+----------------+--------------+
| Field | Type |
+----------------+--------------+
| u_id | int(10) |
| u_name | varchar(50) |
+----------------+--------------+
Table user_info:
+----------------+--------------+
| Field | Type |
+----------------+--------------+
| u_id | int(10) |
| u_prim_genre | varchar(50) |
| u_alt_genre | varchar(50) |
+----------------+--------------+
Table genres:
+----------------+--------------+
| Field | Type |
+----------------+--------------+
| genre_id | int(10) |
| genre_label | varchar(50) |
+----------------+--------------+
What I'd like to see is a listing of each user with the label of their
primary and alternate genre listed. Something like this;
Table user_info:
+----------------+---------------------------------+
| User Name | Primary Genre | Alternate Genre |
+----------------+---------------------------------+
| User #1 | Rock | Hip-Hop |
| User #2 | Pop | Opera |
+----------------+---------------------------------+
I've tried the following, but with no success.
SELECT
u_name,u_prim_genre,u_alt_genre FROM user_contact,user_info
LEFT JOIN genres ON
(user_info.u_prim_genre = genres.genre_id and user_info.u_alt_genre =
genres.genre_id);
I'm thinking this involves the use of a join, but I'm not sure. Can
anyone offer any ideas?