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 :
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
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