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)

Thanks for the reply,

It didn't work but I think I might have something to play with there,

Meanwhile I got this to work

 "SELECT DISTINCT Orders.* FROM (Orders INNER JOIN Material ON Orders.RJKNo = Material.RJKNo) LEFT JOIN Hours ON Orders.RJKNo = Hours.RJKNo WHERE Orders.InvoiceComplete = 'No'"
 

This will select all orders which are not complete and have a material entry against it in the Material Table but it doesn't find anything in the Hours Table.

Swap the word INNER and LEFT around and it will find all orders which are not complete and have Hours against it in the Hours Table but it doeesn't find anything in the Material Table

"SELECT DISTINCT Orders.* FROM (Orders LEFT JOIN Material ON Orders.RJKNo = Material.RJKNo) INNER JOIN Hours ON Orders.RJKNo = Hours.RJKNo WHERE Orders.InvoiceComplete = 'No'"

I need something which combines the two. Perhaps another INNER JOIN?

PS EDIT

I find if I change both words to INNER, I only got the orders which have BOTH Materials and Hours but not the orders which have Material only or Hours only

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

Whey Hey......

I had to add brackets to the first INNER JOIN but essentially it worked.

Pretty simple when you see how.

Thanks for your help

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

statement1
Union All
statement2