Member Avatar for 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
  • 3 Contributors
  • 4 Replies
  • 130 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by showman13

Dani AI

Generated

Short summary: change a member's mem_status from active to free when that member has no subs_track row with an active status. Two safe ways follow (preview with a SELECT first). Notes on earlier replies: 's LEFT JOIN approach was nullified by placing join conditions in the WHERE clause (that turns a LEFT JOIN into an INNER JOIN). 's "NOT IN" idea works in many cases but will misbehave if the subquery can return NULLs; prefer NOT EXISTS or a LEFT JOIN/IS NULL pattern.

Preview (run this first):

SELECT m.mem_id
FROM members m
WHERE m.mem_status = 'A'
  AND NOT EXISTS (
    SELECT 1 FROM subs_track s
    WHERE s.mem_id = m.mem_id
      AND s.status = 'A'
  );

Recommended update (NOT EXISTS — safe with NULLs):

UPDATE members m
SET m.mem_status = 'F'
WHERE m.mem_status = 'A'
  AND NOT EXISTS (
    SELECT 1 FROM subs_track s
    WHERE s.mem_id = m.mem_id
      AND s.status = 'A'
  );

Alternative (LEFT JOIN / IS NULL):

UPDATE members m
LEFT JOIN (
  SELECT DISTINCT mem_id FROM subs_track WHERE status = 'A'
) s ON s.mem_id = m.mem_id
SET m.mem_status = 'F'
WHERE m.mem_status = 'A'
  AND s.mem_id IS NULL;

Practical tips: back up the table or run inside a transaction; run the SELECT preview and only then the UPDATE; limit updates to mem_status='A' to avoid accidental changes to other states. For large tables, update in batches and ensure indexes on subs_track(mem_id, status) (or status, mem_id) and on members(mem_id, mem_status) to keep the query efficient. If mem_status stores the full word "Free" instead of the code 'F', use that literal in the UPDATE.

Recommended Answers

All 4 Replies

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for ebookfinder

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

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.