finding records in one table that aren't in another table

[roachae]

I'm a novice when it comes to MySQL so I'm not too sure about how to go about this. I have two tables, and both tables have two fields I need to deal with.

Table investors has fields Dealer and Rep. Table advisors has fields dealer and rep.
investors.Dealer = advisors.dealer AND investors.Rep = advisors.rep

What I need is to find records in Table One with A and B, but those values for A and B are not in Tables Two's a and b. Make sense?

I found some sql statements that should find what I need, but using only one field.

select * from A left join B on A.x = B.y and B.y is null

How can I use this with both my Dealer/dealer AND Rep/rep fields?

[smantscheff]

Do you mean that you want to select all records from table A with an x/y combination (x,y being field names) which does not occur in table B?
That would be

select A.* from A left join B on A.x=B.x and A.y=B.y where B.x is null

[roachae]

That basically what I want, but the second table will have some x's but not all the y's. Some example to make it clearer:

Scenario 1:
Table A has a record with say x=1 and y=2.
Table B has no records with x=1 or y=2.

Scenario 2:
Table A has a record with x=4 and y=5.
Table B has a record with x=4 but no y=5.

Scenario 2 happens quite often, but Scenario 1 is also happening.
This is needed to create a report so records can be added to Table B as necessary.

[smantscheff]

So what would be the desired result from your example in scenario 2? Do you want to retrieve any records which have A.x=B.x OR A.y=B.y?

[roachae]

I need all results from BOTH scenarios. Like I said, my report needs to catch everything where values of x or y or both are missing from the second table so if they ARE missing, new records can be added to include that x/y combination.

Bascially my report is a list of investors that have an advisor assigned to them, but that advisor may not be in the advisor table. Each advisor has a dealer code and a rep code which are assigned to the investors. If the advisor assigned to the investor (by dealer and rep codes) is not in the advisor table, it needs to be added, or the investor is left "orphaned".

[smantscheff]

But this is what my proposal returns: any records from A with an x/y combination which is not in B.

mysql> select * from a;
+---+---+
| x | y |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
3 rows in set

mysql> select * from b;
+---+---+
| x | y |
+---+---+
| 2 | 2 |
| 1 | 3 |
+---+---+
2 rows in set

mysql> SELECT A.* FROM A LEFT JOIN B on A.x=B.x AND A.y=B.y WHERE B.x IS NULL;
+---+---+
| x | y |
+---+---+
| 1 | 1 |
| 3 | 3 |
+---+---+
2 rows in set

[roachae]

I'm trying it but keep getting an sql syntax error.

Select inv.*, from investor as inv LEFT JOIN advisor as adv ON inv.DealerCode=adv.dealer_code AND inv.RepCode=adv.dealer_rep_code WHERE adv.dealer_code is NULL