2 Replies - 942 Views - Last Post: 23 April 2010 - 04:09 PM Rate Topic: -----

#1 gymangel812  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 116
  • Joined: 15-February 10

Select query from multiple tables not correct

Posted 23 April 2010 - 10:39 AM

I have a DB with 4 tables. I am using this query to select a product_name (in this case tostee) from the tables:
Select products.productid, product_name, product_description, origin, image_name, price
from products, origins, images, prod_origins
Where products.productid = prod_origins.productid
And products.productid = images.productid
And product_name like '%tostee%'

The results I get are correct except the origins column is showing multiple matches for the same description when there should only be two matches. All the other columns are duplicate data in the query except origins. It is showing multiple origins for each name/description/price. I've attached the sql file with all the info for each table if that helps. thanks!!

Attached File(s)



Is This A Good Question/Topic? 0
  • +

Replies To: Select query from multiple tables not correct

#2 Paul-  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 61
  • View blog
  • Posts: 260
  • Joined: 11-December 09

Re: Select query from multiple tables not correct

Posted 23 April 2010 - 11:46 AM

This is a typical mistake I have made many times, when you forget a join condition and get a Cartesian product in the result. You need to add a join between origins and prod_origins.

Select products.productid, product_name, product_description, origin, image_name, price
from products, origins, images, prod_origins
Where products.productid = prod_origins.productid
And products.productid = images.productid
AND PROD_ORIGINS.ORIGINID = ORIGINS.ORIGINID
And product_name like '%tostee%'


Was This Post Helpful? 1
  • +
  • -

#3 gymangel812  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 116
  • Joined: 15-February 10

Re: Select query from multiple tables not correct

Posted 23 April 2010 - 04:09 PM

View PostPaul-, on 23 April 2010 - 10:46 AM, said:

This is a typical mistake I have made many times, when you forget a join condition and get a Cartesian product in the result. You need to add a join between origins and prod_origins.

Select products.productid, product_name, product_description, origin, image_name, price
from products, origins, images, prod_origins
Where products.productid = prod_origins.productid
And products.productid = images.productid
AND PROD_ORIGINS.ORIGINID = ORIGINS.ORIGINID
And product_name like '%tostee%'


that solved it, thanks!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1