[SOLVED] Mysql question

[odd_concept]

I have a question but I don't know how to ask it simply so I must show you

 

These are the table

 

"application" table

applicant_idname

1Mike

2Joe

3Frank

 

"answers" Table

applicant_idquestion_idanswer

11Yes

12No

13Yes

25No

21Yes

 

I need to select a person that has answered YES to question_id 1 and No to question_id 2

So I tried something like this:

 

Select * from applications Left JOIN answers on applications.applicant_id = answers.applicant_id WHERE applicant_id = 1

 

applicant_idnamequestion_idanswer

1Mike1Yes

1Mike2No

1Mike3Yes

1Mike5No

 

That is as far as I can get...

I would like to do this without calling multiple queries to the database with PHP

 

Any help would be greatly appreciated... Thanks in advance

 

[mellis95]

I don't know if it is best practice, but it would be simpler if you had application table as follows:

 

"application" table

applicant_id

name

q1

q2

q3

etc....

 

Then you could do this:

 

Select * from applications Left JOIN answers on applications.applicant_id = answers.applicant_id WHERE applicant_id = 1 AND q1 like 'yes' AND q2 like 'no';

Better yet, instead of storing the words yes and no, make it an int field with a size of 1 and use a value of '0' for no and '1' for yes.

[akitchin]

I don't know if it is best practice, but it would be simpler if you had application table as follows:

 

"application" table

applicant_id

name

q1

q2

q3

etc....

 

Then you could do this:

 

Select * from applications Left JOIN answers on applications.applicant_id = answers.applicant_id WHERE applicant_id = 1 AND q1 like 'yes' AND q2 like 'no';

Better yet, instead of storing the words yes and no, make it an int field with a size of 1 and use a value of '0' for no and '1' for yes.

 

this is most certainly not best practice. in fact, it's terrible practice. you should never have potentially dynamic columns - what happens if you add a question? while you can easily insert a new entry into the `answers` table, you must add a new column and disrupt the structure of the `applications` column.

 

your better bet is to SELECT from the `answers` table and, if you want the name of the applicant, JOIN the `applications` table on the condition you are using above:

 

SELECT an.*, ap.name AS applicant_name
  FROM answers AS an
    LEFT JOIN applications AS ap
    ON an.applicant_id=ap.applicant_id
  WHERE
    (an.question_id=1 AND an.answer='YES') AND
    (an.question_id=2 AND an.answer='No')
  ORDER BY ap.name ASC

 

you may add as many criteria as you like to specify question answers.

[odd_concept]

akitchin, I have tried what you said, but I think there is still a problem, because there is an AND in the WHERE statement

 

(an.question_id=1 AND an.answer='YES') AND

 

That makes the query return no results... You are basically checking each row twice... (Please correct me if I am wrong)

 

[akitchin]

akitchin, I have tried what you said, but I think there is still a problem, because there is an AND in the WHERE statement

 

(an.question_id=1 AND an.answer='YES') AND

 

That makes the query return no results... You are basically checking each row twice... (Please correct me if I am wrong)

 

 

ah yes, forgive my reply - i hadn't thought through the question fully. you can achieve this using subqueries if you want to condense the query to one statement, however, this will be a rather inefficient process:

 

SELECT name
  FROM applications
  WHERE applicant_id IN (SELECT applicant_id FROM answers WHERE question_id=2 AND answer='YES') AND
  applicant_id IN (SELECT applicant_id FROM answers WHERE question_id=3 AND answer='No')
  ORDER BY name

 

hopefully someone with more SQL prowess can stumble on this thread and offer a better solution.

[odd_concept]

Thanks akitchin,

That worked great, it may not be the best solutions but it works.  At least for now or until I figure out something better. 

 

Thanks