Join One Row to Multiple Rows in Another Table

Join one row to multiple rows in another table

Use:

   SELECT x.name,
          GROUP_CONCAT(y.property SEPARATOR ', ')
     FROM PEOPLE x
LEFT JOIN PROPERTIES y ON y.name = x.name
    WHERE x.age > 26
 GROUP BY x.name

You want the MySQL function GROUP_CONCAT (documentation) in order to return a comma separated list of the PROPERTIES.property value.

I used a LEFT JOIN rather than a JOIN in order to include PEOPLE records that don't have a value in the PROPERTIES table - if you only want a list of people with values in the PROPERTIES table, use:

   SELECT x.name,
          GROUP_CONCAT(y.property SEPARATOR ', ')
     FROM PEOPLE x
     JOIN PROPERTIES y ON y.name = x.name
    WHERE x.age > 26
 GROUP BY x.name

I realize this is an example, but using a name is a poor choice for referencial integrity when you consider how many "John Smith"s there are. Assigning a user_id, being a unique value per user, would be a better choice.

How to join in single row result with one row and multiple rows in another table?

Use joins (tbl is second table):

   select t1.id, 
          t2.name book, 
          t3.name page, 
          t4.name line
    from tbl t1
    join treeTbl t2
      on t1.book = t2.id
    join treeTbl t3
       on t3.id=t1.page
    join treeTbl t4
       on t4.id=t1.line

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 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 ;

Joining two tables, multiple rows into a single row different columns

Try this:

SELECT u.*, uah.address as home_address, uaw.address as work_address
FROM users u
LEFT OUTER JOIN user_address uah
    ON u.user_id = uah.user_id
    AND uah.type = 'HOME'
LEFT OUTER JOIN user_address uaw
    ON u.user_id = uaw.user_id
    AND uaw.type = 'WORK'

How to get one row from multiple rows in query and join another table?

Do a three way join and use row number to select the records corresponding to the max card number for each account.

SELECT
    AccountNo, CardNo, CreateDate, value
FROM
(
    SELECT t1.AccountNo, t2.CardNo, t2.CreateDate, t3.value,
        ROW_NUMBER() OVER (PARTITION BY t1.AccountNo ORDER BY t2.CardNo DESC) rn
    FROM Account2Card t1
    LEFT JOIN Card t2
        ON t1.CardNo = t2.CardNo
    LEFT JOIN Transaction t3
        ON t2.AccountNo = t3.accountno
) t
WHERE
    t.rn = 1;

JOIN two tables with multiple rows to a single row, with dynamic columns

SELECT *
FROM user
LEFT JOIN (SELECT user_ref_id, array_to_json(array_agg(extradata.DYNAMIC_EXTRA_FIELD)) AS extra_data
FROM extradata
GROUP BY user_ref_id) extra on extra.user_ref_id = user.user_id

Will produce:

select
i.name 
from interests i
where i.interest in ('Books','Dancing')
and not exists
(
  select 1 from interests i1
  where interest not in ('Books','Dancing')
  AND i.name = i1.name
)
group by i.name
having count(*) = 2

CREATE TABLE saved_professors (
  user_id INT NOT NULL,
  professor_id INT NOT NULL,
  PRIMARY KEY (user_id, professor_id)
);