SQL Left Join First Match Only

SQL Left Join first match only

Turns out I was doing it wrong, I needed to perform a nested select first of just the important columns, and do a distinct select off that to prevent trash columns of 'unique' data from corrupting my good data. The following appears to have resolved the issue... but I will try on the full dataset later.

SELECT DISTINCT P2.*
FROM (
  SELECT
      IDNo
    , FirstName
    , LastName
  FROM people P
) P2

Here is some play data as requested: http://sqlfiddle.com/#!3/050e0d/3

CREATE TABLE people
(
       [entry] int
     , [IDNo] varchar(3)
     , [FirstName] varchar(5)
     , [LastName] varchar(7)
);

INSERT INTO people
    (entry,[IDNo], [FirstName], [LastName])
VALUES
    (1,'uqx', 'bob', 'smith'),
    (2,'abc', 'john', 'willis'),
    (3,'ABC', 'john', 'willis'),
    (4,'aBc', 'john', 'willis'),
    (5,'WTF', 'jeff', 'bridges'),
    (6,'Sss', 'bill', 'doe'),
    (7,'sSs', 'bill', 'doe'),
    (8,'ssS', 'bill', 'doe'),
    (9,'ere', 'sally', 'abby'),
    (10,'wtf', 'jeff', 'bridges')
;

LEFT OUTER JOIN and only return the first match

The best way i can think of is using OUTER APPLY

SELECT *
FROM   Users u
       OUTER apply (SELECT TOP 1 *
                    FROM   Orders o
                    WHERE  u.ID = o.[USER]
                    ORDER  BY TIMESTAMP DESC) ou

Additionally creating a below NON-Clustered Index on ORDERS table will help you to increase the performance of the query

CREATE NONCLUSTERED INDEX IX_ORDERS_USER
  ON ORDERS ([USER], TIMESTAMP)
  INCLUDE ([ITEM], [SPEC]); 

LEFT JOIN only first row

@Matt Dodges answer put me on the right track. Thanks again for all the answers, which helped a lot of guys in the mean time. Got it working like this:

SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
    SELECT artist_id
    FROM feeds_artists fa 
    WHERE fa.feed_id = f.id
    LIMIT 1
)
WHERE f.id = '13815'

How to Join to first row

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle

How to left join with first matching row and fill the rest with null?

Thanks to @Akina I can provide the final version of code for my example:

SELECT name,
   animals.color,
   places.place,
   places.amount amount_in_place,
   CASE WHEN name = LAG(name) OVER (PARTITION BY name ORDER BY place)
   THEN 
      null
   ELSE 
      (SELECT GROUP_CONCAT("Amount: ",amount, " and price: ",price 
      SEPARATOR ", ") AS sales 
      FROM in_sale 
      WHERE in_sale.name=animals.name GROUP BY name) 
   END sales
FROM animals
LEFT JOIN places USING (name)
LEFT JOIN in_sale USING (name)
GROUP BY 1,2,3,4;

Note that it works only for MySQL version 8 or higher.

For older versions we can use self-defined variable:

SELECT x.*,
   @rowname,
   CASE WHEN name = @rowname
   THEN 
      null
   ELSE 
      (SELECT GROUP_CONCAT('Amount: ',amount, ' and price: ',price 
      SEPARATOR ', ') AS sales 
      FROM in_sale 
      WHERE in_sale.name=x.name GROUP BY name) 
   END sales,
   @rowname := name
   from
(SELECT name,
   animals.color,
   places.place,
   places.amount amount_in_place
FROM animals
LEFT JOIN places USING (name)
LEFT JOIN in_sale USING (name)
GROUP BY 1,2,3,4) as x
join (SELECT @rowname := 0) as r;

WARNING! As @philipxy pointed out in a comment, it can give very different and unexpected results. For me, comparing results in columns @rowname and @rowname := name and checking the sales column, works fine every time. (locally 10.4.11-MariaDB and on an external server MySQL 5.7.34-37-log - Percona Server - I'm joining over a dozen tables. It's returning over 20000 rows)

Join records only on first match

One option uses window functions:

select t1.*, t2.goal
from (
    select t1.*,
        row_number() over(partition  by day, user order by id) as rn
    from table1 t1 
) t1
left join table2 t2 on t2.day = t1.day and t2.user = t1.user and t1.rn = 1

A case expression is even simpler:

select t1.*,
    case when row_number() over(partition  by day, user order by id) = 1
        then t2.goal
    end as goal
from table1 t1

SQL Joining on first match only

If your version of MySql is 8.0+ you can use ROW_NUMBER() window function in the table Offers to get the row with the lowest offer_price for each offer_volume and then join to Bids:

SELECT  
    b.user_id AS bidder_user_id,
    o.user_id AS dealer_user_id,
    b.bid_volume,
    o.offer_volume,
    b.bid_price, 
    o.offer_price
FROM  Bids b 
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY offer_volume ORDER BY offer_price) rn 
  FROM Offers 
) o
ON b.bid_volume = o.offer_volume AND b.bid_price >= o.offer_price
WHERE o.rn = 1
ORDER BY b.bid_price DESC;

For previous versions, you could use NOT EXISTS:

SELECT  
    b.user_id AS bidder_user_id,
    o.user_id AS dealer_user_id,
    b.bid_volume,
    o.offer_volume,
    b.bid_price, 
    o.offer_price
FROM  Bids b 
INNER JOIN (
  SELECT o1.* FROM Offers o1
  WHERE NOT EXISTS (
    SELECT 1 FROM Offers o2 
    WHERE o2.offer_volume = o1.offer_volume AND o2.offer_price < o1.offer_price
  )
) o
ON b.bid_volume = o.offer_volume AND b.bid_price >= o.offer_price
ORDER BY b.bid_price DESC;

See the demo.

SQL Two joins; First one a left join; second only join when combination does not exist yet

You can try to use a subquery to UNION ALL two result sets, one is made from Main join Table1 another is made from Main join Table2.

Table2 need to write a column for Factor be 0

Then use the aggregate function to get your result.

Query 1:

SELECT Charge,ID,Name,SUM(Factor) Factor
FROM (
  SELECT m.Charge,m.ID,t1.Name , t1.Factor
  FROM `Main` m
  INNER JOIN `Table1` t1
  ON m.Charge = t1.Charge
  UNION ALL
  SELECT m.Charge,t2.ID,t2.Nom , 0 Factor
  FROM `Main` m
  INNER JOIN `Table2` t2
  ON m.ID = t2.ID
) t1
GROUP BY Charge,ID,Name
ORDER BY Charge

Results:

| Charge | ID | Name | Factor |
|--------|----|------|--------|
|   1001 |  5 |  ZZZ |     12 |
|   1001 |  5 |  XXX |     25 |
|   1001 |  5 |  OOO |      0 |
|   1002 |  8 |  AAA |     15 |
|   1003 | 10 |  YYY |      1 |
|   1003 | 10 |  UUU |      0 |
|   1003 | 10 |  BBB |     32 |
|   1006 |  5 |  ZZZ |      9 |
|   1006 |  5 |  XXX |     14 |
|   1006 |  5 |  RRR |     57 |
|   1006 |  5 |  OOO |      0 |

---

Related Topics