Find PID which exact account numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find PID which exact account numbers

Author

Topic

proser
Starting Member

5 Posts

Posted - 2008-11-14 : 12:53:14

Hello, I'm trying to figure this out but it doesn't seem to work.
I'm trying to find all the PID's account that matches each Scheduled Accounts:

Table PID:


ID          PID         ACCTKY      
----------- ----------- ----------- 
1           1           1
2           1           2
3           1           3
4           2           4
5           2           5
6           3           4

Table Scheduled:


ID          SCHD        ACCTKY      
----------- ----------- ----------- 
1           1           1
2           1           2
3           1           3
4           2           1
5           3           4
6           5           5

I'd like to see:
When SCHD = 1


PID     MATCH
------- --------
1       True
2       False
3       False

Thanks alot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 12:59:00

[code]SELECT p.PID,
CASE WHEN COUNT(s.ID) = 0 THEN 'False'
ELSE 'True'
END
FROM PID p
LEFT JOIN scheduled s
ON p.ACCTKY =s.ACCTKY
AND s.SCHD=1
GROUP BY p.PID
[/code]

proser
Starting Member

5 Posts

Posted - 2008-11-14 : 13:17:12

Sorry if I wasn't being clear:
If I change
Table PID:


ID          PID         ACCTKY      
----------- ----------- ----------- 
1           1           1
2           1           2
3           1           3
4           2           1
5           2           5
6           3           4

I get PID 2 TRUE. I want PID table to match ONLY if all the ACCTKY in Scheduled exists in PID table.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-14 : 13:45:14

i think visakh guided you in right way. Read his reply carefully.

proser
Starting Member

5 Posts

Posted - 2008-11-14 : 14:03:59

Again, sorry for not being clear about the question.

I want to show PID Only if its ACCTKY matches ALL the ACCTKY in Scheduled table for each Schedule ID.

Visakh's solution matches even if one of the acctky not exists in the schedule table:

Here is a better sample set:
PID


ID          PID         ACCTKY      
----------- ----------- ----------- 
1           1           1
2           1           2
3           1           3
4           2           1
5           2           2
6           2           3
7           3           4

SCHD


ID          SCHD        ACCTKY      
----------- ----------- ----------- 
1           1           1
2           1           2
3           1           3
4           2           1
5           2           2
6           2           3
7           2           4
8           3           4
9           5           5

My expected result when SCHD = 1:


PID    MATCH
----- --------
1     TRUE
2     TRUE
3     FALSE

Thanks again

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-14 : 14:24:36

I am getting correct output with visakh's solution.

Declare @PID table
(ID int, PID int, ACCTKY int)
insert @PID
select 1,1,1 union all
select 2,1,2 union all
select 3,1,3 union all
select 4,2,1 union all
select 5,2,2 union all
select 6,2,3 union all
select 7,3,4

Declare @SCHD table
( ID int,SCH int,ACCTKY int)

insert @SCHD
select 1 ,1 ,1 union all
select 2 ,1 ,2 union all
select 3 ,1 ,3 union all
select 4 ,2 , 1 union all
select 5 ,2 ,2 union all
select 6 ,2 ,3 union all
select 7 ,2 ,4 union all
select 8 ,3 ,4 union all
select 9 ,5 ,5

SELECT p.PID,
CASE WHEN COUNT(s.ID) = 0 THEN 'False'
ELSE 'True'
END as Match
FROM @PID p
LEFT JOIN @SCHD s
ON p.ACCTKY =s.ACCTKY
AND s.SCH=1
GROUP BY p.PID

Output

PID MATCH
1 True
2 True
3 False

proser
Starting Member

5 Posts

Posted - 2008-11-14 : 14:37:39

[code]Declare @PID table
(ID int, PID int, ACCTKY int)
insert @PID
select 1,1,1 union all
select 2,1,2 union all
select 3,1,3 union all
select 4,2,1 union all
select 5,2,2 union all
select 6,2,5 union all
select 7,3,4

Declare @SCHD table
( ID int,SCH int,ACCTKY int)

insert @SCHD
select 1 ,1 ,1 union all
select 2 ,1 ,2 union all
select 3 ,1 ,3 union all
select 4 ,2 ,1 union all
select 5 ,2 ,2 union all
select 6 ,2 ,3 union all
select 7 ,2 ,4 union all
select 8 ,3 ,4 union all
select 9 ,5 ,5

SELECT p.PID,
CASE WHEN COUNT(s.ID) = 0 THEN 'False'
ELSE 'True'
END as Match
FROM @PID p
LEFT JOIN @SCHD s
ON p.ACCTKY =s.ACCTKY
AND s.SCH=1
GROUP BY p.PID

Also returns:

PID Match
----------- -----
1 True
2 True
3 False
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-14 : 14:56:41

quote:

Originally posted by proser

Declare @PID table
(ID int, PID int, ACCTKY int)
insert @PID
select 1,1,1 union all
select 2,1,2 union all
select 3,1,3 union all
select 4,2,1 union all
select 5,2,2 union all
select 6,2,5 union all
select 7,3,4


Declare @SCHD table
( ID int,SCH int,ACCTKY int)

insert @SCHD
select 1 ,1 ,1 union all
select 2 ,1 ,2 union all
select 3 ,1 ,3 union all
select 4 ,2 ,1 union all
select 5 ,2 ,2 union all
select 6 ,2 ,3 union all
select 7 ,2 ,4 union all
select 8 ,3 ,4 union all
select 9 ,5 ,5

SELECT p.PID,
CASE WHEN COUNT(s.ID) = 0 or Count(s.ID) <> Count(p.PID) THEN 'False'
ELSE 'True'
END as Match
FROM @PID p
LEFT JOIN @SCHD s
ON p.ACCTKY =s.ACCTKY
AND s.SCH=1
GROUP BY p.PID

Also returns:

PID         Match 
----------- ----- 
1           True
2      False
3           False

proser
Starting Member

5 Posts

Posted - 2008-11-14 : 15:45:38

Not exactly, it still returns true when counts match but acctky not match.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-14 : 15:46:32

[code]select PID
from
(select *, (select count(distinct acctky) from @PID where PID = p.PID) cnt from @PID P) a
left join
(select *, (select count(distinct acctky) from @SCHD where SCH = s.SCH) cnt from @SCHD S where sch = 1) b
on a.acctky = b.acctky
and b.sch = 1 and a.cnt = b.cnt
group by PID
having count(a.cnt) = count(b.cnt)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 23:34:30

[code]SELECT p.PID,
CASE WHEN Count(DISTINCT s.ACCTKY) <> Count(DISTINCT p.ACCTKY) THEN 'False'
ELSE 'True'
END as Match
FROM @PID p
LEFT JOIN @SCHD s
ON p.ACCTKY =s.ACCTKY
AND s.SCH=1
GROUP BY p.PID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources