September 10, 2012 at 10:33 am
I want muliti table join.it's show 1 data/show 2 record But really 1 data/1 record.
And show NULL VALUE when not duplicate values.
select acl.Person_ID as 'CODE'
,pnr.FullName as 'FullName'
,case acl.persontype when 'normal' then 'normal' end as 'Type'
From tbl_aculog acl left join tbl_PerNR pnr On acl.Person_ID=pnr.Person_ID
union
select acl.Person_ID as 'CODE'
,ps.FullName as 'FullName'
,case acl.persontype when 'blacklist' then 'blacklist' end as 'Type'
From tbl_aculog acl left join tbl_Person ps On acl.Person_ID=ps.NPerson_ID
Result:
Person_ID | FullName | Type
00010132 | Stin| normal
00010132 | NULL | NULL
00000579 | Plom | normal
00000579 | NULL | NULL
00001081 | Watson | normal
00001081 | NULL | NULL
5211080 | SOPIT | blacklist
5211080 | NULL | NULL
NULL | NULL | NULL
NULL | NULL | NULL
**Fields Person_ID & FullName & Type is NULL VALUE.
I want Result:
Person_ID | FullName | Type
00010132 | Stin| normal
00000579 | Plom | normal
00001081 | Watson | normal
5211080 | SOPIT | blacklist
NULL | NULL | NULL
NULL | NULL | NULL
Thank you very much for your time 😀
I try union all and where .... is not null, don't work.
September 10, 2012 at 11:07 am
It looks like this might work:
SELECT acl.Person_ID AS 'CODE',
ISNULL(pnr.FullName, ps.FullName) AS 'FullName',
CASE acl.persontype
WHEN 'blacklist' THEN 'blacklist'
WHEN 'normal' THEN 'normal'
END AS 'Type'
FROM tbl_aculog acl
LEFT JOIN tbl_Person ps
ON acl.Person_ID = ps.NPerson_ID
LEFT JOIN tbl_PerNR pnr
ON acl.Person_ID = pnr.Person_ID;
I'm not sure why you have that Case statement in the queries. It doesn't look like it actually does anything, but I can't be sure of that without seeing the actual data and business rules.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 10, 2012 at 11:19 am
It's nice work 😀
Thank you very much SSChampion. 😀
September 10, 2012 at 11:52 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply