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.

CodePudding user response：

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

CodePudding user response：

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