Simple SELECT query which I can not get to work.

dcjones

Hi all

This is doing my head in.

I have 2 tables, members and payments. I want to select the member_id in members and any record in payments with the same member_id and produce a recordset with "members.member_id, paymments.member_id, payments,product_id"

If I run the query below it the results are all the records in the members table and all the records in the payments table

SELECT members.member_id , payments.member_id,product_id
FROM members,payments
WHERE members.member_id = 1338022

I know I am doing this wrong but where.

dcjones

Hi all.

I should have added that the members table has one record with the member_id = 1338022

and the payments table has one record with the member_id = 1338022

Thanks

alimadzi

You're missing the table join in your where clause. Also there's not much point in returning the member_id twice in your results when it's the same value each time.

SELECT members.member_id , product_id
FROM members, payments
WHERE
  members.member_id = payments.member_id AND
  members.member_id = 1338022

alimadzi

Actually, come to think of it, if member_id is in the payments table, why would you need to look at the members table at all? Just do this:

SELECT product_id
FROM payments
WHERE member_id = 1338022

The first rule of table joins is to make sure you actually need one.