result is not what expected...

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thank you very much!
Do you know what was wrong with my statement?

Pat Hartman said:
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
rocco said:
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
Click to expand...
Click to expand...
 
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.
rocco said:
Thank you very much!
Do you know what was wrong with my statement?

Pat Hartman said:
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
rocco said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top