select subquery problem

[mevasquez]

I am having difficulty in the following subquery. I keep getting the syntax error at 'SELECT count(*)'.

SELECT ID, organization, (SELECT count(*) FROM Invoices WHERE paid = 'N') AS paid
FROM clients

What I am trying to do is to get a list of clients and also count any unpaid invoices.

Does anyone know what I am doing wrong.

TIA
Mike

 

[dalchri]

If your query did work, it looks like it would return the total number of unpaid invoices for all clients rather than the number of unpaid invoices per client. I'd recommend a join. Hopefully there is a foreign key to the client table in your invoice table to join on:

SELECT ID, organization, `Count`
FROM clients
LEFT JOIN (
SELECT ClientID, Count(*) AS `Count`
FROM Invoices
WHERE paid = 'N'
GROUP BY ClientID) Unpaid
ON clients.ID = Unpaid.ClientID

 

[mevasquez]

Yes, I want to return the count of all unpaid invoices for all clients. I will try your query. It seems like this will work. I will let you know.

Thanks,
Mike

 

[mevasquez]

It think I just realized my problem. I am using version 3.23.58

 

[dalchri]

In that case:

SELECT ID, organization, COUNT(*)
FROM clients
LEFT JOIN Invoices
ON clients.ID = Invoices.ClientID
WHERE paid Is Null Or paid = 'N'
GROUP BY ClientID, organization

paid is Null in the WHERE clause is necessary to include clients that do not have any unpaid invoices in the list or no invoices at all. If you want only clients with unpaid invoices in the list, you can dispose of that criteria and turn the join into an inner join.

 

[sknyppy]

How can I lookup the 'dept_name' each 'parent_order_id'
when performing a select query?
Will I need to use to use two select queries?

tbl_dept
--------------------------------------
int PK NOT NULL dept_id
int NULL parent_dept_id
char (25) NOT NULL dept_name
char (25) NOT NULL dept_location
--------------------------------------

Regards,
Dave