Multiple Query Same Table But in Different Columns MySQL

MySQL - Group by multiple columns from same table

After lot of tries, I am able to get the data I want

Query ['Female']

SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age 
FROM customer_data bd 
INNER JOIN ( 
    SELECT booking_id, GROUP_CONCAT(DISTINCT gender) AS g 
    FROM customer_data 
    WHERE gender!='' AND age>0 
    GROUP BY booking_id 
    HAVING COUNT(booking_id) > 1 
    ORDER BY booking_id ASC, gender DESC 
) cbd 
WHERE cbd.booking_id = bd.booking_id AND cbd.g = 'Female'

Query ['Male']

SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age 
FROM customer_data bd 
INNER JOIN ( 
    SELECT booking_id, GROUP_CONCAT(DISTINCT gender) AS g 
    FROM customer_data 
    WHERE gender!='' AND age>0 
    GROUP BY booking_id 
    HAVING COUNT(booking_id) > 1 
    ORDER BY booking_id ASC, gender DESC 
) cbd 
WHERE cbd.booking_id = bd.booking_id AND cbd.g ='Male'

Query ['Male and Female']

SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age 
FROM customer_data bd 
INNER JOIN ( 
    SELECT booking_id, GROUP_CONCAT(DISTINCT gender ORDER BY gender DESC) AS g 
    FROM customer_data 
    WHERE gender!='' AND age>0 
    GROUP BY booking_id 
    HAVING COUNT(booking_id) > 1 
    ORDER BY booking_id ASC, gender DESC 
) cbd 
WHERE cbd.booking_id = bd.booking_id AND cbd.g = 'Male,Female'

Get multiple data from same table with different Id

You could have constant values for the missing columns. ie:

SELECT a,b,c,d,e,f,g,h,i from user where id=someid 
union 
select j, k, null, null, null, null, null, null, null from user 
where id=admin_id;

EDIT: It works as suggested:

DBFiddle demo

Your question is not clear and looks like you are after a union, but maybe you were trying to say you want to get back something like:

a,b,c,d,e,f,g,h,i,j,k instead? If so, then:

select * from
(SELECT a,b,c,d,e,f,g,h,i 
from _user where id=someId) t1
cross join
(SELECT j,k 
from _user where id=admin_id) t2;

SQL query with two values from the same table under conditions set by column in the same table

This is a process called pivot.

And can be done using GROUP BY in combination with MAX(CASE END) clauses.

The HAVING clause makes sure a B.aid has both records containing 1 and 2 mode.

SELECT 
   B.aid 
 , MAX(CASE WHEN B.mode = 1 WHEN B.area ELSE 0 END) AS 'area(mode=1)'
 , MAX(CASE WHEN B.mode = 2 WHEN B.area ELSE 0 END) AS 'area(mode=2)'
FROM 
 B
GROUP BY
 B.aid 
HAVING
   SUM(B.mode = 1) = 1
 AND
   SUM(B.mode = 2) = 1
ORDER BY 
 B.aid ASC

MySQL: select two column from the same table with the same name

You can do joins between the same table, so you can do it like:

SELECT g.id as id, a.name as team_a, b.name as team_b 
FROM team a INNER JOIN game g ON g.id_team_A = a.id
INNER JOIN team b ON g.id_team_B = b.id

---

Related Topics