WHERE Query Help

[Canman2005]

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

[champoi]

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

 

[Canman2005]

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?

[crtreedude]

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?

[crtreedude]

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.