MySQL select multiple rows within a row

Question

I want something like this, not sure if my syntax is correct though. I will be executing this with php.

SELECT 
    a.column1,
    a.column2,
    b.column1,
    c.column1,
    IF a.column3 NOT NULL
        THEN (
            SELECT c.column1, c.column2, c.column3, d.column1
            FROM table_d d
            INNER JOIN table_c c
                ON d.column1 = c.column1 AND c.column4 = 1
            WHERE d.column2 = a.column3
        );
    END IF;

FROM table_a a

    INNER JOIN table_b b
        ON a.column1 = b.column1 AND b.column2 = 1

    INNER JOIN table_c c
        ON a.column1 = c.column1 AND c.column2 = 1

WHERE
    a.column1 = 1000
    AND b.column3 = 1
    AND c.column3 = 0

ORDER BY 
        a.column1 ASC

So the output will be something like this:

It would be ok if it has multiple rows with the same data on the first few columns. Something like this:

grey area is from the outer SELECT and white area is from the inner SELECT

Note that both outer and inner select statement has table_c. If without the IF statement, can I do this?

SELECT 
    a.column1,
    a.column2,
    b.column1,
    c.column1,
    cc.column1,
    cc.column2,
    cc.column3,
    d.column1   

FROM table_a a

    INNER JOIN table_b b
        ON a.column1 = b.column1 AND b.column2 = 1

    INNER JOIN table_c c
        ON a.column1 = c.column1 AND c.column2 = 1

    LEFT JOIN table_d d
        ON  a.column3 = d.column2

    INNER JOIN table_c cc
        ON d.column1 = cc.column1 AND cc.column4 = 1

WHERE
    a.column1 = 1000
    AND b.column3 = 1
    AND c.column3 = 0

ORDER BY 
        a.column1 ASC

It kinda feels wrong to me. What if I use fetch_assoc? Is it even possible to do this in one query?

You can. You will need some aliases for the columns to avoid fetching them by index. — Vatev
– Vatev, Commented Jul 28, 2015 at 7:59
aliases as in c.column1 AS c_col1? like this? so if i do this the second code snippet will work? — yukari
– yukari, Commented Jul 28, 2015 at 8:02
It will work regardless, you need the aliases so you can use fetch_assoc to get all the fields. — Vatev
– Vatev, Commented Jul 28, 2015 at 8:06
@Vatev oh!! it worked!! but i have to change the second table_c to left join instead of using inner join to retrieve rows with null values, thank you!! :D — yukari
– yukari, Commented Jul 28, 2015 at 8:23

Thomas Krcal · Accepted Answer · 2015-08-17 20:49:34Z

1

Try something like this:

SELECT 
    a.column1,
    a.column2,
    b.column1,
    c.column1,
    IF (a.column3 NOT NULL,c_2.column1,''),
    IF (a.column3 NOT NULL,c_2.column2,''),
    IF (a.column3 NOT NULL,c_2.column3,''),
    IF (a.column3 NOT NULL,d.column4,'')

FROM table_a a

    INNER JOIN table_b b
        ON a.column1 = b.column1 AND b.column2 = 1

    INNER JOIN table_c c
        ON a.column1 = c.column1 AND c.column2 = 1

    INNER JOIN table_c as c_2
        ON d.column1 = c.column1 AND c.column4 = 1

    INNER JOIN table_d 
        ON a.column1 = c.column1 AND c.column2 = 1
WHERE
    a.column1 = 1000
    AND b.column3 = 1
    AND c.column3 = 0
ORDER BY 
        a.column1 ASC

answered Aug 17, 2015 at 20:49

Thomas Krcal

976 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

stubsthewizard · Accepted Answer · 2015-07-31 16:32:51Z

0

To use the fetch_assoc, you would need to use aliases, otherwise you would only be able to fetch them by index, really. Other than that, it should work.

answered Jul 31, 2015 at 16:32

stubsthewizard

3522 silver badges12 bronze badges

Comments

TheSatinKnight · Accepted Answer · 2015-08-16 02:33:02Z

IF a.column3 NOT NULL THEN

This is what "inner join" is for. By definition the inner really means "only if there's a match". Alternately you can use left outer join and then use a "where" to be sure that no records that have NULL in this position are included. Inner Join will filter out any records that have null in the Match field (ie: NO match = exclude this record).

If you want all the records, but you want blanks in these fields if the linked table has no record ... that's what left outer join is for. Once again, that's actually the definition. 8-)

If by fetch_assoc you refer to a PHP function, you should consider switching to mysqli instead of mysql which allows more readily available "index or name" field association. But in any case, the inner join could remove the need to use "as XXXX" field name aliasing.

Collectives™ on Stack Overflow

MySQL select multiple rows within a row

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related