Select only rows without other occurrence type

Question

I'm not sure how to explain this question so let me try with a simplified example.

I have two tables Tickets and TicketEntities:

Tickets

tID | Customer |  Val
----|----------|------
1   | Paul     |  20
2   | Paul     |  10
3   | Peter    |  15
4   | Jane     |  100
5   | Doe      |  400
6   | John     |  5

TicketEntities

EntityID |  TicketID |   Type
---------|-----------|--------
1        |   1       |   1     
2        |   1       |   2
3        |   3       |   1
4        |   4       |   1
5        |   5       |   2
6        |   6       |   2

I want to create two views (preferably so if one view is possible):

1. [Already solved] View to show me Tickets with TicketEntity type 1. My working solution: Desired Result:

   tID   |   Customer  | Val | EntityID |  Type
   

   -------|-------------|-----|----------|------- 1 | Paul | 20 | 1 | 1 3 | Peter | 15 | 3 | 1 4 | Jane | 100 | 4 | 1

   Note: Not actual query/results

   SQL

   SELECT *
   FROM Tickets AS t
   LEFT JOIN TicketEntities AS e ON t.ID=e.TicketID WHERE e.EntityTypeId = 1 
   

2. View with only Tickets with TicketEntity type 2 but not 1

   Desired Result:

   tID   |   Customer  | Val | EntityID |  Type
   ------|-------------|-----|----------|-------
   5     |     Doe     | 400 |  5       |   2
   6     |     John    | 5   |  6       |   2   
   

   SQL

   SELECT *
     FROM Tickets AS t
     LEFT JOIN TicketEntities AS e ON t.ID=e.TicketID WHERE e.EntityTypeId = 1 
   AND 
   NOT EXISTS (SELECT * 
     FROM dbo.Tickets AS t2
       INNER JOIN dbo.TicketEntities AS e2 ON t2.Id=e2.Ticket_Id 
       WHERE e2.EntityTypeId = 2)
   

3. OR more preferably, the single view:

   Desired Result:

   tID    |    Customer |  Val |  Entity_Type1 |  Entity_Type2
   -------|-------------|------|---------------|---------------
   1      |    Paul     |  20  |  1            |  2
   2      |    Paul     |  10  |               |
   3      |    Peter    |  15  |  1            |    
   4      |    Jane     |  100 |  1            |    
   5      |    Doe      |  400 |               |  2
   6      |    John     |  5   |               |  2  
   

   SQL

   I'm not sure how to go about it. Self join?

Finally, Improvements to the question are welcome. What's the best way to approach this problem?

Answer 1

I think two left joins from the Tickets table to the TicketEntities table should work here:

SELECT
    t.tID,
    t.Customer,
    t.Val,
    te1.Type,
    te2.Type
FROM Tickets t
LEFT JOIN TicketEntities te1
    ON t.tID = te1.TicketID AND te1.Type = 1
LEFT JOIN TicketEntities te2
    ON t.tID = te2.TicketID AND te2.Type = 2;

Demo

Note carefully that we do not impose any WHERE restrictions on the two TicketEntities joins. This is to ensure that we don't filter off any ticket records prematurely.

Answer 2

Try this. Since you already have your data simple Left join with master table of ticket will give the desired result as per your need.

; WITH CT1 AS (
    SELECT *
    FROM Tickets AS t
    LEFT JOIN TicketEntities AS e ON t.ID=e.TicketID WHERE e.EntityTypeId = 1 
), 
CT2 AS (
    SELECT *
      FROM Tickets AS t
      LEFT JOIN TicketEntities AS e ON t.ID=e.TicketID WHERE e.EntityTypeId = 1 
    AND 
    NOT EXISTS (SELECT * 
      FROM dbo.Tickets AS t2
        INNER JOIN dbo.TicketEntities AS e2 ON t2.Id=e2.Ticket_Id 
        WHERE e2.EntityTypeId = 2)
)
SELECT TICKET.ID, TICKET.CUSTOMER, 
      CASE WHEN ISNULL(CT1.EntityID,'')='' THEN CT2.VAL ELSE CT1.VAL END AS VAL,
      ISNULL(CT1.EntityID,'') AS ENTRY_TYPE1, ISNULL(CT2.EntityID,'') AS ENTRY_TYPE1
FROM TICKETS AS TICKET 
LEFT JOIN CT1 ON TICKET.ID=CT1.ID
LEFT JOIN CT2 ON TICKET.ID=CT2.ID

Answer 3

One query for many [Type] values. Let's try pivot function. Consider you must use aggregate function for grouping rows. Query may look like this:

WITH CTE AS (
   SELECT
      T.tID,
      T.Customer,
      T.Val,
      TS.Type 
   FROM
      dbo.Tickets T
      LEFT JOIN dbo.TicketEntities TS ON T.tID = TS.TicketID   
)
SELECT *
FROM
   CTE
   PIVOT (Max(Type) FOR Type IN ([1], [2])) P

You may extend this query replacing ([1], [2]) with select. See: SQL PIVOT SELECT FROM LIST (IN SELECT)