0

i am working with sql from few days as beginner and stuck at a problem!

Problem:

  • Given A table user_email_table which columns are (id, user_id, user_id_email)
  • How to get all user co-related to each and every user extending himself

user_email_table

id | user_id | email_id
1  |    1    |    xyz
2  |    2    |    xyz2
3  |    3    |    xyz3
4  |    4    |    xyz4

Desired Output

id  | user_id_1 | user_id_2 | user_id_1_email | user_id_2_email |
-----------------------------
1   |      1    |     2     | xyz|xyz2|
1   |      1    |     3     |xyz|xyz3|
1   |      1    |     4     |xyz|xyz4|
1   |      2    |     1     |xyz2|xyz1|
1   |      2    |     3     |xyz2|xyz3|
1   |      2    |     4     |xyz2|xyz4|
1   |      3    |     1     |xyz3|xyz1|
1   |      3    |     2     |xyz3|xyz2|
1   |      3    |     4     |xyz3|xyz4|
1   |      4    |     1     |xy4|xyz1|
1   |      4    |     2     |xyz4|xyz2|
1   |      4    |     3     |xyz4|xyz3|

Please Ignore validate data of email fields This are just for reminding to mention these columns in output-table

What SQL query can result into this table

Improve this question
6
  • 1
    Do a self join. Commented Oct 25, 2021 at 13:38
  • @jarlh Don't answer in the comments. Commented Oct 25, 2021 at 13:42
  • @LioElbammalf, that's not an answer, it's a hint! Commented Oct 25, 2021 at 13:43
  • 1
    @jarlh Technically, you shouldn't "hint" in comments either, tempting as it is; they're intended for clarifying the question. If you think the question isn't worth answering, vote to close; if you think it is worth answering, either do so or wait for someone else to do so. Commented Oct 25, 2021 at 13:44
  • Sometimes someone just needs a hint to find the solution. A query written by the person having the problem is something that will be remembered, instead of something just copy-and-pasted. Commented Oct 25, 2021 at 13:51

1 Answer 1

Reset to default
2

You want a "cross join" of the table against itself excluding the self-matching rows. You can do:

select
  1 as id,
  a.user_id as user_id_1,
  b.user_id as user_id_2,
  a.email_id as user_id_1_email,  
  b.email_id as user_id_2_email
from user_email_table a
cross join user_email_table b
where a.user_id <> b.user_id

EDIT:

As @IMSoP points out the query can also use a common join with a join predicate and can be rephrased as:

select
  1 as id,
  a.user_id as user_id_1,
  b.user_id as user_id_2,
  a.email_id as user_id_1_email,  
  b.email_id as user_id_2_email
from user_email_table a
join user_email_table b on a.user_id <> b.user_id
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Since you have a condition relating them, I think this could also be written as an Inner Join (with the where clause as the on clause). I'm not sure which is clearer, to be honest.
@IMSoP That's correct. Added this form as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.