SQL Statement - Need Help

[Stevie]

I've got 4 tables I need to create a SQL statement from which are Cheque_History, Employees, Productions and Employers.

Cheque_History is joined to Employees via Employee_Id.
Employees is joined to Productions via Production_Id.
Productions is joined to Employers via Employer_Id.

I need all values from Cheque_History, FirstName and Surname form Employees, Title from Productions and Name from Employers.

My problem is that not all cheques in Cheque_History have an Employee_Id (as they may have been cancelled), therefore I need a Right Join between Cheque_History and Employees to get all of the cheques.

Can anybody help please. :)

[Jimbob]

i would normally use the query bulder in access, to be honest.

but, I am a cheat!

[Stevie]

Tried that but it won't run the query because of the Right Join.

Kinda the point of my question. ;)

[Jimbob]

what, access won't run the query or your app won't run the query???

[Stevie]

Access.

Access won't run it because two of the tables have a right join, but the other tables are joined normally.

[JHausmann]

Originally posted by Stevie
I've got 4 tables I need to create a SQL statement from which are Cheque_History, Employees, Productions and Employers.

Cheque_History is joined to Employees via Employee_Id.
Employees is joined to Productions via Production_Id.
Productions is joined to Employers via Employer_Id.

I need all values from Cheque_History, FirstName and Surname form Employees, Title from Productions and Name from Employers.

My problem is that not all cheques in Cheque_History have an Employee_Id (as they may have been cancelled), therefore I need a Right Join between Cheque_History and Employees to get all of the cheques.

Can anybody help please. :)

Old style join would be as follows:

select a.field, b.field, c.field, d.field
from Cheque_History a, Employees b, Productions c, Employers d
where d.Employer_Id = c.Employer_Id and
c.Productions = b.Productions and
b.Employee_Id =* a.Employee_Id

[Stevie]

Sorry that won't work (it's how I had it originally), as I will not get the cancelled cheques as they don't have an Employee_Id.

That is why I need a Right Join so that I get all records from Cheque_History, and all records from Employees where Employee_Id matches.

[JHausmann]

then create a temporary table with the elements from the three that use the inner join and, then, right join the temp table to your problem table

--create temporary table, drop temp_table first if it exists
select a.field, b.field, c.field
into temp_table
from Employees a, Productions b, Employers c
where c.Employer_Id = b.Employer_Id and
b.Productions = a.Productions

--now right join
select a.field1, a.field2, a.field3, b.field
from temp_table a, Cheque_History b
where a.Employee_Id =* b.Employee_Id

[Stevie]

Cheers, I didn't think of using a temp table (been trying to do it all in one sql statement), I'll give it a go. :)

[Stevie]

I've tried adding a table to my database (Access) but the code I'm using keeps giving me an error, basically saying it can't be done. See here (http://forums.vb-world.net/showthread.php?threadid=25820).

[Brent vanSchaayk]

This should do it.

Select A.Field, B.Field, C.Field, D.Field
From Cheque_History

RIGHT JOIN Employees
ON Emplyees.Employee_ID = Checque_History.Employee_ID

JOIN Productions
ON Productions.Production_ID = Employees.Production_ID

JOIN Employers
ON Employers.Employer_ID = Productions.Employer_ID

Hope it works.

[JHausmann]

Originally posted by Stevie
I've tried adding a table to my database (Access) but the code I'm using keeps giving me an error, basically saying it can't be done. See here (http://forums.vb-world.net/showthread.php?threadid=25820).

Why are you trying to create the table beforehand? The "Select...into" format creates the table for you.

[Stevie]

Cheers for the help, but I've managed to do it all with SQL now instead of creating the table with ADO.

Thanks JHausmann for the temp table approach idea. :)

[AKA]

Could you please send the solution I want to learn from it.

Could you not use a query instead of a temporary table ?