Select First Record in a One-To-Many Relation Using Left Join

Select the first record in a one to many relation using left join

It seems you don't want the "first" row, but just two different ones based on the language. So you need to joins to the translation table: one for the "title" and one for the "description"

select p.*, t.description as title, d.description
from product p
  left join product_translations t 
         on t.product_id = p.id 
        and t.language = 'en'
        and t.column = 'title'
  left join product_translations d 
         on d.product_id = p.id 
        and d.language = 'en'
        and d.column = 'description'

If you want to retrieve this with a "primary" and "fallback" language, you can do something like this:

with product_texts as (
  select t.product_id, t.value as title, d.value as description, t.lang
  from product_translations t
    join product_translations d 
      on d.product_id = t.product_id
     and d.lang = t.lang
     and d."column" = 'description'
  where t."column" = 'title'   
    and t.product_id = 3
    and t.lang in ('de', 'en')
) 
select t.*
from product_texts t
order by case 
           when t.lang = 'de' then 1
           else 2
         end
limit 1

As it is a bit complicated to always join against that, you can create a function that does this:

create function get_translations(p_product_id int, p_lang text, p_fallback_lang text default 'en')
  returns table (product_id int, title text, description text, lang text)
as
$$ 
  with product_texts as (
    select t.product_id, t.value as title, d.value as description, t.lang
    from product_translations t
      join product_translations d 
        on d.product_id = t.product_id
       and d.lang = t.lang
       and d."column" = 'description'
    where t."column" = 'title'   
      and t.product_id = 3
      and t.lang in (p_lang, p_fallback_lang)
  ) 
  select t.*
  from product_texts t
  order by case 
             when t.lang = p_lang then 1
             else 2
           end
  limit 1
$$
language sql
rows 1
stable;

Then you can do:

select *
from products p
  left join lateral get_translations(p.id, 'de', 'en') on true

There is next to none overhead in calling the function as this will be inlined - just as if you had written the function's query into the main query.

For good performance you want an index on (product_id, lang, "column") or maybe even two filtered indexes:

create index product_lang_title 
  on product_translations (product_id, lang)
where "column" = 'title';

create index product_lang_descr 
   on product_translations (product_id, lang)
where "column" = 'description';

Online example

PL-SQL - First record in a One-to-Many relation left join

You can use the min() aggrenate function with the keep (dense_rank first ...) syntax to get the 'first' matching data from the outer-joined table:

select a.code, a.emp_no,
  min(b.city) keep (dense_rank first order by city, county) as city,
  min(b.county) keep (dense_rank first order by city, county) as county
from table_a a
left join table_b b on b.code = a.code
group by a.code, a.emp_no
order by a.code, a.emp_no;

      CODE     EMP_NO CITY  COUNTY  
---------- ---------- ----- --------
       101      11111 City1 Country1
       102      22222 City4 Country2
       103      33333 City5 Country3
       104      44444               
       105      55555               

You have to define what 'first' means though - I've gone with order by city, county inside the keep clause, but you may have another column you haven't shown that should dictate the order.

(You can order by null to make it somewhat arbitrary, but that's not generally a good idea, not least as running the same query later could give different results for the same data.)

sql left join +one to many relationship

Why are you joining to content_type_product ??
But that aside, try

 SELECT c.uid, n.uid, n.nid, c.message  
 FROM node n  
   LEFT JOIN share_content c 
      ON c.nid = n.nid  
          And c.auto_id
             = (Select Max(auto_id)
                From share_content 
                Where nid = p.nid ) 
 Where n.nid = 40513  
 ORDER BY c.auto_id

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

SQL Left join: selecting the last records in a one-to-many relationship

You got it almost right.

Your first query removes all customers that don't have details with the specified product, because you didn't specifiy the product filter in the ON condition of the first OUTER JOIN.

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
      cust.customer = inv.customer
  AND inv.Product IN ('CC', 'CG', 'CH')
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.date 
       OR inv.date = inv2.date AND inv.customer < inv2.customer
      )
WHERE 
  inv2.customer IS NULL

That should do it.

There is one other thing I think is not quite correct. The AND inv.customer < inv2.customer part should probably be AND inv.id < inv2.id (if there is an id field in the detail table).

That's because the OR condition is filtering the detail records that have the same date by their primary key.

UPDATE

Since the table in question has no primary key field you can use the ROWID ADS feature to solve that:

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
      cust.customer = inv.customer
  AND inv.Product IN ('CC', 'CG', 'CH')
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.date 
       OR inv.date = inv2.date AND inv.ROWID < inv2.ROWID
      )
WHERE 
  inv2.customer IS NULL

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)

---

Related Topics