CODEWITHSUNDEEP
phpmysqldatabase
Kobus Myburgh
Kobus Myburgh

Reputation: 1202

MySQL select row from one table with multiple rows in a second table

I have one table containing "Client" information, and another including "Payment" information for each client.

Simplified client table

client_id | client_name    | client_status
----------+----------------+--------------
1         | John St. Peter | In Arrears
2         | Peter St. John | Up-to-date

Simplified payments table

payment_id | client_id | payment_date | payment_amount
-----------+-----------+--------------+---------------
1          | 1         | 2017-12-12   | 123.45
2          | 2         | 2017-12-15   | 234.56
3          | 1         | 2017-12-17   | 23.45
4          | 1         | 2017-12-21   | 54.32
5          | 2         | 2017-12-23   | 34.56

With the above two tables, I want to produce a single table with a single query with all the pertinent information to generate a search grid where I can filter by any column in either of the two tables, namely, filter by "client_status" from "client" table, or "client_id" from "payments" table.

payment_id | client_id | client_name    | client_status | payment_date | payment_amount
-----------+-----------+----------------+---------------+--------------+---------------
1          | 1         | John St. Peter | In Arrears    | 2017-12-12   | 123.45
2          | 2         | Peter St. John | Up-to-date    | 2017-12-15   | 234.56
3          | 1         | John St. Peter | In Arrears    | 2017-12-17   | 23.45
4          | 1         | John St. Peter | In Arrears    | 2017-12-21   | 54.32
5          | 2         | Peter St. John | Up-to-date    | 2017-12-23   | 34.56

So - in essence, I want to "duplicate records" in the clients table for as many times as necessary for corresponding records in the payments table to facilitate the search. I am using a premade grid tool (DataTables) which is used all over the system with a custom function made by the developer to generate the grid (so I don't want to mess with that function in fear of breaking the rest of the system), so a single query to get that data sounds like the more pragmatic approach. I also have an "Edit" feature for each row, which will edit the "client" table, and put a tabbed structure in the edit screen for the "payments" table, which currently works fine with two queries (one for the client, one getting all the payments for that client).

I have tried UNION as well as various JOIN statements (probably incorrect...), but either get syntax errors or a single result per "client" row, meaning it does not pick up in the filters.

Apologies if this is a duplicate question - I have searched, but could not find an answer that answers this scenario for me.

Upvotes: 2

Views: 1098

Answers (1)

NappingRabbit
NappingRabbit

Reputation: 1918

here is a join...

select *
from clientsTable c,
     paymentsTable p
where c.client_id = p.client_id
order by p.payment_id;

that should give you everything.

edit: for empties... payments with no clients...

select *
from clientsTable c,
     paymentsTable p
where c.client_id = p.client_id 
   or p.client_id = null
order by p.payment_id;

Upvotes: 2

Related Questions