Professional Web Applications Themes

o/t mysql query - MySQL

This should be simple but I'm missing something. If at all possible can somebody please hint how I should be writing the following query (mySQL). I need to query the total count of products that meet all the selected categories. ----------------------------------- cat | product ----------------------------------- 1 1 2 1 1 2 1 3 2 3 Q: so how many products are assigned to both cat 1&2 Thanks in advance. Mark Dyer NZ...

  1. #1

    Default o/t mysql query

    This should be simple but I'm missing something. If at all possible can
    somebody please hint how I should be writing the following query
    (mySQL).
    I need to query the total count of products that meet all the selected
    categories.

    -----------------------------------
    cat | product
    -----------------------------------
    1 1
    2 1
    1 2
    1 3
    2 3

    Q: so how many products are assigned to both cat 1&2

    Thanks in advance.
    Mark Dyer
    NZ

    mark@intelliserve.co.nz Guest

  2. #2

    Default Re: o/t mysql query


    co.nz wrote:
     
    Does the 3 in the product column in the last row mean that there are 3
    products for that row?

    Captain Guest

  3. #3

    Default Re: o/t mysql query

    I think (but not tested)

    SELECT SUM(product) FROM <TABLE> GROUP BY cat



    Captain Paralytic wrote: 
    > Does the 3 in the product column in the last row mean that there are 3
    > products for that row?[/ref]

    blaine@worldweb.com Guest

  4. #4

    Default Re: o/t mysql query

    "co.nz" <co.nz> wrote in
    news:googlegroups.com:
     

    USE test;
    DROP TABLE IF EXISTS foo;
    CREATE TABLE foo (cat TINYINT UNSIGNED NOT NULL, product TINYINT UNSIGNED
    NOT NULL);
    INSERT INTO foo (cat, product) VALUES
    (1, 1), (2, 1), (1, 2), (1, 3), (2, 3);

    SELECT COUNT(*)
    FROM (
    SELECT product
    FROM foo
    WHERE cat IN (1, 2)
    GROUP BY product
    HAVING COUNT(DISTINCT cat) = 2
    ) T;

    --
    felix
    Felix Guest

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Replies: 0
    Last Post: November 4th, 05:38 PM
  3. MySql Query
    By Crazy in forum Dreamweaver AppDev
    Replies: 2
    Last Post: July 3rd, 05:37 PM
  4. MySql query help
    By luckyrye in forum PHP Development
    Replies: 2
    Last Post: May 1st, 05:25 AM
  5. PHP - MySQL Query...
    By Steven Kallstrom in forum PHP Development
    Replies: 2
    Last Post: August 14th, 02:22 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