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.
| Author |
Topic |
|
glennandrewcooper
Starting Member
5 Posts |
 Posted - 2006-06-02 : 08:06:28
|
| Hi there,Lets say I have three tables as follows:SalesInvoices,SalesTransLink,TransactionsThe SalesTransLink table is simply a link to resolve the many-to-many issue.I want to select all records from SalesInvoices, and all Transactions where they are linked, however, I also want some criteria to be placed on one of the Transaction rows that I DON'T want to filter through to the Sales Invoices. For those Transacions that have been filtered out, I want the query to return null values instead.Does this make sense? The following link for an image may explain it better...[url]www.merlinaccounts.co.uk/VAT.jpg[/url] |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-02 : 08:40:51
|
| Post some sample data and the result you wantMadhivananFailing to plan is Planning to fail |
 |
|
|
glennandrewcooper
Starting Member
5 Posts |
Posted - 2006-06-02 : 09:03:22
|
RESOLVED:After (no word of a lie), five hours trying to figure this out, the solution was as easy as:dbo.Sales_Inv_Trans VATLinks LEFT OUTER JOIN dbo.[Transaction] SalesVatTrans ON VATLinks.Transaction_ID = SalesVatTrans.Transaction_ID AND SalesVatTrans.Cr_Code = 'VAT' I didn't realise that a JOIN condition could have more than one criteria, I was trying to set the condition of VAT in the WHERE ClauseThanks anyway :o) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-02 : 09:05:02
|
What is the Cr_Code for invoices 2, 4, 5, 6 ? Try remove the clause dbo.[Transaction].Cr_Code = 'VAT' ?
KH |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-06-02 : 09:05:46
|
Oh i see you found the problem. 
KH |
|
|
|
|
|
|
|
|
Inner Join