queries

If I am not in the right newsgroup, please point me to the correct one, I
did not see one that involves queries.
How can I do a query from 2 tables to see which table does not have the same
number of records per person as the 2nd table? I hope that makes sense.
For instance, I have a client table, then 2 other tables related to each
client. One table has invoices, the other has invoices paid (they are
separate tables for other reasons). I want to know which clients have a
different number of invoices than they do invoices paid.Probably a good question for the .programming group. Can you follow up
there will DDL, sample data and desired results?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Rock" <rockisland@.yahoo.com> wrote in message
news:#yFTZfb0EHA.1392@.tk2msftngp13.phx.gbl...
> If I am not in the right newsgroup, please point me to the correct one, I
> did not see one that involves queries.
> How can I do a query from 2 tables to see which table does not have the
same
> number of records per person as the 2nd table? I hope that makes sense.
> For instance, I have a client table, then 2 other tables related to each
> client. One table has invoices, the other has invoices paid (they are
> separate tables for other reasons). I want to know which clients have a
> different number of invoices than they do invoices paid.
>
>|||As Aaron suggested we really need to see DDL, sample data and expect results
to understand your problem fully. Various questions come to mind. What are
the keys of these tables? Is there a foreign key between them and if so,
what? Do you actually want a count of the invoices or do you just want to
find customers with invoices that don't appear in both tables?
One of these may help. For the first two queries I've assumed that
Invoice_No is the primary key in both tables.
SELECT DISTINCT I.client_id
FROM Invoices AS I
LEFT JOIN PaidInvoices AS P
ON I.invoice_no = P.invoice_no
WHERE P.invoice_no IS NULL
SELECT DISTINCT I.client_id
FROM Invoices AS I
FULL JOIN PaidInvoices AS P
ON I.invoice_no = P.invoice_no
WHERE P.invoice_no IS NULL
OR I.invoice_no IS NULL
SELECT I.client_id, I.invoiced, P.paid
FROM
(SELECT client_id, COUNT(*) AS invoiced
FROM Invoices
GROUP BY client_id) AS I,
(SELECT client_id, COUNT(*) AS paid
FROM PaidInvoices
GROUP BY client_id) AS P
WHERE I.client_id = P.client_id
--
David Portas
SQL Server MVP
--|||Rock,
--This will return all unpaid bills
SELECT * FROM invoices
WHERE NOT EXISTS(SELECT * FROM invoices_paid WHERE
invoices_paid.id=invoices.id)
--This will return all clients and the number of unpaid bills
SELECT COUNT(invoices.id), clients.clients_id FROM invoices
JOIN clients ON clients.clients_id=invoices.clients.id
WHERE NOT EXISTS(SELECT * FROM invoices_paid WHERE
invoices_paid.id=invoices.id)
GROUP BY invoices.id, clients.clients
Regards, Gerald.
"Rock" <rockisland@.yahoo.com> schrieb im Newsbeitrag
news:%23yFTZfb0EHA.1392@.tk2msftngp13.phx.gbl...
> If I am not in the right newsgroup, please point me to the correct one, I
> did not see one that involves queries.
> How can I do a query from 2 tables to see which table does not have the
> same
> number of records per person as the 2nd table? I hope that makes sense.
> For instance, I have a client table, then 2 other tables related to each
> client. One table has invoices, the other has invoices paid (they are
> separate tables for other reasons). I want to know which clients have a
> different number of invoices than they do invoices paid.
>
>