asked on

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 ?

AdrianSRU

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

kaller2

ASKER

but does that join table A to C or B to C ?

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

kaller2

ASKER

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.

AdrianSRU

>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

kaller2

ASKER

#
# 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

ASKER CERTIFIED SOLUTION

AdrianSRU

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kaller2

ASKER

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.