Re: Select member when it meets two requirements

Michael Stassen Tue, 30 Nov 2004 16:04:46 -0800

No, it won't. That will return every member_id that has either specialty_id=6, or specialty_id=33, or both. Mike only wants both.

There are 2 solutions. One is the self-join proposed by Johan, although you usually wouldn't put one of the requirements into the join condition. That is, I would have expected:

  SELECT t1.member_id
  FROM your_table t1
  JOIN your_table t2 ON t1.member_id = t2.member_id
  WHERE t1.specialty_id=6
    AND t2.specialty_id=33;

which should be equivalent. This query doesn't generalize so well, however, as you have to add a join for each specialty_id in your required list, but it can be done:

  SELECT t1.member_id
  FROM your_table t1
  JOIN your_table t2 ON t1.member_id = t2.member_id
  JOIN your_table t3 ON t1.member_id = t3.member_id
  WHERE t1.specialty_id=6
    AND t2.specialty_id=33
    AND t3.specialty_id=49;

The other option is this:

  SELECT member_id
  FROM your_table
  WHERE specialty_id IN (6, 33)
  GROUP BY member_id
  HAVING COUNT(*) = 2;

This generalizes easily. Simply change the HAVING clause to COUNT(*) = N, where N is the number of required specialty_ids in the IN list. For example:

  SELECT member_id
  FROM your_table
  WHERE specialty_id IN (4, 13, 16, 42)
  GROUP BY member_id
  HAVING COUNT(*) = 5;

DISTINCT will not help either way, unless you have duplicate rows. In that case DISTINCT "fixes" the self join method, but then the solution is to remove the duplicates, rather than using DISTINCT.

Michael

Graham Cossey wrote:

Am I missing something?

Will this not do the trick:

SELECT DISTINCT member_id
FROM table
WHERE specialty_id IN(6,33);

Graham

-----Original Message-----
From: Johan Höök [mailto:[EMAIL PROTECTED]
Sent: 30 November 2004 19:56
To: Mike Zornek
Cc: [EMAIL PROTECTED]
Subject: Re: Select member when it meets two requirements


Hi Mike,
you should be able to do:

SELECT DISTINCT t.member_id
FROM table t
INNER JOIN table t2 ON t2.member_id = t.member_id AND t2.speciality_id = 2
WHERE t.speciality_id = 6

/Johan

Mike Zornek wrote:

I'm very much a noob when it comes to MySQL .. Historically


I've only used

it for storage. I need help.

I have a table:


+--------------------+-----------------------+------+-----+-------
--+-------

---------+
| Field              | Type                  | Null | Key |


Default | Extra


+--------------------+-----------------------+------+-----+-------
--+-------

---------+
| memberspecialty_id | int(10) unsigned      |      | PRI | NULL    |
auto_increment |
| member_id          | smallint(10) unsigned |      | MUL | 0       |
|
| specialty_id       | tinyint(3) unsigned   |      | MUL | 0       |
|


+--------------------+-----------------------+------+-----+-------
--+-------

---------+
How would I select all distinct member_id that have a


specialty_id of 6 and

33?

Thanks!

~ Mike
-----
Mike Zornek
Web Designer, Media Developer, Programmer and Geek
Personal site: <http://MikeZornek.com>

--
Johan Höök, Pythagoras Engineering Group
                - MailTo:[EMAIL PROTECTED]
                - http://www.pythagoras.se
Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden
Phone: +46 8 760 00 10 Fax: +46 8 761 22 77


--
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: Select member when it meets two requirements

Reply via email to