Select Records unique to one table 2

[sgidley]

Sorry if this is commonly asked or something, the confusing way to ask the question makes it hard for me to find the answer.

Example:

Table 1 has:
ID
1
1
1
1
2
2
3
3
4
5
5
6
6

Table 2 has:
ID
2
2
3
3
4
4
5

I want to query from Table 1 everything that is NOT in Table 2. Table 2 is also definitely a subset of table 1, so everthing in table 2 is also in table 1.
Results should pull out 1 and 6, Or maybe 1,1,1,6,6, whatever, I'm just trying to pull out the numbers that are not in Table 2. There are duplicates, so I put them in on purpose to illustrate. I have tried playing with "Distinct" Sql statement, but I don't know if that is how to achieve this, especially with duplicates.

What is this type of query called?

Thanks!

 

[LyndonOHRC]

What is this type of query called?" = join

select distinct a.id from t1 as a left join t2 as b on a.id =b.id where b.id is NULL


Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[sgidley]

Thx for the reply, but I'm afraid I don't follow the answer.
I see you said t1 and t2 for my table 1 and table 2 from my example. but what is this "a" and "b"? Is this whole line a literal sql example? I can't tell the difference between when you are giving literal syntax and when you are giving sudo syntax.

Thanks!

Shane

 

[PHV]

but what is this "a" and "b"?
alias

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

all you need to do is replace your table names where T1 and T2 are.... because he used an alias the rest of the query will still work once those are changed....

you may also find this reading helpful: Understanding SQL Joins

Leslie

Have you met Hardy Heron?

 

[sgidley]

I see, so it was a solid SQL command after all. I hadn't seen aliases until now. Thanks all. The link was also very helpful.

 

[LyndonOHRC]

Sorry, I was on the road this AM.

"a" is an alias to represent t1 and "b" is an alias to represent t2. As a query statement gets more complex the alias helps readability and lets you identify column names the occur in more than one table. Aliases are also, usually, much less typing than the table name.

For you purpose you could have written:
select distinct t1.id from t1 left join t2 on t1.id = t2.id where t2.id is NULL

I use alias out of habit and could have been more clear.
Good Luck

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey