SQL Two foreign keys linked to one primary key of another table to pull field from that table

The question title is probably confusing so 开发者_运维问答I'll try to clear it up.

I have two columns in a grid. State and Locality. I have two foreign keys in a table. Notification.fkState and Notification.fkLocality. They are both linked to the primary key of another table. Location.pkLocation I want the ALIASES of State and Locality to equal the Location.Name from the Location Table according to the foreign key value linked the primary key pkLocation.

Is there a statement like

SELECT Location.Name(WHERE Notification.fkState=Location.pkLocation) AS State, Location.Name(WHERE Notification.fkLocality=Location.pkLocation) AS Locality FROM Notification INNER JOIN Locality ON Notification.fkState=Location.pkLocation AND Notification.fkLocality=Location.pkLocation

My AND is currently an OR because when it is AND...it displays nothing.

Any help?

SELECT S.Name AS Locality, N.Name AS State
From Notification N
Inner Join Location S on N.fkstate=S.pkLocation
Inner Join Location L on  N.fkLocality=L.pkLocation