SQL Query returns duplicates when there is only one record in the database
I am experimenting with a test database to learn SQL, (MySQLi PHP)
I am retrieving data from two tables add_images and item, where the primary key for "item" is referred to as a foreign key in "add_images". One "element" will have only one image (add_images).
I need to get specific details about the item that is referenced by its filename.
I only have one record in the database under d7.jpg which is of type VARCHAR. When I run the query below I get 34 results, where should I only get one? Why is this?
This also happens when I run a request in phpmyadmin
Here is the request
SELECT item_name, catagory, brand, store, location, month, year, details FROM add_images, item WHERE add_images.image_name='d7.jpg '
I think I am doing something fundamentally wrong here when I have my tables set up?
Hope someone can advise!
thank
below are two tables
CREATE TABLE IF NOT EXISTS `add_images`( `image_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'unique id for an image',
`item_id` int(10) unsigned NOT NULL COMMENT 'unique id for the item been added',
`image_name` varchar(20) NOT NULL COMMENT 'name of the image',
`type` enum('standard','deleted','profile','look','item') NOT NULL COMMENT 'status and type of image',
`date_added` varchar(50) NOT NULL COMMENT 'date image was added',
PRIMARY KEY (`image_id`),
UNIQUE KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Add images to item or profile picture' AUTO_INCREMENT=50 ;
CREATE TABLE IF NOT EXISTS `item` (
`item_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key for item',
`item_name` varchar(20) NOT NULL COMMENT 'title name of the item',
`catagory` enum('accessories','jackets','coats','footwear','legwear','jeanswear','dresses','shirts','tops','t-shirts','knitwear','skirts','shorts') NOT NULL COMMENT 'item catagory',
`brand` varchar(20) NOT NULL COMMENT 'brand of product',
`store` varchar(20) NOT NULL COMMENT 'store the item was purchased',
`location` varchar(20) NOT NULL COMMENT 'location the item was purchased',
`month` enum('January','February','March','April','May','June','July','August','September','October','November','December') NOT NULL COMMENT 'month the item was purchased',
`year` int(2) NOT NULL COMMENT 'year the item was purchased',
`details` varchar(500) NOT NULL COMMENT 'details about the item description',
`date` varchar(50) NOT NULL COMMENT 'date item created',
PRIMARY KEY (`item_id`),
UNIQUE KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='holds data about the item' AUTO_INCREMENT=72 ;
source to share
Your query does the join but fails the condition in the WHERE clause in the table item
, so the query concatenates all records item
with the add_images result regardless of whether item_id matches. You will want to modify your query to reflect this;
SELECT item_name, catagory, brand, store, location, month, year, details
FROM add_images, item
WHERE add_images.image_name='d7.jpg '
AND item.item_id = add_images.item_id;
EDIT: If you rewrite the query as an explicit join, it is easier for you to see if there is no relationship between tables;
SELECT item_name, catagory, brand, store, location, month, year, details
FROM add_images
JOIN item ON item.item_id = add_images.item_id -- ON specifies the link
WHERE add_images.image_name='d7.jpg ';
source to share
because you are doing a JOIN (implicitly using 2 tables in FROM) without specifying a reference between them.
A good syntax would be:
SELECT item_name, catagory, brand, store, location, month, year, details FROM add_images, item WHERE add_images.image_name='d7.jpg ' AND add_images.item_id=item.item_id
source to share
You need a join condition:
SELECT item_name, catagory, brand, store, location, month, year, details
FROM add_images, item
WHERE add_images.item_id = item.item_id and add_images.image_name='d7.jpg '
- using the syntax SQL-89 or:
SELECT item_name, catagory, brand, store, location, month, year, details
FROM add_images
JOIN item ON add_images.item_id = item.item_id
WHERE add_images.image_name='d7.jpg '
- using the syntax SQL-92.
source to share