SQL: select/delete ID's listed multiple times in differents relations
Hello Experts,
I have the following S_M table wich refer to the attached dummy file.
Column A refers to the Id relations between column B and column C
-1-0 Means there is an S_Id, and not a M_Id
-0-1 Means there is an M_Id and not a S_Id
-1-1 Means there is a M_Id with the same S_Id
-N-1 Means there is multiple S_Id for one M_Id
-1-N means there is multiple M_Id for one S_Id
-S_Int_Id refers to the internal id of S_Id
I want to set up a select query in order to :
Identify all M_Id which exist at the same time in 0-1 relationship and also in N-1, 1-1 or 1-N relationship
Example of data which should be displayed:
Ex:
0-1 CZ_LV15
0-1 CZ_LV46
As the table contains the following value:
0-1 CZ_LV15
N-1 CZ_LV15 14MOM4V 10388
0-1 CZ_LV46
N-1 CZ_LV46 14MOM4V 10388
In the following example CZ_LV15, CZ_LV46 exist multiple times with a different relationship (0-1 and also N-1) .
-Based on the select query I would like to delete the various lines with 0-1 relations displayed in the previous select query.
In that case: 0-1 CZ_LV15 and 0-1 CZ_LV46 should be removed.
I attached the dummy file and highlighted in red the reported examples that should be part of various queries.
If you have questions, please contact me.
Regards,
I have the following S_M table wich refer to the attached dummy file.
Column A refers to the Id relations between column B and column C
-1-0 Means there is an S_Id, and not a M_Id
-0-1 Means there is an M_Id and not a S_Id
-1-1 Means there is a M_Id with the same S_Id
-N-1 Means there is multiple S_Id for one M_Id
-1-N means there is multiple M_Id for one S_Id
-S_Int_Id refers to the internal id of S_Id
I want to set up a select query in order to :
Identify all M_Id which exist at the same time in 0-1 relationship and also in N-1, 1-1 or 1-N relationship
Example of data which should be displayed:
Ex:
0-1 CZ_LV15
0-1 CZ_LV46
As the table contains the following value:
0-1 CZ_LV15
N-1 CZ_LV15 14MOM4V 10388
0-1 CZ_LV46
N-1 CZ_LV46 14MOM4V 10388
In the following example CZ_LV15, CZ_LV46 exist multiple times with a different relationship (0-1 and also N-1) .
-Based on the select query I would like to delete the various lines with 0-1 relations displayed in the previous select query.
In that case: 0-1 CZ_LV15 and 0-1 CZ_LV46 should be removed.
I attached the dummy file and highlighted in red the reported examples that should be part of various queries.
If you have questions, please contact me.
Regards,
ASKER
Sorry I don't why the dummy file wasn't attached.
I highlitghted in red the lines with should be displayed/delete by the various queries.
m-s_relationship.xlsx
I highlitghted in red the lines with should be displayed/delete by the various queries.
m-s_relationship.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both.
And if I want to apply the DELETE query based on the select proposal how should I config the DELETE?
No worries I test DELETE query in a test database.
Thank you very much for your help.
And if I want to apply the DELETE query based on the select proposal how should I config the DELETE?
No worries I test DELETE query in a test database.
Thank you very much for your help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes Delete the 0-1 rows.
Thank you very much for this proposal. I will take the time to test it tomorrow.
Thank you very much for this proposal. I will take the time to test it tomorrow.
CORRECTION:
Remove the "ORDER BY M_Id" from the cte query -- it's certainly not needed and is probably not allowed.
Remove the "ORDER BY M_Id" from the cte query -- it's certainly not needed and is probably not allowed.
ASKER
Hello,
I tested both query and they works.
@ Pawan Kumar Khowal: Just a correction concerning the select query provided at:
https://www.experts-exchange.com/questions/28978950/SQL-select-delete-ID's-listed-multiple-times-in-differents-relations.html?anchor=a41860683¬ificationFollowed=178160725&anchorAnswerId=41860647#a41860647
I added: END in the second SUM otherwise it doesn't work.
I have just a complementary question concerning select query. If I want to display the various field related to the table how should I config the select queries. The proposal just display M_Id and I would like to display the various fields.
Thank you again for your help.
I tested both query and they works.
@ Pawan Kumar Khowal: Just a correction concerning the select query provided at:
https://www.experts-exchange.com/questions/28978950/SQL-select-delete-ID's-listed-multiple-times-in-differents-relations.html?anchor=a41860683¬ificationFollowed=178160725&anchorAnswerId=41860647#a41860647
I added: END in the second SUM otherwise it doesn't work.
I have just a complementary question concerning select query. If I want to display the various field related to the table how should I config the select queries. The proposal just display M_Id and I would like to display the various fields.
Thank you again for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
HAVING has to be the least understood common clause in SQL. Is it really that hard to understand?
ASKER
Ok, sorry for the question I am not an expert of sql. I would try to understand.
@Author - Did you get what you were looking for ? or do you need more help on this?
HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Consider you got different groups using group by and then you want to put a filter on each group then you use having clause.
E.g. You want to find out in each department how many employees with salary > 10k.
So first you group by each department --> and then in each department put filter Having (Salary>10K)
Hope it helps !
HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Consider you got different groups using group by and then you want to put a filter on each group then you use having clause.
E.g. You want to find out in each department how many employees with salary > 10k.
So first you group by each department --> and then in each department put filter Having (Salary>10K)
Hope it helps !
ASKER
@Pawan Kumar Khowal: Thank you for last comment.
Your last query proposal works I just added another condition in order to have just the project with relation 0-1.
WHERE p.s1 >= 1 AND p.s2 >= 1 and Relation = '0-1'
Thank you again for your help.
Regards,
Your last query proposal works I just added another condition in order to have just the project with relation 0-1.
WHERE p.s1 >= 1 AND p.s2 >= 1 and Relation = '0-1'
Thank you again for your help.
Regards,
Great , Thank you !!
ASKER
Thank you for your help.
Sorry LD16, my comment about HAVING was not directed at you, since you were just asking the q, not attempting to answer it.
The other query presumably will function correctly -- I haven't verified it myself -- but it will perform much worse overall.
The other query presumably will function correctly -- I haven't verified it myself -- but it will perform much worse overall.
ASKER
Don't worry ScottPletcher, Thank you again for your help.
Could you please attach some sample data and the expected output ??