CODEWITHSUNDEEP
mysql
celestial mountain
celestial mountain

Reputation: 21

How to get attributes of a foreign key

In a mySQL database, I have a table of users:

enter image description here

I also have a table of comments:

enter image description here

I need to get all of the comments where the posters and recipients are of different genders and display their ID's and Genders. I cannot figure out how to do this. My first step of trouble is I can't get the user's gender simply from their poster/recipient columns.

I tried comments.Poster.Gender but that of course doesn't work. Poster and Recipient are foreign keys to users.ID

Upvotes: 0

Views: 319

Answers (2)

Sélim Achour
Sélim Achour

Reputation: 728

You need to join to users twice once for recipients and once for posters

SELECT c.*, p.Name AS poster_name, p.Gender AS poster_gender, r.Name AS recipient_name, r.Gender AS recipient_gender
  FROM comments c
  INNER JOIN users p ON (c.Poster    = p.ID)
  INNER JOIN users r ON (c.Recipient = r.ID)
  WHERE p.Gender <> r.Gender

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

I'd join the comments table on the users table twice, once for the poster and once for the recipient, and then check their genders:

SELECT c.*, p.gender, r.gender
FROM   comments c
JOIN   users p ON c.poster = p.id
JOIN   users r ON c.recipient = r.id
WHERE  p.gender <> r.gender

Upvotes: 0

Related Questions