Combining Similar Queries - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Combining Similar Queries

Author

Topic

dooza
Starting Member

2 Posts

Posted - 2006-10-20 : 08:32:00

Hi there,
I have 6 queries like this:

SELECT TOP 1 id, flddescript, fldimageloc, fldprice, fldtracklisting, fldblurb, fldSubCat, fldorder, fldcat, fldCategory
FROM dbo.tblProducts
WHERE (fldSubCat = 35)
ORDER BY fldorder DESC

Each one has a different fldSubCat value. How can I combine all 6 into 1?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-20 : 08:37:03

Is this what you want?

Select * from
(SELECT TOP 1 id, flddescript, fldimageloc, fldprice, fldtracklisting, fldblurb, fldSubCat, fldorder, fldcat, fldCategory
FROM dbo.tblProducts
WHERE (fldSubCat = 35)
ORDER BY fldorder DESC) x
union all
select * from (SELECT TOP 1 id, flddescript, fldimageloc, fldprice, fldtracklisting, fldblurb, fldSubCat, fldorder, fldcat, fldCategory
FROM dbo.tblProducts
WHERE (fldSubCat = 36)
ORDER BY fldorder DESC) y
....

Harsh Athalye
India.
"Nothing is Impossible"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-20 : 08:45:04

[code]
SELECT TOP 1 id, flddescript, fldimageloc, fldprice, fldtracklisting, fldblurb, fldSubCat, fldorder, fldcat, fldCategory
FROM dbo.tblProducts
WHERE (fldSubCat in ( 35, 36 , 37, 38, 39, 40 ))
ORDER BY fldorder DESC
[/code]

EDIT : This won't work. Never notice the TOP 1

dooza
Starting Member

2 Posts

Posted - 2006-10-20 : 09:12:58

quote:

Originally posted by harsh_athalye

Is this what you want?

Select * from
(SELECT TOP 1 id, flddescript, fldimageloc, fldprice, fldtracklisting, fldblurb, fldSubCat, fldorder, fldcat, fldCategory
FROM dbo.tblProducts
WHERE (fldSubCat = 35)
ORDER BY fldorder DESC) x
union all
select * from (SELECT TOP 1 id, flddescript, fldimageloc, fldprice, fldtracklisting, fldblurb, fldSubCat, fldorder, fldcat, fldCategory
FROM dbo.tblProducts
WHERE (fldSubCat = 36)
ORDER BY fldorder DESC) y
....

Harsh Athalye
India.
"Nothing is Impossible"

Thats the perfect job! I had tried Unions, but I didn't know about the x and y, that has solved my problem, thank you very much!

Steve

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-20 : 12:29:51

You cant union the queries if they use Order by. You need to make it as derived table and use Union all

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources