Make SQL query to return data from two tables using subqueries

Question

I need to return the car id number from the cars table and the owner's name and surname from the persona table.

Each persona has a car and one persona can have multiple cars.

I made SQL but it returns each car assigned to every persona.

SELECT a.id_number, b.owners 
FROM (
    SELECT number as id_number 
    FROM car 
    WHERE fk_ipasnieks IN (SELECT pk_kods FROM personas)
) as a,
(
    SELECT concat(name, ' ', surname) as owners 
    FROM personas 
    WHERE pk_kods IN (SELECT fk_ipasnieks FROM car)
) as b

Use a `JOIN` ? Table structures + sample data + expected output is welcome — Cid, Apr 01 '21 at 07:17
[Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) — Cid, Apr 01 '21 at 07:18

score 1 · Accepted Answer · answered Apr 01 '21 at 07:33

1

The subqueries need to return the columns that are used to relate the two tables. Otherwise you get a full cross product, pairing each owner with every car.

SELECT a.id_number, b.owners 
FROM (
    SELECT fk_ipasnieks, number as id_number 
    FROM car 
    WHERE fk_ipasnieks IN (SELECT pk_kods FROM personas)
) as a
JOIN (
    SELECT pk_kods, concat(name, ' ', surname) as owners 
    FROM personas 
    WHERE pk_kods IN (SELECT fk_ipasnieks FROM car)
) as b ON a.fk_ipasnieks = b.pk_kods

This would not normally be done using subqueries. The usual way to write this would be to join the tables directly.

SELECT c.number AS id_number, concat(p.name, ' ', p.surname) AS owner
FROM car AS c
JOIN personas as p ON c.fk_ipasnieks = p.pk_kods

answered Apr 01 '21 at 07:33

Barmar

741,623
53
500
612

Thank you, this seems to be the thing that I needed. Also a question, should I need to use one more JOIN if I have a third table that I need to add to my query and get data about the car from table 3? – Mārcis Apr 01 '21 at 07:36
Yes, you use a join for each table you want to combine with. – Barmar Apr 01 '21 at 07:39
Thanks. One more thing, I add one more JOIN and now this Join returns multiple car insurance dates, but I only need to get the car's last insurance date, not the car's all insurance dates. For example, I have a car with ID - 345 and it has 2 insurance dates -2010 and 2021 I need to get the 2021 date not both dates in the list. Sorry for my terrible english and thanks for your help. – Mārcis Apr 01 '21 at 08:47
See https://stackoverflow.com/questions/39420117/group-by-on-first-table-and-keep-the-highest-value-of-the-second-table/39423483#39423483 – Barmar Apr 01 '21 at 08:51

Make SQL query to return data from two tables using subqueries

1 Answers1