Help on joining tables

Hi
I need some advises on how to join several tables and/or use "nested" joins
(if such a thing exist...)
I trying to select some data which involves 5 tables. The relation is as
follows -
Table1 should be joined with table2, table3 and table 4. Then Table 3 should
be joined with Table 5.
I've tried just using a standard "select ... from table1 JOIN Table2...
JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
but that doesn't really work.
I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how do
I tell that I wan't to join table 3 with table5.
I'd be happy if some of you can get me guided in the right direction on how
to do this, since I can't get my head around it.
In BOL it's mentioned that join expressions can be connected with AND or
with OR, but I can't find any examples on how to do it (...and also I'm not
sure if that will solve my problem...)
Regards
SteenSteen
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table1.key INNER JOIN
Table4 ON Table1.key=Table1.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need some advises on how to join several tables and/or use "nested"
joins
> (if such a thing exist...)
> I trying to select some data which involves 5 tables. The relation is as
> follows -
> Table1 should be joined with table2, table3 and table 4. Then Table 3
should
> be joined with Table 5.
> I've tried just using a standard "select ... from table1 JOIN Table2...
> JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
> but that doesn't really work.
> I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how
do
> I tell that I wan't to join table 3 with table5.
> I'd be happy if some of you can get me guided in the right direction on
how
> to do this, since I can't get my head around it.
> In BOL it's mentioned that join expressions can be connected with AND or
> with OR, but I can't find any examples on how to do it (...and also I'm
not
> sure if that will solve my problem...)
> Regards
> Steen
>|||Sorry,should be
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table3.key INNER JOIN
Table4 ON Table1.key=Table4.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Steen
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table1.key INNER JOIN
> Table4 ON Table1.key=Table1.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > I need some advises on how to join several tables and/or use "nested"
> joins
> > (if such a thing exist...)
> >
> > I trying to select some data which involves 5 tables. The relation is as
> > follows -
> >
> > Table1 should be joined with table2, table3 and table 4. Then Table 3
> should
> > be joined with Table 5.
> >
> > I've tried just using a standard "select ... from table1 JOIN
Table2...
> > JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where"
clauses,
> > but that doesn't really work.
> >
> > I think that the JOIN I'm doing with table 1 and 5 isn't correct, but
how
> do
> > I tell that I wan't to join table 3 with table5.
> >
> > I'd be happy if some of you can get me guided in the right direction on
> how
> > to do this, since I can't get my head around it.
> > In BOL it's mentioned that join expressions can be connected with AND or
> > with OR, but I can't find any examples on how to do it (...and also I'm
> not
> > sure if that will solve my problem...)
> >
> > Regards
> > Steen
> >
> >
>|||Thanks Uri...I'll try to work on that
Regards
Steen
Uri Dimant wrote:
> Sorry,should be
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table3.key INNER JOIN
> Table4 ON Table1.key=Table4.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...
>> Steen
>> SELECT col.... FROM Table1 INNER JOIN
>> Table2 ON Table1.key=Table2.key INNER JOIN
>> Table3 ON Table1.key=Table1.key INNER JOIN
>> Table4 ON Table1.key=Table1.key INNER JON
>> Table5 ON Table3.key=Table5.key
>> WHERE ..................
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> I need some advises on how to join several tables and/or use
>> "nested" joins (if such a thing exist...)
>> I trying to select some data which involves 5 tables. The relation
>> is as follows -
>> Table1 should be joined with table2, table3 and table 4. Then Table
>> 3 should be joined with Table 5.
>> I've tried just using a standard "select ... from table1 JOIN
>> Table2... JOIN Table3...JOIN Table 4....JOIN Table 5 and then my
>> "where" clauses, but that doesn't really work.
>> I think that the JOIN I'm doing with table 1 and 5 isn't correct,
>> but how do I tell that I wan't to join table 3 with table5.
>> I'd be happy if some of you can get me guided in the right
>> direction on how to do this, since I can't get my head around it.
>> In BOL it's mentioned that join expressions can be connected with
>> AND or with OR, but I can't find any examples on how to do it
>> (...and also I'm not sure if that will solve my problem...)
>> Regards
>> Steensql

Help on joining tables

Hi
I need some advises on how to join several tables and/or use "nested" joins
(if such a thing exist...)
I trying to select some data which involves 5 tables. The relation is as
follows -
Table1 should be joined with table2, table3 and table 4. Then Table 3 should
be joined with Table 5.
I've tried just using a standard "select ... from table1 JOIN Table2...
JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
but that doesn't really work.
I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how do
I tell that I wan't to join table 3 with table5.
I'd be happy if some of you can get me guided in the right direction on how
to do this, since I can't get my head around it.
In BOL it's mentioned that join expressions can be connected with AND or
with OR, but I can't find any examples on how to do it (...and also I'm not
sure if that will solve my problem...)
Regards
SteenSteen
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table1.key INNER JOIN
Table4 ON Table1.key=Table1.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need some advises on how to join several tables and/or use "nested"
joins
> (if such a thing exist...)
> I trying to select some data which involves 5 tables. The relation is as
> follows -
> Table1 should be joined with table2, table3 and table 4. Then Table 3
should
> be joined with Table 5.
> I've tried just using a standard "select ... from table1 JOIN Table2...
> JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
> but that doesn't really work.
> I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how
do
> I tell that I wan't to join table 3 with table5.
> I'd be happy if some of you can get me guided in the right direction on
how
> to do this, since I can't get my head around it.
> In BOL it's mentioned that join expressions can be connected with AND or
> with OR, but I can't find any examples on how to do it (...and also I'm
not
> sure if that will solve my problem...)
> Regards
> Steen
>|||Sorry,should be
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table3.key INNER JOIN
Table4 ON Table1.key=Table4.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Steen
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table1.key INNER JOIN
> Table4 ON Table1.key=Table1.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> joins
> should
Table2...[vbcol=seagreen]
clauses,[vbcol=seagreen]
how[vbcol=seagreen]
> do
> how
> not
>|||Thanks Uri...I'll try to work on that
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Sorry,should be
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table3.key INNER JOIN
> Table4 ON Table1.key=Table4.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...

Help on joining tables

Hi
I need some advises on how to join several tables and/or use "nested" joins
(if such a thing exist...)
I trying to select some data which involves 5 tables. The relation is as
follows -
Table1 should be joined with table2, table3 and table 4. Then Table 3 should
be joined with Table 5.
I've tried just using a standard "select ... from table1 JOIN Table2...
JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
but that doesn't really work.
I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how do
I tell that I wan't to join table 3 with table5.
I'd be happy if some of you can get me guided in the right direction on how
to do this, since I can't get my head around it.
In BOL it's mentioned that join expressions can be connected with AND or
with OR, but I can't find any examples on how to do it (...and also I'm not
sure if that will solve my problem...)
Regards
Steen
Steen
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table1.key INNER JOIN
Table4 ON Table1.key=Table1.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need some advises on how to join several tables and/or use "nested"
joins
> (if such a thing exist...)
> I trying to select some data which involves 5 tables. The relation is as
> follows -
> Table1 should be joined with table2, table3 and table 4. Then Table 3
should
> be joined with Table 5.
> I've tried just using a standard "select ... from table1 JOIN Table2...
> JOIN Table3...JOIN Table 4....JOIN Table 5 and then my "where" clauses,
> but that doesn't really work.
> I think that the JOIN I'm doing with table 1 and 5 isn't correct, but how
do
> I tell that I wan't to join table 3 with table5.
> I'd be happy if some of you can get me guided in the right direction on
how
> to do this, since I can't get my head around it.
> In BOL it's mentioned that join expressions can be connected with AND or
> with OR, but I can't find any examples on how to do it (...and also I'm
not
> sure if that will solve my problem...)
> Regards
> Steen
>
|||Sorry,should be
SELECT col.... FROM Table1 INNER JOIN
Table2 ON Table1.key=Table2.key INNER JOIN
Table3 ON Table1.key=Table3.key INNER JOIN
Table4 ON Table1.key=Table4.key INNER JON
Table5 ON Table3.key=Table5.key
WHERE ..................
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Steen
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table1.key INNER JOIN
> Table4 ON Table1.key=Table1.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ewYGT%23teEHA.2044@.TK2MSFTNGP10.phx.gbl...
> joins
> should
Table2...[vbcol=seagreen]
clauses,[vbcol=seagreen]
how
> do
> how
> not
>
|||Thanks Uri...I'll try to work on that
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Sorry,should be
> SELECT col.... FROM Table1 INNER JOIN
> Table2 ON Table1.key=Table2.key INNER JOIN
> Table3 ON Table1.key=Table3.key INNER JOIN
> Table4 ON Table1.key=Table4.key INNER JON
> Table5 ON Table3.key=Table5.key
> WHERE ..................
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OK5EIEueEHA.2544@.TK2MSFTNGP10.phx.gbl...

Help on joining tables

Say I have three tables (just using Access for this one).

- inTheNews
- pressReleases
- events

Each for the most part, has identical columns. But, each also has one or two that are unique.

The primary key (unique identifier) in each is set to AutoNumber.

Is there a way to set it up so that the AutoNumber recognizes the numbers from the other tables and doesn't produce numbers identical to records in those tables. I would like to make sure each record in each of these tables has a unique identifier -- unique to all three tables.

Does that make sense?

Thanks.There isn't a well defined way to do this, although there are several different approaches that work reasonably well.

I think that the best way to handle this kind of problem is to create a "parent" table that has the common columns, with an autonumber as its primary key. Then create three sub-tables that have the unique columns for each of the sub-types, and create a (foreign key) relationship between these tables and the parent table.

This reduces redundancy, and it makes it easier to be sure that the autonumber values are only used by one of the sub-types. It isn't perfect, but it is probably the best you can do "out of the box".

-PatP