Clever Geek Handbook

How do I select rows containing part of the values ​​from another list of strings?

In firebird + C # app. I have a table like:

TableA:
        30RTabc1 someothervalue1 someothervalue2
        30RTabc2 someothervalue3 someothervalue4
        30RTabc1 someothervalue5 someothervalue6
        30RTabc4 someothervalue7 someothervalue8
        30RTabc1 someothervalue9 someothervalue10

TableB:
            abc1 someothervalue1 someothervalue2
            abc2 someothervalue3 someothervalue4
            abc3 someothervalue5 someothervalue6

So, as a result, I will have a merged TableC that will have values ​​from TableA where the first column contains values ​​from the first column of TableB, so it will look like this:

The result of joining TableA + TableB, where row column1 of table A contains the row from column TableB:

TableC:
30RTabc1 someothervalue1 someothervalue2
30RTabc2 someothervalue3 someothervalue4
30RTabc1 someothervalue5 someothervalue6
30RTabc1 someothervalue9 someothervalue10

What's the best way to do this, in firebird some sql query or in an app, C # -loop? (The key is column1, other values ​​can be completely different, someothervalue is just an example, everything can be random)

+3


source to share


3 answers


If it is easy to do it in SQL, I see no reason to do it in C #, you will need to extract a lot of data and then abandon it.

As far as how to do it in SQL, it depends on the "30RT" part that can be changed or not:

If not:



SELECT t1.*
FROM TableA t1
INNER JOIN TableB t2
ON t1.Key = '30RT'+t2.Key

If it could be anything:

SELECT t1.*
FROM TableA t1
INNER JOIN TableB t2
ON t1.Key like '%'+t2.Key
+2


source


Generally: delegate tasks to specialists. In this case: let Firebird do the job with a simple JOIN, because she's an expert when it comes to table parsing, combination, lookups, etc.



However, if tables are stored in memmory anyway, you might consider LINQ to join the tables.

+2


source


From the above details, what I get is you have a table with a key to join. If you are using a DataSet you can add a relationship between the table using

DataRelation Relation=new DataRelation(DataCoulumn1,DataColumn2)
DataSet.Relations.Add(Relation)
+1


source






More articles:


All Articles