CODEWITHSUNDEEP
phpmysql
andrebruton
andrebruton

Reputation: 2376

mySQL: Get data from two tables with grouping by Date with unique ID

There are two Stackoverflow questions on how to group tables together by date and I can get it to work. My problem is I only want records that belong to a unique client ID IE Where client_id = x

My tables are:

TABLE INVOICE
invoice_id
client_id
invoice_number
invoice_amount
invoice_date

TABLE PAYMENT
payment_id
invoice_id
payment_amount
payment_date

At the moment my mySQL statement reads through the whole database. How do I get all invoice and payment details just for one client? IE WHERE client_id = x

My mySQL statement is:

SELECT payment_id AS idno, payment_amount AS debit, NULL as credit, payment_date 
AS added_on FROM payment 
UNION ALL 
SELECT invoice_id AS idno, NULL, invoice_total AS credit, invoice_date AS added_on FROM invoice 
WHERE client_id = 24 
ORDER BY added_on

Do I have to have the same amount of columns from each table? How do I identify from which table the data comes from?

Upvotes: 1

Views: 78

Answers (2)

FabienM
FabienM

Reputation: 23

I don't really know how the UNION, but i do know that your 2 two queries need the same amount of columns, just like you did.

I think the reason for your sql statement reading through the whole table is because your two tables are not joined, to do this you can do like @Sid-Hussain mentioned, by joining them in your first SELECT. You then just need to add WHERE client_id = 24 on the your first SELECT

Upvotes: 0

Syed Hussim
Syed Hussim

Reputation: 760

SELECT * FROM invoice i
JOIN payment p ON p.invoice_id = i.invoice_id
WHERE i.client_id= 24

Upvotes: 1

Related Questions