V
V
vasenka2019-11-01 12:11:02
SQL
vasenka, 2019-11-01 12:11:02

How to write a join query with a selection condition for the second table?

Probably a simple question, but I don't see a solution yet. Given: two related tables. You need to combine them so that data from the second table is selected only for a specific match.
Sample data:

table1
|id|number|country|
|123|789-456-46|Mexico|
|654|65-741-697|England|

table2
|first_name|last_name|country|
|Helen|Fedorova|Russia|
|Marat|Saveliev|Mexico|

That is, you need to take from the second table only those users who have country = Mexico. It is clear that the beginning of the request will look like this:
select id.table1 as id, number.table1 as number, first_name.table2 as name,
last_name.table2 as surname from table1 join table2 on

Actually, how to add a selection condition to the union condition?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
I
irishmann, 2019-11-01
@vasenka

More or less like this

SELECT
    t1.id AS id,
    t1.number as number,
    t2.first_name as name,
    t2.last_name as surname
FROM
    table1 t1
    JOIN table2 t2 ON t1.country = t2.country
WHERE
    t1.country = 'Mexico'

The word numbermust be enclosed in quotation marks. `number`for MySQL or "number"for PostgreSQL
UPD. It is better to reconsider the structure. If there are repetitions in the first table, in the country field, then in the final result there will be duplications. Maybe in the second table the country field can be made a secondary key (link to the id of the first table).

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question