View Single Post
  #10 (permalink)  
Old March 22nd, 2007, 09:01 AM
happygv happygv is offline
Friend of Wrox
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post

There are 6 Select statements used, out of which the first 3 are based on Table1 and the later 3 are based on Table2

As you said, you have 2 cases
1) The Names could be exactly the same as the other - So '=' is used in the First select statement.
2) When you say it can be a subset of the other, like 'Aimor sp' to 'Aimor'. So I used <> and datalength() combinations in the second select query.

The third was used to pick the name from Table2, except that the entire statement would be same as that of 2nd select.

Similarly, the last 3 select statements are based on Table2, because... there is no guarantee that
'Aimor sp' can always appear in Table1, It could appear in Table2 also. So for the second set of 3 Select Statements, you can see the difference in the Where Clause, which is marked in BLUE. The difference is Table2.Name like '%Table1.Name%'. This is to handle the other possibility of subset. Thus there are six statements for all thos possibilities.

Here the catch is the UNION. UNION Combines all the 6 results in to One result. Also it gives you the NON-DUPLICATE result among the all 6 statement's results based on how the UNION is Used. When you run each select statement separately, you will understand which select statement result in what. There could be duplicates returned by the select statements. UNION takes care of duplicates. If you want to see the Duplicates too, Replace all UNIONs with UNION ALL. You will see the difference.

Try to add Rows into Table1 and Table2 with different posibilities and test if the code I posted is fool proof. Let me know if that helps.

Hope that explains.

- Vijay G
Strive for Perfection
Reply With Quote