Go Back   Computer Forums > General Computing > Programming
Click Here to Login
Join Computer forums Today


Reply
 
Thread Tools Search this Thread Display Modes
 
Old 03-07-2011, 01:01 PM   #1
CFu
Baseband Member
 
Join Date: Mar 2011
Posts: 52
Question Cartesian product in query

I'm using this statement in Oracle SQL Developer:

SELECT c.customerid
FROM CUSTOMERS c
INNER JOIN ORDERS o
ON c.customerid=o.customerid

.. and I get a Cartesian product (all rows from ORDERS table instead of just the matching id's). Why is this?

I mean, there are only 10 id's in CUSTOMERS and it returns me 800 rows because there are 800 orders.. I don't understand.

Thank you
__________________

CFu is offline   Reply With Quote
Old 03-09-2011, 01:06 PM   #2
Site Team
 
root's Avatar
 
Join Date: Mar 2004
Posts: 8,004
Default Re: Cartesian product in query

it's because you are joining the tables (so c.customerid appears multiple times) and have no limiting function.

what you're doing is joining two tables

basically in table 1 you have say customerid and customer name
in table 2 you have customerid and order id

this is a relational database and enables multiple customers to place multiple orders.

so you have 10 customers, (10 customer id's in the customers table).

they've placed 50 orders.

when you select with your query you're joining the

table 1 looks like
1 bob
2 dave
3 steve

table two looks like
1 bread
1 beans
2 bread
1 sausages

your query is returning the joined tables so the customer id for dave is getting returned as many times as he's ordered stuff.

change the query to select * from customers c inner join orders o on c.customerid=o.customerid and you'll see what I mean about the combined table

you can use select unique if you want to join in this way. (to do what I think you're trying to do)

I guess you're trying to return the customer Id's of people who have ordered.
i.e you can't just say select from customers because steve would be returned even though he's ordered nothing
__________________

__________________
I didn’t fight my way to the top of the food chain to be a vegetarian…
Im sick of people saying 'dont waste paper'. If trees wanted to live, they'd all carry guns.
"The inherent vice of capitalism is the unequal sharing of blessings; The inherent vice of socialism is the equal sharing of miseries."
root is offline   Reply With Quote
Old 03-09-2011, 02:47 PM   #3
CFu
Baseband Member
 
Join Date: Mar 2011
Posts: 52
Default Re: Cartesian product in query

I was trying to return all the customers that have placed orders, but not repeated.
I have achieved this by the following query:

SELECT c.contactname, count(o.customerid) "N. of Orders"
FROM CUSTOMERS c, ORDERS o
WHERE c.customerid=o.customerid GROUP BY c.contactname

There I can see the number of orders each customer has placed (I have no null columns in my tables).

What I wanted in the beginning was this:

SELECT distinct c.contactname, o.customerid
FROM customers c, orders o
WHERE c.customerid=o.customerid
order by 1

Which now works.
CFu is offline   Reply With Quote
Old 03-09-2011, 04:26 PM   #4
Site Team
 
root's Avatar
 
Join Date: Mar 2004
Posts: 8,004
Default Re: Cartesian product in query

distinct and unique is the same keyword in oracle, so you've pretty much came to the same solution I did!
__________________
I didn’t fight my way to the top of the food chain to be a vegetarian…
Im sick of people saying 'dont waste paper'. If trees wanted to live, they'd all carry guns.
"The inherent vice of capitalism is the unequal sharing of blessings; The inherent vice of socialism is the equal sharing of miseries."
root is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -5. The time now is 11:36 PM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0