Conditional Linking
-
My Problem is the following:
I have two tables. I need to get records from the second table where a key field links the two tables. If the second table has records matching this criteria, it's done. If for some records there is no records matching the whole key, i want to check for records using part of the key field (like first 3 charcaters).
And i want the result to be output as one result for both cases.
Example:
Table 1 :
Key field1
----------------
AB1 xyz
AB2 lmn
DE1 xgt
GH1 xvf
TABLE 2:
Key Field1
AB1 lll
AB2 222
DE1 333
GH2 444
The result should be:
AB1 xyz 111
AB2 lmn 222
DE1 xgt 333
GH1 xvf 444
Can I do that using a single Query (view) in an easy way or do I need to do programming for that.
Thanks.
-
Charbel
I'm not sure what you mean by the first three characters of the key, since in your sample data, all Key values are three characters long. If the following doesn't work for you, please post again with more details of exactly what you're looking for.
SELECT t1.Key, t1.Field1, t2.Field1
FROM Table1 t1 INNER JOIN Table2 t2
ON t1.Key = t2.Key
John
-
Hi,
I assume you meant the first 2 characters by your result set.
You could do it via a Union. The second query has to take into consideration what the first returns.
Something like this might work.
Select
table1.[Key],
table1.field1,
table2.field1
From
table1
Inner Join table2
On table1. = table2.
Union
Select
table1.[Key],
table1.field1,
table2.field1
From
table1
Inner Join table2
On Left(table1.,2) = Left(table2.,2)
Where
table1.[Key] Not In
(Select
table1.
From
table1
Inner Join table2
On table1. = table2.)
Regards
Daniel
-
Or
SELECT a., a.field1, COALESCE(b.field1, c.field1)
FROM [table1] a
LEFT JOIN [table2] b ON b. = a.
LEFT JOIN [table2] c ON c. LIKE LEFT(a.,2)+'%' AND c. != a.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks Guys for your replies,
I guess What David and Daniel Suggested will do. but I am worried abour performance as the tables I am talking about can have up to 50,000-100,000 records. would it be safer to use a stored procedure or a VB program to get the same result.
-
David's SQL is pretty neat for your original request. I think you would do well to beat this using vb to find and retrieve the records.
Regards
Daniel
-
If you care about performance you must fix database design.
Left(table1.,2) = Left(table2.,2)
will cause table scan (twice) with following hash join.
It will create #Table in tempdb with 100k*100k=10bn rows.
You need to identify your entities and establish relations (using FK constraints on int ID fields) ON ENTRY, when data is being inserted int the tables.
Table 1 :
ID Key field1
-------------------
1 AB1 xyz
2 AB2 lmn
6 DE1 xgt
9 GH1 xvf
TABLE 2:
Key T1ID Field1
AB1 1 lll
AB2 2 222
DE1 6 333
GH2 9 444
ID in Table1 is IDENTITY field.
Populate T1ID in Table2 using UPDATE based on any of queries suggested and then your query will become pretty easy and quick.
_____________
Code for TallyGenerator -
If you care about performance you must fix database design. Always
...will cause table scan... That is why I used LIKE which did INDEX SEEKS in my test although I did not use 50-100K rows
Nevertheless you are correct Sergiy
Far away is close at hand in the images of elsewhere.
Anon. -
Hi and thanks all for your replies,
Now to explain more the issue, the situation is the following:
In fact there is one table which is imported on daily basis from a legacy system.
And links will be self joins on the same table (using different criteria for records selection). So in order to use the method that Sergiy suggested I will have to do that during the import process which i am trying to avoid, because using the update after importing the data will take the same time as running the query (which will be run once a day to extract the resulting data).
I don't know if the above will change your view of the issue, but i just wanted to make sure that we're looking at the problem from the same angle.
Thanks again,
Charbel
-
If you'll do update during INSERT you'll do it ONCE and FOREVER, not once per day.
And UPDATE will affect only new data, not the whole set.
And if your import will take extra minute noone will notice it. But extra minute waiting time when report is requested usually does not make users happy.
Have you ever been is a diary shop? What salesman does when he takes your cash? He sorts notes and coins by slots ON INSERT. He never dumps into a heap to shift the sorting task to the moment when he needs cash.
Why? Because it's more effective. Proven by generations.
I wonder - is it salesmen are so smart or developers are so stupid?
_____________
Code for TallyGenerator -
I guess a smart guy should read well before answering.
I said that the table is is imported on daily basis from a legacy system. I didn't say that new records are inserted. so the whole table is dumped and re-populated every day with new data.
Also the query result is extracted only once a day. so it won't make any difference if the time taken is to update or extract.
My point is to try to make the whole process take the shortest time.
Thanks anyway.
-
Can you explain the method you are using to import data without inserting it into a table?
Every note or coin is placed into a slot also only once. And is given away once. But anyway they sort it on entry, not exit.
Now about you task. Is there only point of using SQL Server just to build report from the data living in another system?
I know, those old systems are absolutely not fancy and not fashionable. But you know, people use to build quite effective and informative reports 20 years ago. I don't believe that legacy system suddenly lost such ability.
_____________
Code for TallyGenerator -
The whole legacy file is imported using DTS. At what step do you suggest the update to be done, at insert of every record using a trigger? please bear in mind that it's a single table process and the whole data is needed to get the correct result, so the first record in the table will not be matched to any records....
The process is part of a DATA integration process which should take the file from the legacy system, clean it, link it... and feed to another system.
Now about the notes and salesman, I guess if you add up the time taken by the salesman to sort the notes, it will be much bigger than that needed if the whole bunch is sorted at the end, and taking into consideration that the salesman need them sorted to return change fatser(which I don't need).
Any suggestions are very welcome.
-
Charbel, never forget you are working with RELATIONAL DATABASE.
It's built on relations between entities.
If you don't establish relations between your tables then forget about SQL Server. There are plenty of file databases to your service.
To establish relations you don't need any trigger.
First (logically) table must have IDENTITY column. I hope you did not miss that part in my post.
Second table must have FK column to establish relation with Table1. You may populate it this way:
INSERT INTO Table2 (FK_ID, ....)
SELECT Table1.ID, ET.....
FROM ExternalTAble2 ET
INNER JOIN Table1 ON {here is you join criteria}
If you are not sure join will work properly every time replace INNER JOIN with LEFT JOIN and than query for FK_ID IS NULL. If there are such rows you may try to modify criteria and repopulate it. Repeat this exersise until tou are absolutely happy with the result.
Anyway, at the moment when you need to feed result to another system your will be sure the data is clean, consistent and quick to proceed.
_____________
Code for TallyGenerator -
Dear Sergiy,
Just to wrap up the issue,
As i said before there is only one table, no external tables. and the table is self joined using a primary key or a part of the primary key to get the matching records.
So I guess I will do a test of performance between Daniel's, David's and a VB written code and select the best.
Thanks again for your suggestions.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply