outer join query and form question, please assist.

[jessicatran]

hi,

I have 2 tables:

Table 1:
User Desc
AAA apple
BBB orange
CCC orange

Table 2:
User Desc
AAA apple
ZZZ banana

Query result:
User Desc
BBB orange
CCC orange

So basically, I want all entries in Table 1 that aren't in Table 2.

In access, the default is inner join. I tried other options but none would give me the result as described above.

What do I need? Please advise. thx, jess.

 

[SimonB1978]

Hi Jess,

Here is something that works altough it really is not the best way to do it. I'll ty finding something better.

SELECT * FROM Table1 WHERE (User & Desc) NOT IN (SELECT (User & Desc) FROM Table2)

That creates a "key" by concatenating the 2 fields. That works but I can get pretty awkward with more fields.

Simon B.

 

[SimonB1978]

Hi again,

I think this is much prettier:

SELECT Table1.*
FROM Table1 LEFT JOIN Table2 ON (Table1.User = Table2.User AND Table1.Desc = Table2.Desc)
WHERE Table2.User IS NULL

You can join on as many fields as you want.

Simon B.

 

[jessicatran]

hi Simon, thank you very much! the second works well.