Re: selecting rows that match two criteria

[Jonathan Duncan]

Thank you very much Lachlan and SpamVortex!  I appreciate the help!

Jonathan
 
 
[EMAIL PROTECTED] 07/07 6:59 pm  
You can do it one of two ways..  Either you can do a 
self join like the following: 
 
select t1.userid 
from answers t1, 
 answers t2 
where t1.qid = 5 
 and lower(t1.answer)='student' 
 and t2.qid = 6 
 and lower(t2.answer) like 'edu%' 
 and t1.userid = t2.userid 
 
or you can use a union (available in 4.0.0 or greater) 
 
select userid 
 from answers 
where qid = 5 and lower(answer) = 'student' 
UNION 
select userid 
 from answers 
where qid = 6 and lower(answer) like 'edu%' 
 
union automatically does a distinct so you will get 
one row back for each user that answered that they are 
education students. 
 
 
--- Jonathan Duncan [EMAIL PROTECTED] wrote: 
I am trying to figure out what my select statement 
should be to combine 
both of these into one: 
 
SELECT userid FROM Answers WHERE answer like 
Education%; 
SELECT userid FROM Answers WHERE answer=Student; 
 
Table Answers looks like: 
-id int 
-userid int 
-answer text 
 
for each row there would be something like: 
+-++--+ 
| qid | userid | answer   | 
+-++--+ 
|  5  |   3000 | Student  | 
+-++--+ 
 
There are often multiple asnwers for each question. 
 
I want to select the userid's from the table where 
people answered 
Student in question 5 and Education in question 
6. 
 
I am using MySQL 4.08 so I can't do the subquery 
thing. 
 
Thanks, 
Jonathan Duncan 
 
-- 
MySQL General Mailing List 
For list archives: http://lists.mysql.com/mysql 
To unsubscribe:   
 
http://lists.mysql.com/[EMAIL PROTECTED] 
 
 
 
 
-- 
MySQL General Mailing List 
For list archives: http://lists.mysql.com/mysql 
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

selecting rows that match two criteria

[Jonathan Duncan]

I am trying to figure out what my select statement should be to combine
both of these into one:

SELECT userid FROM Answers WHERE answer like Education%;
SELECT userid FROM Answers WHERE answer=Student;

Table Answers looks like:
-id int
-userid int
-answer text

for each row there would be something like:
+-++--+
| qid | userid | answer   |
+-++--+
|  5  |   3000 | Student  |
+-++--+

There are often multiple asnwers for each question.

I want to select the userid's from the table where people answered
Student in question 5 and Education in question 6.

I am using MySQL 4.08 so I can't do the subquery thing.

Thanks,
Jonathan Duncan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: selecting rows that match two criteria

[Lachlan Mulcahy]

Jonathan,

I'm going to assume you want to match the answer fields in the way those 2
query examples do and that the qid field refers to the Question number.

SELECT
userid
FROM
Answers a1 JOIN Answers a2 ON
a1.userid = a2.userid
WHERE
a1.qid = 5 AND
a1.answer = 'Student' AND
a2.qid = 6 AND
a2.answer LIKE 'Education%';

The above joins the table back to another copy of itself by userid. This
allows you to find the students that answered both questions correctly. Give
it a go.

cheers
Lachlan


-Original Message-
From: Jonathan Duncan [mailto:[EMAIL PROTECTED]
Sent: Thursday, 8 July 2004 10:04 AM
To: [EMAIL PROTECTED]
Subject: selecting rows that match two criteria


I am trying to figure out what my select statement should be to combine
both of these into one:

SELECT userid FROM Answers WHERE answer like Education%;
SELECT userid FROM Answers WHERE answer=Student;

Table Answers looks like:
-id int
-userid int
-answer text

for each row there would be something like:
+-++--+
| qid | userid | answer   |
+-++--+
|  5  |   3000 | Student  |
+-++--+

There are often multiple asnwers for each question.

I want to select the userid's from the table where people answered
Student in question 5 and Education in question 6.

I am using MySQL 4.08 so I can't do the subquery thing.

Thanks,
Jonathan Duncan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: selecting rows that match two criteria

[Justin Swanhart]

You can do it one of two ways..  Either you can do a
self join like the following:

select t1.userid 
 from answers t1,
  answers t2
where t1.qid = 5
  and lower(t1.answer)='student'
  and t2.qid = 6
  and lower(t2.answer) like 'edu%'
  and t1.userid = t2.userid

or you can use a union (available in 4.0.0 or greater)

select userid 
  from answers
 where qid = 5 and lower(answer) = 'student'
UNION
select userid
  from answers
 where qid = 6 and lower(answer) like 'edu%'

union automatically does a distinct so you will get
one row back for each user that answered that they are
education students.


--- Jonathan Duncan [EMAIL PROTECTED] wrote:
 I am trying to figure out what my select statement
 should be to combine
 both of these into one:
 
 SELECT userid FROM Answers WHERE answer like
 Education%;
 SELECT userid FROM Answers WHERE answer=Student;
 
 Table Answers looks like:
 -id int
 -userid int
 -answer text
 
 for each row there would be something like:
 +-++--+
 | qid | userid | answer   |
 +-++--+
 |  5  |   3000 | Student  |
 +-++--+
 
 There are often multiple asnwers for each question.
 
 I want to select the userid's from the table where
 people answered
 Student in question 5 and Education in question
 6.
 
 I am using MySQL 4.08 so I can't do the subquery
 thing.
 
 Thanks,
 Jonathan Duncan
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:   

http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]