MySQL Join Multiple Rows as Columns
An INNER JOIN will suffice your needs. MySQL has no PIVOT function by you can still simulate it using CASE and MAX() function.
SELECT a.ID, a.NAME,
MAX(CASE WHEN b.Race_Number = 1 THEN b.Place ELSE NULL END) Race1,
MAX(CASE WHEN b.Race_Number = 2 THEN b.Place ELSE NULL END) Race2,
MAX(CASE WHEN b.Race_Number = 3 THEN b.Place ELSE NULL END) Race3
FROM Table1 a
INNER JOIN Table2 b
ON a.ID = b.ID
GROUP BY a.ID, a.Name
But if you have unknown number of RACE, then a DYNAMIC SQL is much more preferred.
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN b.Race_Number = ', Race_Number,
' THEN b.Place END) AS ', CONCAT('`Race', Race_Number, '`'))
) INTO @sql
FROM Table2;
SET @sql = CONCAT('SELECT s.Student_name, ', @sql, '
FROM Table1 a
LEFT JOIN Table2 b
ON ON a.ID = b.ID
GROUP BY a.ID, a.Name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
How can I simplify MySQL join rows as columns?
You can get the results aggregated by year using the following query.
You will need to add a new column for each faculty colour, but given that this is a known finite list that shouldn't be a problem.
SELECT
MAX(year) AS year,
MAX(IF(faculty = 'Yellow', w_gender, NULL)) AS yellow,
MAX(IF(faculty = 'Green', w_gender, NULL)) AS green,
MAX(IF(faculty = 'Purple', w_gender, NULL)) AS purple
FROM results
GROUP BY year
Here's a simplified working DB fiddle: https://www.db-fiddle.com/f/uoX44nDLSji344iXCdmtfV/0
MYSQL: Join rows as columns
For the limited number of records you have shown,
you can write a simple pivot solution as below.
But, if the number of years increase, you have to generate this query run time and execute.
mysql> select
-> code
-> , max( case when year=2012 then year end ) as y2012
-> , max( case when year=2012 then enquiries end ) as enq_2012
-> , max( case when year=2013 then year end ) as y2013
-> , max( case when year=2013 then enquiries end ) as enq_2013
-> , max( case when year=2014 then year end ) as y2014
-> , max( case when year=2014 then enquiries end ) as enq_2014
-> from so_20150521.so_q30374911
-> group by code
-> order by code;
+------+-------+----------+-------+----------+-------+----------+
| code | y2012 | enq_2012 | y2013 | enq_2013 | y2014 | enq_2014 |
+------+-------+----------+-------+----------+-------+----------+
| 1 | 2012 | 302 | 2013 | 406 | 2014 | 254 |
| 2 | 2012 | 274 | NULL | NULL | 2014 | 396 |
| 3 | 2012 | 288 | 2013 | 297 | 2014 | 187 |
| 4 | 2012 | 301 | 2013 | 199 | 2014 | 213 |
| 5 | 2012 | 192 | NULL | NULL | 2014 | 316 |
| 6 | NULL | NULL | NULL | NULL | 2014 | 222 |
+------+-------+----------+-------+----------+-------+----------+
6 rows in set (0.02 sec)
Demo on SQL Fiddle at http://sqlfiddle.com/#!9/4a46d/2
Further simplification can be:
mysql> select
-> code
-> , max( case when year=2012 then enquiries end ) as enq_2012
-> , max( case when year=2013 then enquiries end ) as enq_2013
-> , max( case when year=2014 then enquiries end ) as enq_2014
-> from so_20150521.so_q30374911
-> group by code
-> order by code;
+------+----------+----------+----------+
| code | enq_2012 | enq_2013 | enq_2014 |
+------+----------+----------+----------+
| 1 | 302 | 406 | 254 |
| 2 | 274 | NULL | 396 |
| 3 | 288 | 297 | 187 |
| 4 | 301 | 199 | 213 |
| 5 | 192 | NULL | 316 |
| 6 | NULL | NULL | 222 |
+------+----------+----------+----------+
6 rows in set (0.00 sec)
MySQL select row from one table with multiple rows in a second table and get array of multi row in selected row
What you want a fairly straightforward SELECT query with some LEFT/INNER JOIN(s).
This website has some good examples/explanations which seem very close to your need: https://www.mysqltutorial.org/mysql-inner-join.aspx
I would give you a quick working example, but it is not really clear to me what datatype the relevant columns are. Both tables' _id-columns are likely some variant of INTEGER, are they also both primary keys (or otherwise atleast indexed ?), the client_name/ticket_name are likely VARCHAR/TEXT/STRING types, but how exactly is the remaining column stored? as json or array or ? (+details)
Also you tagged your post with PHP, are you just after the SQL query ? or looking for PHP code with the SQL inside it.
updated
Improved version of the schema
CREATE TABLE clients (
client_id SERIAL,
client_name VARCHAR(255) NOT NULL,
PRIMARY KEY (client_id)
);
CREATE TABLE tickets (
ticket_id SERIAL,
ticket_name VARCHAR(255) NOT NULL,
ticket_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (ticket_id)
);
-- A junction table to glue those 2 tables together (N to N relationship)
CREATE TABLE client_tickets (
client_id BIGINT UNSIGNED NOT NULL,
ticket_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (client_id, ticket_id)
);
I have changed the datatypes.client_name and ticket_name are still VARCHARS. I've flagged them as NOT NULL (eg: required fields), but you can remove that part if you don't like that.client_id/ticket_id/ticket_price are also NOT NULL but changing that has negative side-effects.
ticket_price is now a DECIMAL field, which can store numbers such as 1299.50 or 50.00 The (10,2) bit means it covers every possible number up to 8 whole digits (dollars/euros/whatever), and 2 decimals (cents). so you can store anything from $ -99.999.999,99 to $ 99.999.999,99 .
in SQL always write numbers (like lets say 70k) in this notation: 70000.00 (eg: a dot, not a comma; and no thousandseperators).
client_id and ticket_id are both SERIALs now, which is shorthand for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE and theyre both PRIMARY KEYs on top of that. That probably sounds complicated but they're still just ordinary INTEGERs with values like 4 or 12 etc.
The UNIQUE bit prevents you from having 2 clients with the same ID number, and the AUTO_INCREMENT means that when you add a new client, you dont have to specify an ID (though you are allowed to); you can just do:
INSERT INTO clients (client_name) values ('Fantastic Mr Fox');
and the client_id will automatically be set (incrementing over time). And the same goes for ticket_id in the other table.
.
I've replaced your original client_tickets column, into a separate junction table.
Records in there store the client_id of a client and the ticket_id that belongs to them.
A client can have multiple records in the junction table (one record for each ticket they own).
Likewise, a ticket can be mentioned on any number of rows.
It's possible for a certain client_id to not have any records in the junction table.
Likewise, it's possible for a certain ticket_id to not have any records in the junction table.
Identical records cannot exist in this table (enforced by PRIMARY KEY).
Testdata
Next, we can put some data in there to be able to test it:
-- Create some tickets
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (1, 'ticketone', '30' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (2, 'tickettwo', '40' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (3, 'ticketthree', '50' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (4, 'ticketfour', '60' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (5, 'ticketfive', '70' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (6, 'ticketsix', '4' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (7, 'ticketseven', '9' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (8, 'ticketeight', '500' );
-- Create some users, and link them to some of these tickets
INSERT INTO clients (client_id, client_name) values (1, 'John');
INSERT INTO client_tickets (client_id, ticket_id) values (1, 3);
INSERT INTO client_tickets (client_id, ticket_id) values (1, 7);
INSERT INTO client_tickets (client_id, ticket_id) values (1, 1);
INSERT INTO clients (client_id, client_name) values (2, 'Peter');
INSERT INTO client_tickets (client_id, ticket_id) values (2, 5);
INSERT INTO client_tickets (client_id, ticket_id) values (2, 2);
INSERT INTO client_tickets (client_id, ticket_id) values (2, 3);
INSERT INTO clients (client_id, client_name) values (3, 'Eddie');
INSERT INTO client_tickets (client_id, ticket_id) values (3, 8);
INSERT INTO clients (client_id, client_name) values (9, 'Fred');
-- Note: ticket #3 is owned by both client #1/#2;
-- Note: ticket #4 and #6 are unused;
-- Note: client #9 (Fred) has no tickets;
Queries
Get all the existing relationships (ticket-less clients are left out & owner-less tickets are left out)
SELECT clients.*
, tickets.*
FROM client_tickets AS ct
INNER JOIN clients ON ct.client_id = clients.client_id
INNER JOIN tickets ON ct.ticket_id = tickets.ticket_id
ORDER BY clients.client_id ASC
, tickets.ticket_id ASC ;
Get all the tickets that are still free (owner-less)
SELECT tickets.*
FROM tickets
WHERE tickets.ticket_id NOT IN (
SELECT ct.ticket_id
FROM client_tickets AS ct
)
ORDER BY tickets.ticket_id ASC ;
Get a list of ALL clients (even ticketless ones), and include how many tickets each has and the total price of their tickets.
SELECT clients.*
, COALESCE(COUNT(tickets.ticket_id), 0) AS amount_of_tickets
, COALESCE(SUM(tickets.ticket_price), 0.00) AS total_price
FROM clients
LEFT JOIN client_tickets AS ct ON ct.client_id = clients.client_id
LEFT JOIN tickets ON ct.ticket_id = tickets.ticket_id
GROUP BY clients.client_id
ORDER BY clients.client_id ASC ;
Put all the juicy info together (owner-less tickets are left out)
SELECT clients.*
, COALESCE(COUNT(sub.ticket_id), 0) AS amount_of_tickets
, COALESCE(SUM(sub.ticket_price), 0.00) AS total_price
, JSON_ARRAYAGG(sub.js_tickets_row) AS js_tickets_rows
FROM clients
LEFT JOIN client_tickets AS ct ON ct.client_id = clients.client_id
LEFT JOIN (
SELECT tickets.*
, JSON_OBJECT( 'ticket_id', tickets.ticket_id
, 'ticket_name', tickets.ticket_name
, 'ticket_price', tickets.ticket_price
) AS js_tickets_row
FROM tickets
) AS sub ON ct.ticket_id = sub.ticket_id
GROUP BY clients.client_id
ORDER BY clients.client_id ASC ;
-- sidenote: output column `js_tickets_rows` (a json array) may contain NULL values
An list of all tickets with some aggregate data
SELECT tickets.*
, IF(COALESCE(COUNT(clients.client_id), 0) > 0
, TRUE, FALSE) AS active
, COALESCE( COUNT(clients.client_id), 0) AS amount_of_clients
, IF(COALESCE( COUNT(clients.client_id), 0) > 0
, GROUP_CONCAT(clients.client_name SEPARATOR ', ')
, NULL) AS client_names
FROM tickets
LEFT JOIN client_tickets AS ct ON ct.ticket_id = tickets.ticket_id
LEFT JOIN clients ON ct.client_id = clients.client_id
GROUP BY tickets.ticket_id
ORDER BY tickets.ticket_id ASC
, clients.client_id ASC ;
Join one row to multiple rows in another table in Mysql
You can use group_concat() and a correlated subquery:
select
u.*,
(
select group_concat(d.name)
from user_departments ud
inner join departments d on d.d_id = ud.id
where ud.u_id = u.id
) dept_names
from users u
This can also be done with a join, and outer aggregation:
select u.*, group_concat(d.name) dept_names
from users u
left join user_departments ud on ud.u_id = u.id
left join departments d on d.d_id = ud.id
group by u.id
MySQL Join two tables and combine multiple rows into one
In the LEFT JOIN, you will need to either move the filter status = 0 into a join condition, OR if you leave the filter in the WHERE clause, then status = 0 OR status IS NULL to avoid filtering out campaigns with no messages at all - I've done the first option.
As per the comment, you will need to GROUP the data by the campaign columns, and apply aggregate functions to all non-grouped columns, in order to guarantee just one row per group - GROUP_CONCAT will concatenate all text values in each GROUP. I've arbitrarily used MIN to resolve a value for shop and message, but you may need to adjust otherwise. (You can also do a DISTINCT in a GROUP CONCAT, if required).
SELECT
c.campaign_id,
MIN(shop_id) AS shop_id,
campaign_type,
GROUP_CONCAT(contact_number) AS users_mobile_numbers,
MIN(message) AS message
FROM sms_campaign c
LEFT JOIN sms_recipients r
ON u.campaign_id = c.campaign_id AND status = 0
GROUP BY c.campaign_id, campaign_type;
MySQL join multiple rows of query into one column
You can do this in one query by using GROUP_CONCAT
SELECT a.id,
GROUP_CONCAT(DISTINCT b.country_code SEPARATOR ' ,') `countries`,
GROUP_CONCAT(DISTINCT c.OS SEPARATOR ' ,') `os`,
FROM client_commands a
LEFT JOIN command_countries b on b.command_id = a.id
LEFT JOIN command_os c on c.command_id = a.id
WHERE a.completed = 0
GROUP BY a.id
if you want the ordered results in in a row you can use ORDER BY in GROUP_CONCAT like
GROUP_CONCAT(b.country_code ORDER BY b.command_id DESC SEPARATOR ' ,') `countries`
But be aware of that fact it has a limit of 1024 character to concat set by default but this can be increased b,steps provided in manual
Join multiple tables and show table's row as column with value in same table
You need little JOINs with conditional aggrgation :
select t1.id, t1.name,
sum(case when t3.name = 'a' then t2.value else 0 end) as A,
sum(case when t3.name = 'b' then t2.value else 0 end) as B,
sum(case when t3.name = 'c' then t2.value else 0 end) as C
from table1 t1 inner join
table2 t2
on t2.table1_id = t1.id inner join
table3 t3
on t3.key_id = t2.key
where t1.id = 1
group by t1.id, t1.name;
However, this would do aggregation with only known values prior, if you want go with dynamic way, then you might need programming approach in MySQL.
Related Topics
Difference Between Eloquent\Model::Get() and All()
PHP Remove Elements from Associative Array
How to Check If Directory Contents Has Changed with PHP
How to Set Current Page "Active" in PHP
Get the Price of an Item on Steam Community Market with PHP and Regex
Pdo Exception Questions - How to Catch Them
Turn Plain Text Urls into Active Links Using PHP
File_Get_Contents() How to Fix Error "Failed to Open Stream", "No Such File"
Browser Displays � Instead of '
Php: Catch Exception and Continue Execution, Is It Possible
PHP Session Ids -- How Are They Generated
How to Send Cookies with File_Get_Contents
Creating an Image Without Storing It as a Local File
Syntax Error, Unexpected T_Encapsed_And_Whitespace, Expecting T_String or T_Variable or T_Num_String