I heard my PM saying today "Their are two types of Idiots. One who will take multiple trips to the database to compare value. Others who will take the values in memory and compare then their".
We have a situation. We need to take values from one table and check if it exists in the second table. Depending on weather it exists in the second table or not, we need to display with / without highlight it in the datagrid.
There is one catch. The query is written by the user in one of the application screen and the user has no idea that the 2nd condition table needs to be applied. I know you would debate that we need to create a screen, so that the user will select what is required instead of writing the query. I know... but we need this asap. Screen will be in the next release.
Let me elaborate: Consider that we have table A and table B. Both these tables have ID column. The user writes (on the application screen)
SELECT * FROM A
We have a situation. We need to take values from one table and check if it exists in the second table. Depending on weather it exists in the second table or not, we need to display with / without highlight it in the datagrid.
There is one catch. The query is written by the user in one of the application screen and the user has no idea that the 2nd condition table needs to be applied. I know you would debate that we need to create a screen, so that the user will select what is required instead of writing the query. I know... but we need this asap. Screen will be in the next release.
Let me elaborate: Consider that we have table A and table B. Both these tables have ID column. The user writes (on the application screen)
SELECT * FROM A
I can check that this ID does not exist in table B by executing
SELECT * FROM A
WHERE A.ID NOT IN
(SELECT DISTINCT ID FROM B)
This query will return all from A which does not exist in B. But I need both that exist and that does not exist and I need to highlight the rows in the datagrid depending on this condition.
You could argue that I can use JOIN. Whats the problem with JOIN?
SELECT A.ID, COUNT (B.ID)
FROM A JOIN B ON A.ID = B.ID
WHERE A.ID = B.ID
GROUP BY A.ID
Remember the user will write a part of the query and we need to add the rest of it. Join query will be difficult to add to an unknown query from the user. The syntax to create a join query can become really complicated as we do not know what the user plans to input. Imagine if he writes a join query we need to have conditions to add our query before WHERE but after ON condition. This can result in complicated things.
Of course I am not going to do multiple trips to the database to compare the values. I don't plan to take one row of A and compare it to all the values of B, that will be the worst thing ever written. I could have taken all the rows from table A and table B and put them in separate datasets. Then i could easily loop through the dataset and figure out if a rows from dataset A exists in dataset B and color code the rows in the datagrid accordingly. But that again would mean more than one round to the database. Isn't this a good solution or do you have any better idea?
SELECT * FROM A
WHERE A.ID NOT IN
(SELECT DISTINCT ID FROM B)
This query will return all from A which does not exist in B. But I need both that exist and that does not exist and I need to highlight the rows in the datagrid depending on this condition.
You could argue that I can use JOIN. Whats the problem with JOIN?
SELECT A.ID, COUNT (B.ID)
FROM A JOIN B ON A.ID = B.ID
WHERE A.ID = B.ID
GROUP BY A.ID
Remember the user will write a part of the query and we need to add the rest of it. Join query will be difficult to add to an unknown query from the user. The syntax to create a join query can become really complicated as we do not know what the user plans to input. Imagine if he writes a join query we need to have conditions to add our query before WHERE but after ON condition. This can result in complicated things.
Of course I am not going to do multiple trips to the database to compare the values. I don't plan to take one row of A and compare it to all the values of B, that will be the worst thing ever written. I could have taken all the rows from table A and table B and put them in separate datasets. Then i could easily loop through the dataset and figure out if a rows from dataset A exists in dataset B and color code the rows in the datagrid accordingly. But that again would mean more than one round to the database. Isn't this a good solution or do you have any better idea?
I have!
After an hour of brainstorming we decided to use this:-
SELECT A.ID,
CASE
WHEN (A.ID NOT IN (SELECT B.ID FROM B)
THEN 'DOES NOT EXIST'
ELSE 'EXISTS' END AS [STATUS]
FROM A
The output of this query is
A.ID STATUS
----------------------------------------------
1 EXISTS
2 DOES NOT EXIST
Now we can easily color code this in datagrid depending on Status. We did not face the problem we faced with Join. All we did was add the following code before the "From"
,
CASE
WHEN (A.ID NOT IN (SELECT B.ID FROM B)
THEN 'DOES NOT EXIST'
ELSE 'EXISTS' END AS [STATUS]
and added validations so that the user could not enter keywords like "Update, Insert, Delete, Drop, Into..."
Comments
Post a Comment