Chained joins

Is it possible to chain a join to an outer joined table?
I have three tables, A, B, and C
Table A parent records have optional child records in table B.

So I did an outer join and it works fine.
But I want to expand a column of the child table B.

Parent Table A ---> left outer joined to optional child table B ----> lookup a column of B in table C

Can that be done ?




LVL 2
kaller2Asked:
Who is Participating?
 
AdrianSRUConnect With a Mentor Commented:
When you list the tables in the FROM clause and handle the join conditions in the WHERE clause, you are essentially getting an INNER JOIN.  You need to use LEFT JOINs like this:

SELECT A.b, B.c, C.d FROM (A LEFT JOIN B ON A.b=B.b) LEFT JOIN C ON B.c=C.c


-Adrian
0
 
AdrianSRUCommented:
You can have as many joins as you want.  It would look like this:

SELECT <columns> FROM (<table_A> LEFT JOIN <table_B> ON <join_condition>) LEFT JOIN <table_C> ON <join_condition;


--Adrian
0
 
kaller2Author Commented:
but does that join table A to C or B to C ?

I notice you used brackets around the FROM (), is that the trick ?

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kaller2Author Commented:
I dont see any round brackets syntax used like that in the manual and it gaives an error when I try to use them like that.
0
 
AdrianSRUCommented:
>but does that join table A to C or B to C ?

The join is performed based on the join conditions that are specified.  Here is an example:

TableA (
ID bigint not null primary key,
TableA_Field int)

TableB (
ID bigint not null primary key,
TableB_Field int)

TableC (
ID bigint not null primary key,
TabkeC_Field int)

This query joins TableA with TableB, and then joins TableB with TableC:

SELECT * FROM (TableA LEFT JOIN TableB ON TableA.TableA_Field=TableB.ID) LEFT JOIN TableC ON TableB.TableB_Field=TableC.ID


>I dont see any round brackets syntax used like that in the manual and it gaives an error when I try to use them like that.

The () around the join between TableA and TableB are not necessary, I just use them for readability.  Post the query that you are working with and I should be able to figure out what the problem is.


-Adrian
0
 
kaller2Author Commented:
#
# Table structure for table `A`
#

CREATE TABLE A (
  a tinyint(4) NOT NULL default '0',
  b tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (a,b)
) TYPE=MyISAM;

#
# Dumping data for table `A`
#

INSERT INTO A VALUES (100, 10);
INSERT INTO A VALUES (100, 11);
INSERT INTO A VALUES (100, 12);
# --------------------------------------------------------

#
# Table structure for table `B`
#

CREATE TABLE B (
  b tinyint(4) NOT NULL default '0',
  c tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (b)
) TYPE=MyISAM;

#
# Dumping data for table `B`
#

INSERT INTO B VALUES (10, 30);
INSERT INTO B VALUES (11, 30);
# --------------------------------------------------------

#
# Table structure for table `C`
#

CREATE TABLE C (
  c tinyint(4) NOT NULL default '0',
  d varchar(4) NOT NULL default '0',
  PRIMARY KEY  (c)
) TYPE=MyISAM;

#
# Dumping data for table `C`
#

INSERT INTO C VALUES (30, 'good');
INSERT INTO C VALUES (31, 'bad');

=========================================
The problem is to join A to B with an outer join,
and then chain the result to C

For example:
 
select A.b, B.c, C.d
from A,B,C
where A.b=B.b and B.c=C.c

gives
        
b       c       d
10       30       good
11       30       good

Which is not an outer join.
We need

b       c       d
10       30       good
11       30       good
12     NULL   NULL
0
 
kaller2Author Commented:
Lesson learned: be careful with table order with asymmetric queries. The chained join will be ok as left join for my purposes.  Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.