Query to find recs without matching recs in second table

[showman13]

I know this sounds strange, but I messed up in a 'Manual' change to a DB table, and need to find a couple of records that I have no pathway to find...

Explanation

We have a member table called 'members'

several different position tables, one for each level called position_1, position_2 etc

I was supposed to change the ownership of a couple positions in position_4, basically by changing the mem_id in the position record to the new owners Member ID (position was won in a contest)

Instead, I changed them in position_1.

When I realized what I had done, I also realized that I had no way to go back because there is nothing else that tracks who the original owner of that position was...

That is a mistake I won't make again because I'll write a script to do this from now on as well as track who the original owner was,
BUT, in the meantime I need to find the member Records in 'members' that don't have a matching record in position_1.

Could someone help me with the query to list the mem_id and user from 'members' that doesn't have a matching record in 'position_1' based on matching 'mem_id'?

It would be greatly appreciated, as this is a time sensative issue...

Thanks
Douglas

[pritaeas]

You can do a WHERE position_1 NOT IN (SELECT mem_id FROM members) If you want more detailed info, post your tables structures and some example data.

[smantscheff]

That is a mistake I won't make again because I'll write a script to do

Yes you will. And your database will crash someday. And then you'd better had some backup mechanism - and if you had, you might retrieve the lost information from there. So set the backup high on your priority list.

[showman13]

You can do a WHERE position_1 NOT IN (SELECT mem_id FROM members) If you want more detailed info, post your tables structures and some example data.

table 'members'
Field Type
mem_id int(8) auto increment - Member Id used to link to other table data
user varchar(30) Username for log in
pass varchar(32) Password for log in
bypass_purchase char(1) Switch to indicate if old member or new
plus about 30 other fields of info

mem_id user pass bypass_purchase Either 'P'ending or 'Y'es or 'N'o

10000001 user1 090cf2ebd033591e155ae38ba8acccbe Y

10000002 user2 e053a844d0c68d300164a79dde769e39 P

10000003 user3 f57644d65525a204c43cc63af63cffe2 N

table 'position_1'
Field Type
pos_id int(9) auto increment - Position Id
mem_id int(8) Member Id used to link to member Record
prev_pos_id int(9)
create_date datetime
last_update datetime
status char(1) 'A'ctive or 'C'ycled

pos_id mem_id prev_pos_id create_date last_update status Active / Cycled
100000001 10000001 0 2011-12-27 15:19:15 2011-12-27 15:19:15 C
100000002 10000001 0 2011-12-27 15:19:15 2011-12-27 15:19:15 C
100000003 10000001 0 2011-12-27 15:19:15 2011-12-27 15:19:15 C

if the member record has a bypass_purchase of 'P' or 'Y' and there is no matching mem_id in the position_1 table, then I need to see those records, displaying the mem_id and the user

Seems pretty simple, but I am totally baffled.
Thanks in advance for your assistance with this.

Douglas

[smantscheff]

select mem_id, user
from members
where (bypass_purchase = 'P' or bypass_purchase ='Y')
and not (mem_id in select mem_id from position_1)

or

select mem_id, user
from members left join position_1
on members.mem_id=position_1.mem_id
where (bypass_purchase = 'P' or bypass_purchase ='Y')
and position_1.mem_id is null

[showman13]

I copied and pasted both options and tested them with the following results.

The first one looked like it would work, and I'm guessing that it is very close...

select mem_id, user
from members
where (bypass_purchase = 'P' or bypass_purchase ='Y')
and not (mem_id in select mem_id from position_1)

or

Error

SQL query: Documentation

SELECT mem_id, user
FROM members
WHERE (
bypass_purchase = 'P'
OR bypass_purchase = 'Y'
)
AND NOT (
mem_id
IN SELECT mem_id
FROM position_1
)
LIMIT 0 , 30

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select mem_id from position_1)
LIMIT 0, 30' at line 4

select mem_id, user
from members left join position_1
on members.mem_id=position_1.mem_id
where (bypass_purchase = 'P' or bypass_purchase ='Y')
and position_1.mem_id is null

Error

SQL query: Documentation

SELECT mem_id, user
FROM members
LEFT JOIN position_1 ON members.mem_id = position_1.mem_id
WHERE (
bypass_purchase = 'P'
OR bypass_purchase = 'Y'
)
AND position_1.mem_id IS NULL
LIMIT 0 , 30

MySQL said: Documentation
#1052 - Column 'mem_id' in field list is ambiguous

[smantscheff]

Prepend the ambiguous field names with the table names:

SELECT members.mem_id, user
FROM members
LEFT JOIN position_1 ON members.mem_id = position_1.mem_id
WHERE (
bypass_purchase = 'P'
OR bypass_purchase = 'Y'

)
AND position_1.mem_id IS NULL

[showman13]

Prepend the ambiguous field names with the table names:

SELECT members.mem_id, user
FROM members
LEFT JOIN position_1 ON members.mem_id = position_1.mem_id
WHERE (
bypass_purchase = 'P'
OR bypass_purchase = 'Y'

)
AND position_1.mem_id IS NULL

Well, I got results that time, but there are 1497 records in the position_1 table, and I got 1509 records returned

What I expected was 2 records in the members table selection criteria that didn't have matching records in the position_1 table...

So, I think I need to keep working on it.

Can it be done without using a Left Join? (only because I have never understood them)

using this query

SELECT members.mem_id, members.user
FROM members
LEFT JOIN position_1 ON members.mem_id = position_1.mem_id
WHERE (
members.bypass_purchase = 'P'
OR members.bypass_purchase = 'Y'
)

[smantscheff]

It might help if you knew what you are doing.
I omitted the clause AND position_1.mem_id IS NULL in the SQL syntax formatting in my query above. Add it, and you will get more sensible results.
And yes, it can be done without a left join: see my example #1.
A left join contains all rows from the left table and all matching rows from the right table. It contains even rows where there is no match in the right table. For those rows any right table column expression evaluates to NULL.

[showman13]

It might help if you knew what you are doing.

You are right, it would help if I knew what I was doing...

And that is why I am here.

If I knew what I was doing, I wouldn't need to come here and ask questions so I can learn the things that I don't know.

I thought that was the point of the Discussion Community...

Apparently I didn't explain what I needed to do as well as I thought I did...

Basically, I have a member table that has about 1000 records in it with the primary key being 'mem_id'
about 800 of them have the field 'bypass_purchase' set to either 'P' or 'Y'
Each of those 800 are supposed to have a record in the 'position_1' table (a free position given to them)
The primary key on the position_1 table is 'pos_id' with a field called 'mem_id' to tie the records to the members records

I accidentally re-assigned the ownership of two of those records in 'position_1' this morning by changing the 'mem_id' field in position_1 to a different members ID

What I need to do is look at all the records in the members table with a P or Y in the bypass_purchase field, and determine which of them does NOT have a matching record in the position_1 table ( there should only be 2 of them)

Hopefully that is a little better explanation of what is needed.

Thanks
Douglas

[smantscheff]

I don't mind that you don't know how to tackle your problem - that's how we all got here in the first place. What upsets me is that you got an error message from mysql which tells you where the trouble with my solution is:

#1052 - Column 'mem_id' in field list is ambiguous

Your first explanation was fine already. I believe that my two attempts at a solution - if used as intended - should give you the desired result. So, did you include the clause "AND position_1.mem_id IS NULL" in your query or not?

[showman13]

I don't mind that you don't know how to tackle your problem - that's how we all got here in the first place. What upsets me is that you got an error message from mysql which tells you where the trouble with my solution is:

Your first explanation was fine already. I believe that my two attempts at a solution - if used as intended - should give you the desired result. So, did you include the clause "AND position_1.mem_id IS NULL" in your query or not?

Yes, in fact I did include the AND .... in the query, but the result set contained 600+ records...

Sorry for the delay, but was working with the owner of the website and explained what I was trying to do and she made a comment that keyed the resolution, and that took about 30 seconds to fix...

Didn't end up needing to do the query to resolve it.

I appreciate your attempts at helping me and hope that next time I have a question I will be more clear (attentive) in the process...

And BTW, when I saw the 'ambiguous' reference, while I know what the word itself means, I had no idea what it meant in reference to the query, or how to resolve the ambiguity.

Thanks again,
Douglas