MySQL getting result using 3 Tables and case statements

Question

I have 3 tables named person,person1, person2. All the 3 table has 2 columns name and phno. Now my requirement is, if i give any particular phno it has to show either yes or no for the corresponding table.

My sample table is :

Person Table:
Name      phno
abc       1234
def       2345
ghi       3456

and person2 table is

Name      phno
abc       1234
def       2345
ghi              

and person3 table is

Name       phno
abc         
def        2345
ghi        3456

As you can see, the same name can contain phno in one table and not in the other table. The required output is like :

Output 1:
 Phno            person         person2     person3
 1234              yes            yes         no

Output 2:
phno              person         person2     person3
3456                 yes           no             yes

I have tried something like this..

select a.phno,case when a.phno = '1234'then 'Y' else 'N' end as phone_number1,
case when b.phno = '1234' then 'Y' else 'N' end as phone_number2,
case when c.phno = '1234' then 'Y' else 'N' end as phone_number3
from person as a, person2 as b, person3 as c
where a.phno = '1234' and b.phno = '1234' and c.phno = '1234';

can anyone help me to improve this to achieve what I want.Thanks

Answer 1

MySQL does not support full outer joins, so you will have to use unions:

select phno,
if(count(phno1) = 0, 'No', 'Yes') as person,
if(count(phno2) = 0, 'No', 'Yes') as person2,
if(count(phno3) = 0, 'No', 'Yes') as person3,
from (select phno, phno as phno1, NULL as phno2 , NULL as phno3 from person  
      union
      select phno, NULL as phno1, phno as phno2 , NULL as phno3 from person2   
      union
      select phno, NULL as phno1, NULL as phno2 , phno as phno3 from person3
) sub
group by phno

If you only want to check for a single person, you can use Aditys code slight modifications:

SELECT DISTINCT p.phno AS 'phno',
(CASE WHEN ISNULL(person.phno) THEN 'NO' ELSE 'Yes' END) AS 'person',
(CASE WHEN ISNULL(person2.phno) THEN 'NO' ELSE 'Yes' END) AS 'person2',
(CASE WHEN ISNULL(person3.phno) THEN 'NO' ELSE 'Yes' END ) AS 'person3'
FROM (SELECT 4567 AS phno) p
LEFT JOIN person ON p.phno = person.phno
LEFT JOIN person2 ON p.phno = person2.phno
LEFT JOIN person3 ON p.phno = person3.phno;

It does work, I just tested it myself, feel free to execute my test and confirm:

DROP TABLE IF EXISTS person;
CREATE TABLE person(NAME VARCHAR(255), phno INT);
DROP TABLE IF EXISTS person2;
CREATE TABLE person2(NAME VARCHAR(255), phno INT);
DROP TABLE IF EXISTS person3;
CREATE TABLE person3(NAME VARCHAR(255), phno INT);

INSERT INTO person(NAME, phno)
VALUES ('abc', 1234),('def', 2345),('ghi', 3456),('jkl', NULL);

INSERT INTO person2(NAME, phno)
VALUES ('abc', 1234),('def', 2345),('ghi', NULL),('jkl', 4567);

INSERT INTO person3(NAME, phno)
VALUES ('abc', NULL),('def', 2345),('ghi', 3456),('jkl', 4567);

SELECT DISTINCT p.phno AS 'phno',
(CASE WHEN ISNULL(person.phno) THEN 'NO' ELSE 'Yes' END) AS 'person',
(CASE WHEN ISNULL(person2.phno) THEN 'NO' ELSE 'Yes' END) AS 'person2',
(CASE WHEN ISNULL(person3.phno) THEN 'NO' ELSE 'Yes' END ) AS 'person3'
FROM (SELECT 4567 AS phno) p
LEFT JOIN person ON p.phno = person.phno
LEFT JOIN person2 ON p.phno = person2.phno
LEFT JOIN person3 ON p.phno = person3.phno;

result:

phno  person  person2  person3
4567  NO      Yes      Yes

Answer 2

Try with this one.

select distinct person.phno as 'phno',
(case when isnull(person.phno) then 'NO' else 'Yes' end) as 'person',
(case when isnull(person2.phno) then 'NO' else 'Yes' end) as 'peron2',
(case when isnull(person3.phno) then 'NO' else 'Yes' end ) as 'person3'
from person  
full outer join person2 on person.phno = person2.phno
full outer join person3 on person3.phno = peron2.phno