Professional Web Applications Themes

Throwing away a column in subquery - MySQL

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é...

  1. #1

    Default Throwing away a column in subquery

    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é Guest

  2. #2

    Default Re: Throwing away a column in subquery

    André Hänsel wrote: 

    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


    Paul Guest

  3. #3

    Default Re: Throwing away a column in subquery

    On 9 Jun., 12:12, "Paul Lautman" <com> wrote: 





    >
    > 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[/ref]

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

    André Guest

  4. #4

    Default Re: Throwing away a column in subquery

    André Hänsel wrote: 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> 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[/ref]
    >
    > And then make one extra query per customer to get the customer names?
    > Why is that more efficient?[/ref]

    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


    Paul Guest

Similar Threads

  1. get max/min of subquery
    By gil in forum MySQL
    Replies: 4
    Last Post: May 24th, 03:44 PM
  2. Best way to use subquery?
    By greg.scharlemann@gmail.com in forum MySQL
    Replies: 4
    Last Post: November 27th, 01:25 AM
  3. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  4. Help with subquery
    By Nalini in forum Dreamweaver AppDev
    Replies: 3
    Last Post: August 15th, 12:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139