CODEWITHSUNDEEP
mysqldatabase
Dominik
Dominik

Reputation: 1383

How to make nested INSERT SELECT in which i could use field from previous select

I have got that statement:

select Imie as imie, Nazwisko as nazwisko, null, (SELECT id_adresu as adres from united_warehouse.adres 
where adres= (SELECT CONCAT(Ulica,' ', Nr_domu, ' ', Nr_mieszk, ' ', Miejscowosc) FROM Anna_database.osoba where Imie=imie))  as adres,
    Kod_poczt as kod_poczt, Kraj as id_kraju, Nr_tel as nr_tel from Anna_database.osoba;

enter image description here

In subquery i want to select row which contain CONCAT(Ulica,' ', Nr_domu, ' ', Nr_mieszk, ' ', Miejscowosc) which i'am selecting from 'osoba' table and it is the same string like in adres.adres table.

Unfortunately it returns me that Error Code: 1242. Subquery returns more than 1 row.

Upvotes: 1

Views: 52

Answers (2)

Muhammad Usman
Muhammad Usman

Reputation: 10148

Your select statement may return multiple values. It all depends what you want in final result of the query.

Case-1

If you want only one row to be returned by your subquery you can do something like

select Imie as imie, Nazwisko as nazwisko, null, (SELECT id_adresu as adres from united_warehouse.adres  
where  adres= (SELECT TOP(1) CONCAT(Ulica,' ', Nr_domu, ' ', Nr_mieszk, ' ', Miejscowosc) FROM Anna_database.osoba where Imie=imie))  as adres,
Kod_poczt as kod_poczt, Kraj as id_kraju, Nr_tel as nr_tel from Anna_database.osoba;

Case-2

You can do something like

select Imie as imie, Nazwisko as nazwisko, null, (SELECT id_adresu as adres from united_warehouse.adres 
where adres in (SELECT CONCAT(Ulica,' ', Nr_domu, ' ', Nr_mieszk, ' ', Miejscowosc) FROM Anna_database.osoba where Imie=imie))  as adres,
Kod_poczt as kod_poczt, Kraj as id_kraju, Nr_tel as nr_tel from Anna_database.osoba;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

The error say that you obtain more then a row for you inner select so you can use limit 1 for get only one result

  select 
        Imie as imie
      , Nazwisko as nazwisko
      , null
      , (SELECT id_adresu as adres 
            from united_warehouse.adres 
            where adres = ( SELECT CONCAT(Ulica,' ', Nr_domu, ' ', Nr_mieszk, ' ', Miejscowosc) 
                            FROM Anna_database.osoba 
                            where Imie=imie
                            limit 1) )  as adres
      , Kod_poczt as kod_poczt
      , Kraj as id_kraju
      , Nr_tel as nr_tel 
  from Anna_database.osoba;

Upvotes: 1

Related Questions