CODEWITHSUNDEEP
sqljoin
Nhiz
Nhiz

Reputation: 120

SQL query not getting it right

I have a table (BUDDY) with these attributes

if the status='a' means that requestor and requested are buddies however how can i know the buddies of a certain user if the user can either be a requestor and a requested?

SELECT requestor, requested
FROM buddy,user 
WHERE user_id = requestor or user_id = requested

this is giving me multiple values?

Upvotes: 1

Views: 54

Answers (4)

D Stanley
D Stanley

Reputation: 152501

Just join to user for both conditions:

SELECT requestor, requested, u.user_name AS Buddy
FROM buddy b
INNER JOIN user u 
  ON (@userID = requestor AND b.requested = u.user_id)
  OR (@userID = requested AND b.requestor = u.user_id)
WHERE status = 'a'

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

If you want to get some fields from the user, you need to join buddy to the user table. Otherwise, you are going to get an uncorrelated cross-join.

However, it does not look like you need any of the user's columns, so a simple change below should do the trick:

SELECT requestor, requested
FROM buddy
WHERE user_id = requestor or user_id = requested

If you want to add fields from the user, add a join:

SELECT b.requestor, b.requested, u.first_name, u.last_name
FROM buddy b
JOIN user u ON b.requestor=u.id OR b.requested=u.id
WHERE user_id = b.requestor or user_id = b.requested

Upvotes: 2

Linger
Linger

Reputation: 15048

How about the following:

SELECT b.requested  AS MyBuddy
FROM buddy AS b
INNER JOIN user AS u1 u1.user_id = b.requestor
WHERE  buddy.status = 'a'
AND u1.user_id = 101

UNION

SELECT b.requestor AS MyBuddy
FROM buddy AS b
INNER JOIN user AS u2 u2.user_id = b.requested
WHERE  buddy.status = 'a'
AND u2.user_id = 101

The first query searches the requestor for the user in question and if the status is a then it pulls the requested as the MyBuddy. The second query searches the requested for the user in question and if the status is a then it pulls the requested as the MyBuddy. Then you UNION the two queries together to get the complete list.

I joined to the user table because likely you will want to use something like u1.name instead of b.requested and b.requestor in the SELECT.

Upvotes: 0

Lluis Martinez
Lluis Martinez

Reputation: 1973

Given a user U

SELECT requestor
FROM buddy
WHERE requested = U AND status='a'
UNION
SELECT requested
FROM buddy
WHERE requestor = U AND status='a'

Upvotes: 0

Related Questions