How to Limit Results of a Left Join

How to limit results of a LEFT JOIN

This is where ranking functions would be very useful. Unfortunately, MySQL does not yet support them. Instead, you can try something like the following.

Select ta.product_id, ta.product_name
    , tb.transaction_date
From tbl_product As ta
    Left Join   (
                Select tx1.product_id, tx1.transaction_id, tx1.transaction_date
                    , (Select Count(*)
                        From tbl_transaction As tx2
                        Where tx2.product_id = tx1.product_id
                            And tx2.transaction_id < tx1.transaction_id) As [Rank]
                From tbl_transaction As tx1
                ) as tb
        On tb.product_id = ta.product_id
            And tb.[rank] <= 4
Limit 10

Limiting a left join to returning one result?

The error is clear -- you just need to create an alias for the subquery following its closing ) and use it in your ON clause since every table, derived or real, must have its own identifier. Then, you'll need to include movie_id in the subquery's select list to be able to join on it. Since the subquery already includes WHERE popularity = 0, you don't need to include it in the join's ON clause.

LEFT JOIN (
  SELECT
    movie_id, 
    movie_name 
  FROM movies 
  WHERE popularity = 0
  ORDER BY movie_name
  LIMIT 1
) the_alias ON t1.movie_id = the_alias.movie_id

If you are using one of these columns in the outer SELECT, reference it via the_alias.movie_name for example.

Update after understanding the requirement better:

To get one per group to join against, you can use an aggregate MAX() or MIN() on the movie_id and group it in the subquery. No subquery LIMIT is then necessary -- you'll receive the first movie_id per name withMIN() or the last with MAX().

LEFT JOIN (
  SELECT
    movie_name,
    MIN(movie_id) AS movie_id
  FROM movies
  WHERE popularity = 0
  GROUP BY movie_name
) the_alias ON t1.movie_id = the_alias.movie_id

How to limit result set of an SQL LEFT JOIN query by count

One way is to create an inline view that gets the customers that have more than one order and the inner join to it. You could also do an IN or EXISTS if you don't like the JOIN

SELECT 
    `customers`.`id` AS `cust_id`,
    `orders`.`id` AS `order_id`
FROM
    `customers`
LEFT JOIN `orders` ON
    `customers`.`id` = `orders`.`customer_id`
INNER JOIN 
      (SELECT `customer_id `    
       FROM `orders`
       GROUP BY `customer_id`    
       HAVING COUNT(id) > 1) as `morethanone`
On
    `customer`.`id`  = `morethanone`.`custmor_id`
ORDER BY
    `cust_id`

LIMIT / Filtering on LEFT JOIN

Snowflake supports joining laterally. That is, on to a function or correlated sub-query. This allows you to join on to a query that returns just one row (per input row).

SELECT  
  purchases.date
 ,purchases.revenue
 ,clicks.campaign_id
FROM 
  purchases    
LEFT JOIN LATERAL
(
  SELECT
    campaign_id 
   ,user_id
   ,click_time                       
  FROM 
    campaign_clicks
  WHERE
            user_id = purchases.user_id
    -- only select campaign clicks that occurred before the purchase                
    AND click_time <  purchases.purchase_time
    -- only include clicks that occurred within 3 days of the purchase               
    AND click_time >= DATEADD(days, -3, purchases.purchase_time)
  ORDER BY
    click_time DESC
  LIMIT
    1                        
)
  AS clicks

MySQL left join with LIMIT 1 not returning desired result

If you want exactly one row per join you should use a UNIQUE (or PRIMARY) KEY of the joined table in the ON clause. That is probably srvc_test.srvcTestID.

SELECT lctn.testID AS assetID, lctn.ClientID, srvc_test.serviceDate, srvc_test.servicePassed
FROM lctn_test AS lctn
LEFT JOIN srvc_test ON srvc_test.srvcTestID = (
    SELECT srvc_test.srvcTestID
    FROM srvc_test
    WHERE srvc_test.testID = lctn.testID
    ORDER BY srvc_test.serviceDate DESC
    LIMIT 1)
WHERE lctn.ClientID = 34
ORDER BY assetID

How do I limit a LEFT JOIN to the 1st result in SQL Server?

Since it's SQL Server 2000 and ranking functions are out, you could make your subquery SELECTs aggregate:

SELECT UserID, MAX(PhoneNumber) AS HomePhone FROM [...] GROUP BY UserID

iff you don't care WHICH of a user's Home numbers are returned...

---

Related Topics