SQL Query Help  
Author Message
JordiValldauraRiqu





PostPosted: Thu Jun 10 17:16:05 CDT 2004 Top

SQL Server Developer >> SQL Query Help

I have a query that joins 2 tables and reports on those transactions in a given date range,........ Is there a way to report on the other data? as in list all parts that do not have a corresponding record in the download tabel for example?

my original query looks like this, but it reports on things that have a parts record on the download and item table.... i need to know all the parts that have no corresponding download transaction. Could someone please help me out with this...

select d.part, i.price, count(d.part) as TotalCount, sum(d.qty) as TotalQTY,
i.Description , cast(convert(money, i.inventory) * CONVERT(money, i.price) as decimal(10,2)) as totalInv from download d, items i where i.part = d.part and d.qDate >= '06/10/00' and d.qDate<= '06/10/04' group by d.part,i.description, i.inventory, i.price order by TotalCount DESC

SQL Server118  
 
 
anonymous





PostPosted: Thu Jun 10 17:16:05 CDT 2004 Top

SQL Server Developer >> SQL Query Help If I understood you properly then you want all items from the "items" table even if there is no match in the "download" table

Using the outer join syntax doing a right join you can do this way. If you meant the other way around change to a left and if both then use a full join

select
d.part,
i.price,
count(d.part) as TotalCount,
sum(d.qty) as TotalQTY,
i.[Description],
cast(convert(money, i.inventory) * CONVERT(money, i.price) as decimal(10,2)) as totalInv
from
download
right join
items i
ON
i.part = d.par
WHER
(d.qDate BETWEEN '06/10/00' and '06/10/04'
group by
d.part
i.[description],
i.inventory,
i.price
order by
TotalCount DESC