Hi,

of course the following statement doesn't work because the subquery
returns a column too much:

SELECT * FROM customers WHERE customer_id IN (SELECT
customer_id,COUNT(*) AS number FROM orders GROUP BY customer_id HAVING
number>1)

("Find all customers with more than one order")

Can I throw away the surplus column that I only need for the HAVING?

Regards,
André

André Hänsel wrote:
> Hi,
>
> of course the following statement doesn't work because the subquery
> returns a column too much:
>
> SELECT * FROM customers WHERE customer_id IN (SELECT
> customer_id,COUNT(*) AS number FROM orders GROUP BY customer_id HAVING
> number>1)
>
> ("Find all customers with more than one order")
>
> Can I throw away the surplus column that I only need for the HAVING?
>
> Regards,
> André

Why throw away the extra column when you can throw away the subselect and
make the whole thing efficient.

SELECT
*
FROM `orders`
GROUP BY `customer_id`
HAVING COUNT(*) > 1

On 9 Jun., 12:12, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
> André Hänsel wrote:
> > Hi,
>
> > of course the following statement doesn't work because the subquery
> > returns a column too much:
>
> > SELECT * FROM customers WHERE customer_id IN (SELECT
> > customer_id,COUNT(*) AS number FROM orders GROUP BY customer_id HAVING
> > number>1)
>
> > ("Find all customers with more than one order")
>
> > Can I throw away the surplus column that I only need for the HAVING?
>
> > Regards,
> > André
>
> Why throw away the extra column when you can throw away the subselect and
> make the whole thing efficient.
>
> SELECT
> *
> FROM `orders`
> GROUP BY `customer_id`
> HAVING COUNT(*) > 1

And then make one extra query per customer to get the customer names?
Why is that more efficient?

André Hänsel wrote:
> On 9 Jun., 12:12, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>> André Hänsel wrote:
>>> Hi,
>>
>>> of course the following statement doesn't work because the subquery
>>> returns a column too much:
>>
>>> SELECT * FROM customers WHERE customer_id IN (SELECT
>>> customer_id,COUNT(*) AS number FROM orders GROUP BY customer_id
>>> HAVING number>1)
>>
>>> ("Find all customers with more than one order")
>>
>>> Can I throw away the surplus column that I only need for the HAVING?
>>
>>> Regards,
>>> André
>>
>> Why throw away the extra column when you can throw away the
>> subselect and make the whole thing efficient.
>>
>> SELECT
>> *
>> FROM `orders`
>> GROUP BY `customer_id`
>> HAVING COUNT(*) > 1
>
> And then make one extra query per customer to get the customer names?
> Why is that more efficient?

Just do a join, still far more efficienct than a subselect

SELECT
*
FROM `orders` `o`
JOIN `customers` `c` USING(`customer_id`)
GROUP BY `o`.`customer_id`
HAVING COUNT(*) > 1