Merging 2 Selects Into One AND Add a Variable 2

[jw2000]

I am try to merge merge these 2 statements into a Single select and add a checkedField with values Y or N. All records from PlaneType should be returned and checkField = Y if a pilot has that specific PlaneTypeId.

PlaneType Table:
PlaneTypeId (primary key), PlaneTypeDescription

PilotPlaneType Table:
PilotId, PlaneTypeId (no primary key for this table)


select * from PlaneType
select * from PilotPlaneType where PilotId = 13081


I am not sure where to put the logic for checkedField (Y or N) to get this to work:


SELECT a.*,
b.pilotId,
c.checked
FROM PlaneType a, Pilot b,
(SELECT planeTypeId checked
FROM PlaneType ) c
WHERE b.pilotId= 13081

 

[amrita418]

Is this what you are looking for?

select    a.*, 
          b.pilotid, 
          case when b.pilotid is null then 'N' else 'Y' end checked
from      planetype
          left outer join pilotplanetype
          on (a.planetypeid = b.planetypeid
              and b.pilotid = 13081)

Regards,
AA

 

[travisbrown]

SELECT 
PlaneTypeID, 
PlaneTypeDescription, 
CASE b.pilotId WHEN NULL THEN 'No' ELSE 'Yes' END AS CheckField
FROM PlaneType a 
LEFT OUTER JOIN PilotPlaneType b 
ON a.PlaneTypeID = b.PlaneTypeID
WHERE b.pilotId= 13081

Or use isNull, IIF, Coalesce.

 

[travisbrown]

Beat me to it.

 

[jw2000]

AA - I get "The column prefix 'a' does not match with a table name or alias name used in the query."

TravisBrown - Your select works but how can I return all PlaneTypes. Right now it only returns PlaneTypes with CheckField = 'Yes'

 

[amrita418]

I missed the alias names.

CODE
select    a.*, 
          b.pilotid, 
          case when b.pilotid is null then 'N' else 'Y' end checked
from      planetype [COLOR=red] a [/color]
          left outer join pilotplanetype [COLOR=red] b [/color]
          on (a.planetypeid = b.planetypeid
              and b.pilotid = 13081)

 

[travisbrown]

Sorry. Wasn't thinking.

SELECT
a.PlaneTypeID,
a.PlaneTypeDescription,
CASE  WHEN b.pilotId IS NULL THEN 'N' ELSE 'Y' END AS CheckField

FROM PlaneType a
LEFT OUTER JOIN 

(SELECT p.PlaneTypeID, p.pilotId  
FROM PilotPlaneType p WHERE p.pilotId = 12) b

ON a.PlaneTypeID = b.PlaneTypeID

 

[travisbrown]

Or

SELECT
a.PlaneTypeID, a.PlaneTypeDescription,
CASE WHEN b.pilotid IS NULL THEN 'N' ELSE 'Y' END CheckField
FROM
PlaneType a
LEFT OUTER JOIN pilotplanetype b
ON(a.planetypeid = b.planetypeid
AND b.pilotid = 13)

 

[jw2000]

I find joins difficult. Can you explain how your left outer join works?

 

[jbenson001]

Left Outer Join will show rows from the table on the LEFT of the Join, even if no matching row exists in the table on the RIGHT of the join. The values will display as being NULL.

Ex.
PlaneTypeid, PlaneTypeDescription, PiolotID
1 Boeing 747 NULL

 

[travisbrown]

Like jbenson001 wrote.

The left and right outer joins refer to the side the table is on that you want to display all rows. Non matches show null. This is a handy way to compare records in tables too, showing all the records in one table that are not in another.

Inner joins show only records where there is matching value in both tables.

Cross joins show all possible combinations. You'll probably rarely use cross joins.

If you google inner join, outer join, etc, there are some pretty good explanatory tutorials out there. Once you get a handle on joins, everything gets a bit easier.