Select Query - Hope I can explain what I want ..

[Autosys]

Hi there!

I have a scenario where I am selecting from 2 tables:

tbl_name and tbl_matched

tbl_name has 2 columns: ID and NAME
tbl_matched has 2 columns: ID and STATUS

I would like to: "within one go" return the results of: ID, NAME, MATCHED, REJECTED

The MATCHED and REJECTED will be determined by the value of the STATUS column which can either hold a 'C' for matched and a 'R' for Rejected.

My query below returns the same amount of R's than it does C's ... when there are many more C's than R's.

select
a.id "ID",
a.name "NAME",
count(b.status) "MATCHED",
count(c.status) "REJECTED"
from tbl_name a,
tbl_matched b,
tbl_matched c
where
(a.id=b.id AND b.status = 'C') OR (a.id=c.id AND c.status = 'R')
group by a.id, a.name;

If anyone has any advice then that would be greatly appreciated! Thx S

 

[gmmastros]

Try this...

Select A.Id, 
       A.Name, 
       Sum(Case When B.Status = 'C' Then 1 Else 0 End) as Matched, 
       Sum(Case When B.Status <> 'C' Then 1 Else 0 End) As Rejected
From   tbl_name As A
       Inner Join tbl_matched As B
          On A.Id = B.Id
Group By A.Id, A.Name

If this works for you, and you want me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

SELECT Id,
       Name,
       SUM(CASE WHEN Status = 'Matched'
                THEN 1 ELSE 0 END) AS Matched,
       SUM(CASE WHEN Status = 'Matched'
                THEN 0 ELSE 1 END) AS Rejected
FROM tbl_name 
GROUP BY Id, Name

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[PWise]

try

Select id,name ,MATCHED,REJECTED
from tbl_name a
left join(Select id count(*) MATCHED from tbl_matched where status ='C')b on b.id=a.id
left join(Select id count(*) REJECTED from tbl_matched where status ='R')c on c.id=a.id

 

[bborissov]

Oops,
I didn't saw that two tables are involved in the query.
Sorry.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Autosys]

Thanks for all the replies so far ..

I will be able to try it out again first thing in the morning and get back to you

Something to note ... It does not mean that something has been rejected if it does not have a C in the MATCHED column as this column could also have other non related values other than R ... should have mentioned this sorry!

gmmastros - would your above query still be valid considering the above? sorry if it's common sense.

S

 

[bborissov]

No, if it is a little bit opposite. If the status doesn't have 'C' that it is rejected. If I understand you right only these with status 'R' are rejected, aren't they?
If so try this (a small change in George's query)

Select A.Id,
       A.Name,
       Sum(Case When B.Status <> 'R' Then 1 Else 0 End) as Matched,
       Sum(Case When B.Status = 'R' Then 1 Else 0 End) As Rejected
From   tbl_name As A
       Inner Join tbl_matched As B  On A.Id = B.Id
Group By A.Id, A.Name

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

Well... you would have to change <> 'C' to = 'R'.

Basically, what I am doing is.....

I am using SUM instead of count. But think of it this way... if each item has a value of 1, then Sum and Count produce the same results, right? Now add the twist with the case statement. If we want to count the C's, it's the same thing as summing the value 1 for each C, otherwise 0.

I think I'm not explaining this very well.

[tt]
Status
------
C
C
C
R
R
D
[/tt]

Suppose that is your table.

Now, We introduce the CASE statement.

[tt]
Status Case When Status = 'C' Then 1 Else 0
------ ------------------------------------
C 1
C 1
C 1
R 0
R 0
D 0
[/tt]

Now, from the above example, summing the numeric column is the same thing as counting the C's. Make sense now?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Autosys]

I think that explains great thanks George

Also cheers bborissov!

 

[bborissov]

No, all cheers must be to George.
I didn't do anything here
As you see our answers have a minute difference. And that is because George wrote the whole explanation, I just change his query

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP