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

Question

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.

If you post your attempts at using 'JOIN' you should be close and get help in fixing them. — Nigel Ren
– Nigel Ren, Commented Dec 27, 2017 at 17:01
You want filter values from both tables combined basically right? — Selaka Nanayakkara
– Selaka Nanayakkara, Commented Dec 27, 2017 at 17:03
SelakaN that is correct. So far @NappingRabbit seems to have a working answer. I never thought of using the two tables in one FROM. I kept playing with LEFT JOIN and UNION to no avail. — Kobus Myburgh
– Kobus Myburgh, Commented Dec 27, 2017 at 17:04
As I understand, your attempt was correct. It's not clear what went wrong with your query previously. So actually you don't even have a question that I can answer :-) — Paul Spiegel
– Paul Spiegel, Commented Dec 27, 2017 at 19:06

NappingRabbit · Accepted Answer · 2017-12-27 19:57:53Z

2

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;

edited Dec 27, 2017 at 19:57

answered Dec 27, 2017 at 17:01

NappingRabbit

1,9191 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kobus Myburgh Over a year ago

Wow - that was fast, and also appears to be working! Thank you - I want to implement, then will mark as correct answer. Thank you!

NappingRabbit Over a year ago

it will not as it is written.

Kobus Myburgh Over a year ago

Yeah - I just picked that up. So - cannot mark this as correct. Can you adjust your answer, @NappingRabbit?

Kobus Myburgh Over a year ago

@PaulSpiegel commented on my original question, and I feel really silly, because that is a very simple query - and it works. I had no internet last night, so I could not really research why it did not work.

NappingRabbit Over a year ago

so you want clients with no payments and payments with no clients also?

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related