CODEWITHSUNDEEP
mysqljoin
yycroman
yycroman

Reputation: 7611

mysql: two tables but three select criteria

I have two tables: users and user_depts. Let's say (for this question) that users only has an 'id' column, and user_depts has 2: 'uid' and 'did'.

I am looking for an SQL line that will return all the user IDs for all the departments with which a given user ID (let's say 7, though this'll come dynamically from PHP) is associated.

What I've tried is:

SELECT id FROM users, user_depts
WHERE users.id = user_depts.uid
AND user_depts.uid = 7

But of course this does nothing but return 7. I think I might have to join the table to itself, but I only know the shortcut syntax for joining, and it doesn't seem to be sufficient. Any pointers would be greatly appreciated!

Upvotes: 2

Views: 139

Answers (3)

Deepak
Deepak

Reputation: 370

select uid from user_depts where did in (select did from user_depts where uid=7)

  1. First select all the did with which a user is associated using subquery
  2. Then select all the user_id for the selected departments.

It's the best way you can do it. and if you want to remove repeated result then you can use distinct

Upvotes: 0

Andreas Helgegren
Andreas Helgegren

Reputation: 1668

I am looking for an SQL line that will return all the user IDs for all the departments with which a given user ID (let's say 7, though this'll come dynamically from PHP) is associated.

If this means you want to find all the users with the userid: 7 that has a user_departent connected to it this is your query:

select users.id from users
inner join user_depts.uid = users.id
where users.id = 7

Upvotes: 0

knittl
knittl

Reputation: 265201

Use EXISTS:

SELECT uid FROM user_depts
WHERE EXISTS (
  SELECT * FROM user_depts a
  WHERE uid = 7 AND a.did = user_depts.did
)

Upvotes: 1

Related Questions