0

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.

2
Contributors
1
Reply
2
Views
11 Years
Discussion Span
Last Post by chmonalisa
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.