update structure using join to update 2 tables

[showman13]

Good Morning All,

I have a query I need to do, but haven't been able to get it clear in my mind how to structure it...

I have two tables 'members' and 'subs_track'

they are linked by the 'mem_id' field in both

There is a single record for each member in the members table but there can be anywhere from 0 to 10 records in the subs_track table

Each of the records in the subs_track table has a status field that is Active or Failed
and the members records have a mem_status field that is Active / Free / Hold / Terminated

What I want to be able to do is check each member record against the subs_track table, and if there are NO Active records, then change the mem_status field in the member record to 'F'ree...

Doesn't seem that it would be that hard to do, but for some reason my brain is just not working.

Any help would be greatly appreciated.

Douglas

Here is a query that gives me the list of Active members with active records in the subs_track table, but I need to know the ones that don't have an active record in the subs_track table so I can change their status... Hope that makes sense.

SELECT a.mem_id
FROM members a, subs_track b
WHERE a.mem_id = b.mem_id
AND b.status = 'A'
AND a.mem_status = 'A'
GROUP BY a.mem_id 
ORDER BY a.mem_id

[madCoder]

Haven't done this in a while but I think you can use count() with HAVING:

SELECT a.mem_id, count(b.mem_id) as trackCount
FROM members a LEFT JOIN subs_track b USING(mem_id)
WHERE a.mem_id = b.mem_id
AND b.status = 'A'
AND a.mem_status = 'A'
GROUP BY a.mem_id 
HAVING trackCount = 0
ORDER BY a.mem_id

I haven't tested this but the gist of it is using a count and keeping those results with 0 entries on the right side of the join.

[showman13]

Haven't done this in a while but I think you can use count() with HAVING:

SELECT a.mem_id, count(b.mem_id) as trackCount
FROM members a LEFT JOIN subs_track b USING(mem_id)
WHERE a.mem_id = b.mem_id
AND b.status = 'A'
AND a.mem_status = 'A'
GROUP BY a.mem_id 
HAVING trackCount = 0
ORDER BY a.mem_id

I haven't tested this but the gist of it is using a count and keeping those results with 0 entries on the right side of the join.

Thank You for your response...

I plugged that query into the phpMyAdmin and got 0 results back.

What I need to do is an update of the member record setting the mem_status to Free if there aren't any matching records, but I guess that would be an easy enough transition to go from a select to an update, once the select is getting the correct data back.

That is the syntax for MySql, correct? Maybe I just need to play with it a bit.

[ebookfinder]

update member where memid not in select memid from track,
that's it

[showman13]

update member where memid not in select memid from track,
that's it

No idea what this meant...

Decided to take the long route around resolving this issue, so I'm marking this as solved to get it off your board..

thanks anyway.

Doug