SQL selecting records help

[kloner]

SELECT E.Name, E.GroupClass, E.NIIN
FROM Equipment E
WHERE (E.Assessor LIKE 'te%') AND (E.ArchivedBy IS NULL OR
WHERE (E.Assessor LIKE 'te%') AND
(E.ArchivedBy IS NULL OR E.ArchivedBy = '') AND
(E.ArchivedByID IS NULL OR E.ArchivedByID = 0) AND
(E.ArchivedByPosID IS NULL OR E.ArchivedByPosID = 0) AND
(E.NIIN = EPN.NIIN)


Now I also need another field called "MRN" from a table called "EquipPartNumbers".
How do i display the "MRN" field in my results??

If i do :

SELECT E.Name, E.GroupClass, E.NIIN, EPN.MRN
FROM Equipment E
WHERE (E.Assessor LIKE 'te%') AND
(E.ArchivedBy IS NULL OR E.ArchivedBy = '') AND
(E.ArchivedByID IS NULL OR E.ArchivedByID = 0) AND
(E.ArchivedByPosID IS NULL OR E.ArchivedByPosID = 0) AND
(E.NIIN = EPN.NIIN)

Now the above returns all the MRN's where the NIIN is in both the Equipment
and EquipPartNumbers tables. But what if the NIIN doesn't exist in the Parts Table??
I still want the record to exist in my results with a Null/empty entry for that MRN.

Any ideas??




kloner

 

[tlbroadbent]

Use a LEFT JOIN or LEFT OUTER JOIN.

SELECT E.Name, E.GroupClass, E.NIIN, EPN.MRN
FROM Equipment E
LEFT JOIN quipPartNumbers EPN
ON (E.NIIN = EPN.NIIN)
WHERE (E.Assessor LIKE 'te%') AND
(E.ArchivedBy IS NULL OR E.ArchivedBy = '') AND
(E.ArchivedByID IS NULL OR E.ArchivedByID = 0) AND
(E.ArchivedByPosID IS NULL OR E.ArchivedByPosID = 0)
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[kloner]

Thankyou for your help! That worked!

Can someone explain what the different JOIN's are, and what they do?

kloner

 

[tlbroadbent]

See SQL BOL and the MSDN website for an explanation.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_4ox9.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_09_3mk3.asp

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.