Union problem

[JBBJWS]

I need to do a report based on 6 ticket tables and a product table (for the product description). Here is what I have tried: (I am only using 2 ticket files for this illustration)

select ticket1.*, products.description as productname
from ticket1, products
where ticket1.productid = products.productid
union all
select ticket2.*, products.description as productname
from tickets2, products
where ticket2.productid = products.productid

I get a non-unique table reference on products when I try to execute the query. What is the best way to accomplish what I am trying to do?

 

[tlbroadbent]

How about joining the union of the Ticket tables to the Products table? This requires the use of a sub-query. I think the syntax is simpler and eliminates the possibility of duplicates.[ul]Select t.*, p.Description As ProductName
(Select * From ticket1
Union All
Select * From ticket2) As t
Inner Join Products p
On t.productid = p.productid[/ul] Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Cagliostro]

Hoe about eliminating stars and put only colums you need instead of them?

select ticket1.*, products.description as productname
from ticket1, products
where ticket1.productid = products.productid
union all
select ticket2.*, products.description as productname
from tickets2, products
where ticket2.productid = products.productid John Fill

ivfmd@mail.md

 

[JBBJWS]

Thanks very much for the quick responses. I got the original query to work by using aliases. Here is what it looks like now (I may play around with the inner join method if I have time):


SELECT T1.* ,P1.Description as ProductName,
L1.LocationId,
L1.Description as LocationName,
C1.Description as CustomerName
FROM Tkscale T1, Inproduc P1, Inlocat L1, Slcust C1
WHERE P1.ProductID = T1.ProductID AND
L1.LocationId = T1.LocationID AND
C1.CustomerId = T1.CustomerId AND
T1.VoidStatus = 'A' AND T1.OkStatus = 'O'

UNION ALL SELECT T2.*
P2.Description as ProductName,
L2.LocationId,
L2.Description as LocationName,
C2.Description as CustomerName
FROM Tkmisc T2, Inproduc P2, Inlocat L2, Slcust C2
WHERE P2.ProductID = T2.ProductID AND
L2.LocationId = T2.LocationID AND
C2.CustomerId = T2.CustomerId AND
T2.VoidStatus = 'A' AND T2.OkStatus = 'O'
...

I am still in the process of developing the report, I will get rid of the *'s and only select what I need in a later iteration when I know for sure which fields are required. Thanks again for your responses.