Return Only One Row from the Right-Most Table for Every Row in the Left-Most Table

Return only one row from the right-most table for every row in the left-most table

Use:

  SELECT u.id,
         u.name,
         MIN(t.spent) AS spent
    FROM USERS u
    JOIN TRANSACTIONS t ON t.uid = u.id
GROUP BY u.id, u.name

Mind that this will only return users who have at least one TRANSACTIONS record. If you want to see users who don't have supporting records as well as those who do - use:

   SELECT u.id,
          u.name,
          COALESCE(MIN(t.spent), 0) AS spent
     FROM USERS u
LEFT JOIN TRANSACTIONS t ON t.uid = u.id
 GROUP BY u.id, u.name

How to return only one row from the right-most table using mysql join

You can't do this just with a join. Here is one method:

select e.*, t.*
from events e join
     tickets t
     on e.event_id = t.event_id
where t.id = (select min(t2.id)
              from tickets t2
              where t2.event_id = t.event_id
             );

Forcing left join to only return one row from matching Ids in the right table

One method uses row_number():

Select F.*, B.*
from Foo f left join
     (select b.*, row_number() over (partition by b.id order by id) as seqnum
      from bar b
     ) b
     on f.Id = B.Id and seqnum = 1; 

The order by specifies what you mean by "first". The order by id is an arbitrary ordering.

And alternative method uses outer apply:

Select F.*, B.*
from Foo f outer apply
     (select top 1 b.*
      from bar b
      where f.Id = B.Id
     ) b; 

In this case, you would add an order by to the subquery to get the "first" based on some column. Also, this should have better performance than the previous version.

Sql return one row from right table every left table result

Because you just need a random name, you can use group by since you're using mysql:

select t1.ip, t1.count, t2.name
from table1 t1 
    join table2 t2 on t1.ip = t2.ip
group by t1.ip

Depending on your data, you may need an outer join as well.

• SQL Fiddle Demo

LEFT JOIN but take only one row from right side

Use OUTER APPLY:

SELECT dok1.*, k2.*
FROM ks__dokument dok1 OUTER APPLY
     (SELECT TOP (1) *
      FROM ks_pz 
      WHERE ks_id = kp_ksid
      ) k2
WHERE ks_usuniety = 0 AND
      ks_data_otrzymania >= '2020-08-31'
ORDER BY ks_rok, ks_nr ASC;

Normally, there would be an ORDER BY in the subquery to specify which row to return.

The structure of your question makes it impossible to know if the ORDER BY should be in the subquery or in the outer query -- and the same for the WHERE conditions.

You really need to specify the tables where columns are coming from.

How to return rows from left table where condition on right table true on all row without sub query

Try this:

SELECT p.id, p.title, GROUP_CONCAT(number ORDER BY number) val
  FROM Parent p JOIN Childs c ON p.id=c.p_id
  GROUP BY p.id, p.title
  HAVING SUBSTRING_INDEX(val,',',1) > 3;

Here's a fiddle demo

SQL join to include all records on the left most table and only show records from the two right table where two condition are met

When dealing with outer joins you need to cater for the possibility of NULLs in columns from those joined tables. This is especially true when it comes to the where clause because if you don't allow for NULLs when referring to outer joined tables you create the conditions that equate to an inner join.

e.g.

SELECT
      hpd_help_desk.INCIDENT_NUMBER
    , hpd_associations.association_type01 AS "HPD_ASSOCIATIONS_Request"
    , chg_infrastructure_change.request_id AS "CHG_Request"
    , chg_infrastructure_change.status_reason AS "CHG_Status_Reason"
    , chg_infrastructure_change.description2 AS "CHG__Desc2"
    , chg_infrastructure_change.infrastructure_change_id AS "CHG_infrastructure_change_id"
FROM helix_access.hpd_help_desk
LEFT OUTER JOIN helix_access.hpd_associations
    ON hpd_help_desk.incident_number = hpd_associations.request_id02
LEFT OUTER JOIN helix_access.chg_infrastructure_change
    ON chg_infrastructure_change.infrastructure_change_id = hpd_associations.request_id01
WHERE (hpd_associations.association_type01 = 17000 
         OR hpd_associations.association_type01 IS NULL)

Also, an alternative to using the where clause you can extend the join conditions instead e.g.

LEFT OUTER JOIN helix_access.hpd_associations
    ON hpd_help_desk.incident_number = hpd_associations.request_id02
    AND hpd_associations.association_type01 = 17000
LEFT OUTER JOIN helix_access.chg_infrastructure_change
   ON chg_infrastructure_change.infrastructure_change_id  = hpd_associations.request_id01
   AND chg_infrastructure_change.CRQ IS NOT NULL
   AND chg_infrastructure_change.HPD_ASSOCIATIONS_Request = 17000

This will limit the rows included in the result to just those rows that meet ALL conditions in each join.

Regarding "If there hpd_associations.association_type01 has multiple rows per hpd_help_desk.INCIDENT_NUMBER and no chg_request CRQ's / HPD_ASSOCIATIONS_Request = 17000, simply take the first row." This may require use of row_number() over() and to do that would require you to join to a subquery instead of joining diretly to the table. e.g.

LEFT OUTER JOIN (
    SELECT *
    , row_number() over(partition by request_id02
                        order by CRQ) as rn
    FROM helix_access.hpd_associations
    AND hpd_associations.association_type01 = 17000
    ) AS assocs ON hpd_help_desk.incident_number = assocs.request_id02
                AND assocs.rn = 1 /* this is how you get just one row (for each request_id02) */

MySQL JOIN Query - one row from right table for each row left table with prioritizing contained data

In MySQL8:

SELECT 
  f.id,
  f.flat,
  MAX(CASE WHEN rr.rn = 1 THEN rr.`name` END) AS name1,
  MAX(CASE WHEN rr.rn = 1 THEN rr.phone END) AS phone1,
  MAX(CASE WHEN rr.rn = 1 THEN rr.mail END) AS email1,
  MAX(CASE WHEN rr.rn = 2 THEN rr.`name` END) AS name2,
  MAX(CASE WHEN rr.rn = 2 THEN rr.phone END) AS phone2,
  MAX(CASE WHEN rr.rn = 2 THEN rr.mail END) AS email2
FROM
house f
LEFT JOIN
(
 SELECT 
  r.*, 
  ROW_NUMBER() OVER(PARTITION BY r.fid ORDER BY 
    (CASE WHEN r.phone IS NOT NULL THEN -2 ELSE 0 END + CASE WHEN r.mail IS NOT NULL THEN -1 ELSE 0 END), r.fid
  ) rn
 FROM
  renter r
) rr
ON rr.fid = f.id and rr.rn <= 2
GROUP BY f.id, f.flat

In MySQL < 8 you'll have to fake ROW_NUMBER/PARTITION BY using this undocumented (might suddenly stop working) technique:

SELECT 
  f.id,
  f.flat,
  MAX(CASE WHEN rr.rn = 1 THEN rr.`name` END) AS name1,
  MAX(CASE WHEN rr.rn = 1 THEN rr.phone END) AS phone1,
  MAX(CASE WHEN rr.rn = 1 THEN rr.mail END) AS email1,
  MAX(CASE WHEN rr.rn = 2 THEN rr.`name` END) AS name2,
  MAX(CASE WHEN rr.rn = 2 THEN rr.phone END) AS phone2,
  MAX(CASE WHEN rr.rn = 2 THEN rr.mail END) AS email2
FROM
house f
LEFT JOIN
(
 SELECT 
  r.*, 
  @rn:=CASE WHEN r.fid=@previd THEN @rn+1 ELSE 1 END as rn,
  @previd:=r.fid
 FROM
  (select @rn:=0,@previd:=-1) x,
  renter r
  ORDER BY r.fid, (CASE WHEN r.phone IS NOT NULL THEN -2 ELSE 0 END + CASE WHEN r.mail IS NOT NULL THEN -1 ELSE 0 END)
) rr
ON rr.fid = f.id and rr.rn <= 2
GROUP BY f.id, f.flat

https://www.db-fiddle.com/f/dYS68AFFGTxZxfia1UtJEK/0

How it works:

Your renter table has a row number applied, that counts the rows in priority order. If a row has a phone, it scores -2, if a row has an email it scores -1. added together these come to -3 if a row has both. When ordered ascending, it means that -3 has higher priority (is the first row out of the sort) than a -2 or -1. Row number puts a number on the row like 1,2,3.. it restarts everytime the flat id number changes.

We take our augmented data set and join it onto the flats, abd we say we are only interested in rows <=2 on the rownumber because you only want name1 and name2 etc.

But this data is still in a single column:

FlatID, Name,  RN
1,      Bill,  1
1,      Cloe,  2

To turn the column into a row, we use a pivoting operation. The standard way to do this is to use CASE WHEN rn = 1 or 2 ...:

SELECT *, case when rn = 1 then name end as name, case when rn = 2 then name end

Produces

FlatID, Name1,  Name2,  RN
1,      Bill,   null,   1
1,      null,   Cloe,   2

Now we use MAX() to group up onto a single row for the FlatID, and because MAX discards nulls, the Bill and Cloe are perserved and become one row. RN has done its job and is discarded:

FlatID, Name1,  Name2
1,      Bill,   Cloe

The bottom query (mysql5.x) uses the same technique, it just uses variables to imitate row_number()

---

For future questions, please ensure you post the MySQL version and also attempt to make an example set of data on db-fiddle.com (like i did above) or similar - it will get more people interested in helping you if they don't have to mess around creating tables and loading them with data to test their theories

---

Related Topics