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.

[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.

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 ?