union problem please help ! - 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

Transact-SQL (2000)

union problem please help !

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-11-19 : 03:29:28

I have the following table structure
tblproducts (productid,productname,submit_date,counter)
tblproddetails (productid,proddesc)
tblvotes (productid,votes)
tblos (productid,osid)

now i want to display all the records from the table products irrespective of their entry being in tblos,tblvotes .Iam using the following query:
select tblproducts.productid ,productname,tblproddetails.productdesc,submit_date,isnull(counter,0) as counter,''as osid ,'' as votes from tblproducts,tblproddetails where tblproducts.productid=tblproddetails.productid and tblproducts.categoryid in (select categoryid from tblcategory where categoryname like 'Business and productivity%')
union
select '','','','',''as counter,isnull(osid,0)as osid,''as votes from tblprodos where product=(select product from tblproducts where categoryid in (select categoryid from tblcategory where categoryname like ('business and productivity%')))
union
select productid,'','','',''as counter,'' as osid ,count(voteid) as votes from tblvotes where productid in (select productid from tblproducts where categoryid in (select categoryid from tblcategory where categoryname like 'business and productivity%')) group by productid order by counter desc

but it gives me wrong results .
it gives me an extra result for the votes recors.i mean that for productid 1 if the record is present in tblvotes it should give me a single record with the vote count but i get two records for the same productid 1.
I know there is something wrong with the union but can't make it out.
is there anything like intersection (which we have in oracle) in ms sql ?
u'r help is appreciated.
harshal.

Edited by - harshal_in on 11/19/2002 04:06:15

Edited by - harshal_in on 11/19/2002 04:38:45

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-11-19 : 07:32:33

select tblproducts.productid ,productname,tblproddetails.productdesc,submit_date,coalesce(counter,0) as counter,''as osid ,'' as votes from tblproducts LEFT OUTER join tblproddetails ON tblproducts.productid=tblproddetails.productid and tblproducts.categoryid in (select categoryid from tblcategory where categoryname like 'Business and productivity%')
union
select '','','','',''as counter,coalesce(osid,0)as osid,''as votes from tblprodos where product=(select product from tblproducts where categoryid EXISTS (select categoryid from tblcategory where categoryname like ('business and productivity%')))
union
select productid,'','','',''as counter,'' as osid ,count(voteid) as votes from tblvotes where productid EXISTS (select productid from tblblos where categoryid EXISTS (select categoryid from tblcategory where categoryname like 'business and productivity%')) group by productid order by counter desc

If this still dosen't work it is becuase there is more than one product being returned in this subquery

product = (select product from tblproducts where categoryid EXISTS (select categoryid from tblcategory where categoryname like ('business and productivity%')))

of special note though is the fact that tblbos was spelled incorrectly

Voted best SQL forum nickname...."Tutorial-D"

Edited by - sitka on 11/19/2002 07:53:21

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-19 : 13:52:37

I don't really like UNION queries, and I don't think we need one or should use one here. Instead of all that, how about:


SELECT ProductID, ProductName, ProdDesc, ISNULL(osid,0) as ProductOSID, ISNULL(Votes,0) as ProductVotes
FROM
  tblproducts
INNER JOIN tblCategories
ON
  tblproducts.CategoryID = tblCategories.CategoryID
LEFT OUTER JOIN tblVotes
ON 
  tblVotes.ProductID = tblProducts.productID
LEFT OUTER JOIN tblos
ON
  tblos.ProductID = tblProducts.ProductID
LEFT OUTER JOIN tblProdDetails
ON 
  tblProdDetails.ProductID = tblProducts.ProductID
WHERE
  tblCategories.Categoryname LIKE 'business and productivity%'

Note that if ProductID is NOT the primary key of all these tables, you would need each of the joined tables to be a SUBQUERY that GROUPS by ProductID and performs a SUM or whatever you like on the other fields. I don't think this is the case in your example, however.

If it were, you would replace


LEFT OUTER JOIN tblVotes

with


LEFT OUTER JOIN (SELECT ProductID, SUM(Votes) as Votes FROM tblVotes GROUP BY ProductID) tblVotes

I hope this works for you.

Edited by - jsmith8858 on 11/19/2002 13:56:22

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-11-19 : 23:54:42

quote:

I don't really like UNION queries, and I don't think we need one or should use one here. Instead of all that, how about:

SELECT ProductID, ProductName, ProdDesc, ISNULL(osid,0) as ProductOSID, ISNULL(Votes,0) as ProductVotes
FROM
tblproducts
INNER JOIN tblCategories
ON
tblproducts.CategoryID = tblCategories.CategoryID
LEFT OUTER JOIN tblVotes
ON
tblVotes.ProductID = tblProducts.productID
LEFT OUTER JOIN tblos
ON
tblos.ProductID = tblProducts.ProductID
LEFT OUTER JOIN tblProdDetails
ON
tblProdDetails.ProductID = tblProducts.ProductID
WHERE
tblCategories.Categoryname LIKE 'business and productivity%'

Note that if ProductID is NOT the primary key of all these tables, you would need each of the joined tables to be a SUBQUERY that GROUPS by ProductID and performs a SUM or whatever you like on the other fields. I don't think this is the case in your example, however.

If it were, you would replace

LEFT OUTER JOIN tblVotes

with

LEFT OUTER JOIN (SELECT ProductID, SUM(Votes) as Votes FROM tblVotes GROUP BY ProductID) tblVotes

I hope this works for you.

Edited by - jsmith8858 on 11/19/2002 13:56:22

thanks a lot jsmith
that worked after some twiking !!!
thanks again.
regards,
Harshal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources