double subquery...

can anyone tell me why this doesn't work? is a subquery (double)
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
and from tLabel where Label_id = '1'You could try:
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
union
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
or
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
from tGenere where generi_tipo = 'soul'
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'xx', 'www.pausini.it', label_nome, generi_id
and from tLabel where Label_id = '1'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Actually, thinking a bit more about this, these 2 queries aren't equivalent,
depending on whether duplicates are possible or not. If duplicaates are
allowed, UNION ALL comes into play, and if not and you use the alternative
solution, the 2nd separate query needs to use WHERE NOT EXISTS.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Your syntax apparently does not make sense. Can you elaborate on what
exactly you are trying to do here?
Without the structures of the tables tCantante, tGenere and tLabel as well
as the relationships among them, it is a bit hard to suggest an accurate
solution.
Anith|||basicly what i have to do is very easy...
i want a quey that insert a record in a table, 2 of the values that i
insert i want take from other 2 different tables!
so i want insert in tCantante
Cantanti_nome = 'Laura'
Cantanti_sitointernet='www.pausini.it'
cantanti_descrizione= the value coming "from tLabel where Label_id =
'1' "
cantanti_genere_id=the value coming "from tGenere where generi_tipo =
'soul'"
to do this i want to use the subquerys!
btw, if i would have only a value to insert, as the following example,
it works good:
--
INSERT INTO tCantante (cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id)
select 'Laura', 'www.pausini.it', 'xx', generi_id
from tGenere where generi_tipo = 'soul'
--
thank
francesco
Anith Sen ha scritto:

> Your syntax apparently does not make sense. Can you elaborate on what
> exactly you are trying to do here?
> Without the structures of the tables tCantante, tGenere and tLabel as well
> as the relationships among them, it is a bit hard to suggest an accurate
> solution.
> --
> Anith|||>> cantanti_descrizione= the value coming "from tLabel where Label_id = '1'[vbcol=seagreen]
What if there are more than one row in tLabel for Label_id value 1?
[vbcol=seagreen]
What if there are more than one row in tGenere for generi_tipo value 'soul'?
[vbcol=seagreen]
Subqueries and correlated subqueries are very common and not that hard to
come up with as long as the one who writes it is familiar with the table
structure and the nature of the data involved.
Given the above requirements assuming there are only one row in the
corresponding tables matching those mentioned values, one could come up with
an insert statement like:
INSERT INTO tCantante (
cantanti_nome, cantanti_sitointernet,
cantanti_descrizione, cantanti_genere_id )
SELECT 'xx', 'www.pausini.it',
( SELECT label_nome FROM tLabel WHERE Label_id = '1' ),
( SELECT generi_id FROM tGenere WHERE generi_tipo = 'soul' ) ;
But most likely there may be some relationships between these tables that
you have not mentioned in your post. It is much better for others to
understand your table structures and sample data before coming up with a
solution. For details refer to: www.aspfaq.com/5006
Anith