Sql joins & union problem

Other SQL Server Topics
1

Hi Everybody,
I finally got access to this when I forget my password. Here is my problem.
I have two tables with the same three columns ... AccountName, Invoice, file.
I need to run a query where the file on table 2 does not exist. I tried this but it pulls other accounts outside of its own. Fore example.

Table 1 Table 2
AccountName Invoice filename AccountName Invoice filename
EdsWorld 123 Ed.ply EdsWorld 3434 random.ply

This is my statements which is doing the above
Select t1.AccountName, t1.Invoice, T1.filename, t2.AccountName, t2.Invoice, t2.filename from TABLE1 t1 Inner JOIN TABLE2 t2 on t1.AccountName = t2.AccountName and t1.Invoice = t2.Invoice and t1.filename <> t2.filename

How do I get my statement to bring me back this result where the 2nd table has no record?

Example ...

Table 1 Table 2
AccountName Invoice filename AccountName Invoice filename
EdsWorld 123 Ed.ply

Greatly appreciate everyones help

Edb2019

2

You will get a better response if you post create table statements and insert statements to create a test case so those who help can demonstrate the solution using your data example. There are several ways, here's one:
Use a left outer join
Select t1.AccountName, t1.Invoice, T1.filename, t2.AccountName, t2.Invoice, t2.filename from TABLE1 t1 LEFT OUTER JOIN TABLE2 t2 on t1.filename = t2.filename
WHERE t2.filename is null;

3

Thank you Jatorre. I will simplify this next time. Now my query works great with what you wrote but now what if I want to be specific by AccountName as EdsWorld.

Currently the query is coming up with all AccountNames but with nothing on table 2 which is half correct. I tried this but it does not work ...

Select t1.AccountName, t1.Invoice, T1.filename, t2.AccountName, t2.Invoice, t2.filename from TABLE1 t1 LEFT OUTER JOIN TABLE2 t2 on t1.filename = t2.filename
WHERE t1.AccountName = 'EdsWorld' and t2.filename is null

Apparently my query comes up empty in SQL 2008. Please advise

4 
SELECT t1.AccountName, t1.Invoice, T1.filename
FROM TABLE1 t1 
WHERE NOT EXISTS(SELECT 1 FROM TABLE2 t2 WHERE t1.AccountName = t2.AccountName AND
    t1.Invoice = t2.Invoice AND t1.filename = t2.filename)
5

Thank you Scot and Jatore. I figured out my problem. The query I modified with Jatore worked. I just had no record called 'EdsWorld' in the column AccountName

That's why it came up blank.

Thanks a ton everybody. Steaks on me tonight in riverside, ca

6

You can use 'JOIN' query or 'LEFT JOIN' or 'Full OUTER JOIN' for display record from table 1 . or table 2 or both.