CODEWITHSUNDEEP
sqlleft-joininner-join
Ayoub Salhi
Ayoub Salhi

Reputation: 452

Join two select statements one table

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

Upvotes: 1

Views: 45

Answers (3)

jose_bacoy
jose_bacoy

Reputation: 12704

If you want subquery, this will work too.

SELECT ord.Orderid,
(select Publication_Name 
 from tbl_Publications 
 where pubid=ord.PublicationID1) as publication_name1,
 (select Publication_Name 
 from tbl_Publications 
 where pubid=ord.PublicationID2) as publication_name2
FROM tbl_Orders ord

Upvotes: 0

Kasper Sanguesa-Franz
Kasper Sanguesa-Franz

Reputation: 617

You would use joins like this

SELECT 
    orderId, 
    b.Publication_Name as PublicationName1 
    c.Publication_Name as PublicationName2 
from tbl_Orders a
LEFT JOIN tbl_Publications b ON a.PublicationID1 = b.PubID
Left JOIN tbl_Publications c ON a.PublicationID2 = c.PubID

In most cases you wouldn't use sub queries as they do feel very heavy on the system - so if you can avoid it PLEASE work without sub queries.

I would move that to a table under it where you can have multiple order items.

The order inventory would be a link over to your own inventory and you would also include information about what the price was etc so further price changes doesn't affect you :)

Upvotes: 1

Racil Hilan
Racil Hilan

Reputation: 25361

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

Upvotes: 1

Related Questions