sql / php loop question

vocoder

i have a table, lets say it looks like this

year, number, assigned, 1st_select, 2nd_select, 3rd_select

so some entries could look like this

2003, 10, true, true, false, true
2003, 10, true, false, true, false
2002,09,true,false,false,false
2002,09,true,false,false,false
2002,10,false,false,false,false

basically, i need to find all entries that have the same year and number....and have assigned set to true, but 1st,2nd, and 3rd select all set to false. the problem is that i need to find all years,number sequences that are identical..and have all selects set to false.

what is the best way to do this?

zhabala

I don't know what exactly you mean, but you can:

SELECT distinct year, number FROM table WHERE
1st_select AND 2nd_select AND 3rd_select AND assigned

to get all unique combinations

or just

SELECT * FROM table WHERE
1st_select AND 2nd_select AND 3rd_select AND assigned

to get all records where this condition is true

Zdenek

vocoder

well what i'm trying to do is....for every entry that is set to assigned, there should be at least one of the select marked true.

there are errors in the database, and i am trying to find which entries are listed as not being assigned....but have either 1st,2nd,or 3rd set to true (which normally should be impossible)

zhabala

So this one:

SELECT * FROM table WHERE
NOT 1st_select AND NOT 2nd_select AND NOT 3rd_select AND assigned

SELECT * FROM table WHERE
1st_select = 'f' AND 2nd_select = 'f' AND 3rd_select = 'f' AND assigned = 't'

I am not pretty sure, which one is for MySQL, but one of them should work

Zdenek

zhabala

Sorry I missread your explanation, please try this:

SELECT * FROM table WHERE
( 1st_select OR 2nd_select OR 3rd_select ) AND NOT assigned

SELECT * FROM table WHERE
( 1st_select = 't' OR 2nd_select = 't' OR 3rd_select = 't' ) AND assigned = 'f'

I am not pretty sure, which one is for MySQL, but one of them should work

Zdenek