Null values in My SQL query

Question

My Query is

SELECT i.status,i.amount_total, i.id,sc.category as category,us.fname as name,us.company as company,
(SELECT transaction_id FROM invoice_payments WHERE invoice_payments.invoice_id = i.id) as transaction_id ,
(SELECT created  FROM invoice_payments WHERE invoice_payments.invoice_id = i.id and invoice_payments.created BETWEEN '2015-01-01' and '2016-01-01') as created 
FROM invoices i
inner JOIN 
users as us ON us.id = i.client_id 
inner JOIN subscriptioncategory as sc ON sc.user_id = us.id

and my result is

in the Created column, i should not get the null values. are there any simple query to fix it.

That's why you need to use invoice_payments.created in the subselect, not just created in main select... — Luke
– Luke, Commented Aug 2, 2016 at 10:36

Jens · Accepted Answer · 2016-08-02 10:34:50Z

1

Simply join all tables with inner join:

SELECT i.status,i.amount_total, i.id,sc.category as category,us.fname as name,us.company as company,
ip.transaction_id,
ip1.created 
FROM invoices i
inner JOIN 
users as us ON us.id = i.client_id 
inner JOIN subscriptioncategory as sc ON sc.user_id = us.id
join invoice_payments ip on ip.invoice_id = i.id 
join invoice_payments ip1 WHERE ip1.invoice_id = i.id and ip1.created BETWEEN '2015-01-01' and '2016-01-01'

answered Aug 2, 2016 at 10:34

Jens

69.6k15 gold badges107 silver badges133 bronze badges

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

Collectives™ on Stack Overflow

Null values in My SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related