Inner Joining the Same Table Multiple Times

Inner Joining the same table multiple times

You need to use table aliases. You have mentioned the same table more than once in the from clause. The query is something like this:

SELECT b.BlankTypeCode, b.BlankCode, pa1.Amount, pa1.Type, p1.PurchaseDate, pa2.DatePaid
FROM Blank b
INNER JOIN Ticket t
ON b.BlankCode = t.Blank_BlankCode
INNER JOIN MCO_Blank mb
ON b.BlankCode = mb.Blank_BlankCode
INNER JOIN Purchase p1
ON  t.PurchaseID = p1.PurchaseID
INNER JOIN Purchase p2
ON mb.PurchaseID = p2.PurchaseID
INNER JOIN Payment pa1
ON t.PurchaseID = pa1.PurchaseID
INNER JOIN Payment pa2
ON mc.PurchaseID = pa2.PurchaseID
WHERE pa1.Status = "Paid";

I had to make a guess at which payment and purchase is intended for the aliases. These may not be correct in the from and where clauses.

SQL: How to join the same table multiple times?

Having two join clauses is the right way to go. You're just missing giving them different aliases in order to distinguish between the two:

SELECT    td.name AS "Name",
          sci.country AS "Start Country",
          eci.country AS "End Country"
FROM      trip_details td
LEFT JOIN country_info sci ON sci.id = td.start_country_id
LEFT JOIN country_info eci ON eci.id = td.end_country_id

joining same table multiple times in Oracle

You can ensure that you only query fnd_currencies once by using a subquery factoring clause. That would look like this (and keeps @gordonlinoff happy by using ANSI 92 syntax):

with ccy as ( select * 
              from fnd_currencies )
select fca.descr as acct_currency
      ,fcr.descr as receipt_currency
      ,fcp.descr as project_currency
      ,pct.*
from pa_commitment_txns pct
left outer join ccy     fca on fca.currency_code = pct.acct_currency_code
left outer join ccy     fcr on fcr.currency_code = pct.receipt_currency_code
left outer join ccy     fcp on fcp.currency_code = pct.project_currency_code

Whether this will actually give you an improved execution time depends on the details of your data, which you haven't vouchsafed to us.

Inner join multiple times on same table on same column

You want decode two value for rank (one for newrank and one for promoterrank ) then you should join tha table rank two time eg:

    SELECT A.*
      , B.rank as newrank
      , C.rank as promoterrank
      DATE_FORMAT(promotiontime, '%Y-%m-%dT%H:%i:%s0Z') 
    FROM promotions A 
    INNER JOIN ranks B ON A.newrank = B.id 
    INNER JOIN ranks C ON A.promoterrank = C.id
    WHERE agent='".$_POST['agent']."' ORDER BY id DESC

anyway you should avoid the use of php var in sql code .. (you are at risk for sqlinjection) so take a look at prepared statement and binding param

Joining same table multiple times in SQL

You can Case expression:

SELECT tran_ref, SUM(CASE WHEN tran_id = 1 THEN AMOUNT END) AS AMOUNT_01,
SUM(CASE WHEN tran_id = 2 THEN AMOUNT END) AS AMOUNT_02,
SUM(CASE WHEN tran_id = 3 THEN AMOUNT END) AS AMOUNT_03,
COUNT(*) count_of_ref_rows
FROM TABLE1
GROUP BY tran_ref;

Also, you can use PIVOT clause for this:

select a.*, b.count_total from
(SELECT tran_ref, [1] as amount_01, [2] as amount_02, [3] as amount_03
FROM TABLE1
PIVOT(sum(amount) FOR tran_id IN ([1] , [2], [3])) pivot_table) a
 inner join (SELECT tran_ref, count(1) count_total
FROM TABLE1 group by tran_ref) b on (a.tran_ref = b.tran_ref);

SQL Server : join same column multiple times

you need to add the left join 3 instead of 1 for each column:

SELECT
  [Projects].[Project],
  Manager.[Resource] AS Manager,
  Leader.[Resource] AS Leader,
  Engineer.[Resource] AS Engineer
FROM [Projects]
LEFT JOIN [Resources] Manager
       ON [Projects].[Manager] = Manager.[ID]
LEFT JOIN  [Resources] Leader
       ON [Projects].[Leader] = Leader.[ID]
LEFT JOIN [Resources] Engineer
       ON [Projects].[Engineer] = Engineer.[ID]
WHERE [Project].[Projects] = 'pro_18001'

MySQL: How do I join same table multiple times?

You need to use multiple LEFT JOINs:

SELECT 
    ticket.ticket_id,  
    a1.attr_val AS attr_val1,
    a2.attr_val AS attr_val2,
    a3.attr_val AS attr_val3
FROM ticket
    LEFT JOIN attr a1 ON ticket.ticket_id=a1.ticket_id AND a1.attr_type=1
    LEFT JOIN attr a2 ON ticket.ticket_id=a2.ticket_id AND a2.attr_type=2
    LEFT JOIN attr a3 ON ticket.ticket_id=a3.ticket_id AND a3.attr_type=3

Here is an example: SQL Fiddle.

MySQL inner join query select from same table multiple times

First, of all you have bad DB design and I kindly advice to normalize your DB.

Second, if you can not re-design the DB you can use multiple joins with aliases like:

SELECT
    customer.name, customer.address, tbl_order.order_date, 
    tbl_order.product1_id, inv1.product1_name,
    tbl_order.product2_id, inv2.product2_name 
FROM tbl_order 
INNER JOIN customer ON tbl_order.customer_id = customer.id 
INNER JOIN inventory AS inv1 ON tbl_order.product1_id = inv1.id 
INNER JOIN inventory AS inv2 ON tbl_order.product2_id = inv2.id 
WHERE  YEAR(tbl_order.order_date)='$year'

---

Related Topics