SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How many of a series is in another series

    SO here is how my logic works, if you start with this basic query

    SELECT group_concat(DISTINCT some_fr_key) FROM tbl1
    group by some_fr_key

    You get a list of some_fr_key, comma separated.


    Now you can do
    SELECT count(*) from tbl1
    where some_fr_key IN (1,2,3,4,5)

    Gives you the number of times some_fr_key has been used and was in the series of numbers, essentially how many of one series is in another series.


    What I'm trying to do was
    select count( some_fr_key in (1,2,3,4,5) from tbl1

    Now much to my logical flaw this returns 1 possibly 2 haven't figured out why. Because in is boolean and returns correct if some_fr_key was in taht series.

    How can I do "SELECT count(*) from tbl1
    where some_fr_key IN (1,2,3,4,5)" in the select w/o a sub select?

  2. #2
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Above question is a little too confusing, here is a simpler version

    You have two tables

    table 1. people has key id
    table 2. people_colors has people_id, and color_id

    I want to do
    Code MySQL:
    SELECT count(pc.color_id), group_concat(pc.color_id),
    FROM people p
    LEFT JOIN people_colors pc ON (pc.people_id = p.id)
    GROUP BY people.id

    This returns me the number of colors, and a list of the colors per person.

    I also want to know how many colors a person has in a a given list of color_id?

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Are you looking for something like this:

    Code SQL:
    CREATE TABLE people (
    	people_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,name VARCHAR(24)
    	,PRIMARY KEY(people_id)
    );
     
    CREATE TABLE colors (
    	color_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,name VARCHAR(24)
    	,PRIMARY KEY(color_id)
    );
     
    CREATE TABLE people_colors (
        people_colors_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
    	,people_id MEDIUMINT UNSIGNED NOT NULL
    	,color_id MEDIUMINT UNSIGNED NOT NULL
    	,PRIMARY KEY(people_colors_id)
    	,INDEX(people_id)
    	,INDEX(color_id)
    );

    Code SQL:
    SELECT
         p.people_id
         ,GROUP_CONCAT(CONCAT(c.name,':',COALESCE(pc.colors,0))) AS colors
      FROM
         people p
     CROSS
      JOIN
         colors c
      LEFT
      JOIN
         (SELECT
               pc.people_id
               ,pc.color_id
               ,COUNT(*) AS colors
            FROM
               people_colors pc
           GROUP
              BY
               pc.people_id
               ,pc.color_id) AS pc
        ON
          p.people_id = pc.people_id
       AND
          c.color_id = pc.color_id
     GROUP
        BY
          p.people_id
    Attached Images Attached Images


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
  •