question

AbbottF avatar image
AbbottF asked

Trying to UNION 2 SELECTs from same tables

I am attempting to UNION 2 queries from the same tables (with different WHEREs) and would like the output returned as a 2 column / 1 row result. I am using this query: SELECT ca.Name AS AgeGroup FROM Category AS ca INNER JOIN Category AS parentca ON ca.ParentCategoryID = parentca.CategoryID INNER JOIN ProductCategory AS prodca ON ca.CategoryID = prodca.CategoryID WHERE (parentca.Name = N'Books By Age') AND (prodca.ProductID = 30891) UNION SELECT cn.Name AS CategoryName FROM Category AS cn INNER JOIN Category AS parentcn ON cn.ParentCategoryID = parentcn.CategoryID INNER JOIN ProductCategory AS prodcn ON cn.CategoryID = prodcn.CategoryID WHERE (parentcn.Name = N'Kids Books') AND (prodcn.ProductID = 30891) The result is: **AgeGroup** Action & Adventure Young Adult I was hoping for: **AgeGroup**..........**CategoryName** Young Adult ...... Action & Adventure What should I change to get this result in a single query? Thanks - Abbott
selectquery-resultsunion
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs by you voting. For each helpful answer below, click on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the thumbs up next to that answer.
0 Likes 0 ·
JohnM avatar image
JohnM answered
Why can't you just put it all in one statement? Like this: SELECT ca.Name AS AgeGroup, ca.Category FROM Category AS ca INNER JOIN Category AS parentca ON ca.ParentCategoryID = parentca.CategoryID INNER JOIN ProductCategory AS prodca ON ca.CategoryID = prodca.CategoryID WHERE (parentca.Name = N'Books By Age' or parentca.Name=N'Kids Books') AND (prodca.ProductID = 30891) Or am I missing something else? Hope this helps!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks John. I need to get only 1 row returned (I'm constrained by the requirements of the eCommerce package I'm using). Your entry ca.Category is not a column name but a table name. These are really two distinct queries (with the product id being the common denominator). My end objective is to add these 2 columns to 75 other columns being returned by a heavy weight stored procedure. This is my first foray into complex queries. Abbott
0 Likes 0 ·
MarcTemkin avatar image
MarcTemkin answered
If I understand your question correctly then a solution for your problem is: SELECT DISTINCT ca.Name AS AgeGroup , cn.Name AS CategoryName FROM Category AS ca INNER JOIN Category AS parentca ON ca.ParentCategoryID = parentca.CategoryID INNER JOIN ProductCategory AS prodca ON ca.CategoryID = prodca.CategoryID CROSS JOIN Category AS cn INNER JOIN Category AS parentcn ON cn.ParentCategoryID = parentcn.CategoryID INNER JOIN ProductCategory AS prodcn ON cn.CategoryID = prodcn.CategoryID WHERE ( parentca.Name = N'Books By Age' ) AND ( parentcn.Name = N'Kids Books' ) AND prodca.ProductID = prodcn.ProductID ORDER BY AgeGroup , CategoryName This works even if I add new pairs of data provided that I have correctly implemented your database design from your original question. CREATE TABLE Category ( name NVARCHAR(36) , parentcategoryid INT NULL , categoryid INT NOT NULL ) CREATE TABLE ProductCategory ( categoryid INT NOT NULL , productid INT NOT NULL, ) INSERT category ( name, parentcategoryid, categoryid ) VALUES ( 'Books By Age', 0, 1 ), ( 'Kids Books', 0, 2 ), ( 'Young Adult', 1, 3 ), ( 'Child', 1, 7 ), ( 'Action & Adventure', 2, 4 ), ( 'Castles & Dragons', 2, 8 ), ( 'Animal Stories', 2, 9 ), ( 'Stock Exchange Adventures', 2, 10 ) INSERT ProductCategory ( categoryid, productid ) VALUES ( 3, 30891 ), ( 4, 30891 ), ( 7, 30892 ), ( 8, 30892 ), ( 7, 30892 ), ( 9, 30892 ), ( 3, 30893 ), ( 10, 30893 ) I hope this helps! Marc
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Marc. Thanks. With a minor change WHERE ( parentca.Name = N'Books By Age' ) AND ( parentcn.Name = N'Kids Books' ) AND prodca.ProductID = 30891 AND prodcn.ProductID = 30891 I get the specific row that I will be unioning with another query. The answer also shed some light on some of the myriad holes in my sql knowledge. Abbott
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.