SQL multiple select statements in 1 string

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

SQL multiple select statements in 1 string

Author

Topic

Renegade_AD
Starting Member

2 Posts

Posted - 2012-01-04 : 06:24:31

I am calling 4 select statements. Currently I am using union to collect results. This is not good it places results in one column and the results are manipulated cause wrong results

I am looking to place the results in the 4 specific columns or to get results to always return 4 rows

The problem is there should be 4 row results on ever occasion but there isn't eg if the physical result of each query is 3,1,1,1 the full statement run together result is 3,1 in 2 rows

for some reason it combines all common values but there is no consistency eg if actual individual results is 1,0,0,1 the combined in one statement the result is 1,0 in 2 rows

This is the problem in essence

Sometimes it works like individual statement = 3,2,0,1 the combined statement returns the same in 4 rows

[CODE]
SELECT ALL COUNT(*) FROM betplaced WHERE Odds = '" & Odds & "' UNION SELECT ALL COUNT(*) FROM betplaced, raceresults WHERE raceresults.RaceID = betplaced.RaceID AND betplaced.Odds = '" & Odds & "' AND raceresults.WinLoss = 'Win' UNION SELECT ALL COUNT(*) FROM betplaced, raceresults WHERE raceresults.RaceID = betplaced.RaceID AND betplaced.Odds = '" & Odds & "' AND raceresults.WinLoss = 'Loss' UNION SELECT ALL COUNT(*) FROM betplaced, raceresults WHERE raceresults.RaceID = betplaced.RaceID AND betplaced.Odds = '" & Odds & "' AND raceresults.WinLoss = 'NB'

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-04 : 07:05:02

Replace each occurrence of UNION with UNION ALL.

UNION ALL simply slaps the four results sets together without any attempt to remove duplicates.