get list of categories, and a random item from each category

[jordanwb]

I have the following two tables:

 

gd_gallery_categories, CREATE TABLE `gd_gallery_categories` (
  `gallery_cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `gallery_name` varchar(64) DEFAULT NULL,
  `gallery_description` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`gallery_cat_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

gd_gallery_images, CREATE TABLE `gd_gallery_images` (
  `gallery_image_id` int(11) NOT NULL AUTO_INCREMENT,
  `gallery_cat_id` int(11) DEFAULT NULL,
  `gallery_image_name` varchar(64) DEFAULT NULL,
  `gallery_image_path` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`gallery_image_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

 

What I want to do is get all the categories in gd_gallery_categories and for each category also retrieve a random image associated with that category in the same row. The value stored in gd_gallery_images.gallery_cat_id is the category id stored in gd_gallery_categories.gallery_cat_id

[The Little Guy]

I believe something like this should work:

 

SELECT * FROM gd_gallery_categories c JOIN gd_gallery_images i USING(gallery_cat_id) order by rand();

[jordanwb]

I believe something like this should work:

 

SELECT * FROM gd_gallery_categories c JOIN gd_gallery_images i USING(gallery_cat_id) order by rand();

 

Thanks for replying,

 

No that won't work. I want to get each record from gd_gallery_categories and also get one random record from gd_gallery_images in the category. The records returned from gd_gallery_categories would be ordered by gallery_cat_id in descending order.

[The Little Guy]

That will get one record from gd_gallery_categories...

[jordanwb]

For each record in gd_gallery_categories there will be one or more records in gd_gallery_images associated with that record in gd_gallery_categories. For each record I read from gd_gallery_categories, I want to get one random record from gd_gallery_images associated with that record.

[The Little Guy]

Which is what that will do.

 

Have you even tested it?

[jordanwb]

Which is what that will do.

 

Have you even tested it?

 

Yes I did, see picture of results: http://img405.imageshack.us/img405/8157/27243565.png The query returns every record in gd_gallery_images and lists the same single record in gd_gallery_categories twice. There should be only one result, the single row from gd_gallery_categories and either one of the two records in gd_gallery_images.

[The Little Guy]

Try it with the group by:

 

SELECT * FROM gd_gallery_categories c JOIN gd_gallery_images i USING(gallery_cat_id) group by c.gallery_cat_id order by rand();

[jordanwb]

Try it with the group by:

 

SELECT * FROM gd_gallery_categories c JOIN gd_gallery_images i USING(gallery_cat_id) group by c.gallery_cat_id order by rand();

 

That'll work, thanks. On a related note, where does "c" and "i" come from?

[The Little Guy]

that temporarily renames the tables to c and i, so instead of doing:

 

gd_gallery_categories.column_name

 

you can do:

 

c.column_name