Re: only 1 row from a join

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 01/12/04 

Date: Sun, 11 Jan 2004 17:55:41 -0600

(reply cross-posted to microsoft.public.sqlserver.programming)

> I need to select one column of the first row of a left
> join operation to use it to insert one row in a table, so
> a put (in a trigger):

Which row is the 'first'? The row with the earliest datetime value? Rows
in a relational database table have no order so you need to specify your
rules that identify the 'first' row.

> SET @hora=(SELECT horasCumples.hora FROM horasCumples
> LEFT JOIN Vcitas ON horasCumples.hora=Vcitas.hora)

Only horasCumples.hora is referenced in the column so the LEFT JOIN to the
Vcitas table serves no purpose. You will get all rows from horasCumples
regardless of the Vcitas table contents. This may not be exactly what you
want but you can use the following to select the horasCumple.hora value with
the earliest date:

    SELECT @hora= MIN(hora) FROM horasCumples

There are some fundamental problems with your trigger that need to be
addressed. Importantly, more than one row can be inserted with a single
INSERT statement. The example below illustrates a set-based technique that
eliminates the need to select values from the inserted table into variables.

INSERT INTO Vcitas
    (
    usuario,
    fecha,
    hora,
    motivo,
    tipo,
    citaCon,
    aviso,
    descripcion,
    categoria,
    zona,
    horaAbsoluta,
    fechaAbsoluta
    )
SELECT
    i.usuario,
    i.fechanac,
    @hora,
    'Cumpleaņos',
    'P',
    i.alias,
    'Si',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
FROM inserted i 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"pedro j." <anonymous@discussions.microsoft.com> wrote in message
news:01d101c3d893$c5d9e710$a101280a@phx.gbl...
I need to select one column of the first row of a left
join operation to use it to insert one row in a table, so
a put (in a trigger):
CREATE TRIGGER NEWCUMPLE
ON contactos
AFTER INSERT
AS
declare @fecha datetime, @alias varchar(20),@usuario
varchar(20),@grupo varchar(20)
set @fecha =(select fechanac from inserted)
set @alias=(select alias from inserted)
set @usuario=(select usuario from inserted)
set @grupo= (select grupo from inserted)
IF (@fecha IS NOT NULL)   /* Tiene fecha de nacimiento */
 begin
 /***crear una cita de cumpleaņos ***/
  DECLARE @hora CHAR(5)
  SET @hora=(SELECT horasCumples.hora FROM horasCumples
LEFT JOIN Vcitas ON horasCumples.hora=Vcitas.hora)
  INSERT INTO Vcitas
(usuario,fecha,hora,motivo,tipo,citaCon,aviso,descripcion,c
ategoria,zona,horaAbsoluta,fechaAbsoluta)
  VALUES
(@usuario,@fecha,@hora,'Cumpleaņos','P',@alias,'Si',NULL,NU
LL,NULL,NULL,NULL)
   /*** prueba:  update contactos set fechanac
= '10/10/1980' where alias=@alias and usuario=@usuario and
grupo=@grupo ***/
 end
GO
this returns me a message saying that the subquery
returned more than 1 result and it is not allowed (in the
set @hora=(select... line)
What may I do?
Thnx in advance