SQL Left Join Subquery Alias

SQL LEFT JOIN Subquery Alias

You didn't select post_id in the subquery. You have to select it in the subquery like this:

SELECT wp_woocommerce_order_items.order_id As No_Commande
FROM  wp_woocommerce_order_items
LEFT JOIN 
    (
        SELECT meta_value As Prenom, post_id  -- <----- this
        FROM wp_postmeta
        WHERE meta_key = '_shipping_first_name'
    ) AS a
ON wp_woocommerce_order_items.order_id = a.post_id
WHERE  wp_woocommerce_order_items.order_id =2198 

Using an Alias on a Subquery and then doing a LEFT JOIN with it in the ON clause

You forgot to add the column name thing in ON clause. It should be like

SELECT * FROM 
  (SELECT DISTINCT Thing FROM BigList) AS SomeThing
  LEFT JOIN thingDetail ON SomeThing.Thing = thingDetail.Thing

Subquery table-alias not being recognized in JOIN-ON/WHERE

It's because you're not selecting itemID in your subquery. In addition, you're selecting the price column without aggregating by it. Try replacing

LEFT OUTER JOIN (SELECT price, COUNT(*) as stock
        FROM auction_house
        GROUP BY itemID) as AH on ii.itemID = AH.itemID

with

LEFT OUTER JOIN (SELECT itemID, price, COUNT(*) as stock
        FROM auction_house
        GROUP BY itemID, price) as AH on ii.itemID = AH.itemID

Join two subqueries aliases

wrong sintax you should use the left join for the subquery and not join the alias

  SELECT a_id, a_sid, a_pid, a_trait1, a_trait2, a_trait3 
  FROM  ( 
      SELECT  id AS a_id, sid AS a_sid, pid AS a_pid
        , max(trait1) AS a_trait1, trait2 AS a_trait2, trait3 AS a_trait3 
      FROM  table_a 
      GROUP BY id
  ) AS derived_a 
  left JOIN  (
      SELECT id AS s_id, trait10 AS s_trait10
        , trait11 AS s_trait11, trait12 AS s_trait12 
      FROM table_s) AS derived_s  ON derived_a.a_sid = derived_s.s_id

Incorrect Syntax Left Join subquery with aggregation

Your subquery is missing the SELECT keyword... And an alias too. You also need to align the subquery column alias for the date column with the outer query:

SELECT
    lastdate.[Max Date of Record],      ---------------> column alias
    billing.[Club Code],
    ...
FROM [dbo].[view_Club_Transactions_0100_(15) Dues_Summary] billing
LEFT JOIN (
    SELECT              -------------------------------> "SELECT" keyword
        MAX([Date of Record]) [Max Date of Record], ---> column alias
        [Member Code with Name]
    FROM [dbo].[view_Club_Transactions_0100_(15) Dues_Summary]
    GROUP BY [Member Code with Name]
) lastdate              -------------------------------> subquery alias
ON billing.[Member Code with Name]=lastdate.[Member Code with Name]
WHERE ...

I actually suspect that you can skip the self join and use window functions instead. That could be:

SELECT *
FROM (
    SELECT
       MAX([Date of Record]) OVER(PARTITION BY [Member Code with Name]) [Max Date of Record],
       [Club Code],
       [Club Name],
       [Member Code with Name],
       [Activity Code],
       [Category Code],
       [Dues Net Amount],
       [Dues Gross Amount],
       [Member Type Code],
       [Member Join Date],
       [Member Status Rule Code]
    FROM [dbo].[view_Club_Transactions_0100_(15) Dues_Summary] 
) t
WHERE [Member Status Rule Code] = N'ZRESIGN' AND [Activity Code] = N'DUES'

How do i use subqueries in a left join as a table

The problem is that ORM based queries automatically alias the selected fields, so you don't get

SELECT UID, SID ...

but

SELECT UID as School__UID, SID as School__SID

hence referring to sq.UID will fail, as no such field name was selected.

To avoid this problem you can either use an alias that matches the original field name:

->select([
    'UID' => 'UID',
    'SID' => 'SID',
    // ...
])

use the lower level database query that doesn't automatically create aliases:

$subquery = $scl
    ->getConnection() // connection() in older CakePHP versions
    ->newQuery()
    ->from($scl->getTable()); // table() in older CakePHP versions

or refer to the aliased fields in the main query:

'conditions' => [
    'sq.' . $scl->getAlias() . '__UID = TeacherID', // alias() in older CakePHP versions
    'sq.' . $scl->getAlias() . '__SID = ClassID',
]

---

Related Topics