CODEWITHSUNDEEP
sqlsql-serverjoininner-joinnative-sql
HowToTellAChild
HowToTellAChild

Reputation: 739

Invalid SQL result in INNER JOIN

I have the following db (SQL Server) structure:

CREATE TABLE MASTER
(
    ID uniqueidentifier NOT NULL,
    NAME varchar(50) NOT NULL,
    ADDRESS varchar(200) NULL
)
;

CREATE TABLE CONNECTOR
(
    ID uniqueidentifier NOT NULL,
    CODE varchar(50) NOT NULL,
    MASTER_ID uniqueidentifier NULL
)
;

ALTER TABLE MASTER
    ADD CONSTRAINT PK_MASTER
    PRIMARY KEY (ID ASC)
;

ALTER TABLE CONNECTOR
    ADD CONSTRAINT PK_CONNECTOR_MASTER
    PRIMARY KEY (ID ASC)
;

ALTER TABLE CONNECTOR ADD CONSTRAINT FK_CONNECTOR_MASTER
    FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID) ON DELETE No Action ON UPDATE No Action
;

This is the set MASTER table:

ID                                     NAME ADDRESS
11111111-1111-0000-0000-000000000001    N1  A1
11111111-1111-0000-0000-000000000002    N2  A2
11111111-1111-0000-0000-000000000003    N3  A3

This is the set of CONNECTOR table:

ID                                     CODE MASTER_ID
22222222-1111-0000-0000-000000000001    X   11111111-1111-0000-0000-000000000001
22222222-1111-0000-0000-000000000002    Y   11111111-1111-0000-0000-000000000001
22222222-1111-0000-0000-000000000003    X   11111111-1111-0000-0000-000000000002
22222222-1111-0000-0000-000000000004    W   11111111-1111-0000-0000-000000000002
22222222-1111-0000-0000-000000000005    U   11111111-1111-0000-0000-000000000003

This is my query:

select * 
    from test.dbo.MASTER m
    inner join test.dbo.CONNECTOR c ON c.MASTER_ID=m.ID 
    where 'X' IN (select c2.CODE from CONNECTOR c2 where MASTER_ID='11111111-1111-0000-0000-000000000001');

And this is my result: (MASTER.ID, MASTER.NAME, MASTER.ADDRESS, CONNECTOR.ID, CONNECTOR.MASTER_ID)

11111111-1111-0000-0000-000000000001    N1  A1  22222222-1111-0000-0000-000000000001    X   11111111-1111-0000-0000-000000000001
11111111-1111-0000-0000-000000000001    N1  A1  22222222-1111-0000-0000-000000000002    Y   11111111-1111-0000-0000-000000000001
11111111-1111-0000-0000-000000000002    N2  A2  22222222-1111-0000-0000-000000000003    X   11111111-1111-0000-0000-000000000002
11111111-1111-0000-0000-000000000002    N2  A2  22222222-1111-0000-0000-000000000004    W   11111111-1111-0000-0000-000000000002
11111111-1111-0000-0000-000000000003    N3  A3  22222222-1111-0000-0000-000000000005    U   11111111-1111-0000-0000-000000000003

My Question is: Why? I am expecting only one row, this one:

11111111-1111-0000-0000-000000000001    N1  A1  22222222-1111-0000-0000-000000000001    X   11111111-1111-0000-0000-000000000001

Upvotes: 0

Views: 70

Answers (4)

SelVazi
SelVazi

Reputation: 16063

You can include the condition on MASTER_ID and code on the join condition :

SELECT * 
from MASTER m
inner join CONNECTOR c ON c.MASTER_ID=m.ID 
                       and c.code = 'X' and c.MASTER_ID='11111111-1111-0000-0000-000000000001'

Upvotes: 0

abolfazl sadeghi
abolfazl sadeghi

Reputation: 2368

You used a conditional,

'X' IN (select c2.CODE from CONNECTOR c2 
where MASTER_ID='11111111-1111-0000-0000-000000000001');

if result of subquery is X (similar x=x is True). ignore conditional subquery and use only Top join

select * 
 from test.dbo.MASTER m
inner join test.dbo.CONNECTOR c ON c.MASTER_ID=m.ID

else if result of subquery is not x (similar x!=other thing(c,b,..) is False). ignore join Top and Result is empty

If you want to your expected, you must use this code

select * 
    from dbo.MASTER m
    inner join  (
            select c2.CODE,MASTER_ID from CONNECTOR c2 
            where MASTER_ID='11111111-1111-0000-0000-000000000001'
            and c2.CODE='X'
    )c ON c.MASTER_ID=m.ID

Upvotes: 0

Pranav MS
Pranav MS

Reputation: 2296

You sub query

select c2.Code from Connector c2 where Master_id='11111111-1111-0000-0000-000000000001'

returns two rows of result

enter image description here

To get the one row result probably you need to change your where condition like

select * 
from test.master m
inner join test.Connector  c ON c.Master_id=m.ID 
where c.code='X' and c.Master_id='11111111-1111-0000-0000-000000000001'

This query + will give only the expected output.

enter image description here

Anyway try this and let me know.

Upvotes: 1

Dhwanit Shah
Dhwanit Shah

Reputation: 11

Your where clause is checking if 'X' is in the subquery, and it is, so this resolves to where true and returns all rows.

Upvotes: 1

Related Questions