CODEWITHSUNDEEP
mysqlmultiple-tables
Motaz M. El Shazly
Motaz M. El Shazly

Reputation: 49

SELECT multiple values to the same key in multiple tables

I have two tables in the following structure

table_1 

uid | name | age
1   | John | 24
2   | Adam | 35
3   | Sara | 26

table_2

id | uid | meta_key | meta_value
1  | 2   | location | NY
2  | 2   | school   | NYU
3  | 3   | Location | NY
4  | 3   | school   | XYZ
6  | 1   | location | NY
6  | 1   | school   | NYU

What I am trying to do is to select the users from table_1 where their location is NY and school is NYU

here is the query I tried using with no luck

SELECT
  tabl_1.uid
FROM `tabl_1`, `tabl_2`
WHERE
  tabl_1.uid = tabl_2.uid
  AND table_2.meta_key IN ('location', 'school')
  AND table_2.meta_value IN ('NY', 'NYU')
ORDER BY tabl_1.uid ASC

I have looked everywhere without any luck, if you have a query that works or a link to a solution that would much appreciated, thank you.

Upvotes: 3

Views: 2416

Answers (3)

neildt
neildt

Reputation: 5352

Try 

SELECT table_1.Uid
FROM table_1
INNER JOIN table_2 ON table_1.Uid = table_2.Uid
WHERE (table_2.meta_key = 'location' AND table_2.meta_value = 'NY')
AND (table_2.meta_key = 'school' AND table_2.meta_valaye = 'NYU')

Upvotes: 0

Anda Iancu
Anda Iancu

Reputation: 530

Try: http://sqlfiddle.com/#!2/cc913c/27

SELECT t.* FROM 
(
  SELECT a.uid, a.name, GROUP_CONCAT(b.meta_key, b.meta_value) AS mkv 
  FROM table_1 a
  LEFT JOIN table_2 b ON b.uid = a.uid 
  GROUP BY a.uid, a.name
) AS t
WHERE 
  t.mkv IN ("schoolNYU,locationNY");

Upvotes: 1

Marco
Marco

Reputation: 57593

You should try

SELECT t1.uid
FROM tabl_1 t1 INNER JOIN tabl_2 t2
ON t1.uid = t2.uid AND t2.meta_key = 'location' AND t2.meta_value = 'NY'
INNER JOIN tabl_2 t3
ON t1.uid = t3.uid AND t3.meta_key = 'school' AND t3.meta_value = 'NYU'

Check result on http://sqlfiddle.com/#!2/f35ef/1/0

Upvotes: 5

Related Questions