0

Note: The actual schema isn't male/female, but some other criteria. I'm just using male/female for this example to make it easier to understand.

I have a table "users", which contains a column user_name and user_gender. The gender can be "M" or "F".

The problem is that I have another table, "messages", that has a column for "sender" and "receiver". These columns contains user_name in each row.

How can I use INNER JOIN so that I can get messages where only males send to females?

I know easily how to specify it once, binding users.user_name to "sender" or "receiver" but not both.

To expand on my question, how do see which top 10 pairs where a male sent the most messages to a female? Note, this means unique A/B pairs, so I want to return cases where a guy sends a single female a ton of messages, not when a guy spams a lot of messages to different females.

james chang
  • 199
  • 1
  • 1
  • 8
  • Look into [self-join tables](http://www.tutorialspoint.com/sql/sql-self-joins.htm) where Users will be joined twice to Messages table: one on receiver column and the other one on sender column. Then condition receiver and sender columns with WHERE clause on M or F condition. – Parfait Feb 27 '15 at 16:09
  • See http://stackoverflow.com/questions/4267929/whats-the-best-way-to-join-on-the-same-table-twice or http://stackoverflow.com/questions/10710271/join-table-twice-on-two-different-columns-of-the-same-table – LittleBobbyTables - Au Revoir Feb 27 '15 at 16:14

1 Answers1

1

Think of your messages table as a "cross table" connecting two rows in the users table. When you join to a table like that, give users two different aliases, and refer to them in your join conditions, like this:

select *
from messages msg
join users m on msg.sender = m.user_id AND m.user_gender='M'
join users f on msg.receiver = f.user_id AND f.user_gender='F'

With this skeleton in hand, you should be able to figure out the rest of your query:

  • Use GROUP BY to group by m.user_id, f.user_id, and count(*) to count
  • Order by COUNT(*) to get the highest sender+receiver pairs at the top
  • Use LIMIT to grab the top ten pairs.
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523