[SOLVED] SQL Statement - Select from two tables, create column if secondary table has related record

SQL Statement - Select from two tables, create column if secondary table has related record

I'm posting here because I have not been able to find what I'm looking for, or even the correct keywords to search on. If there are better answers that I was unable to find, please feel free to point me in that direction.

However I have two tables which Table 1 is the primary table, and I need to SELECT all records out of it and add an additional column in the SELECT that returns if any related records in Table 2.

I have boiled the problem down to the following and any help would be much appreciated.

Table 1 has a many relationship to Table 2
SELECT must return all rows from Table 1
SELECT must have an additional column (preferably a BOOLEAN/INTEGER) column that represents if there are any related records in Table 2.
SELECT must work in both Access and SQL Server

 TABLE 1
--------
GUID1 | DATA FIELD | DATA FIELD
GUID2 | DATA FIELD | DATA FIELD
GUID3 | DATA FIELD | DATA FIELD

 TABLE 2
--------
GUID1 | TABLE 1 GUID | DATA FIELD | DATA FIELD
GUID2 | TABLE 1 GUID | DATA FIELD | DATA FIELD
GUID3 | TABLE 2 GUID | DATA FIELD | DATA FIELD
GUID4 | TABLE 2 GUID | DATA FIELD | DATA FIELD


SELECTED TABLE ( 1 JOINED ON TABLE 2 )
--------
GUID1 | DATA FIELD | DATA FIELD | 1 (EXISTS IN TABLE 2)
GUID2 | DATA FIELD | DATA FIELD | 1 (EXISTS IN TABLE 2)
GUID3 | DATA FIELD | DATA FIELD | 0 (DOES NOT EXISTS IN TABLE 2)

Solution

You can use a LEFT OUTER JOIN with a case statement to check if the data in the second table is null. Here is an example:

SELECT First.*, 
   CASE 
     WHEN Second.DATA3 IS NULL
     THEN 0
     ELSE 1
   END
FROM First
LEFT OUTER JOIN Second ON First.GUID1 = Second.GUID1

SQL Fiddle: http://sqlfiddle.com/#!6/ab17a/1