Clever Geek Handbook

MySQL Query from two tables

I have a database with two tables: members

and profilefields

.

members

has columns: ID, name, email


profilefields

has columns:ID, field1, field2, etc.

I would like to select name

and email

for each row in members

, based on the queryprofilefields

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

Get Id From profilefields

Where field1 = X

AND field2 = Y

Get name and email from members for those Ids

I am really new to this so I am very grateful for any help.

+3


source to share


4 answers


You can use the operator in

:



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


source


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 INNER JOIN

in a sentence FROM

to link tables in their field id

. The filter goes to the offer WHERE

, and the fields you want to return are in the offer SELECT

.

+4


source


If these tables are related to 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
+3


source


You can do it like this:

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

+1


source






More articles:


All Articles