Mulitiple Table query

Anon

If someone can help me with this, that would be great, or if you can just put me where to read about it because i did research on this topic, but didnt find any specifics on how to work this.

I have two tables, like this

Table A
name_id name address sex

Table B
purchase_id product_name purchase_date name_id

You can see Table A is customer info, Table B contains purchases by cusomters. name_id is the relationship between the two tables.

The Mysql query i want to run is: The user picks a particular product and wants to see how many Males and Females bought that product.

So first in Table B i need to choose the records for the correct product, record the name_id. then search Table A and only grab sex if name_id came from Table B. (did that make sense?)

EXAMPLE:
Table A
1 john xxxxxx M
2 steve xxxxxx M
3 jane xxxxxx F
4 joan xxxxxx F

Table b
26 Yo-Yo 01-01-2001 4
78 Jacket 01-01-2001 1
89 Yo-Yo 01-05-2001 3
34 Boat 02-01-2002 2

? How many Females bought Yo-Yo's?

Of course, the answer will be 2!

Having trouble putting that into a SQL statement

Anon

"SELECT sex FROM tablea,tableb WHERE tablea.name_id=tableb.name_id and tableb.product_id='$product_id'"

that should work for what you are trying to do.

Cheers

Joe

Anon

SELECT count(sex) from tableA,tableB where tableA.name_id=tableB.name_id and product_name='Yo-Yo' and sex='F'

Anon

Thanks guys, its working great

iifs044

A quick twist on Ivan's solution ... you could include SEX in the select statement, and leave it out of the WHERE clause, which would permit you to see all sexes, and how many each respective sex purchased. For example:

SELECT sex, count(sex)
from tableA,tableB
where tableA.name_id=tableB.name_id
and product_name='Yo-Yo'
group by sex

or if you want an entire report of all products purchased with counts by sex, use:

select product_name, sex, count(sex)
from tableA, tableB
where tableA.name_id = tableB.name_id
group by product_name, sex