Multiple JOIN statements 1

[4waystop]

Hi

I'm having trouble trying to join the information of three tables together in an single SQL statement. It can be done in two statements but its a bit wasteful!

I'm trying to get information from the Contract and ClientRT tables which fulfil certain specifications(Contract.FromDate must be between two dates and ClientRT.ReportsTo must be within a set of values). When I find this data I want to get PartMaster.DescText for the selected record.

This is my code:

var select = "SELECT Contract.ContractNo, Contract.CustomerID, Contract.FromDate, ClientRT.FirstName, ClientRT.LastName, ClientRT.PhoneBusiness PartMaster.DescText AS ToolDesc ";
select = select + "FROM (ClientRT RIGHT JOIN Contract ON ClientRT.AccNumber=Contract.CustomerID) LEFT JOIN PartMaster ON Contract.PartNumber=PartMaster.PartNumber ";
select = select + "WHERE ClientRT.ReportsTo IN("+rsCustomers('AccNumber');
	rsCustomers.MoveNext();
	while(!rsCustomers.EOF){
		select = select + "," + rsCustomers('AccNumber');
		rsCustomers.MoveNext();
	}
	
select = select + ") AND Contract.FromDate >= '"+getUSDateString(today)+"' AND Contract.FromDate < '"+getUSDateString(until)+"'";

And this is the resulting statement, which will not sucessfully open a recordset:

SELECT Contract.ContractNo, Contract.CustomerID, Contract.FromDate, ClientRT.FirstName, ClientRT.LastName,ClientRT.PhoneBusiness PartMaster.DescText AS ToolDesc FROM (ClientRT RIGHT JOIN Contract ON ClientRT.AccNumber=Contract.CustomerID) LEFT JOIN PartMaster ON Contract.PartNumber=PartMaster.PartNumber WHERE ClientRT.ReportsTo IN(59,61) AND Contract.FromDate>='9/15/2004' AND Contract.FromDate<'9/22/2004'

The error thrown-up tells me there is incorrect syntax near '.'!! Its got to be something to do with the join statements as it works if I don't try to get information from the PartMaster table. I've tried various different types of join and they don't seem to work.

Any help much appreciated!!!

 

[Veep]

What kind of database are you running this on? Access? If so it's not going to fly because JET can't resolve mixed right and left joins. The solution is to create a couple of queries and then join them into a third. Use the third query as a parameterized stored procedure. See this article on how to do this:

http://www.stardeveloper.com/articles/display.html?article=2001050101&page=1

If this is SQLServer then I would just create a SP to return your recordset.

 

[4waystop]

Sorry - I forgot to put that in. The database I'm running this on is MS SQL Server 2000 (accessing it using server-side javascript). Do multiple inner joins work with SQL Server?

It wouldn't work with inner joins either though so I'm not sure that mixed left and right joins is the problem. I was ideally hoping to do it without having to do two or more queries though if there is a way? I've seen multiple inner joins in queries before, only in my case it doesn't seem to work!

Stored procedures are ok, only I'm not the only database administrator and it really wouldn't surprise me if a "clear-out" operation gets them deleted!!

 

[Veep]

If you've got Enterprise Manager just create a View of what you are trying to represent and see if your View returns any records. That's probably the easiest way to see where the problem lies because you can 'visually' play with it. The mixed joins should work with SQLServer but honestly I can't think of any instances where I've mixed right and left joins. It's usually something like table1 Inner Join on table2, table3 Left Join on table2 or else all Joins are going in the same direction on table1.

 

[fattyfatpants]

I did notice that you were missing a comma in your SQL statement between ClientRT.PhoneBuiness and Partmaster.DescText but I'm not sure if that was a typo or not

SELECT Contract.ContractNo, Contract.CustomerID, Contract.FromDate, ClientRT.FirstName, 
       ClientRT.LastName, ClientRT.PhoneBusiness, PartMaster.DescText AS ToolDesc 
  FROM ClientRT RIGHT JOIN (Partmaster RIGHT JOIN Contract ON PartMaster.PartNumber = Contract.PartNumber) 
       Contract ON ClientRT.AccNumber = Contract.CustomerID
 WHERE ClientRT.ReportsTo IN(59,61) 
   AND Contract.FromDate >= '9/15/2004' 
   AND Contract.FromDate < '9/22/2004'