A question about complex SQL statement

Question

Table A has columns ID and AName, Table B has columns BName and ID.

B.ID is foreign key of A.ID.

Write a SQL statement to show data like: print column AName and C which describe whether Table B has ID in Table A, if exists 1 or else 0.

So if A is:

1 aaa
2 bbb

B is:

something,2

output is:

aaa,0
bbb,1

Answer 1

Something like the following will work for SQL Server

SELECT 
    A.AName,
    CASE 
        WHEN B.ID IS NOT NULL THEN 1
        ELSE 0 
    END AS C
FROM
    TableA A
LEFT JOIN
    TableB B
ON
A.ID = B.ID

See the LEFT JOIN? That would return a resultset including all rows from Table A and will include values from Table B where Table B derived fields are used in the SELECT clause and are satisfied by the JOIN criteria; in your example then, the value for B.ID will be 2 for the row in Table A with ID 2. Conevrsely, B.ID value will be NULL for the row in Table A with ID 1. In order to transform these values into 1 or 0 respectively then, we use a CASE statement and check whether the value is null or not and return the appropriate value based on the evaluation of this expression.

See this answer for more information on JOIN clauses

Answer 2

1. This is not a complex query.
2. The precise answer will depend on what database product you are using, which you do not state.
3. You need to look up the OUTER JOIN keyword in your SQL manual, as well as the CASE or IIF SQL function.

Answer 3

You may want to use a subquery instead of a LEFT JOIN:

Test Data (using MySQL):

CREATE TABLE a (id int, aname varchar(100));
CREATE TABLE b (bname varchar(100), id int);

INSERT INTO a VALUES ('1', 'aaa');
INSERT INTO a VALUES ('2', 'bbb');
INSERT INTO b VALUES ('something', '2');

Query:

SELECT 
    a.aname, 
    CASE EXISTS(SELECT b.id FROM b WHERE b.id = a.id) 
        WHEN 1 THEN '1' 
        ELSE '0' 
    END AS output
FROM a;

Output:

+-------+--------+
| aname | output |
+-------+--------+
| aaa   | 0      | 
| bbb   | 1      | 
+-------+--------+