Query Question

[tomtimms]

I am trying to see if a database either doesn't have a record, or if it contains 1 of 2 results. 

 

I have a total of 2 databases and there are joined by ID.  Database 1 contains the following.

 

ID | Amount

1    100

2    200

3    300

4    400

5    500

 

Database 2 has

 

ID : Type

1 : on_file

1:  yes

2. on_file

2: yes

3: on_file

4: yes

 

I need a query that will join database 1 to database 2 on ID then it will return the results where ID doesn't have either "on_file" or "yes" or both.  So my results would end up like.

 

ID | Misssing

3    yes

4    on_file

5    yes,on_file

 

I can't wrap my head around this on where to start, I figured out how to get the results where "on_file" and "yes" are both found however can't figure out how to get the results where they are not.  Any starting point would be great.

[tomtimms]

I mean 2 tables instead of databases.

[ignace]

Very poor db design.

 

How does yes and on_file differ? What is either meaning?

 

SELECT (CASE table2.Type WHEN 'on_file' THEN 'yes' WHEN 'yes' THEN 'on_file' ELSE 'missing' END) AS Status, ID
FROM table1 RIGHT JOIN table2 USING (ID)
WHERE Status = 'missing';

 

 

[tomtimms]

My database is fine, I just shortened it up and changed things around to show what I am trying to accomplish.  yes and on file are fields that are required from a user in order for them to get paid.  So yes represents if they were verified and on_file represents there tax information.  If both of those are in the table then they get paid, however if they both are not then they don't.  I am trying to get a query that shows either 1.  there both missing and 2.  if they have one inserted, to get the field that is missing.  I hope that clears it up a little bit.