Nadeem Nadeem - 3 years ago 169
SQL Question

SQL Query to retrieve username on reference from a child table

I am working with MySQL. I have two tables,

fb_data
and
fb_not
.

Columns of first table are :


  • id

  • username

  • useremail

  • userpass

  • fbappid

  • fbapplink

  • fbuserid



Columns of second table are :


  • fk_id

  • liked_one

  • liker_one




The
liked_one
and
liker_one
columns are referencing
fbuserid
of fb_data table. Simply they are foreign keys. What I want to do is, to get the names of
liker_one
column which is in
fb_not
table. And I want to get the names from
username
column of
fb_data
table.


Check this Image


This demonstration shows how the query should work. I want to get the user name of each liker from the fb_data table.

Answer Source

You can get your desired output by inner joining fb_not to fb_data twice; once for liker_one and once for liked_one as below .

SELECT DISTINCT CONCAT (
        t2.username
        ,'('
        ,t2.fbuserid
        ,') is the liker who liked '
        ,t3.username
        ,'('
        ,t3.fbuserid
        ,') post'
        ) AS Col1
FROM fb_not t1
INNER JOIN fb_data t2 ON t1.liker_one = t2.fbuserid
INNER JOIN fb_data t3 ON t1.liked_one = t3.fbuserid

distinct is used to remove duplicate rows.

Result:

Col1
----------------------------------------------------
nadeem(1234) is the liker who liked nadee(4321) post

You can check demo here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download