A question about complex SQL statement
Table A has columns ID and AName, Table B has columns BName and ID.
开发者_如何学JAVAB.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
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
- This is not a complex query.
- The precise answer will depend on what database product you are using, which you do not state.
- You need to look up the OUTER JOIN keyword in your SQL manual, as well as the CASE or IIF SQL function.
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 |
+-------+--------+
精彩评论