Using different columns values twice in a single SQL query?

I have a mySQL table called "User" containing multiple mixed values as this:

[user_id] [user_email] [birthday]
---------------------------------
1         x@xxx.com    01/01/1981
2         y@yyy.com    02/02/1982
3         z@zzz.com    03/03/1983

I have another table called "Name" which contains name of the user, but also of some movies like this:

[node_id]  [name]       [user_id]
----------------------------------
9          John Doe     1
10         Star Wars    90
11         Mike Smith   2
12         Mary Lord    3
13         Rocky III    91

Finally, I have a third table named "Vote" with which is a relationship between a user and some movies he likes.

[vote_id] [node_id]  [user_id] 
------------------------------
1         10         1
2         10         2
3         13         1
12        10         3
13        13         2

What I'm struggling to do is pull a query with twice the "name" value for two separate things: the name of the user, and the name of the movie he likes. Like this:

[user_id]  [user_name]  [Birthday]  [movie_name]
-------------------------------------------------   
1          John Doe     01/01/1981  Star Wars
2          Mike Smith   02/02/1982  Star Wars
1          John Doe     01/01/1981  Rocky III
3          Mary Lord    03/03/1983  Rocky III
2          Mike Smith   02/02/1982  Rocky III


SELECT user.id,
       node.name,
       user.birthday,
       IF(node.type = "movie", node.name, "")
  FROM user, 
       node
  JOIN vote ON开发者_JAVA技巧 vote.user_id = user.user_id
 WHERE user.id = node.id

I think I'm all mixed up... anyone can help please?

Assuming your schema is exactly what you posted above this should work verbatim.

Query

  SELECT user.user_id,
       node.name user_name,
       user.birthday,
       (select node.name from node where node_id = vote.node_id) as movie_name
  FROM user
  JOIN node ON user.user_id = node.user_id
  JOIN vote ON vote.user_id = user.user_id

Result

You have got the database structure wrong. Store the user name in your first table "User"

I would strongly suggest that you store the user_name in the users table. With that change you can then have a much more simple query and a properly normalized schema.

New proposed schema.

users table

(Added user_name column)

[user_id][name][user_email][birthday]
1        name1 x@xxx.com   01/01/1981
2        name2 y@yyy.com   02/02/1982
3        name3 z@zzz.com   03/03/1983

nodes table (call this movies)

(removed user entries and the user_id column as you'll be using votes to link these to users)

[node_id]   [name]
10        Star Wars     
11        Mike Smith
12        Mary Lord
13        Rocky III

votes table (call this something like movies_users)

(removed the vote_id column as it's just a join table)

[node_id]       [user_id]
10              1
10              2
13              1
10              3
13              2

Then your query should look something like this:

select users.user_id, users.name, users.birthday, nodes.name as movie_name
from users
join votes on users.id = votes.user_id
join nodes on votes.node_id = nodes.node_id

select user_id,user_name,birthday,name from user,name,vote

where (and here you do all the joins like user_id from one table equals user_id from another table) But here we have a problem which makes me impossible to understand how to write the correct code you have 2 fields in two different tables, user_name and name, you want to join the tables by this name? I don't understand.) I think you are mixing the movie names with the user names, reformulate the question please

I agree with the other answers that you would be better off if you moved the user name into the user table. However, if you are stuck with your current table structure, try this:

SELECT user.id,
       uname.name user_name,
       user.birthday,
       movie.name movie_name
  FROM user 
  JOIN node uname ON uname.user_id = user.user_id
  JOIN vote ON vote.user_id = user.user_id
  JOIN node movie ON vote.node_id = movie.id

(Assuming votes can only be cast for Movies, it should be unnecessary to blank out non-movies as these should never exist.)