CODEWITHSUNDEEP
mysqljoin
luqita
luqita

Reputation: 4077

MySQL: is this possible to do?

I am trying to accomplish something, but I'm a bit of a noob, and don't know if this can be done in one query.

I have two tables that look like this:

table 1:

link_id    target_country
-------------------------
1          5
2          3

table 2:

id_country   country
-------------------------
5            Belgium
5            Russia
2            USA

So, from the data above, link_id 1 has a target_country of 5, which means that this link belongs to Belgium and Russia (since id_country 5 in table 2 is linked to these countries)

My idea is that after passing several link_ids to a mysql query, I can obtain something like:

How can I do so with one query and only by passing link_ids? Thank you!

Upvotes: 0

Views: 51

Answers (4)

Fabian Barney
Fabian Barney

Reputation: 14549

SELECT t2.country, t1.link_id 
FROM table_1 t1, table_2 t2 
WHERE t1.target_country = t2.id_country

Upvotes: 1

Matt McHugh
Matt McHugh

Reputation: 4095

SELECT link_id, country
FROM table1 links
    INNER JOIN table2 countries ON links.target_country = countries.id_country

Upvotes: 2

ty812
ty812

Reputation: 3323

SELECT 
    table2.country 
FROM 
    table1, 
    table2 
WHERE
    table2.country_id = table1.target_country AND
    table1.target_country = 5;

make sure that you actually do have a primary index on table2 - the country_id cannot be a primary index if it is not unique!

Upvotes: 1

Glen Solsberry
Glen Solsberry

Reputation: 12320

SELECT country, link_id
FROM table_2
INNER JOIN table_1
ON table_2.id_country = table_1.target_country
WHERE table_1.link_id IN (1, 2);

This is a standard JOIN.

Upvotes: 1

Related Questions