Two select statements in a single statement

Question

I basically have two tables called images and users. The images table has the following fields:

i_id | s_id | u_id | name | filename |

u_id is a foreign key to the u_id field in the users table, which has these fields:

u_id | username | password | email |

I'm running a query like this:

SELECT s_id, u_id, name, filename, filesize FROM images WHERE name = 'fYhWId'

This returns the u_id of the user, among other things. But I want to return the users username, not their u_id. So basically, within that SELECT statement, I also want to run:

SELECT username FROM users WHERE u_id = 1

I could use two queries for this, but I'm trying to cut down on the queries my application runs, and I know there's a way to combine this into one query, but I just don't know it :<

Does anyone know the answer? Thanks!

Answer 1

You need to join the tables

SELECT i.s_id, i.u_id,u.username, i.name, i.filename, i.filesize 
FROM images i 
INNER JOIN users u 
on u.u_id = i.u_id 
WHERE i.name = 'fYhWId'

Answer 2

SELECT username FROM users WHERE u_id = (SELECT TOP 1 u_id FROM images WHERE name = 'fYhWId')

or

SELECT username FROM users WHERE u_id IN (SELECT u_id FROM images WHERE name = 'fYhWId')

or

SELECT username,  s_id, images.u_id, name, filename, filesize 
FROM images 
INNER JOIN users on images.u_id = users.u_id
WHERE name = 'fYhWId'