Select unique with count

Databases
1

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

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

actually, it’s the opposite :slight_smile:

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

3

what exactly are you interested in counting?

please explain the one-to-many relationships involved between your various tables

4

Use GROUP BY instead of SELECT UNIQUE?

5

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’

6

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)

7

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?

8

…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!

9

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?

10

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

11

hum…half right, just from the other side :slight_smile:

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

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?

13

nearly :slight_smile:

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…

WHERE family_en.name='$1' AND collections_en.colname='$2'

…are string values sent from the navigation, i.e. family=‘metals’, collection=‘gold’

14

try this –

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
15

Hi Rudy…erm, it doesnt work :slight_smile: …i get

#1054 - Unknown column 'diagrams.diagid' in 'field list'

I dont know if itll help, but heres structures and a bit of data


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

how are diagrams related to collections?

17

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

okay, now i am totally lost

unless you can precisely identify which columns have to match between tables, i can’t help you

19

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? :wink:

20

yes :slight_smile: