Inner Join

Currently using VB6 to retreive data using the 'Data Control'

I have a master table called  "Orders", another table called "Material" and another called "Hours"

Using 'INNER JOIN' I can call all orders from the master table where the invoices are not completed AND that have an entry against it in the Material table by matching the RJKNo field in each table with the following

"SELECT DISTINCT Orders.RJKNo FROM Orders INNER JOIN Material ON Orders.RJKNo = Material.RJKNo WHERE Orders.InvoiceComplete = 'No'"

I can also search for all orders again, this time looking for orders that have hours against them with

"SELECT DISTINCT Orders.RJKNo FROM Orders INNER JOIN Hours ON Orders.RJKNo = Hours.RJKNo WHERE Orders.InvoiceComplete = 'No'"

How do I create 1 query out of these two which will search for records that have either Material OR Hours.

I've tried nesting the 'INNER JOIN' but I can't get the sytax correct.

Any takers

Madius (Scotland UK)

SELECT DISTINCT Orders.RJKNo
FROM Orders
LEFT JOIN Material ON Orders.RJKNo = Material.RJKNo
LEFT JOIN Hours ON Orders.RJKNo = Hours.RJKNo
WHERE Orders.InvoiceComplete = 'No'
AND (Material.RJKNo is not Null OR Hours.RJKNo is not Null)

This should solve the problem, if the join is successful then the key field will have a value (ie.. not null).

You could also do a union with the two statements, ie:

statement1
Union All
statement2