| |
|
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.
|
 |
12-26-08, 13:28
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 25
|
|
|
Joining two tables
|
|
 [FONT="Arial"] I am trying to join two tables as part of a CREATE TABLE AS
statement. I have tried using all types of JOIN (left, right, cross...) my problem is that I need the output to show entries from both side whether there is a match or not
This are the source tables:
l7 l8
group_id loy7 group_id loy8
1 7 1 5
2 2 3 1
4 3 4 7
This is the result I got:
Group_id loy7 loy8
1 7 5
2 2
4 3 7
This is what I need to get:
Group_id loy7 loy8
1 7 5
2 2
3 1
4 3 7
Any suggestions?
|
Last edited by obwan; 12-26-08 at 13:43.
|
12-26-08, 13:59
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,609
|
|
My guess would be a cross join on group_id.
-PatP
|
|
12-26-08, 14:16
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 25
|
|
|
|
Thank you Pat, I ran the following
create table obr_loytran_78z as select l7.group_id, l7.loyalty_trans loy7,
l8.loyalty_trans loy8
from obr_loyalty2007 l7
cross join obr_loyalty2008 l8 on(l8.group_id)
order by l7.group_id;
and received a 263,371 rows results, the original l7 table has 730 rows and the l8 table has 915 rows.
I'm puzzled.
|
|
12-26-08, 14:40
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,609
|
|
Your ON clause is sub-optimal... You need a more complex expression like I7.order_id = I8.order_id.
-PatP
|
|
12-26-08, 14:42
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 25
|
|
I will try that, thank you
|
|
12-26-08, 15:08
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 25
|
|
I ran the following and it did not work it returned only rows with matching group_id
create table obr_loytran_78z as select l7.group_id, l7.loyalty_trans loy7,
l8.loyalty_trans loy8
from obr_loyalty2007 l7
cross join obr_loyalty2008 l8 on(l7.group_id = l8.group_id)
order by l7.group_id;
|
|
12-26-08, 16:35
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,609
|
|
My bad... According to the documentation MySQL currently handles CROSS joins as though they were INNER joins, where at least with Inno-Db MySQL used to handle CROSS joins as FULL joins. There may be a new exception to JOIN handling that allows MySQL to do what SQL calls a FULL join, but I'd recommend doing the "heavy lifting" yourself.
Keep in mind that this is pure "air code", it has not been tested at all:
Code:
CREATE TABLE obr_loytran_78z
AS SELECT l71.group_id, l71.loyalty_trans loy7, l81.loyalty_trans loy8
FROM obr_loyalty2007 l71
LEFT JOIN obr_loyalty2008 l81
ON (l71.group_id = l81.group_id)
UNION SELECT l72.group_id, l72.loyalty_trans loy7, l82.loyalty_trans loy8
FROM obr_loyalty2007 l72
RIGHT JOIN obr_loyalty2008 l82
ON (l72.group_id = l82.group_id)
WHERE I72.group_id IS NULL
ORDER BY l71.group_id;
-PatP
|
|
12-29-08, 09:52
|
|
Registered User
|
|
Join Date: Mar 2007
Posts: 25
|
|
Sorry Pat, still did not work, I'm giving up. HAPPY NEW YEAR 
|
|
12-29-08, 10:01
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Code:
SELECT Coalesce(l7.group_id, l8.group_id) As [group_id]
, l7.loy7
, l8.loy8
FROM l7
FULL
JOIN l8
ON l7.group_id = l8.group_id
?
|
|
12-29-08, 10:08
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Oh and of course you can just use UNION and remove the excusion join
Code:
SELECT l7.group_id
, l7.loy7
, l8.loy8
FROM @l7 l7
LEFT
JOIN @l8 l8
ON l7.group_id = l8.group_id
UNION
SELECT l8.group_id
, l7.loy7
, l8.loy8
FROM @l8 l8
LEFT
JOIN @l7 l7
ON l7.group_id = l8.group_id
*shrug*
|
|
12-29-08, 10:08
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Code:
SELECT l7.group_id
, l7.loy7
, l8.loy8
FROM @l7 l7
LEFT
JOIN @l8 l8
ON l7.group_id = l8.group_id
UNION ALL
SELECT l8.group_id
, l7.loy7
, l8.loy8
FROM @l8 l8
LEFT
JOIN @l7 l7
ON l7.group_id = l8.group_id
WHERE l7.group_id IS NULL
Because MySQL is funny with FULLs, in't it!
|
|
12-29-08, 10:11
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Those last two posts have appeared in the wrong order... oh well!
|
|
| 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
>
Joining two tables
