SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select unique with count

    HI Chaps, sorry i havent done any database work for a while so im rather rusty.

    So, i have the following, with the 'WHERE' $'s being passed as text

    Code:
    SELECT products.prodid, products.code1, products.famlink, products.collink, family_en.famid, family_en.name, family_en.text, collections_en.colname, collections_en.colid, collections_en.main,
    diagrams.diagid, diagrams.dthumb
    FROM products
    LEFT JOIN family_en ON products.famlink=family_en.famid
    LEFT JOIN collections_en ON products.collink=collections_en.colid
    LEFT JOIN diagrams ON products.code1=diagrams.diagid
    WHERE family_en.name='$1' AND collections_en.colname='$2'
    This functions fine (even though its not very clean!)

    I want to add two other elements that im rather nervous in doing!

    Firstly, SELECT UNIQUE for the products.code1
    Secondly add a COUNT to the total of products.prodid for each of the UNIQUE diagram.diagid's

    The SELECT UNIQUE from products.code1 refers to the diagram.diagid. In the products db, maybe this code is refered to in over 100 instances, to which i just require one to import the diagram image. In the diagram table its entered just once.
    Now, because i add a SELECT UNQUE for the diagram code, then obvioulsy it will not pull down all the records from the products table, as there is a limit on records retrieved....therefore the COUNT will be inacurate

    Is there a way a can get around this......?

    Thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what exactly are you interested in counting?

    please explain the one-to-many relationships involved between your various tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Use GROUP BY instead of SELECT UNIQUE?

  4. #4
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey

    For the count, im looking for the total number of items in the products table, which after the conditions is the total 'products.code1'

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    products
    ----------------------------------------
    Code 1
    eg 7020 (refers to diagrams.diagid)

    Collink
    eg 1 (refers to collections_en.colid)

    famno
    eg 1 (refers to family_en.famid)

    Diagrams
    -----------------------------------
    diagid
    eg 7020 (to be used to pull down image later)

    family_en
    ---------------------------------------
    famid
    eg 1( unique id for each family of products)

    name
    eg metals (this is also used for $1)

    collections_en
    --------------------------------------
    colid
    (unique id for each collection)
    colname
    eg gold (this is also used for $2)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by barney0o0 View Post
    hey

    For the count, im looking for the total number of items in the products table, which after the conditions is the total 'products.code1'
    hey

    so why do you need the diagrams table if all you want to do is count products?

    is it possible for a product to have no diagrams at all, and you want to exclude these products from the count?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...to try and explain in some kind of context

    The inital filter is to select items from the products table where the famlink and collink are equal to $1 and $2

    The products table contains over 500 items. Each product has the 'code1' which refers to a diagram code (diagrams.diagid). In the products db there could be up to 10 products that 'code1' are the same.

    In the webpage, i want to add a diagram image (for example 7020(.jpg)), then 'count' how many products have the diagram in the code1 field).

    I will then apply a repeat region to the diagram/count to show all possible diagram images and counts relative to that code. Some 'collectons' have products that contain numerous diagrams.....some collections products just have the one.


    .......i hope this helps a little, rather than confuse!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you keep talking about a count but i don't see a COUNT(*) nor a GROUP BY in your query

    consequently it's pretty hard to understand what you want

    each diagram has multiple products?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    theres no count or distinct 'cus i dont know if its possible!

    so, for the products table:

    prodid code1 famlink collink
    -------------------------------------------------------
    1 7020 1 2
    2 7020 1 1
    3 7014 1 2
    4 7011 2 3
    5 7011 3 2

    In the diagrams table all the codes in diagrams.diagid are unique, i.e. 7020 only appears once...... products have numerous diagrams (i.e 7020 in the above table twice)

    am i getting clearer than mud?!

    b

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by barney0o0 View Post
    products have numerous diagrams (i.e 7020 in the above table twice)
    actually, it's the opposite

    diagrams (e.g. 7020) have numerous products (1, 2)

    so the same diagram describes two products



    now let's move on to the next part of the puzzle...
    Some 'collectons' have products that contain numerous diagrams
    so apparently a single product can have more than one diagram?

    then something isn't right about your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hum......half right, just from the other side

    So yes, you are right, a diagram can have numerous products, and a product can only have one diagram....

    'collections' have numerous diagrams, products just have one......

    If i have a div with a repeat region, i would like to have something like:

    diagram image (7020)
    This contains X products

    diagram image (7014)
    This contains X products

    etc, etc

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't know what a repeat region is, but i can sort of imagine it

    so you want to list the diagrams in a particular collection, and show the count of the number of products in that collection, but only products belonging to a certain family?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nearly

    so you want to list the diagrams in a particular collection
    within a collection, due to the amount of products, the image code will be retrieved numerous time, hence i was thinking about using SELECT UNIQUE to retreive the diagram code just once...

    show the count of the number of products in that collection
    count of the number of products that refer to that diagram

    The last bit.........

    Code:
    WHERE family_en.name='$1' AND collections_en.colname='$2'
    ..are string values sent from the navigation, i.e. family='metals', collection='gold'

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    SELECT diagrams.diagid
         , diagrams.dthumb
         , COUNT(*) AS products
      FROM collections_en
    INNER
      JOIN products
        ON products.collink = collections_en.colid
     WHERE EXISTS
           ( SELECT 1
               FROM family_en 
              WHERE famid = products.famlink
                AND name='$1' )
       AND collections_en.colname = '$2'
    GROUP
        BY diagrams.diagid
         , diagrams.dthumb
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy..erm, it doesnt work ...i get
    Code:
    #1054 - Unknown column 'diagrams.diagid' in 'field list'
    I dont know if itll help, but heres structures and a bit of data

    Code:
    CREATE TABLE IF NOT EXISTS `collections_en` (
      `colid` int(11) NOT NULL AUTO_INCREMENT,
      `colname` varchar(255) COLLATE utf8_bin NOT NULL,
      `famno` varchar(3) COLLATE utf8_bin NOT NULL,
      `thumb` varchar(255) COLLATE utf8_bin NOT NULL,
      `main` varchar(255) COLLATE utf8_bin NOT NULL,
      `new` varchar(2) COLLATE utf8_bin NOT NULL,
      `feature` varchar(2) COLLATE utf8_bin NOT NULL,
      PRIMARY KEY (`colid`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=34 ;
    
    --
    -- Dumping data for table `collections_en`
    --
    
    INSERT INTO `collections_en` (`colid`, `colname`, `famno`, `thumb`, `main`, `new`, `feature`) VALUES
    (1, 'Anilines', '4', 'Anilines_1S.jpg', 'Anilines_1.jpg', '', ''),
    
    CREATE TABLE IF NOT EXISTS `family_en` (
      `famid` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(120) NOT NULL,
      `text` text NOT NULL,
      `thumb_image` varchar(255) NOT NULL,
      `main_image` varchar(255) NOT NULL,
      `alt` varchar(255) NOT NULL,
      `title` varchar(255) NOT NULL,
      `home_thumb` varchar(255) NOT NULL,
      PRIMARY KEY (`famid`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
    
    --
    -- Dumping data for table `family_en`
    --
    
    INSERT INTO `family_en` (`famid`, `name`, `text`, `thumb_image`, `main_image`, `alt`, `title`, `home_thumb`) VALUES
    (1, 'Aluminium', '<p>A traditional collection of wooden profiles covered by various aluminium foils</p>', '', 'fam_metallic.jpg', '', '', 'metalic.jpg');
    
    CREATE TABLE IF NOT EXISTS `products` (
      `prodid` int(11) NOT NULL AUTO_INCREMENT,
      `code1` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
      `code2` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
      `thumb` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `famlink` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
      `collink` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
      `mainimage` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `desc_en` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `desc_it` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`prodid`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;
    
    --
    -- Dumping data for table `products`
    --
    
    INSERT INTO `products` (`prodid`, `code1`, `code2`, `thumb`, `famlink`, `collink`, `mainimage`, `desc_en`, `desc_it`) VALUES
    (1, '7002', '0525', '7002', '4', '6', '', '', '');
    
    
    CREATE TABLE IF NOT EXISTS `diagrams` (
      `did` int(11) NOT NULL AUTO_INCREMENT,
      `diagid` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
      `dthumb` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `dmain` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `wid` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      `hei` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`did`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;
    
    --
    -- Dumping data for table `diagrams`
    --
    
    INSERT INTO `diagrams` (`did`, `diagid`, `dthumb`, `dmain`, `wid`, `hei`) VALUES
    (1, '7002', '7002', '', '', '');

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how are diagrams related to collections?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Initally the 'products' are relative to the 'collctions' and the 'families', i.e. selecting all products that 'collink' and 'famlink' are true to the passed variable...

    Then select diagrams that are applicable to the products

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, now i am totally lost

    unless you can precisely identify which columns have to match between tables, i can't help you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hum.........

    so, initally select all products (from products) where products.famlink = $1 and products.collink = $2.

    $1 and $2 are text, i.e. aluminum/gold. Therefore i need to link the familiy_en(.famidid)/collections_en(.colid) to retrieve the 'text'....as in the 'products' they are numbers relative to the familiy_en/collections_en records.

    Based on my first crude post...this was achieved.

    I then want to extend the script to include two other elements.

    Firstly, link from products.code1 to diagrams.diagid, this was achieved in my first post, however i only want to pull down one unique diagrams.diagid (or products.code1) so that i can display a image.

    The second part is to count the number of products that have the same diagrams.diagid (the same as products.code1)

    So, then, i would be able to produce a list of each applicable diagrams and add the number of products(.code1) are equal to diagrams(.diagid)


    diagram image (7020)
    This contains X products

    diagram image (7014)
    This contains X products


    Based on my first attempt, the script ran through all the records.....i.e. displying 7020 the number of times the product had that code, rather than showing it just the once...

    ..have i confused more?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by barney0o0 View Post
    ..have i confused more?
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Evangelist
    Join Date
    Mar 2006
    Posts
    412
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh rudy.........

    Ok, rather than thowing the baby out with the bathwater...how about scrapping the count/group by bit.....and just focus on the first point of select unique?


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
  •