How can I combine these two statement??? 1

[BiggerBrother]

I need to join these two statements, so that I can take one value from the other?

select sum(sale_quantity)
FROM 
AS_customers, AS_sales
WHERE 
cus_id=sale_cus_id AND
cus_id=1 AND 
sale_prod_id=1

SELECT COUNT(*)
FROM AS_collection, AS_customers
WHERE 
col_cus_id = cus_id AND
cus_id=1 AND
col_prod_id = 1

Basically, I need to get sum(sale_quantity) - COUNT(*), how do I link these two statements, and only return the one value in this statement?

Many thanks

BB

 

[TonyGroves]

If you're using MySQL 4.1, then you could use:
[tt]
SELECT SUM(total)
FROM
(
SELECT SUM(sale_quantity) total
FROM AS_customers, AS_sales
WHERE
cus_id=sale_cus_id AND cus_id=1 AND sale_prod_id=1
UNION ALL
SELECT 0-COUNT(*)
FROM AS_collection, AS_customers
WHERE
col_cus_id = cus_id AND cus_id=1 AND col_prod_id = 1
)
t
[/tt]

 

[BiggerBrother]

Ah, I was recently upgraded to 4.1.10, so please bare with me.

I get the error:#1248 - Every derived table must have its own alias

What does this mean?

Many thanks

BB

 

[TonyGroves]

It's saying that whenever you define a sub-query, you have to give it a name. If you look at the bottom of the query, you'll notice a little "t". Put that in and it should work.

 

[BiggerBrother]

Stupidly I left the t off, thinking it was your signature.

Whoops

Thanks, I would never have got there!

BB