Only those whoe have an offer or service(Not Null Value)-CodePudding

I have 3 tables like this

1st table:person

PID	MOBILE
1	123
2	234
3	345
4	456

2nd table: offer

PID	of_id	of_name
1	11	offer 1
3	12	offer 2
3	13	offer 3

3td table: service

PID	ser_id	ser_name
2	10	ser 2
1	11	ser 1

I want table like this:

PID	mobile	of_name	ser_name
1	123	offer 1	ser 1
2	234		ser 2
3	345	offer 3
3	34	offer 3

i tried:

select distinct p.pid,p.mobile,o.of_name,s.ser_name 
  from person p
left join (select pid,of_name from offer) o on p.pid = o.pid
left join (select pid,ser_name from service) s on p.pid = s.pid

but i got:

PID	mobile	of_name	ser_name
1	123	offer 1	ser 1
2	234		ser 2
3	345	offer 3
3	34	offer 3
4	456

So, My table should only include those who have an offer or service.

CodePudding user response：

My table should only include those who have an offer or service.

You can filter to only include the rows where there is either an offer or a service by checking that the PID from those tables is not NULL:

SELECT p.pid,
       p.mobile,
       o.of_name,
       s.ser_name 
FROM   person p
       LEFT OUTER JOIN offer o
       ON p.pid = o.pid
       LEFT OUTER JOIN service s
       ON p.pid = s.pid
WHERE  o.pid IS NOT NULL
OR     s.pid IS NOT NULL

Which, for the sample data:

CREATE TABLE person (PID, MOBILE) AS
SELECT 1, 123 FROM DUAL UNION ALL
SELECT 2, 234 FROM DUAL UNION ALL
SELECT 3, 345 FROM DUAL UNION ALL
SELECT 4, 456 FROM DUAL;

CREATE TABLE offer (PID, of_id, of_name) AS
SELECT 1, 11, 'offer 1' FROM DUAL UNION ALL
SELECT 3, 12, 'offer 2' FROM DUAL UNION ALL
SELECT 3, 13, 'offer 3' FROM DUAL;

CREATE TABLE service (PID, ser_id, ser_name) AS
SELECT 2, 10, 'ser 2' FROM DUAL UNION ALL
SELECT 1, 11, 'ser 1' FROM DUAL;

Outputs:

PID MOBILE OF_NAME SER_NAME

1 123 offer 1 ser 1

3 345 offer 2 null

3 345 offer 3 null

2 234 null ser 2

db<>fiddle here

CodePudding user response：

try like below

with cte as(select p.pid,p.mbl,
o.name as offername,s.name as service_name from person p 
left join offer o on p.pid=o.pid
left join service s on p.pid=s.pid
) select pid,mbl,offername,service_name
from cte where pid in (select pid from offer
union 
select pid from service)