title: Struggling with a sql query using joins
body: <p>I am currently developing an entity framework application in .NET and I am struggling with one query that I have to do with T-SQL.</p>
  
  <p>First of all I want to show you my model from the database and know if the model is right in terms of normalization. I want to mention that I am not an expert at SQL Server database design and maybe this would be easy for the experienced but for me it is not.</p>
  
  <p>The objective of the application I am currently developing is to implement an inventory control system with INS and OUTS to be able to know in which events this equipment had been used and when it was taken in and out with the precise date.</p>
  
  <p>For this example I am taking only 6 tables from my database design.</p>
  
  <p>I have 3 base tables and 3 auxiliary tables to relate the base tables.</p>
  
  <p><strong>Equipo</strong>:</p>
  
  <pre><code>Id | int | not null | primary key
  Marca | nvarchar(max) | not null
  Modelo | nvarchar(max) | not null
  </code></pre>
  
  <p><strong>Evento</strong>:</p>
  
  <pre><code>Id | int | not null | primary key
  Sede | nvarchar(max) | not null
  </code></pre>
  
  <p><strong>Salida</strong>:</p>
  
  <pre><code>Id | int | not null | primary key
  Fecha | datetime2(0) | not null
  </code></pre>
  
  <p>And the next three tables are the tables that relates the base tables.</p>
  
  <p><strong>EventoEquipo</strong>:</p>
  
  <pre><code>EventoId | int | not null | primary key (References Evento.Id)
  EquipoId | int | not null | primary key (References Equipo.Id)
  </code></pre>
  
  <p><strong>SalidaEvento</strong>:</p>
  
  <pre><code>SalidaId | int | not null | primary key (References Salida.Id)
  EventoId | int | not null | (References Evento.Id)
  </code></pre>
  
  <p><strong>SalidaEquipo</strong>:</p>
  
  <pre><code>SalidaId | int | not null | primary key (References Salida.Id)
  EquipoId | int | not null | (References Equipo.Id)
  </code></pre>
  
  <p>The SQL query that I need to find is a query that allows me to get the Events from an specific Equipment with their out dates and in case there is no out registered then I should get a null in the out date.</p>
  
  <p>I was able to develop this partial query:</p>
  
  <pre><code>DECLARE @EquipoId int
  SET @EquipoId = 4
  
  SELECT ev.Nombre 
  FROM Evento ev 
  INNER JOIN EventoEquipo eve ON eve.EventoId = ev.Id
  INNER JOIN Equipo eq ON eq.Id = eve.EquipoId
  WHERE eq.Id = @EquipoId
  </code></pre>
  
  <p>This query allows me to get the Events in which the Equipment was used but if I proceed with the same logic for the outs I only will get the events that have an out registered and I need to get all the events even if there is no out registered.</p>
  
  <p>Thank you in advance!</p>
  
  <p>PS: Sorry for the basic table visuals but I was unable to upload more than 2 images.</p>