MySQL inner join limit 1 row from second table

I have 2 (MySQL) tables , exchange table can have 1–n rows in exchitems, when an exchange record has multiple rows, I would like to display the word “multi”, but when there is only 1 row, I would like to display the row’s details:

First table (exchange):

xid (PK) | cusid | xdate      | xref   | xtotal
1        | 1     | 2021-10-01 | 345667 | 500
2        | 1     | 2021-10-01 | 345668 | 200
3        | 1     | 2021-10-02 | 345669 | 450
4        | 1     | 2021-10-03 | 345670 | 1200

And the second table (exchitems):

chid (PK) | xid | cusid | xcur| xsell| xbuy
1         | 1   | 1     | USD | 300  | 0
2         | 1   | 1     | EUR | 0    | 400
3         | 2   | 1     | USD | 200  | 0
4         | 3   | 1     | EUR | 0    | 500
5         | 4   | 1     | EUR | 0    | 800
6         | 4   | 1     | USD | 300  | 0

The exchange table must have at least 1 row in exchtiems table, and this is what I would like to get:

xid | cusid | xdate      | xref   | xcur  | xsell | xbuy | xtotal
1   | 1     | 2021-10-01 | 345667 | multi | 0     | 0    | 500
2   | 1     | 2021-10-01 | 345668 | USD   | 200   | 0    | 200
3   | 1     | 2021-10-02 | 345669 | EUR   | 0     | 500  | 450
4   | 1     | 2021-10-03 | 345670 | multi | 0     | 0    | 1200

Using the following query, i am able to get the all records, but I would like to limit the exchitems table to one row “any row” when there are multiple rows, the count is used to display the word “multi” when it is > 1:

SELECT a.xid,a.xdate,a.xref,a.xtotal,b.xcur,b.xsell,b.xbuy,
(SELECT COUNT(*) FROM exchitems c WHERE c.xid= a.xid) AS tRec
FROM (exchange a 
INNER JOIN exchitems b ON a.xid= b.xid AND a.cusid= b.cusid)  
WHERE a.cusid = 1 
ORDER BY a.xdate DESC,a.xid DESC

I have tried many different queries but couldn’t achieve what I want.
Any help is highly appreciated.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Untested, but this should work.

SELECT
    a.xid,
    a.cusid,
    a.xdate,
    a.xref,
    -- if distinct currency in the group is > 1 then the word 'multi', else currency.
    IF(COUNT(DISTINCT b.xcur) > 1, 'multi', b.xcur) AS `xcur`,
    b.xsell,
    b.xbuy,
    a.xtotal
FROM exchange a
JOIN exchitems b ON a.xid = b.xid AND a.cusid = b.cusid
WHERE a.cusid = 1
GROUP BY xid -- will let you have exchange rows with groups of exchitems 1:n
ORDER BY a.xdate DESC, a.xid DESC

Method 2

You can modify your current query to the following:

SELECT a.xid, a.cusid, a.xdate,
       a.xref,
       GROUP_CONCAT(b.xcur),
       MIN(b.xsell),
       MIN(b.xbuy),
       MAX(a.xtotal)
FROM (exchange a 
INNER JOIN exchitems b ON a.xid= b.xid AND a.cusid= b.cusid)  
WHERE a.cusid = 1 
GROUP BY a.xid,a.cusid,a.xdate,a.xref
ORDER BY a.xid;

The result will look like this:

The part where I use MIN and MAX is according to your expected result. You may want to clarify which value to show there is you have multiple value. If I change that to GROUP_CONCAT:

 SELECT a.xid, a.cusid, a.xdate,
        a.xref,
        GROUP_CONCAT(b.xcur),
        GROUP_CONCAT(b.xsell),
        GROUP_CONCAT(b.xbuy),
        GROUP_CONCAT(a.xtotal)
 FROM (exchange a 
 INNER JOIN exchitems b ON a.xid= b.xid AND a.cusid= b.cusid)  
 WHERE a.cusid = 1 
 GROUP BY a.xid,a.cusid,a.xdate,a.xref
 ORDER BY a.xid;

Then you’ll see a more elaborate result:

To make the xcur value show multi, you probably can do something like:

SELECT a.xid, a.cusid, a.xdate,
       CASE WHEN COUNT(b.xcur) > 1 THEN 'multi' ELSE MAX(b.xcur) END AS xcur,
       MIN(b.xsell),
       MIN(b.xbuy),
       MAX(a.xtotal)
FROM (exchange a 
INNER JOIN exchitems b ON a.xid= b.xid AND a.cusid= b.cusid)  
WHERE a.cusid = 1 
GROUP BY a.xid,a.cusid,a.xdate,a.xref
ORDER BY a.xid;

Demo fiddle

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0