CODEWITHSUNDEEP
phpmysql
user1096972
user1096972

Reputation: 33

Compare two tables with mysql through php

Say I have these two mysql tables:

country
----
username        Country
John            USA
Sarah           Canada

fav_color
----
username        Color
John            Blue
Sarah           Green

How can I select Sarah's favorite color, but ONLY if she is in Canada?

Something like:

SELECT color FROM fav_color WHERE name='sarah' AND username=(a canadian username)

Upvotes: 0

Views: 1196

Answers (4)

Joachim Isaksson
Joachim Isaksson

Reputation: 181087

A pretty straight forward JOIN;

SELECT color FROM fav_color f
JOIN country c ON c.username=f.username
WHERE c.username='Sarah'
  AND c.country='Canada';

Demo here.

Upvotes: 3

Odinn
Odinn

Reputation: 808

select color from fav_color where 
    name='sarah' and 
    name in (
        select name from country where country='canada'
    )

Upvotes: 1

Ry-
Ry-

Reputation: 225281

How about using WHERE EXISTS?

SELECT color FROM fav_color
    WHERE EXISTS
        (SELECT username FROM country
             WHERE country.username = fav_color.username
             AND country.Country = 'Canada')

Upvotes: 1

octern
octern

Reputation: 4868

Select on the two tables and specify that you want to match records by username:

SELECT color FROM fav_color, country 
    WHERE fav_color.username='Sarah' AND
    fav_color.username = country.username AND
    country = 'Canada';

Upvotes: 1

Related Questions