concatenate query result in a string

Question

I am getting a db result as 3 rows (Please see image link below).

The sql statement used is

select tevmkt.ev_mkt_id, tevmkt.name, tevmkt.ev_id, tevoc.ev_oc_id, 
       tevoc.desc, tevoc.fb_result, tevoc.lp_num, tevoc.lp_den, 
       tev.start_time
from tevmkt, tev,tevoc
where tevmkt.name = '|Match Result|' and tev.ev_id=tevmkt.ev_id and 
      tevoc.ev_mkt_id=tevmkt.ev_mkt_id and tev.start_time>=Today;

I will like to use php to concatenate each of the 3 rows into string or maybe use SQL statement.

So, the first 3 rows will display as ;

632274|Match Result||Draw||Aldershot Town||Arsenal FC|

And the next 3 rows

637799|Match Result||Draw||Southend United||Oxford United|

Please learn to use proper `join` syntax and table aliases. They both help make queries much easier to read. — Gordon Linoff, Aug 04 '15 at 01:25

score 0 · Answer 1 · answered Aug 04 '15 at 01:30

0

You can use concat

Sample base on your query

select CONCAT(tevmkt.ev_mkt_id, tevmkt.name, tevmkt.ev_id, tevoc.ev_oc_id, 
       tevoc.desc, tevoc.fb_result, tevoc.lp_num, tevoc.lp_den, 
       tev.start_time)
from tevmkt, tev,tevoc
where tevmkt.name = '|Match Result|' and tev.ev_id=tevmkt.ev_id and 
      tevoc.ev_mkt_id=tevmkt.ev_mkt_id and tev.start_time>=Today;

answered Aug 04 '15 at 01:30

jameshwart lopez

2,993
6
35
65

You missed that he wants to concatenate the `desc` column from different rows. – Barmar Aug 04 '15 at 01:50

Ricardo Henriques · Answer 2 · 2015-08-04T13:51:58.803

For what I see your model looks something like this:

CREATE TABLE tevmkt(
    ev_mkt_id   INT,
    ev_id       INT,
    name        CHAR(25)
);

CREATE TABLE tev(
    ev_id       INT,
    start_time  DATETIME YEAR TO SECOND
);

CREATE TABLE tevoc(
    ev_mkt_id   INT,
    desc        CHAR(25),
    fb_result   CHAR(1),
    lp_num      SMALLINT,
    lp_den      SMALLINT
);

You join tevmkt with tev by ev_id in a 1-to-1 relation.

You filter the records on tevmkt using the name field and the records on tev using the start_time field.

Now, you join the tevmkt with the tevoc by ev_mkt_id in a one to many relation, for what I see 1-to-3.

Your goal is to have a 1-to-1 also. Looking at you example I see three rows for each event and I conclude that they are:

1 row for home team, where fb_result is H;
1 row for away team, where fb_result is A;
1 row for result, I'm not sure on this one so I will handle it with care;

If this is the case, you can get your result set straigth using the next joins and filters:

SELECT
    tevmkt.ev_mkt_id,
    tevmkt.name,
    (
        TRIM(tevoc_result.desc) ||
        TRIM(tevoc_away.desc)   ||
        TRIM(tevoc_home.desc)
    ) AS desc
FROM tevmkt
    JOIN tev ON 
        tev.ev_id = tevmkt.ev_id 
    JOIN tevoc tevoc_result ON 
        tevoc_result.ev_mkt_id = tevmkt.ev_mkt_id 
    JOIN tevoc tevoc_away ON 
        tevoc_away.ev_mkt_id = tevmkt.ev_mkt_id 
    JOIN tevoc tevoc_home ON 
        tevoc_home.ev_mkt_id = tevmkt.ev_mkt_id 
WHERE
    tevmkt.name = '|Match Result|'
    AND tev.start_time >= TODAY
    AND tevoc_result.fb_result NOT IN ('H', 'A')
    AND tevoc_away.fb_result = 'A'
    AND tevoc_home.fb_result = 'H'
;

concatenate query result in a string

2 Answers2