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
4条答案
按热度按时间vh0rcniy1#
Your where clause is checking if
'X'
is in the subquery, and it is, so this resolves towhere true
and returns all rows.kuuvgm7e2#
You sub query
returns two rows of result
To get the one row result probably you need to change your where condition like
This query + will give only the expected output.
Anyway try this and let me know.
sz81bmfz3#
You used a conditional,
if result of subquery is X (similar x=x is True). ignore conditional subquery and use only Top join
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
ukqbszuj4#
You can include the condition on
MASTER_ID
andcode
on the join condition :