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
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.
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!
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
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
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
17 People are following this question.
