Join two select statements one table

Question

I would like to join two tables to show one result set. Any help would be appreciated

tbl_Orders:

┌─────────┬────────────────┬────────────────┐
│ OrderID │ PublicationID1 │ PublicationID2 │
├─────────┼────────────────┼────────────────┤
│       1 │              1 │              2 │
│       2 │              3 │              1 │
│       3 │              1 │              3 │
└─────────┴────────────────┴────────────────┘

tbl_Publications:

┌───────┬─────────────────────────┐
│ PubID │    Publication_Name     │
├───────┼─────────────────────────┤
│     1 │ "Dilevry of .."         │
│     2 │ "Pickup of ..."         │
│     3 │ "Emailing documents.."  │
└───────┴─────────────────────────┘

Result I'm looking for is:

┌──────────┬────────────────────────┬────────────────────────┐
│  OrderID │    PublicationName1    │   PublicationName2     │
├──────────┼────────────────────────┼────────────────────────┤
│        1 │ "Dilevry of .."        │ "Pickup of ..."        │
│        2 │ "Emailing documents.." │ "Dilevry of .."        │
│        3 │ "Dilevry of .."        │ "Emailing documents.." │
└──────────┴────────────────────────┴────────────────────────┘

The query I tried:

SELECT *  FROM
    (SELECT Organization_ID, OrganizationName, a.PublicationID2, Publication_Name
     FROM tbl_Orders a, tbl_Publications b
     WHERE a.PublicationID2= b.PubID) t1 
Left JOIN
    (SELECT b.PubID, PublicationName AS Publication_Name2
     FROM tbl_Orders a, tbl_Publications b
     WHERE a.PublicationID2= b.PubID) t2 ON t1.Pub_ID2 = t2.Pub_ID

Answer 1

You don't need subqueries. Simply LEFT JOIN the tbl_Publications twice, once on PublicationID1 and another on PublicationID2:

SELECT o.OrderID,
       p1.Publication_Name AS PublicationName1,
       p2.Publication_Name AS PublicationName2
FROM tbl_Orders o
LEFT JOIN tbl_Publications p1 ON o.PublicationID1 = p1.PubID
Left JOIN tbl_Publications p2 ON o.PublicationID2 = p2.PubID