Join with three MySQL tables

Question

I have three tables

Table 1

userid  mobile
1       123456789
2       321654987
3       987456321

Table2

revid   userid  revdes  mobile
1       2       ASD     123456789
2       2       DSA     123456348
3       1       QWE     963258124

Table3

revid   revloc
1       asdf
3       dsaq

I need output, for example, where userid = 2

userid  revid    revdes     mobile      revloc  inTable1
2       1        ASD        123456789   asdf    true
2       2        DSA        123456348   NULL    false

In the inTable1 column above, the first row element is true because the mobile “123456789” is available in table 1

I am using MySQL.

Answer 1

You can achieve what you want using a series of left unions. The hard part of your request was that you knew that you wanted to join Table1and Table2using a mobile phone number, not a user ID.

SELECT t2.userid, t2.revid, t2.revdes, t2.mobile, t3.revloc,
    t1.mobile IS NOT NULL AS inTable1
FROM Table2 t2 LEFT JOIN Table1 t1
    ON t2.mobile = t1.mobile
LEFT JOIN Table3 t3
      ON t2.revid = t3.revid
WHERE t2.userid = 2

Follow the link below for a demo version:

SQLFiddle

Answer 2

left join , null :

SELECT    t2.userid, t2.revid, t2.revdes, t2.mobile, t3.revloc, 
          t1.mobile IS NOT NULL AS inTable1
FROM      table2 t2
JOIN      table3 t3 ON t2.revid = t3.revid
LEFT JOIN table1 t1 ON t2.mobile = t1.mobile

Answer 3

, :

SELECT Table1.userid, Table2.revid, Table2.revdes, Table2.mobile, Table3.revloc, IF(Table1.mobile = Table2.mobile, true, false) as inTable1
FROM Table1 
JOIN Table2 ON Table2.userid = Table1.userid 
JOIN Table3 ON Table2.revid = Table3.revid 

Answer 4

SELECT t2.userid, t2.revid, t2.revdes, t2.mobile, t3.revloc,
    CASE WHEN (SELECT t4.mobile from Table1 t4 where t4.userid = T2.userid) = T2.mobile
    THEN true ELSE false END AS inTable1
FROM Table1 t1, Table2 t2, Table3 t3
WHERE (t2.userid = t1.userid) AND (t2.revid = t3.revid)