CODEWITHSUNDEEP
sqlsql-server
Rasti
Rasti

Reputation: 25

SQL insert into with multiple selects

I'm trying to do a query where I want to insert in this table data from a few selections with specified conditions.

 INSERT INTO concediati (nume, prenume, idclient, idrent, idcar) 
 VALUES
     ((SELECT clients.Firstname FROM clients WHERE id = 1),
      (SELECT clients.lastname FROM clients WHERE id = 1), 
      (SELECT clients.id FROM clients WHERE idteam = 1), 
      (SELECT rentcar.id FROM rentcar WHERE idteam = 1), 
      (SELECT cars.id FROM cars WHERE idteam = 1) 
     );

The problem with this query is that it throws an error:

"Subquery returns more than 1 value This is not allowed when the subquery follows =, <, <=,>, or when used as an expression"

I tried to do this cross join query .. but it surpassed me .. I do not understand how to do or what to do.

I am doing this because I would like to "lay off" an employee and after all the data to be sent to a table or assigned to another employee.

Upvotes: 1

Views: 87

Answers (2)

Eric
Eric

Reputation: 3257

You could JOIN the 3 tables.

INSERT INTO concediati (nume, prenume, idclient, idrent, idcar) 
SELECT c.FirstName, c.LastName, c.id, rc.id, ca.id
FROM clients c
JOIN rentcar rc ON rc.idteam = c.id
JOIN cars ca ON ca.idteam = c.id
WHERE c.id = 1

Adding condition on the JOIN

INSERT INTO concediati (nume, prenume, idclient, idrent, idcar) 
SELECT c.FirstName, c.LastName, c.id, rc.id, ca.id
FROM clients c
JOIN cars ca ON ca.idteam = c.id
JOIN rentcar rc ON rc.idteam = c.id AND rc.cardid = c.id
WHERE c.id = 1

Upvotes: 2

Lee Mac
Lee Mac

Reputation: 16015

Assuming that each of your subqueries returns a single record, you could use a cross join in the following way:

insert into concediati (nume, prenume, idclient, idrent, idcar) 
select 
    c1.firstname,
    c1.lastname, 
    c2.id, 
    c3.id,
    c4.id
from 
    clients c1, 
    clients c2, 
    rentcar c3, 
    cars c4
where 
    c1.id = 1 and 
    c2.idteam = 1 and
    c3.idteam = 1 and 
    c4.idteam = 1

Alternatively, using joins:

insert into concediati (nume, prenume, idclient, idrent, idcar) 
select 
    c1.firstname,
    c1.lastname, 
    c2.id, 
    c3.id,
    c4.id
from 
    (
        (
            clients c1 inner join clients c2 on c1.id = c2.idteam
        )
        inner join rentcar c3 on c1.id = c3.idteam
    )
    inner join cars c4 on c1.id = c4.idteam
where 
    c1.id = 1

(Since the RDBMS was not specified, I've assumed MS Access syntax for the joins since it's more fussy when it comes to bracketing).

Upvotes: 1

Related Questions