Canman2005 Posted December 13, 2008 Share Posted December 13, 2008 Hi all I have the following database itemid primary 1 1 1 0 2 1 3 0 3 0 4 0 The field `itemid`refers to the item ID in another database, the `primary` field refers to which rows are the primary rows, basically any rows that have the same `itemid` means they belong together. How can I do a QUERY to grab all rows where there is no `primary` set to 1, so with my above data, you would get the following itemid primary 3 0 3 0 4 0 it only returns this is because `itemid` 2 has its `primary` set to 1 and also `itemid` 1 has at least one of its rows `primary` set to 1 Does this make sense? Can anyone help Thanks Dave Quote Link to comment Share on other sites More sharing options...
champoi Posted December 13, 2008 Share Posted December 13, 2008 add this to your query, $sql = "SELECT * FROM `items` WHERE `primary`!='1' "; or this: $sql = "SELECT * FROM `items` WHERE `primary`<'1' "; or this: $sql = "SELECT * FROM `items` WHERE `primary`='0' "; depends on how you want to use it Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted December 13, 2008 Author Share Posted December 13, 2008 Sorry might not have explained it correct. I guess $sql = "SELECT * FROM `items` WHERE `primary`!='1' "; would work. But if there are two rows for example with the same itemid and one of those rows has `primary` set to 1, then it would not return either row. But then if there were two rows with the same `itemid` and neither of them have `primary` set to 1, then it would return them Does that make much sense? Quote Link to comment Share on other sites More sharing options...
crtreedude Posted December 13, 2008 Share Posted December 13, 2008 If I understand correctly, you should use a subquery select * from items where not exists (select * from item where primary = 1) In this way, if the itemid has ANY record where primary is set to one, it will not return that itemid Is this what you are looking for? Quote Link to comment Share on other sites More sharing options...
crtreedude Posted December 13, 2008 Share Posted December 13, 2008 Sorry, I didn't complete a lot of the statement select * from items a where not exists (select * from Items b where a.itemid = b.itemid and b.primary = 1) I haven't tested it, but something like that should do it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.