Select distinct and multiple rows 1

[rsmithinfo]

My tables are little more complex but the example below explains my problem in more simple terms.

Table1

test_id
test_name

Table2

table2_id
test_id
test_questionyes1
test_questionyes2

I would like to select the distinct test_id's where all the rows in Table2 have questionyes1 set to 1 and there are no rows where test_questionyes2 is set to 1, so:

SELECT DISTINCT t1.test_id FROM Table1 t1 INNER JOIN Table2 t2 ON t1.test_id = t2.test_id where t2.test_questionyes1 = 1 and t2.test_questionyes2 = 0;

but that won't work for what I want it to return - it will return all test_ids that have test_questionyes1 set to 1, irrespective of if there are rows in table2 with t2.test_questionyes2 = 1.

e.g. there is a test_id 3 it has the following entries in Table2:

1 3 1 1
2 3 0 1

it should not return this test_id as there is a row where test_questionyes2 = 1 (the second row) but it will because the criteria are satisfied in the first row.

Does this make sense?

 

[r937]

select Table1.test_id 
  from Table1  
inner 
  join ( 
       select test_id
         from Table2 
       group
           by test_id
       having count(*)
            = sum(test_questionyes1)
          and count(
               case when test_questionyes2 = 1
                    then 937 
                 end
                   ) = 0
       ) as good_ones             
    on good_ones.test_id = Table1.test_id

r937.com | rudy.ca

 

[rsmithinfo]

r937,

OK thanks that looks like it complex enough Would you mind explaining it a little so that I know the reason behind it? I just hate to modify something without knowing how it's meant to work.

 

[rsmithinfo]

Also will this work in MySQL?

and finally can it be done on multiple fields, i.e. many different test_questionyes's?

 

[r937]

start by looking at the subquery

it performs a GROUP BY on each test

in the HAVING clause, the first condition ensures that...

all the rows in Table2 have questionyes1 set to 1

then the second condition in the HAVING clause ensures that...

there are no rows where test_questionyes2 is set to 1

this last condition is ensured simply by counting some non-null value (937 will always do in a pinch) whenever test_questionyes2 is set to 1, and then ensuring that the count is zero



if both HAVING conditions are true, then this test_id is one of the good ones, so INNER JOIN it to Table1, and bob's your uncle

;-)

r937.com | rudy.ca

 

[r937]

yes, it will work in MySQL

yes, you can extend this method to other questions

tip: if you want solutions that will work in MySQL, post future questions in the MySQL forum, not the ANSI SQL forum

r937.com | rudy.ca

 

[rsmithinfo]

Thanks for that - I will post in the MySQL one from now on!

Now time to try and fit this into a larger query that's being built using PHP

 

[rsmithinfo]

I know this should be a mysql question but it's to do with this thread. The sql above uses a nested select in the inner join - does this work with all mysql versions? I'm forced to be using 3.23.56 on my web server

 

[r937]

see, if you would've mentioned that at the outset, i wouldn't have taken all that time to write a solution that you can't use

sorry pal

p.s. your host is a dinosaur, do yourself a favour and find another host

r937.com | rudy.ca

 

[rsmithinfo]

Hi,

Sorry it didn't occur to me as I test locally using mysql 4. I know the host is a dinosaur - very annoying.

Thanks for all your help though.

Rob

 

[r937]

select Table1.test_id 
  from Table1  
inner 
  join Table2
    on Table2.test_id = Table1.test_id  
group
    by Table1.test_id
having count(*)
     = sum(Table2.test_questionyes1)
   and count(
         case when Table2.test_questionyes2 = 1
              then 937 
          end
            ) = 0

will be slightly slower than the subquery version, but will give the same results

r937.com | rudy.ca

 

[rsmithinfo]

Thanks very much