[Solved] Repeating values... arghhhh

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

[Solved] Repeating values... arghhhh

Post by Steveo31 »

Code: Select all

SELECT  DISTINCT i.store_name, i.product_name, i.uploadDate, up.thumb_path
FROM inventory AS i, uploads AS up
ORDER  BY i.uploadDate DESC
I don't think the database structure is relevant at this point, but let me know if you need it.

This is returning:

Code: Select all

store_name   	  product_name   	  uploadDate   	  thumb_path
Steve's Knick-Knacks 	Toaster    	2004-10-26 	members/steveo31/toaster_thumb.jpg
Steve's Knick-Knacks 	Toaster    	2004-10-26 	members/steveo31/oil-filter_thumb.jpg
Steve's Knick-Knacks 	Oil Filter 	2004-10-25 	members/steveo31/toaster_thumb.jpg
Steve's Knick-Knacks 	Oil Filter 	2004-10-25 	members/steveo31/oil-filter_thumb.jpg
in phpMyAdmin. I thought the DISTINCT would work, but it doesn't. I just need to pull those fields out once... not twice.

Any ideas, or do you need more info?
Last edited by Steveo31 on Tue Oct 26, 2004 6:59 pm, edited 1 time in total.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Your SQL is incorrect, you are going to have to be more specific about what you are trying to get out of the database, how are you joining the 2 tables etc.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

If you look at the return results each row is distinct (product_name and thumb path vary). You may want to look up DISTINCT ON ... to select the columns you wish to be unique.
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

Yeah, kinda figured that wayne ;) :D

I'm sure there's a way to pull them out without DISTINCT...

What I'm attempting is to pull the store_name, product_name, and upload date out of a table called "inventory". In addition, pull out the thumbnail path from a table called "uploads". This script has been chopped up a lot, so a while ago I had a WHERE clause to pull only the info out WHERE store_name is the one owned by the user.

Code: Select all

CREATE TABLE `inventory` (
  `id` int(10) NOT NULL auto_increment,
  `product_id` varchar(100) NOT NULL default '',
  `product_name` varchar(30) NOT NULL default '',
  `uploadDate` date NOT NULL default '0000-00-00',
  `store_name` varchar(30) NOT NULL default '',
  `store_owner` varchar(30) NOT NULL default '',
  `store_email` varchar(50) NOT NULL default '',
  `search_hits` int(10) NOT NULL default '0',
  `hits` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `product_name` (`product_name`,`store_name`)
)

Code: Select all

CREATE TABLE `uploads` (
  `id` int(10) NOT NULL auto_increment,
  `username` varchar(30) NOT NULL default '',
  `lastUploadDate` date NOT NULL default '0000-00-00',
  `product_id` varchar(100) NOT NULL default '',
  `product_name` varchar(20) NOT NULL default '',
  `image_path` varchar(50) default 'members/nophoto_thumb.jpg',
  `thumb_path` varchar(50) default 'members/nophoto_thumb.jpg',
  PRIMARY KEY  (`id`)
)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Assuming product_name in uploads is a foreign-key to product_name in inventory...

Code: Select all

SELECT i.store_name, i.product_name, i.uploadDate, t.thumb_path
FROM inventory AS i 
INNER JOIN uploads AS u ON i.product_name=u.product_name
Btw, be consistent when naming attributes. You seem to use a lot of _ and then you you uploadDate instead of upload_date
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

Hah, funny you should mention that. I was thinking about changing them the other day.

Code works great, thanks!
Post Reply