CODEWITHSUNDEEP
mysqlsql
Mārcis
Mārcis

Reputation: 91

Make SQL query to return data from two tables using subqueries

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

Upvotes: 0

Views: 144

Answers (1)

Barmar
Barmar

Reputation: 781058

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

Upvotes: 1

Related Questions