Select, Sum, Join and Group SQL Issue

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)

Select, Sum, Join and Group SQL Issue

Author

Topic

badman3k
Starting Member

15 Posts

Posted - 2009-04-16 : 11:51:33

I'm trying to select all the invoices from a table all with the associated data. This is the SQL I currently have:

select invoices.invoice_id, projects.project_id, name, stage_name, sum(qty * invoice_items.cost) as net_value from invoices inner join invoice_items on (invoices.invoice_id = invoice_items.invoice_id) inner join projects on (projects.project_id = invoices.project_id) inner join sites on (sites.site_id = projects.project_id) inner join clients on (clients.client_id = sites.client_id) inner join invoice_stages on (invoice_stages.stage_id = invoices.stage_id) where invoices.invoice_id is not null group by invoices.invoice_id, projects.project_id, name, stage_name

The above only outputs those invoices where the project_id is 1 (I'm assuming that it's actually using the group_by project_id and then using the first found project_id).

Can someone point me at the issue with the above and how to rectify it to make it output every invoice regardless of the project_id?

Many thanks in advance,

Rich

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 14:03:01

i dont think problem is in your query. it may be that only records with project_id has matching info in other tables. try replacing inner join with left outer join and see


select invoices.invoice_id, projects.project_id, 
name, stage_name, sum(qty * invoice_items.cost) as net_value 
from invoices 
inner join invoice_items 
on (invoices.invoice_id = invoice_items.invoice_id) 
inner join projects 
on (projects.project_id = invoices.project_id) 
inner join sites 
on (sites.site_id = projects.project_id) 
inner join clients 
on (clients.client_id = sites.client_id) 
inner join invoice_stages 
on (invoice_stages.stage_id = invoices.stage_id) 
where invoices.invoice_id is not null 
group by invoices.invoice_id, projects.project_id, name, stage_name

badman3k
Starting Member

15 Posts

Posted - 2009-04-16 : 14:32:59

quote:
Originally posted by visakh16

i dont think problem is in your query. it may be that only records with project_id has matching info in other tables. try replacing inner join with left outer join and see

visakh16, thanks for getting back to me. It was indeed with the SQL... a typo on my part. I apologise for wasting your time and thanks again for looking and responding. One of my inner joins was:

inner join sites on (sites.site_id = projects.project_id)

and it should have been

inner join sites on (sites.site_id = projects.site_id)

Thanks again.
Rich

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 14:35:19

no probs...glad that you sorted it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources