SQL left join - and after on clause is not working

Question

I have a scenario in left join of SQL which is not generating required output which i need. Following is description in tabular form and my tried queries,

Table A

A_ID        // PK OF TABLE A
IS_ACTIVE   // VALUE=1 OR 0

Table B

B_ID        // PK OF TABLE B
A_ID        // FK OF TABLE A IN TABLE B

Sample Records of Table A

A_ID    IS_ACTIVE
1       1
2       0
3       1
4       0
5       0

Sample Records of Table B

B_ID    A_ID
1       1
2       1
3       4
4       4
5       4
6       4

Select * from A left join B on A.A_ID=B.A_ID

A_ID    IS_ACTIVE   B_ID    A_ID
1       1           1        1
1       1           2        1
2       0           NULL    NULL
3       1           NULL    NULL
4       0           3       4
4       0           4       4
4       0           5       4
4       0           6       4
5       0           NULL    NULL

Select * from A left join B on A.A_ID=B.A_ID and A.IS_ACTIVE=0

Following output is the actual output of above query with no effect to records by adding AND is_active=0 after ON clause.

A_ID    IS_ACTIVE   B_ID    A_ID
1       1           1        1
1       1           2        1
2       0           NULL    NULL
3       1           NULL    NULL
4       0           3       4
4       0           4       4
4       0           5       4
4       0           6       4
5       0           NULL    NULL

Following output is the required output which i need to solve my problem.

A_ID    IS_ACTIVE   B_ID    A_ID
1       1           NULL    NULL
1       1           NULL    NULL
2       0           NULL    NULL
3       1           NULL    NULL
4       0           3       4
4       0           4       4
4       0           5       4
4       0           6       4
5       0           NULL    NULL

I am facing problem in getting exact records which are required. I need all records from Table A and matching records from Table B but those records of Table B which are equal to is_active=0 of Table A.

Note : Query should show all records of Table A

Please help me how can i get this scenario in Left Join of SQL.

@JeremyC. did you read the question? it's in the on clause, there is no where clause in his select statement.... — Zohar Peled, Jun 17 '15 at 08:48
Your required output looks weird. e.g. the first two lines `is_active = 1` but `B_ID` still `null`? — Stefan Steinegger, Jun 17 '15 at 08:56
@islamuddin for me your query gives the expected output though — Jeremy C., Jun 17 '15 at 09:00
@StefanSteinegger because there is no b_id for that a_id in table b — Jeremy C., Jun 17 '15 at 09:00
@StefanSteinegger my bad, misread your comment, again, I have not been awake for long, it is supposed to be null when is_active is 1, it's supposed to show the value for b_id when is_active = 0 (I thought you were talking about the record for a_id 2) — Jeremy C., Jun 17 '15 at 09:28
@JeremyC. I see, I thought it was is_active = 1 ... sorry. I understand now. — Stefan Steinegger, Jun 17 '15 at 09:39

score 3 · Accepted Answer · answered Jun 17 '15 at 08:50

3

I tried your examples as code. And I get the result you needed. What is the problem?

CREATE TABLE #a(a_id int, is_active bit)
CREATE TABLE #b(b_id int, a_id int)

INSERT INTO #a(a_id,is_active)
VALUES(1,1),(2,0),(3,1),(4,0),(5,0)

INSERT INTO #b(b_id,a_id)
VALUES(1,1),(2,1),(3,4),(4,4),(5,4),(6,4)

SELECT * 
FROM #a as a
LEFT JOIN #b as b
        ON a.a_id = b.a_id
        AND a.is_active = 0

DROP TABLE #a
DROP TABLE #b

answered Jun 17 '15 at 08:50

Ionic

3,884
1
12
33

Please check again, this is not producing desired result. – Dev D Jun 17 '15 at 09:01
I get the same result as the questioner excepted. what is the difference? Do I oversee something? – Ionic Jun 17 '15 at 09:02
Please see the "required output " mentioned in question. It is not same which your query produced. Somewhere question is confusing. – Dev D Jun 17 '15 at 09:09
As you seen, the answer was marked as answer. So it seems to be the result. :-) – Ionic Jun 17 '15 at 09:10
Thats why I have said the question is confusing. It was not asked properly. – Dev D Jun 17 '15 at 09:11

score 0 · Answer 2 · answered Jun 17 '15 at 08:48

0

Have you tried:

Select * from A left join B on A.A_ID=B.A_ID 
Where A.IS_ACTIVE=0

answered Jun 17 '15 at 08:48

Dev D

225
1
13

SQL left join - and after on clause is not working

2 Answers2

Linked