removing SQL results within the query.

[gerkintrigg]

is there a way in SQL to say "grab all the records that fit this description, then remove all these from it"?

 

I'm trying to work out how to get all members who have not uploaded a picture and all members who have and had it activated and set as primary in the "pictures" table.

 

I have no idea how to do it. I tried asking loads of people, but think i might be asking the wrong question.

 

Thanks.

[HuggieBear]

Yes, it's possible.

 

You need to let people know the column names in each table and I'm sure someone can help you out.

 

Regards

Huggie

[Jessica]

"is there a way in SQL to say "grab all the records that fit this description, then remove all these from it"?"

 

Remove from the table, or just remove from your results?

 

[gerkintrigg]

remove from the results...

So I need to select all member.id results (from member table) and then remove all results where primary!='y' and active !='y' from the pictures table.

 

Hope this makes it clearer.

 

Thanks

[Jessica]

So you can do that in your query.

"SELECT members.*, pictures.* FROM members, pictures WHERE pictures.primary != 'y' AND pictures.active != 'y' AND members.id = pictures.(whatever the memberid is?)";

[gerkintrigg]

Thanks Jesirose, that would work, except that some members don't have any pictures uploaded, thus there would be no record in the pictures table that corresponds to the member

.

[Jessica]

So what you'd need to do then is just get all of the members, then get all of the pictures where primary != 'y' AND active != y.  So you'll end up with 1 or 0 pictures per member, right? So when getting the pics out, save them in an array using the member's ID as the key. Then when you loop through your members, you can easily check if they have a picture in the array.

[rantsh]

you can always use a (LEFT) join. can you tell how your tables are setted up?