result is not what expected...

[Guest]

hello,
I wrote this code

SELECT C_PROTO.ID, [MIA OTHDEMO].X
FROM C_PROTO LEFT JOIN (SELECT "X" AS X, D_OTHDEMO.ID AS ID FROM D_OTHDEMO)
AS [MIA OTHDEMO] ON [MIA OTHDEMO].ID=C_PROTO.ID

table C_PROTO has 114 records while D_OTHDEMO only 15 (and [MIA D_OTHDEMO]
too I suppose!).

What I would like is to have two columns: one listing all the ID from
C_PROTO and one showing "X" ONLY where C_PROTO.ID=D_OTHDEMO.ID (and [MIA
D_OTHDEMO].ID)

But this is nto working: it shows "X" on for any C_PROTO record!!!

Why?
Rocco

 

[Pat Hartman]

SELECT C_PROTO.ID, IIf(IsNull([MIA OTHDEMO].ID),Null, "X") As colX
FROM C_PROTO LEFT JOIN [MIA OTHDEMO] ON [MIA OTHDEMO].ID=C_PROTO.ID

 

[Guest]

Thank you very much!
Do you know what was wrong with my statement?

SELECT C_PROTO.ID, IIf(IsNull([MIA OTHDEMO].ID),Null, "X") As colX
FROM C_PROTO LEFT JOIN [MIA OTHDEMO] ON [MIA OTHDEMO].ID=C_PROTO.ID

hello,
I wrote this code

SELECT C_PROTO.ID, [MIA OTHDEMO].X
FROM C_PROTO LEFT JOIN (SELECT "X" AS X, D_OTHDEMO.ID AS ID FROM D_OTHDEMO)
AS [MIA OTHDEMO] ON [MIA OTHDEMO].ID=C_PROTO.ID

table C_PROTO has 114 records while D_OTHDEMO only 15 (and [MIA D_OTHDEMO]
too I suppose!).

What I would like is to have two columns: one listing all the ID from
C_PROTO and one showing "X" ONLY where C_PROTO.ID=D_OTHDEMO.ID (and [MIA
D_OTHDEMO].ID)

But this is nto working: it shows "X" on for any C_PROTO record!!!

Why?
Rocco

 

[Pat Hartman]

I don't really know. Probably the problem was that the inner select was
returning more than a single column. In any event a simple left join is
more efficient than a subselect.

Thank you very much!
Do you know what was wrong with my statement?

SELECT C_PROTO.ID, IIf(IsNull([MIA OTHDEMO].ID),Null, "X") As colX
FROM C_PROTO LEFT JOIN [MIA OTHDEMO] ON [MIA OTHDEMO].ID=C_PROTO.ID

hello,
I wrote this code

SELECT C_PROTO.ID, [MIA OTHDEMO].X
FROM C_PROTO LEFT JOIN (SELECT "X" AS X, D_OTHDEMO.ID AS ID FROM D_OTHDEMO)
AS [MIA OTHDEMO] ON [MIA OTHDEMO].ID=C_PROTO.ID

table C_PROTO has 114 records while D_OTHDEMO only 15 (and [MIA D_OTHDEMO]
too I suppose!).

What I would like is to have two columns: one listing all the ID from
C_PROTO and one showing "X" ONLY where C_PROTO.ID=D_OTHDEMO.ID (and [MIA
D_OTHDEMO].ID)

But this is nto working: it shows "X" on for any C_PROTO record!!!

Why?
Rocco