Home / Questions / 561956
Accepted
jano
Asked: 2022-10-13 17:23:56 +0800 CST 2022-10-13 17:23:56 +0800 CST

Select a DB record twice but with different conditions

  • 772

I am working in SQL, I have 3 tables as you can see in the image, in the TipoAddressEntidad table I have if it is email or home address, what I require is to show the name (in entity), the email address and the home address.

I have the code separately but I need it to return 4 columns: ID, Name, Address, Email, in a single query. I hope you can understand and help me

select distinct en.Id, en.Nombre, de.Nombre Direccion from Persona.Entidad as en
inner join Persona.DireccionEntidad as de on de.EntidadId=en.Id
where de.TipoDireccionEntidadId=1

select distinct en.Id, en.Nombre, de.Nombre Email from Persona.Entidad as en
inner join Persona.DireccionEntidad as de on de.EntidadId=en.Id
where de.TipoDireccionEntidadId=2

Table Design

sql

1 Answers

  1. Best Answer

    You can do it in various ways.

    For the scenario I am not going to use the Person schema and directly use the dbo.

    Create table Entidad (id int, nombre varchar(100))
Create table DireccionEntidad(Id int, EntidadId int, Nombre varchar(100), TipoDireccionEntidadId int)
-- la tercera tabla no es necesaria, porque ya conoces los ids.

Insert into Entidad (id, nombre)
Values
(1,'Eva'),
(2,'Ana'),
(3,'Bea');
Insert into DireccionEntidad(Id, EntidadId,Nombre, TipoDireccionEntidadId)
values
(1,2,'[email protected]',2),
(2,2,'Calle Ana',1),
(3,3,'[email protected]',2),
(4,1,'Calle Eva',1);

    One option is to use a Left joinand apply the join pattern to it.

    select en.Id, 
       en.Nombre, 
       de1.Nombre As Direccion, 
       de2.Nombre As Email
from Entidad as en
left join DireccionEntidad as de1 on de1.EntidadId=en.Id And de1.TipoDireccionEntidadId = 1
left join DireccionEntidad as de2 on de2.EntidadId=en.Id And de2.TipoDireccionEntidadId = 2

    This way you can relate several times, giving them a different alias, to each of the tables.

    id Name Address E-mail
    1 Eve Eve Street null
    two Ana Anne Street [email protected]
    3 bea null [email protected]

    You have to take into account that if a person has several emails or addresses, you will have repeated person rows.

    Insert into DireccionEntidad(Id, EntidadId,Nombre, TipoDireccionEntidadId)
values
(5,2,'[email protected]',2);
    id Name Address E-mail
    1 Eve Eve Street null
    two Ana Anne Street [email protected]
    two Ana Anne Street [email protected]
    3 bea null [email protected]

    But if you add another address for Ana then, to which address is each of her emails given?

    Operator Apply Option

    When you only want one, you can use the apply operator.

    select en.Id, 
       en.Nombre, 
       dire.Nombre As Direccion, 
       email.Nombre As Email
from Entidad as en
outer apply (
    Select top(1) * from DireccionEntidad de1
    where de1.EntidadId = en.id
    and de1.TipoDireccionEntidadId = 1
    order by de1.Id-- este order by es con el que juegas para que solo sea 1 
) as dire
outer apply (
    Select top(1) * from DireccionEntidad de2
    where de2.EntidadId = en.id
    and de2.TipoDireccionEntidadId = 2
    order by de2.Id-- este order by es con el que juegas para que solo sea 1 
) as email

    Within the sets that are in parentheses, and that only return one result for each id of the outer set, you have an order by to specify which one you prefer.

    Apply operator

    • 1