multiple select - 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

General SQL Server Forums

New to SQL Server Programming

multiple select

Author

Topic

ashley.s
Starting Member

9 Posts

Posted - 2012-06-19 : 06:32:45

Hi everyone, I have 3 tables as follows
Table 1
ClientID ClientName
1 ABC
2 XYZ
Table 2
InvoiceNum Date
V001 20/05/12
V002 21/05/12
Table 3
ClientID InvoiceNum
1 V001
2 V002
I need to find the invoicenum and date for a specific clientname. I am stuck on this.
If i do,
Select clientid from table1 where clientname=ABC
then
I have to use that result to look for the invoicenum and date.
Is it possible to do all that in a query?
Regards,
Ashley

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-19 : 06:38:18

select t2.*
from Table1 t1
join Table3 t3
on t1.ClientID = t3.ClientID
join Table2 t2
on t3.InvoiceNum = t2.InvoiceNum
where t1.ClientName = 'ABC'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ashley.s
Starting Member

9 Posts

Posted - 2012-06-19 : 07:24:09

Hi nigel,
thank you so much.
I tried out something similar. Thank you so much. Can you look into my query below? i am doing the sum of a column.

select InvDetails.InvoiceNo,InvDetails.FileRef, InvDetails.Remarks, Inv.Name, Inv.Date, sum(InvAmt.Amount) from InvDetails inner join Client on InvDetails.ClientID = Client.ClientID inner join Inv on Inv.InvoiceNo = InvDetails.InvoiceNo inner join InvAmt on InvAmt.InvoiceNo = InvDetails.InvoiceNo where Client.[Client's Name]='SLX nTech-Vision Ltd' group by InvDetails.InvoiceNo

I am getting this error
Column 'InvDetails.FileRef' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Can you please advise?
Sorry if i am posting on the same thread.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-19 : 09:48:51

A SUM is an aggregate function. All of the other columns in your select either have to also be aggregated, or be put into a GROUP BY.

You may wish to try this link. It could help a great deal. http://www.w3schools.com/sql/default.asp

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources