| | |
Cartesian Product Problem
Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
Thread Solved
![]() |
Hi everyone,
I am still fairly new to Oracle and SQL. I have two tables with the same information in them. Table1 has the same columns as table2. The differnce between them is the date column. Table1 has 2005 records while table2 has 2006 orders. I am trying to write a SQL statement that will display the contents of these tables across. Meaning, I have all columns from table1 on the left and then immediately right of that, I have the elements from table2. I want it like this so I can look at the 2005 records and the 2006 records going right across the row so that I can compare them. Below is an example of the statement I am trying to use:
I want to have the result table look like this:
date | qty | amt | date2 | Quantity | Amount
The columns going straight across.
I keep getting warnings that this gives me a cartesian product. Table1 and Table2 each have roughly 50,000 rows in them, so if this is a cartesian join, this is a huge problem. Can anyone help me accomplish this?
Thanks,
Nick
I am still fairly new to Oracle and SQL. I have two tables with the same information in them. Table1 has the same columns as table2. The differnce between them is the date column. Table1 has 2005 records while table2 has 2006 orders. I am trying to write a SQL statement that will display the contents of these tables across. Meaning, I have all columns from table1 on the left and then immediately right of that, I have the elements from table2. I want it like this so I can look at the 2005 records and the 2006 records going right across the row so that I can compare them. Below is an example of the statement I am trying to use:
Oracle Syntax (Toggle Plain Text)
SELECT a.DATE, a.qty, a.amt, b.DATE AS Date2 b.qty AS Quantity, b.amt AS Amount FROM table1 a, table2 b;
I want to have the result table look like this:
date | qty | amt | date2 | Quantity | Amount
The columns going straight across.
I keep getting warnings that this gives me a cartesian product. Table1 and Table2 each have roughly 50,000 rows in them, so if this is a cartesian join, this is a huge problem. Can anyone help me accomplish this?
Thanks,
Nick
Your problem is that you are not specifying a column to join on.
What do the two tables have in common? What is it about a record in table A that means it should be placed next to a record in table B?
Usually on a join you would say something like
By not specifying what to join it links every tuple in table A to every one in table B, so you have A*B tuples returned.
If there is nothing in common between the tables (as I suspect if they are identical) the correct way of displaying the data would be to use a UNION.
This will give you A+B records back
What do the two tables have in common? What is it about a record in table A that means it should be placed next to a record in table B?
Usually on a join you would say something like
select pos.*
from person per
join posession pos on pos.person_id = per.id
where per.id = 5By not specifying what to join it links every tuple in table A to every one in table B, so you have A*B tuples returned.
If there is nothing in common between the tables (as I suspect if they are identical) the correct way of displaying the data would be to use a UNION.
Oracle Syntax (Toggle Plain Text)
SELECT * FROM tableA UNION SELECT * FROM tableB
This will give you A+B records back
Note to self... pocket cup
![]() |
Similar Threads
- Multiple results (MySQL)
- HP Laptop won't boot up (Troubleshooting Dead Machines)
- Question about SELECT statement (MySQL)
- figuring out PK/FK (MS SQL)
- MySql multiple table query problem.... (MySQL)
- Creating a Database from scratch, need assistance (MS Access and FileMaker Pro)
Other Threads in the Oracle Forum
- Previous Thread: order and then compare values from same column
- Next Thread: maximum no of column in a table
| Thread Tools | Search this Thread |
2009predictions acquisition amazon.com bartz bernanke cia citrix cloudcomputing crm database dell economy enterprise enterprise2.0 enterprisesoftware federalreserve forbes hp ibm intellipedia internet larryellison layoffs linux loughridge mediawiki michaeljackson microsoft neverland nortel notebooks oil operatingsystem oracle palm rimm saas salesforce sap seagate socialcomputing sun sybase technologystocks virtualiron virtualization vmware wiki wikipedia xen yahoo zoho





