Two foreign keys of one table. How to write a SELECT statement?

Question

User table

user_id username thumb_id fullimage_id 1 jprescott 14 15 2 lpausch 18 19 

Image table

 image_id path 14 jprescott/small.jpg 15 jprescott/big.jpg 16 msamuels/small.jpg 17 msamuels/big.jpg 18 lpausch/small.jpg 19 lpausch/big.jpg 

Now, how to write a SELECT statement to retrieve a user with your finger and full paths? The problem is having two foreign keys on the same table.

Answer 1

You make two connections:

 SELECT u.username, i1.path AS thumb, i2.path AS full FROM users AS u JOIN images AS i1 ON u.thumb_id = i1.image_id JOIN images AS i2 ON u.fullimage_id = i2.image_id 

Answer 2

What about:

 select u.username, i1.path, i2.path from users u, images i1, images i2 where u.user_id = ? and u.thumb_id = i1.image_id and u.fullimage_id = i2.image_id