By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,377 Members | 5,346 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,377 IT Pros & Developers. It's quick & easy.

Retrieving data that matches a list

P: 7
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
Mar 18 '08 #1
Share this Question
Share on Google+
5 Replies


P: 3
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)
Mar 18 '08 #2

debasisdas
Expert 5K+
P: 8,127
The second query should perform faster.
Mar 18 '08 #3

P: 7
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.
Mar 18 '08 #4

ck9663
Expert 2.5K+
P: 2,878
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.
Expand|Select|Wrap|Line Numbers
  1. select Schedule_Date, Work_Order_Num,
  2. tblCode1.FCodeDescription , tblCode2.FCodeDescription, tblCode3.FCodeDescription
  3. from
  4. (select Schedule_Date, Work_Order_Num, 
  5. Code1 =
  6.    case when substring(rtrim(Table1.FCode) + space(9),1,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),1,3) end,
  7. Code2 =
  8.    case when substring(rtrim(Table1.FCode) + space(9),4,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),4,3) end,
  9. Code3 =
  10.    case when substring(rtrim(Table1.FCode) + space(9),7,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),7,3) end,
  11. from Table1) tbl1
  12. left join table2 tblCode1 on tbl1.Code1 = tblCode1.FCode
  13. left join table2 tblCode2 on tbl1.Code2 = tblCode2.FCode
  14. left join table2 tblCode3 on tbl1.Code3 = tblCode3.FCode
  15.  
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
Mar 19 '08 #5

P: 7
Expand|Select|Wrap|Line Numbers
  1. select Schedule_Date, Work_Order_Num,
  2. tblCode1.FCodeDescription , tblCode2.FCodeDescription, tblCode3.FCodeDescription
  3. from
  4. (select Schedule_Date, Work_Order_Num, 
  5. Code1 =
  6.    case when substring(rtrim(Table1.FCode) + space(9),1,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),1,3) end,
  7. Code2 =
  8.    case when substring(rtrim(Table1.FCode) + space(9),4,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),4,3) end,
  9. Code3 =
  10.    case when substring(rtrim(Table1.FCode) + space(9),7,3) = '' then NULL else  substring(rtrim(Table1.FCode) + space(9),7,3) end,
  11. from Table1) tbl1
  12. left join table2 tblCode1 on tbl1.Code1 = tblCode1.FCode
  13. left join table2 tblCode2 on tbl1.Code2 = tblCode2.FCode
  14. left join table2 tblCode3 on tbl1.Code3 = tblCode3.FCode
  15.  
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.
Mar 19 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.