Check for data existing in two rows from two tables

Question

I have a task to create a view that will add a column which checks if data in two rows of one table matches the same two rows of another table.

I have these tables:

Table 1
Name               ID                            Team
John               35                           1
Josh               89                           1
Josh               89                           2
Josh               89                           3
Jim                25                           2

Table2
Name             ID                            Team
Josh             89                           1
John             35                           1

I need to create a view adds a column which places a number 1 whenever table1.id and table1.team match table2.id and table2.team. Like this:

NEW view
NAME            ID                            TEAM                    CHECK
John            35                           1                              1
Josh            89                           1                              1
Josh            89                           2                              0
Josh            89                           3                              0
Jim             25                           2                              0

However, the closest I can get is this code:

Select distinct 
     table1.name, table1.id, table1.team,
                CASE
                     WHEN
                          (table1.id = table2.id)
                     AND 
                          (table1.team = table2.team)
                     THEN 1
                     ELSE 0
                END AS check
FROM 
     table1, table2 order by check desc;

Which returns:

NEW view
NAME         ID                            TEAM                    CHECK
John         35                           1                              1
Josh         89                           1                              1
John ----    35 -----                     1  -----                       0
Josh ----    89 -----                     1  -----                       0
Josh         89                           2                              0
Josh         89                           3                              0
Jim          25                           2                              0

without the distinct I get duplicates of every row and with distinct I get one row labled 0 for every row that's labled 1

I tried:

Select a.id, a.team, 
    CASE
        WHEN
            ( select  a.id from v_admins a
              Where  
                    a.id = (select  o.id from oncallorder o)
              And  
                    a.team = ( select o.team from oncallorder o) 
            )
        THEN 1
        ELSE 0
    END AS adminisoncall
    FROM v_admins a, oncallorder o 
    order by adminisoncall desc;

but it just errors out with: Error Code: 1242. Subquery returns more than 1 row

any thoughts?

The tables don't need to be joined. no data from one is being joined to another table. It's simply a check using a case statement. and then adding a 1 if the case statement is true and a 0 if it's false. The problem is that the very simple case statement is triggering false twice for everything it finds to label as false and once for everything it finds to label as 1. — Joe.Burch

Joe.Burch Joe.Burch · Accepted Answer · 2018-04-19T18:02:28

Thanks to help from one of my coworkers this is the solution:

Select table1.name, table1.id, table1.team,
                CASE
                    WHEN
                        table2.team is not null
                    THEN 1
                    ELSE 0
                END as check
FROM table1 left outer join table2 
    on table1.id = table2.id and table1.team = table2.team  
    order by check desc;

The case statement actually can include the section:

(table1.id = table2.id) AND (table1.team = table2.team)

however a not null is all that's really needed. The real issue came from having two tables in the FROM section. This in inadvertently created a cross join where it had to run the above select statement for both tables. Thus showing duplicates of every row. Therefore a specifically defined join was needed. An inner join would only show the data that exists in both.

However, a left outer join would show all the data in the left table and the data where it exists in the right table.

hopefully this helps others in the same situation.

Check for data existing in two rows from two tables

2 Answers