[SOLVED] self join and count

[M.O.S. Studios]

hey guys,

 

im trying to send a my sql query that return information about an invoice and return a row count as a column,

 

table format is

 

invoices:

client_name - invoice_number - [...] - `complete`

 

invoice_prod

 

ref - qty [...]

 

soo if invoice_number has 3 rows where ref = invoice_number the db will ander this

 

client_name    invoice_number  Complete    count(*)

peter griffin            1                  true          3

 

 

im not sure whats wrong with my code, any advice helps

 

 

SELECT `invoice_number`, `complete`, `invoice_prod`.count(*) FROM `invoices`, `invoice_prod` WHERE `invoices`.`index` = `invoice_prod`.`ref`
LIMIT 0 , 30

[Ken2k7]

What does qty in invoice_prod represent?

[M.O.S. Studios]

how meny of each product they bought, how ever the count funcction just represents howmeny differn't products they bought

 

ie,

they bought

 

3 x apples

1 x orange

1 x juicer

 

incoice_pro would have 3 rows as follows:

 

ref - qty - name

1      3      apples

1      1      orange

1      1      juicer

 

 

the total would come out like this:

client_name    invoice_number  Complete    count(*)

peter griffin            1                  true          3

 

 

p.s. can mysql do the math of how meny total products?

[Ken2k7]

Not tested.

SELECT i.client_name, i.invoice_number, i.complete, COUNT(ip.ref) AS count
FROM invoices i
INNER JOIN invoices_prod ip
     ON i.invoice_number = ip.ref
GROUP BY i.invoice_number

[M.O.S. Studios]

thats awesome, thanks

 

worked liek a charm