Query recs from one table that don't exist in 7 other tables

[showman13]

OK, this is probably a variation on a previous question but the original question was never resolved, so I will present it on it's own to avoid confusion.

I have a member data table called 'members'

Primary key is 'mem_id'

I have 7 different 'position' tables called
position_1, position_2, position_3, etc...

What I need is a single query to get all records from the members table
SELECT mem_id, user, fname, lname, email
FROM members
WHERE mem_status='F'
AND the mem_id does NOT exist in any of the 7 position tables in the field also called 'mem_id'

Simple, RIGHT? LOL

I'm hoping that it is relatively simple, or at least understandable for someone that really doesn't understand joins except for the simplest applications.

Thanks in advance.

Douglas

[hericles]

SELECT mem_id FROM members WHERE mem_id NOT IN (SELECT mem_id FROM position_1) would give you the men_id's in members that aren't in position_1.
You can extend the NOT IN (SELECT... to include the other position tables using a standard JOIN.
The result (untested) should be those mem_id's that only feature in the members table. I hope its right anyway;)

[showman13]

SELECT mem_id FROM members WHERE mem_id NOT IN (SELECT mem_id FROM position_1) would give you the men_id's in members that aren't in position_1. You can extend the NOT IN (SELECT... to include the other position tables using a standard JOIN. The result (untested) should be those mem_id's that only feature in the members table. I hope its right anyway;)

OK, thanks for the quick response...

I will play with that a little bit.. Sounds like it should get me close anyway.

[showman13]

I think You were correct... with one slight caveat...

I had issues with Join... But had great results with UNION

It does seem to be quite slow, so my guess is that there is a better approach, and possibly someone can point out to me what that might be, but here is what appears to be working

$sql = "
    SELECT mem_id, user, fname, lname, email, mem_status
    FROM members WHERE mem_status = 'F' AND mem_id NOT IN (SELECT mem_id FROM position_1 UNION SELECT mem_id FROM position_2 UNION SELECT mem_id FROM position_3 UNION SELECT mem_id FROM position_4 UNION SELECT mem_id FROM position_5 UNION SELECT mem_id FROM position_6 UNION SELECT mem_id FROM position_7)

    ";

Preliminary results appear to be correct, but have some manual testing and comparing to do in the morning when I am fresher, to be sure.

Thank you again for your response, and I'm marking this one Solved.

[smantscheff]

A cleaner table structure would have made this easier.
I'd propose to have a separate position table as a n:1 child table of members which records the positions:

create table positions (mem_id integer not null, position enum(1,2,3,4,5,6,7), foreign key (mem_id) references members (mem_id));

Then you could state your query as:

select mem_id from members where not (mem_id in( select (mem_id from positions));

or

select m.mem_id from members m left join positions p on m.mem_id=p.mem_id where p.mem_id is null;

[showman13]

A cleaner table structure would have made this easier. I'd propose to have a separate position table as a n:1 child table of members which records the positions:

create table positions (mem_id integer not null, position enum(1,2,3,4,5,6,7), foreign key (mem_id) references members (mem_id));

Then you could state your query as:

select mem_id from members where not (mem_id in( select (mem_id from positions));

or

select m.mem_id from members m left join positions p on m.mem_id=p.mem_id where p.mem_id is null;

Thank You for the Response...

If I understood what you were referring to, I could more easily evaluate the merits.

But as it is, I don't see myself changing the table structure.

It was designed this way because they have plans to add more table levels as they go along, so generating a new table for a level when they decide they want it will be quite simple.

By the looks of it I need to spend a few weeks just focusing on a MySql education.

If there are any other suggestions for speeding up the query, that don't involve re-structuring the tables, I wouldn't mind hearing them.

Thanks again
Douglas

[showman13]

This was originally solved a couple months ago, but I found a new issue to address regarding this query

I am finding that the structure of the query, while providing the correct result is taking a long time to complete whenever the page is loaded that utilizes it.

Basically this is the oritinal query

SELECT mem_id, user
    FROM members WHERE mem_status = 'F' AND mem_id NOT IN (SELECT mem_id FROM position_1 UNION SELECT mem_id FROM position_2 UNION SELECT mem_id FROM position_3 UNION SELECT mem_id FROM position_4 UNION SELECT mem_id FROM position_5 UNION SELECT mem_id FROM position_6 UNION SELECT mem_id FROM position_7)
    ORDER BY user

The result set is currently about 6,000 records, and takes on average 16 seconds to load...

I decided that I could get by with just the number of records and didn't really need to list them in a drop down box as originally planned, so I changed the query to this

SELECT count(mem_id)
    FROM members WHERE mem_status = 'F' AND mem_id NOT IN (SELECT mem_id FROM position_1 UNION SELECT mem_id FROM position_2 UNION SELECT mem_id FROM position_3 UNION SELECT mem_id FROM position_4 UNION SELECT mem_id FROM position_5 UNION SELECT mem_id FROM position_6 UNION SELECT mem_id FROM position_7)

I still get the same number of records in the count, but unfortunately it is still taking the same amount of time to do the query within a second or 2...

Is there another way to accomplish this that would be faster?

The Database is growing quickly and expect that it will be in the 100K + range before long, so this needs to get addresses sooner than later.

Thanks in advance for your response...

And if someone could tell me how to get this new system to email me when someone responds like the old system did, I'd appreciate that as well..

Douglas

[smantscheff]

In the long run you will change the table structure. If you're now starting to get in trouble because of performance issues, chances are that you will find a workaround - which will work until the next magnitude of records has been reached.

As a workaround you might try to split the UNION subquery into single conditions. I'm not sure how the query optimizer will handle it, but it might be worth a try:

SELECT count(mem_id)
FROM members WHERE mem_status = 'F' 
AND (mem_id NOT IN (SELECT mem_id FROM position_1))
AND (mem_id NOT IN (SELECT mem_id FROM position_2))
AND (mem_id NOT IN (SELECT mem_id FROM position_3))
AND (mem_id NOT IN (SELECT mem_id FROM position_4))
AND (mem_id NOT IN (SELECT mem_id FROM position_5))
AND (mem_id NOT IN (SELECT mem_id FROM position_6))
AND (mem_id NOT IN (SELECT mem_id FROM position_7))

or

SELECT count(mem_id)
FROM members WHERE mem_status = 'F' 
AND NOT (
mem_id  IN (SELECT mem_id FROM position_1)
OR  mem_id  IN (SELECT mem_id FROM position_2)
OR  mem_id  IN (SELECT mem_id FROM position_3)
OR  mem_id  IN (SELECT mem_id FROM position_4)
OR  mem_id  IN (SELECT mem_id FROM position_5)
OR  mem_id  IN (SELECT mem_id FROM position_6)
OR  mem_id  IN (SELECT mem_id FROM position_7)
)