By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

Join Problem...

P: n/a
I'm a bit new to MySQL (know MS SQL well...and that may be the
problem...getting the syntax confused) and I'm having a join problem...can
anyone offer some help? Here's my problem:
I have table1 that needs to "left" join to table1A, table1B, and table1C
which is corrently done with the following:

select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b on table1.ID = table1b.ID
left join table1c on table1.ID = table1c.ID
order by table1.x

The problem arises when I need an "inner" join from table1b to table2.
Here's what I'm trying to do, but it's not working.

select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b
inner join table2 on table1b.XX = table2.XX
on table1.ID = table1b.ID
left join table1c on table1.ID = table1c.ID
order by table1.x

Any help would greatly be appreciated.
TIA
-Bruce Duncan
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Bruce Duncan" <bruce~w~duncan@~hotmail.com> wrote in message
news:10*************@corp.supernews.com...
I'm a bit new to MySQL (know MS SQL well...and that may be the
problem...getting the syntax confused) and I'm having a join problem...can
anyone offer some help? Here's my problem:
I have table1 that needs to "left" join to table1A, table1B, and table1C
which is corrently done with the following:

select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b on table1.ID = table1b.ID
left join table1c on table1.ID = table1c.ID
order by table1.x

The problem arises when I need an "inner" join from table1b to table2.
Here's what I'm trying to do, but it's not working.

select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b
inner join table2 on table1b.XX = table2.XX
on table1.ID = table1b.ID
left join table1c on table1.ID = table1c.ID
order by table1.x

Any help would greatly be appreciated.
TIA
-Bruce Duncan


Just to let everyone know what I did to get it to work:
select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b on table1.ID = table1b.ID
inner join table2 on table1b.XX = table2.XX
left join table1c on table1.ID = table1c.ID
order by table1.x

-Bruce Duncan

Jul 20 '05 #2

P: n/a
You have gotten rid of the error message, but that does not mean your query
semantics remain intact. When mixing inner and outer joins, join order is
important, so I normally specify it using paranthesis. So i recommend you
verify that you're getting the correct resultset.

Consider following scenario:
table1
ID:
1
2
3

table1b
ID XX
1 xx
3 yy

table2
XX
xx
yy
zz

Now, which IDs do you want to see in the output - all three, or just 1 and
3?

BTW, if I were MySQL internals developer, I would produce an error for your
"correct" query as well - for the reasons stated above. Most other SQL
engines would say something like "a table can not be part of both inner and
outer joins"

"Bruce Duncan" <bruce~w~duncan@~hotmail.com> wrote in message
news:10*************@corp.supernews.com...
"Bruce Duncan" <bruce~w~duncan@~hotmail.com> wrote in message
news:10*************@corp.supernews.com...
I'm a bit new to MySQL (know MS SQL well...and that may be the
problem...getting the syntax confused) and I'm having a join problem...can anyone offer some help? Here's my problem:
I have table1 that needs to "left" join to table1A, table1B, and table1C
which is corrently done with the following:

select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b on table1.ID = table1b.ID
left join table1c on table1.ID = table1c.ID
order by table1.x

The problem arises when I need an "inner" join from table1b to table2.
Here's what I'm trying to do, but it's not working.

select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b
inner join table2 on table1b.XX = table2.XX
on table1.ID = table1b.ID
left join table1c on table1.ID = table1c.ID
order by table1.x

Any help would greatly be appreciated.
TIA
-Bruce Duncan


Just to let everyone know what I did to get it to work:
select table1.x, table1a.y, table1b.z, table1c.q
from table1
left join table1a on table1.ID = table1a.ID
left join table1b on table1.ID = table1b.ID
inner join table2 on table1b.XX = table2.XX
left join table1c on table1.ID = table1c.ID
order by table1.x

-Bruce Duncan


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.