Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL select multiple rows within a row

Tags:

sql

php

mysql

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?

like image 512
yukari Avatar asked Jul 28 '15 07:07

yukari


People also ask

How do I select multiple records in MySQL?

To select multiple values, you can use where clause with OR and IN operator.

How do I select multiple rows in a single column?

To select more than one row in the data view, click one row, then hold the Control (Windows) or Command (Mac) key and select each of the other rows you wish to edit or remove. To select a continuous list, click one row, then hold the Shift key and click the last row.

How do I select multiple rows in a table?

Select multiple items in a table or list using shift-click and ctrl-click. This can be useful to add multiple resources to a graph or modify multiple items at a time. To select a range of items, hold down the shift key and then click on the top item followed by the bottom item.


1 Answers

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
like image 123
Thomas Krcal Avatar answered Sep 20 '22 15:09

Thomas Krcal