Retrieving data that matches a list

[gadall]

I've got two tables that I need to match data, the part that has me stymied is that in table1 there is a field that can hold 1 to 3 codes, that will match up against table 2.

Table 1
----------
Schedule_Date (DateTime)
Work_Order_Num (nchar(8))
FCode (nvarchar(9)) <--- 3, 3 character codes



Table 2
-----------
FCode (nchar(3))
FCodeDescription (nvarchar(100))


What would be the best way of going about this query?

Dallas

[cripton]

I've got two tables that I need to match data, the part that has me stymied is that in table1 there is a field that can hold 1 to 3 codes, that will match up against table 2.

Table 1
----------
Schedule_Date (DateTime)
Work_Order_Num (nchar(8))
FCode (nvarchar(9)) <--- 3, 3 character codes



Table 2
-----------
FCode (nchar(3))
FCodeDescription (nvarchar(100))


What would be the best way of going about this query?

Dallas

hope this will help

select a.* , b.*
from table1 a, table2 b
where a.Fcode = b.FCode

------------------------------------------------------------

select * from Table1
where FCode in (select FCode from Table2)

[debasisdas]

The second query should perform faster.

[gadall]

My problem is I need *multiple* FCodeDescriptions returned for each record in Table 1.

Some Sample data:
Table 1
----------
Schedule_Date Work_Order_Num FCode
03/12/2008 13259127 F02F03


Table 2
-----------
FCode FCodeDescription
F01 Work Not Completed
F02 Trash in Work Area
F03 Drop Not Tagged


I need the query to return:

03/12/2008, 13258127, Trash in Work Area Drop Not Tagged


FYI the previous examples do not return a match for records that have more than one FCode in the Table 1 record.

[ck9663]

My problem is I need *multiple* FCodeDescriptions returned for each record in Table 1.

Some Sample data:
Table 1
----------
Schedule_Date Work_Order_Num FCode
03/12/2008 13259127 F02F03


Table 2
-----------
FCode FCodeDescription
F01 Work Not Completed
F02 Trash in Work Area
F03 Drop Not Tagged


I need the query to return:

03/12/2008, 13258127, Trash in Work Area Drop Not Tagged


FYI the previous examples do not return a match for records that have more than one FCode in the Table 1 record.

select Schedule_Date, Work_Order_Num,
tblCode1.FCodeDescription , tblCode2.FCodeDescription, tblCode3.FCodeDescription
from
(select Schedule_Date, Work_Order_Num, 
Code1 =
   case when substring(rtrim(Table1.FCode) + space(9),1,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),1,3) end,
Code2 =
   case when substring(rtrim(Table1.FCode) + space(9),4,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),4,3) end,
Code3 =
   case when substring(rtrim(Table1.FCode) + space(9),7,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),7,3) end,
from Table1) tbl1
left join table2 tblCode1 on tbl1.Code1 = tblCode1.FCode
left join table2 tblCode2 on tbl1.Code2 = tblCode2.FCode
left join table2 tblCode3 on tbl1.Code3 = tblCode3.FCode
 

You can still clean this code up and join the table directly using case..when - substring. I just do it this way to make it cleaner so that what am trying to say would actually look like make sense :)

-- CK

[gadall]

select Schedule_Date, Work_Order_Num,
tblCode1.FCodeDescription , tblCode2.FCodeDescription, tblCode3.FCodeDescription
from
(select Schedule_Date, Work_Order_Num, 
Code1 =
   case when substring(rtrim(Table1.FCode) + space(9),1,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),1,3) end,
Code2 =
   case when substring(rtrim(Table1.FCode) + space(9),4,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),4,3) end,
Code3 =
   case when substring(rtrim(Table1.FCode) + space(9),7,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),7,3) end,
from Table1) tbl1
left join table2 tblCode1 on tbl1.Code1 = tblCode1.FCode
left join table2 tblCode2 on tbl1.Code2 = tblCode2.FCode
left join table2 tblCode3 on tbl1.Code3 = tblCode3.FCode
 

You can still clean this code up and join the table directly using case..when - substring. I just do it this way to make it cleaner so that what am trying to say would actually look like make sense :)

-- CK

Thanks this works with the minor edit of removing the "," after the last end of the case statements.