I have 3 tables that need be joined.
(Not necessarily needed info but might help: using Coldfusion with ODBC to Foxpro database)

Table 1: Timecard
--------
loginname date wrknum time
----------- --------- --------- ------
afrank 7/17/06 1 3
afrank 7/17/06 2 2
afrank 7/17/06 3 3
afrank 5/20/06 1 3
rjohn 7/17/06 1 3
.....

Table 2: Defaults
-----------
loginname wrknum
----------- ---------
afrank 1
afrank 2
afrank 3
afrank 4
afrank 5
....

Table 3: Workorders
wrknum description
---------- ---------------
1 descrip1
2 descrip2
.....


The result needs to be for the user afrank, date=7/20
and would look like:

Resultant Table:
loginname wrknum time description
----------- ---------- ------ ------------
afrank 1 3 descrip1
afrank 2 2 descrip2
afrank 3 3 descrip3
afrank 4 NULL descrip4
afrank 5 NULL descrip5


I've tried an outer left join but cannot get the right data. Basically everything from the timecard table and the leftovers from the default table with descriptions for all.

buddy,

here it is


select t2.loginname,t2.wrknum,t1.time,t3.description

from table2 t2

left outer join table1 t1 on t1.loginname=t2.loginname
inner join table3 t3 on t3.worknum=t2.worknum

Thanks,
Chaitanya.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.