Select into?

[ludmann]

I have 3 tables
Table 1: CurrentInstrumentset with columns ID, Status, Type
Table 2: InstrumentsetType with columns ID, Type, AutoID
Table 3: Auto with columns AutoID, AutoName

I want to get all the CurrentInstrumentsets, but want to display the AutoName for each

Like:

SELECT CI.ID, CI.Status, CI.Type, A.AutoName
FROM CurrentInstrumentset CI, Auto A, InstrumentsetType IT
WHERE CI.ID = IT.ID
AND IT.AutoID = A.AutoID

My problem is that in the CurrentInstrumentset if the Status = 'NOT EXIST' (then the CI.Type = 'unknown'), which

means there is no corresponding IT.Type in the InstrumentsetType.
So,also there is no associated AutoName or AutoID.

What I need is to display all the Instrumentsets like in the select statement above, but if there is no corresponding

IT.Type, IT.AutoID then just leave the cell empty or null or whatever.

Like

ID Status Type AutoName
123 EXIST 001 Auto
124 NOT EXIST unknown
125 NOT EXIST unknown
126 EXIST 003 Mixed

Can anyone tell me if this is possible?
If yes, how can I do it?

Thank you,

Marika

 

[vongrunt]

Use left outer joins (SQL-92 syntax)... and ISNULL() for face-lifting.