Best Logic?

Transact-SQL
1

I have ordersets that I need to find the orders within each set that HAS NOT been used/ordered.
So far, I can only get the orders that were used/ordered.
If the order_id is the linking field between 2 tables, how can I link them to get what has not been ordered and doesnt have an order_id?

2

use NOT EXISTS or LEFT JOIN

3

SELECT *
FROM cl_otl coo2
WHERE not EXISTS
( select * from
tbl2
WHERE coo2.otl_id = tbl2.template)
I am wondering if there is a way to get all the fields from tbl2 instead of or in addition to the fields from cl_otl?

4

You are selecting rows that does not exists in the tbl2. It means there isn't any matching rows from tbl2, so how / what are you going to show ?

5

Agree, so is there a better way to do this that? If I used the left join would that give me all the data except the one it's missing?

6 
SELECT coo2.*, tbl2.*
FROM cl_otl coo2 
LEFT OUTER JOIN tbl2 ON coo2.otl_id = tbl2.template
7

Yes, but all the columns from tbl2 will be NULL - so not sure what the point of including them is? Other than perhaps to have the metadata about the columns which, for example, a Form could use to correct size / data type data entry fields etc.

8

You have 2 tables : cl_otl and tbl2.
[1] There are some matching rows (based on co_otl.otl_id and tbl2.template)
[2] there are some rows in co_otl but does not exists in tbl2
[3] there are some rows in tbl2 but does not exists in cl_otl

what do you want ? You can select more than one option :slight_smile:

9

Option 2, please!