Search for records in one table that don't have a match in another table

[showman13]

I'm not sure that my title is descriptive enough, but here is what I need to do...

I had a bit of a screwup today with launching a site...

Within about 10 minutes of opening the site, the members created 337 transactions (stored in trans_table)
Each of those transaction should relate to a position in another table... (position_table)
But for some reason only 245 positions were created.

Now my task is to figure out which transactions in the trans_table don't have a matching record in the position_table

The position_table has a field called mem_id that should match to a field in the trans_table called from_id

And there is also a field in the position_table called pos_trans_id that should match trans_id in the trans_table

I guess what I really need to do is find all of the transactions in the trans_table that don't have a matching record in the position_table and the best match would be the trans_id against the pos_trans_id since there is only a one to one match on that.

I haven't a clue about how to form that query...

Any help that you have would be appreciated.

thanks in advance
Douglas

[urtrivedi]

I hope this is what you are looking for:

select t.* from trans_table t left outer join position_table p on t.trans_id=p.pos_trans_id
where p.pos_trans_id is null

[simplypixie]

To keep it really simple (unless I am missing something), you could just use

SELECT * FROM trans_table WHERE from_id NOT IN (SELECT mem_id FROM position_table)

[showman13]

Well, the first option returned a total of 1352 records with many duplicates

And the second option returned a total of 871 records with many duplicates

BUT, the good news is that between the two examples I gained a much better understanding of doing a query within a query (whatever that is called)

And came up with this query as a variation from those samples

SELECT * FROM trans_table WHERE trans_id NOT IN (SELECT pos_trans_id FROM position_table)

This returned 191 records, which is more than I really wanted, but I guess it is what it is... (from what I can determine, this is accurate)

Thank you both for your feedback. It made it possible for me to have a better understanding, and to me that is what it is all about.

Douglas