mysql field entry lookup query... help needed

[acctman]

go to table rate_members get field m_id then go to rate_pictures where i_user = m_id check to see if any i_status entries have a 2 but no 0 or 1.

 

Hope that's not confusing what i'm trying to do is check all member ids and see which i_user does not have a picture marked with a "1" in the i_status field. one i_user can have multiple i_status entries , so all the entries if no entry has a 1 for that i_user then output that i_user/m_id number.

 

i_status = 1 main pic

i_status = 0 pending approval

i_status = 2 additional pics

 

i'm doing a database/user clean up of where there might have be a coding error and user accounts were given the wrong status number. some users where given a 2 instead of a 1. so there images are not showing. I need to get a print out of those users so i can fix it really quick

[fenway]

How about table structure and some queries?

[acctman]

How about table structure and some queries?

 

Table: rate_members

Field: m_id

39

40

41

 

Table: rate_pictures

Field: i_user

Field: i_status

 

So with those two tables... i want to grab all the m_id fields, then go to rate_pictures and match each m_id to i_user. an i_user entry can appear multiple times since its a picture table, so find all the matching i_user = m_id and only print out if the i_user = m_id does not have an i_status != 1 where i_admin = 1. so even if there is like 50 i_status entries for m_id (39) = i_user (39) if none of the i_status's = 1 and i_admin =1 then output the m_id. that'll let me know there is a problem with that profile.

[acctman]

this is what i've come up with so far, can anyone assist me.

 

SELECT i_user FROM rate_pictures WHERE i_status != 1 AND i_admin = 1 ( SELECT m_id FROM rate_members )

[acctman]

can anyone help? i know its a pretty difficult query since I'm pretty much trying to run two queries in one

[acctman]

this is how the table looks, and i need to retrieve 010000 since out of all of its entries there is no i_status = 2

 

tbl: rate_pictures

 

i_user    i_status

010000 1

010000 1

010000 1

010000 1

 

i_user    i_status

130000 1

130000 1

130000 1

130000 2

 

i_user    i_status

150000 1

150000 1

150000 1

150000 2

[cags]

Well that is as simple as...

 

SELECT * FROM table WHERE i_status != 2

 

... I really don't understand what your doing. It sound like you want a simple join. You mention at one point that m_user needs to match i_user, which can be done by doing something like...

 

SELECT t1.*, t2.* FROM table t1 JOIN other_table t2 ON t1.i_user=t2.m_user WHERE t1.i_status != 2

[acctman]

Well that is as simple as...

 

SELECT * FROM table WHERE i_status != 2

 

... I really don't understand what your doing. It sound like you want a simple join. You mention at one point that m_user needs to match i_user, which can be done by doing something like...

 

SELECT t1.*, t2.* FROM table t1 JOIN other_table t2 ON t1.i_user=t2.m_user WHERE t1.i_status != 2

 

i was thinking about it, and thought all i need to do is process one table instead of too. basically i'm looking for any i_user that does not have an i_status of 2, but the tricky part for me atleast is that I can't figure out how to process the i_status results as a group. Meaning i_user can have like 1-100 i_status entries so the query needs to look at all of those entries as a group... Find all the entries for 010000 then tell me if i_status = 2 is present in any of them.

[Gamic]

Did you mean something, more along the lines of, this?

SELECT I_USER
FROM RATE_PICUTRES T1 
WHERE I_STATUS <> 2
EXCEPT  --where the user id is not also in
SELECT I_USER
FROM RATE_PICUTRES T1 
WHERE I_STATUS = 2