Help with joining multiple select statements

Question

Post reply

Help with joining multiple select statements

Austin Moran

Old Hand

Points: 375
More actions
September 27, 2005 at 9:53 am

#91332

I have three separate SELECT statements that I would like to join together into a single statement. Ultimately I want to produce a single dataset with three separate columns, instead of three separate datasets with a single record. Can someone give me a hand with this? I will really appreciate any help that you might be able to offer. I am still in the "just learning" stage. THANKS VERY MUCH, in advance. - Austin
(here are the three separate SELECT statements
SELECT count (distinct dbo.Member.NRDS_ID) AS 'ACTIVE MEMBERS'
FROM dbo.Member INNER JOIN
dbo.Member_Association ON dbo.Member.Member_Number = dbo.Member_Association.Member_Number
WHERE (dbo.Member_Association.Status = 'A') AND (dbo.Member_Association.Member_Type_Code = 'R' OR
dbo.Member_Association.Member_Type_Code = 'RA') AND (dbo.Member_Association.Primary_Indicator = 'P')
SELECT Count (Distinct dbo.Member.NRDS_ID)AS 'VALID BIRTHDATES'
FROM dbo.Member INNER JOIN
dbo.Member_Association ON dbo.Member.Member_Number = dbo.Member_Association.Member_Number
WHERE (dbo.Member_Association.Status = 'A') AND (dbo.Member_Association.Member_Type_Code = 'R' OR
dbo.Member_Association.Member_Type_Code = 'RA') AND (dbo.Member_Association.Primary_Indicator = 'P') AND
(dbo.Member.Birth_Date > '18500101')
SELECT Count (distinct dbo.Member.NRDS_ID) AS 'BIRTHDATES WITH ALL ZEROES'
FROM dbo.Member INNER JOIN
dbo.Member_Association ON dbo.Member.Member_Number = dbo.Member_Association.Member_Number
WHERE (dbo.Member_Association.Status = 'A') AND (dbo.Member_Association.Member_Type_Code = 'R' OR
dbo.Member_Association.Member_Type_Code = 'RA') AND (dbo.Member_Association.Primary_Indicator = 'P') AND
(dbo.Member.Birth_Date='00000000')

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

How about this?

Select * from

(SELECT count (distinct dbo.Member.NRDS_ID) AS 'ACTIVE MEMBERS'

FROM dbo.Member INNER JOIN

dbo.Member_Association ON dbo.Member.Member_Number = dbo.Member_Association.Member_Number

WHERE (dbo.Member_Association.Status = 'A') AND (dbo.Member_Association.Member_Type_Code = 'R' OR

dbo.Member_Association.Member_Type_Code = 'RA') AND (dbo.Member_Association.Primary_Indicator = 'P')) dtA

cross join

(SELECT Count (Distinct dbo.Member.NRDS_ID)AS 'VALID BIRTHDATES'

FROM dbo.Member INNER JOIN

dbo.Member_Association ON dbo.Member.Member_Number = dbo.Member_Association.Member_Number

WHERE (dbo.Member_Association.Status = 'A') AND (dbo.Member_Association.Member_Type_Code = 'R' OR

dbo.Member_Association.Member_Type_Code = 'RA') AND (dbo.Member_Association.Primary_Indicator = 'P') AND

(dbo.Member.Birth_Date > '18500101')) dtB

CROSS JOIN

(SELECT Count (distinct dbo.Member.NRDS_ID) AS 'BIRTHDATES WITH ALL ZEROES'

FROM dbo.Member INNER JOIN

dbo.Member_Association ON dbo.Member.Member_Number = dbo.Member_Association.Member_Number

WHERE (dbo.Member_Association.Status = 'A') AND (dbo.Member_Association.Member_Type_Code = 'R' OR

dbo.Member_Association.Member_Type_Code = 'RA') AND (dbo.Member_Association.Primary_Indicator = 'P') AND

(dbo.Member.Birth_Date='00000000')) dtC

Austin Moran Old Hand Points: 375 More actions · Answer 2

Thanks so very much! This is exactly what I was hoping for!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

HTH.

Make sure you keep this at one row/select or else you'll have a full blown cartesian plan .

Ken Lee-263418 SSC Veteran Points: 266 More actions · Answer 4

This is a nitpick, but that produces a record set, not a dataset

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

September 27, 2005 at 12:43 pm

#593416

So true... I guess he started learning in .NET .

Robert Banuelos Newbie Points: 3 More actions · Answer 6

I should probably start a new post for this, but my question is along the same line of thought......

I keep getting a "FROM" error when I try the query below.

Please and THANK YOU........

SELECT

Compare1.txtCategory, Compare1.CategorySubTotal ,

Compare2.txtCategory, Compare2.CategorySubTotal,

(Compare1.CategorySubTotal - Compare2.CategorySubTotal) as difference

FROM

(SELECT txtCategory, SUM(curAmount) AS CategorySubTotal

FROM tblEstimatesLineItems

WHERE (lngzEstimateId = @Compare1_lngzEstimateId)

GROUP BY txtCategory) Compare1

INNER JOIN <----- "What will work here"

(SELECT txtCategory, SUM(curAmount) AS CategorySubTotal

FROM tblEstimatesLineItems

WHERE (lngzEstimateId = @Compare2_lngzEstimateId)

GROUP BY txtCategory) Compare2;

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Yes start a new post.

also

INNER JOIN <----- "What will work here"

(SELECT txtCategory, SUM(curAmount) AS CategorySubTotal

FROM tblEstimatesLineItems

WHERE (lngzEstimateId = @Compare2_lngzEstimateId)

GROUP BY txtCategory) Compare2 on compare1.id = compare2.id;

this is if you need a join... a cross join might be what you need too.