If no record is found in child table help!!

ldtiw

Hey,

ok, i have a table of users and a table containing profiles. how do i print out the users who after 30 days have not added profile??

Ive tried:

SELECT ipals_members.m_id , ipals_members.m_joined, DATE_ADD(ipals_members.m_joined,INTERVAL 1 MONTH) as expires, ipals_profile.m_id, ipals_profile.p_id
FROM ipals_members LEFT JOIN ipals_profile ON ipals_members.m_id = ipals_profile.m_id
WHERE ipals_profile.m_id != ipals_members.m_id AND '$date' >= '$expires'

but that brings up nothing. So basically if there is no record found in the profile table (related using m_id) and its been 30 days since they joined. I need to be able to get the list of users from the member table.

Any help would be great! thanks!

Danny

paulnaj

Assuming that everything else is correct ...

SELECT ipals_members.m_id , ipals_members.m_joined, DATE_ADD(ipals_members.m_joined,INTERVAL 1 MONTH) as expires, ipals_profile.m_id, ipals_profile.p_id 
FROM ipals_members LEFT JOIN ipals_profile ON ipals_members.m_id = ipals_profile.m_id 
WHERE ipals_profile.m_id != ipals_members.m_id AND '$date' >= expires

... just the last line ... you're referring to PHP variable $expires ... did you mean the SQL column expires.

Paul 🙂

ldtiw

Hey,

still cant get it to work. I was trying to refer to the

DATE_ADD(ipals_members.m_joined,INTERVAL 1 MONTH) as expires

i cant think how to get the records. Need them so i can delete the user.

Ok, ill add a little more detail. the table ipals_profile is referenced using m_id from the ipals_members table. If the m_id isnt found in the profile table, i need the details from the members table to show so i can (if i choose to do so) delete them.

But need the selection to also be based on the date. If it has been 30 days since they joined and the profile isnt found the details are shown!!

Tryed 101 ways and cant do it.

Any help would be great!

Danny

paulnaj

Hi Danny,

Try ...

SELECT  ipals_members.m_id, ipals_members.m_joined
	, ipals_profile.m_id, ipals_profile.p_id 
FROM 	ipals_members 
LEFT JOIN ipals_profile USING (m_id)
WHERE ipals_members.m_joined < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
AND ipals_profile.p_id IS NULL

... and , if no joy, try removing the last line to see what happens.

Paul🙂