CODEWITHSUNDEEP
mysqlsqldatabase
lcheney
lcheney

Reputation: 75

MySQL Query Of Two Tables

I have a database with two tables: members and profilefields.

members has the columns: ID, name, email
profilefields has the columns: ID, field1, field2, etc.

I'd like to select the name and email of each row in members, based on a query of profilefields

I think this is how it works, but I don't know how to make the query:

Get id from profilefields where field1 = X AND field2 = Y Get name and email from members for those IDs

I'm really new to this so I'd really appreciate any help.

Upvotes: 3

Views: 96

Answers (4)

arezkibe
arezkibe

Reputation: 13

You can do it like that :

Select ID, name, email from members where ID in ( Select id from profilefields where field1 = x and field2 =y)

Upvotes: 1

Maksym
Maksym

Reputation: 4584

If those tables related based on ID

SELECT m.name, m.email
FROM members m
JOIN profilefields p
ON m.ID=p.ID 
AND p.field1=X 
AND p.field2=Y

Upvotes: 3

JNevill
JNevill

Reputation: 50308

This should do the trick:

SELECT
    m.name,
    m.email
FROM
    members m
    INNER JOIN profilefields pf ON
    m.ID = pf.id
WHERE
    pf.field1=X AND
    pf.field2=Y

Here we use an INNER JOIN in the FROM clause to link the tables ON their id field. The filter goes into the WHERE clause, and the fields you want to bring back are up in the SELECT clause.

Upvotes: 4

Mureinik
Mureinik

Reputation: 312259

You could use the in operator:

SELECT name, email
FROM   members
WHERE  id IN (SELECT id
              FROM   profilefields
              WHERE  field1 = 'X' and field2 = 'Y')

Upvotes: 2

Related Questions