Page 1 of 1

Query Help

Posted: Wed Nov 17, 2004 11:26 am
by dstefani
I have a feeling I can't do a 'straight' query the way this client is asking me.

Here is the table schema:

Code: Select all

mysql> describe members;
+--------------------------+--------------+------+-----+------------+----------------+
| Field                    | Type         | Null | Key | Default    | Extra          |
+--------------------------+--------------+------+-----+------------+----------------+
| me_id                    | int(6)       |      | PRI | NULL       | auto_increment |
| me_company_id            | varchar(250) |      |     |            |                |
| me_company_name          | varchar(250) |      |     |            |                |
| me_first_name            | varchar(100) |      |     |            |                |
| me_last_name             | varchar(200) |      |     |            |                |
| me_phone                 | varchar(20)  |      |     |            |                |
| me_fax                   | varchar(20)  | YES  |     | NULL       |                |
| me_email                 | varchar(200) | YES  |     | NULL       |                |
| me_address_1             | varchar(150) | YES  |     | NULL       |                |
| me_address_2             | varchar(150) | YES  |     | NULL       |                |
| me_city                  | varchar(150) | YES  |     | NULL       |                |
| me_state                 | char(2)      | YES  |     | NULL       |                |
| me_zip_code              | varchar(20)  | YES  |     | NULL       |                |
| me_website               | varchar(250) | YES  |     | NULL       |                |
| me_member_type           | varchar(150) | YES  |     | NULL       |                |
| me_is_board_mem          | tinyint(1)   |      |     | 0          |                |
| me_cpc                   | tinyint(1)   |      |     | 0          |                |
| me_gov                   | tinyint(1)   |      |     | 0          |                |
| me_chapter               | varchar(50)  | YES  |     | NULL       |                |
| me_internet_referral_rep | varchar(150) |      |     | 0          |                |
| me_internet_referral_co  | varchar(150) |      |     | 0          |                |
| me_participation_points  | int(5)       | YES  |     | NULL       |                |
| me_reg_ref_other         | varchar(200) | YES  |     | NULL       |                |
| me_reg_city_list         | text         | YES  |     | NULL       |                |
| me_assoc_sup_list        | text         | YES  |     | NULL       |                |
| me_login                 | varchar(100) | YES  |     | NULL       |                |
| me_password              | varchar(20)  | YES  |     | NULL       |                |
| me_prime                 | char(1)      |      |     | 1          |                |
| me_is_analyst            | tinyint(1)   |      |     | 0          |                |
| me_is_admin              | tinyint(1)   |      |     | 0          |                |
| me_mem_pending           | tinyint(1)   |      |     | 0          |                |
| me_rec_add_by            | varchar(100) |      |     |            |                |
| me_rec_add_date          | date         |      |     | 0000-00-00 |                |
| me_rec_update_by         | varchar(100) | YES  |     | NULL       |                |
| me_rec_update_date       | date         | YES  |     | NULL       |                |
+--------------------------+--------------+------+-----+------------+----------------+
35 rows in set (0.04 sec)
And heres what he's asking:
Output anybody who is a regular member (me_member_type = regular) and a prime representative (me_prime = P) and who has a " Company internet referral " in the me_internet_referral_co field.

OR

Output anybody who is a Regular member and who is marked " internet referral " in the me_internet_referral_rep field


Please sort the output by participation points (highest to lowest) and then alphabetically by company name.
I have a feeling that I'm going to have to do something a bit more here.
I'm running MySQL 4.1 on his server.

Any help would be appreciated.

Thanks,

- dstefani

Posted: Wed Nov 17, 2004 11:40 am
by Weirdan
Ehh.... pretty straightforward query, at least as it seams to me:

Code: Select all

select 
  *
from 
  members
where
  ( me_member_type = 'regular' and me_prime = 'P' and me_internet_referral_co = 'Company internet referral' )
    OR
  ( me_member_type = 'regular' and me_internet_referral_rep = 'internet referral' )
order by 
  me_participation_points desc,
  me_company_name asc
I have a feeling it's something like student's appointment.... ;)

Posted: Wed Nov 17, 2004 11:51 am
by dstefani
Thanks,

I haven't had the need to use OR much on MySQL. I didn't think that I could group like that using ().
? How is this like 'students apointment' ?

Thanks again,

- dstefani

Posted: Wed Nov 17, 2004 11:54 am
by Weirdan
dstefani wrote: ? How is this like 'students apointment' ?
It was too simple and could be translated to SQL directly :)

Posted: Wed Nov 17, 2004 12:01 pm
by dstefani
OK, thanks.

- dstefani