| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-16-11, 12:53
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 76
|
|
|
Need Help in cartsian product
|
|
Hi can you help me on this how to make a cartesian product in two tables,is this displaying all the columns in the table_A full join Table_b is this a cartesian product meant?please help me thank you in advance and i am hoping for your positive response.
|
|
08-16-11, 13:08
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
See the syntax and description of joined-table.
subselect - IBM DB2 9.7 for Linux, UNIX, and Windows
Quote:
joined-table
Code:
.-INNER-----.
>>-+-table-reference--+-----------+--JOIN--table-reference--ON--join-condition-+-><
| '-| outer |-' |
+-table-reference--CROSS JOIN--table-reference------------------------------+
'-(--joined-table--)--------------------------------------------------------'
outer
.-OUTER-.
|--+-LEFT--+--+-------+-----------------------------------------|
+-RIGHT-+
'-FULL--'
A joined table specifies an intermediate result table that is the result of either an inner join or an outer join. The table is derived by applying one of the join operators: CROSS, INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER to its operands.
Cross joins represent the cross product of the tables, where each row of the left table is combined with every row of the right table. ...
...
|
You can make a cartesian product by
table_a CROSS JOIN table_b
Another not so apparent way may be
table_a JOIN table_b ON 0=0
|
|
08-16-11, 15:05
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 76
|
|
|
|
Quote:
Originally Posted by tonkuma
|
you mean like this,correct me if i am wrong
select A.col1,A.col2,A.col3,B.col1,B.col2,B.col3
from Table_A A cross join Table_B B
|
Last edited by jemz; 08-17-11 at 00:42.
|
08-16-11, 17:50
|
|
∞∞∞∞∞∞
|
|
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
|
|
select count(sir) from dbforums.db2 where user = 'jemz'
|
|
08-16-11, 21:04
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I'm sorry!
I have no authority to edit the jemz's posts.
I'm not sure the error message nor SQLSTATE.
UPDATE dbforums.db2 SET post = REPLACE(post , 'sir' , '') where user = 'jemz'
SQL0551N "TONKUMA" does not have the required authorization or privilege to
perform operation "UPDATE" on object "DBFORUMS.DB2". SQLSTATE=42501
|
Last edited by tonkuma; 08-16-11 at 21:29.
|
08-17-11, 00:46
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 76
|
|
Quote:
Originally Posted by tonkuma
I'm sorry!
I have no authority to edit the jemz's posts.
I'm not sure the error message nor SQLSTATE.
UPDATE dbforums.db2 SET post = REPLACE(post , 'sir' , '') where user = 'jemz'
SQL0551N "TONKUMA" does not have the required authorization or privilege to
perform operation "UPDATE" on object "DBFORUMS.DB2". SQLSTATE=42501
|
Hi, Tonkuma i apologize if i call you sir,...i will remove all the sir that addresses to you....I hope this will be okay now.
|
|
08-23-11, 12:11
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 76
|
|
Quote:
Originally Posted by tonkuma
|
Hi tonkuma i get back to this thread i am confuse with this,
table_a cross join table_b
do i need the where clause?
because if i only use
table_a CROSS JOIN table_b
i got 450 rows to display but if i specify the where clause
a.column = b.column
i got only 17 records... is this correct?please help me on this.
|
|
08-23-11, 14:48
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
... if i only use
table_a CROSS JOIN table_b
i got 450 rows to display but if i specify the where clause
a.column = b.column
i got only 17 records... is this correct?please help me on this.
|
It must be worked exactly as definition of joins and roles of where clause.
I can't explain more accurately and detailed than in manuals.
Please see carefully syntax and descriptions of subselect.
subselect - IBM DB2 9.7 for Linux, UNIX, and Windows
What issue do you have in the results?
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
table_a(column) AS (
VALUES
1, 2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18
)
,table_b(column) AS (
VALUES
2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26
)
SELECT COUNT(*) AS count_rows
FROM table_a a CROSS JOIN table_b b
-- WHERE a.column = b.column
;
------------------------------------------------------------------------------
COUNT_ROWS
-----------
450
1 record(s) selected.
Example 2:
Code:
------------------------------ Commands Entered ------------------------------
WITH
table_a(column) AS (
VALUES
1, 2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18
)
,table_b(column) AS (
VALUES
2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26
)
SELECT COUNT(*) AS count_rows
FROM table_a a CROSS JOIN table_b b
WHERE a.column = b.column
;
------------------------------------------------------------------------------
COUNT_ROWS
-----------
17
1 record(s) selected.
|
Last edited by tonkuma; 08-23-11 at 15:41.
Reason: Add examples.
|
08-23-11, 23:56
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 76
|
|
Quote:
Originally Posted by tonkuma
It must be worked exactly as definition of joins and roles of where clause.
I can't explain more accurately and detailed than in manuals.
Please see carefully syntax and descriptions of subselect.
subselect - IBM DB2 9.7 for Linux, UNIX, and Windows
What issue do you have in the results?
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
table_a(column) AS (
VALUES
1, 2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18
)
,table_b(column) AS (
VALUES
2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26
)
SELECT COUNT(*) AS count_rows
FROM table_a a CROSS JOIN table_b b
-- WHERE a.column = b.column
;
------------------------------------------------------------------------------
COUNT_ROWS
-----------
450
1 record(s) selected.
Example 2:
Code:
------------------------------ Commands Entered ------------------------------
WITH
table_a(column) AS (
VALUES
1, 2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18
)
,table_b(column) AS (
VALUES
2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26
)
SELECT COUNT(*) AS count_rows
FROM table_a a CROSS JOIN table_b b
WHERE a.column = b.column
;
------------------------------------------------------------------------------
COUNT_ROWS
-----------
17
1 record(s) selected.
|
Hi tonkuma,thank you for the reply,i am confuse what should i use when making the cartesian product of the 2 tables...
I attached the problem of cartesian,the table is to be used is the one that i send you before in other thread...please help me sir
please see the attachment
|
Last edited by jemz; 08-24-11 at 01:41.
|
08-24-11, 00:47
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 76
|
|
Quote:
Originally Posted by tonkuma
It must be worked exactly as definition of joins and roles of where clause.
I can't explain more accurately and detailed than in manuals.
Please see carefully syntax and descriptions of subselect.
subselect - IBM DB2 9.7 for Linux, UNIX, and Windows
What issue do you have in the results?
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
table_a(column) AS (
VALUES
1, 2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18
)
,table_b(column) AS (
VALUES
2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26
)
SELECT COUNT(*) AS count_rows
FROM table_a a CROSS JOIN table_b b
-- WHERE a.column = b.column
;
------------------------------------------------------------------------------
COUNT_ROWS
-----------
450
1 record(s) selected.
Example 2:
Code:
------------------------------ Commands Entered ------------------------------
WITH
table_a(column) AS (
VALUES
1, 2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18
)
,table_b(column) AS (
VALUES
2, 3, 4, 5, 6, 7, 8, 9,10
,11,12,13,14,15,16,17,18,19,20
,21,22,23,24,25,26
)
SELECT COUNT(*) AS count_rows
FROM table_a a CROSS JOIN table_b b
WHERE a.column = b.column
;
------------------------------------------------------------------------------
COUNT_ROWS
-----------
17
1 record(s) selected.
|
Hi Tonkuma, i send you pm,please read it.
|
|
08-24-11, 01:20
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Usually, you need not to use cartesian product.
Use it when you can't get expected result with inner join or outer join.
I'm not so interesting in this subject, now.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Need Help in cartsian product
