sql query over multiple rows?

[rsiedl]

hi,

 

i have a database with the following:

 

Table structure (search_stats):

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

| Field  | Type      | Null | Key | Default | Extra          |

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

| id      | int(11)    |      | PRI | NULL    | auto_increment |

| PMID    | int(11)    |      | MUL | 0      |                |

| PMkey  | varchar(4) |      | MUL |        |                |

| PMvalue | longtext  |      |    |        |                |

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

 

Sample data:

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

| id    | PMID    | PMkey | PMvalue |

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

| 27941 |  7321569 | MH    | Female  |

| 27976 |  293324 | MH    | Adult  |

| 27982 |  293324 | MH    | Female  |

| 28039 |  1168727 | MH    | Female  |

| 28164 |  4493249 | MH    | Female  |

| 28165 |  4493249 | AD    | Brazil    |

| 28206 |  5545272 | MH    | Adult  |

| 28214 |  5545272 | MH    | Female  |

| 28252 |  5841289 | MH    | Female  |

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

 

can anyone suggest how i would write an sql statement to do something like:

 

select PMID from search_stats where MH="Female" and AD="Brazil"

(which should return just the PMID of 4493249)

 

* the above statement is just to demonstrate what i am trying to achieve

 

cheers,

reagen

[bubblegum.anarchy]

Use the JOIN syntax

[fenway]

You actually need a self-join for this...