How to rewrite the query if that table is unrelated?

|
Ayush1705199 2020-02-26 10:05:44
It is showing me an error
Ayush1705199 2020-02-26 10:06:24
Ssms is currently running. Please close any open instance of SSMS and run installer again
piterden 2020-02-26 10:07:31
WTF SSMS is?
Ayush1705199 2020-02-26 10:09:18
piterden 2020-02-26 10:07:31
WTF SSMS is?

SSMs is sql server management studio

piterden 2020-02-26 10:10:01
Hear first time about
piterden 2020-02-26 10:10:32
And this is MySQL related group
Ayush1705199 2020-02-26 10:11:01
Oh so sorry
piterden 2020-02-26 10:11:21
No worries)
MasterZiv 2020-02-26 10:59:25
Dhruva 2020-02-26 09:09:09
How Can I make the Search Engine for my database.I mean like LinkedIn where It should search from my own DB and how it can be mapped

These are not DB related questions.
Ask in relevant groups

HK 2020-02-26 15:42:53
Pls share cluster setup steps on Linux guide
madno 2020-02-26 20:24:46
can this query be optimized?
madno 2020-02-26 20:25:12
it takes around 20 secs to execute
madno 2020-02-26 21:20:08
It can be written without joins?
piterden 2020-02-26 21:37:48
Of course no. It is indexed column in the most cases
MasterZiv 2020-02-26 22:36:45
Send query text, DDL of all tables.
MasterZiv 2020-02-27 00:11:43
madno 2020-02-26 20:24:46
can this query be optimized?

Above

MasterZiv 2020-02-27 00:12:01
No.
piterden 2020-02-27 07:41:49
What the fix is it? Damn!
MasterZiv 2020-02-27 09:16:04
First, your query has wrong GROUP BY, you must have all fields not covered by any aggregates in the group by clause
MasterZiv 2020-02-27 09:17:27
First you must correct it and try, as optimizing a wrong query is useless
madno 2020-02-27 09:38:15
MasterZiv 2020-02-27 09:16:04
First, your query has wrong GROUP BY, you must have all fields not covered by any aggregates in the group by clause

I should have only one column in GROUP BY??

MasterZiv 2020-02-27 10:07:13
madno 2020-02-27 09:38:15
I should have only one column in GROUP BY??

No, you can have as many as it is needed

madno 2020-02-27 11:50:57
MasterZiv 2020-02-27 10:07:13
No, you can have as many as it is needed

I am not able to understand..that’s query gives the correct result what is required

MasterZiv 2020-02-27 11:58:40
madno 2020-02-27 11:50:57
I am not able to understand..that’s query gives the correct result what is required

NO, this is a happy coincidence only.

MasterZiv 2020-02-27 12:06:06
madno 2020-02-27 11:50:57
I am not able to understand..that’s query gives the correct result what is required

You have LIMIT 10 there in the query , how can you judge when you don’t even see all results?

MasterZiv 2020-02-27 12:13:52
All is much worse then I expected first, you don’t only have wrong group by, but you also have forgotten to mention one join condition between tables t4 and t1, !
This is because you use non-ANSI joins, don’t use them ever!

This is how the query should be like
SELECT
t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
COUNT(distinct(t4.id)) AS No_of_incidents
FROM `t_incidents` t4
JOIN `t_user_departments` t1 on t1.archive=FALSE
JOIN `ta_user_department` t2 on t2.department_id=t1.id
JOIN `t_core_users` t3 on t2.user_id= t3.id and t4.s17 = CONCAT(t3.display_name,’-‘,t3.id) AND t3.archive=FALSE
WHERE t4.archive=FALSE
AND t4.s17 IS NOT NULL
AND t4.s17 <>”
AND t4.is_template=FALSE
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,t4.s17 DESC
LIMIT 10;
Problems:
0) you don’t have any join condition on table t_user_departments t1
1) you have very bad join condition on t_core_users t3 — condition by expression, index will never be used,
2) you have wrong GROUP BY.
3) and you cover all this with LIMIT , just for you not to notice any problems in data.

This is wonderful result, I can say!
3 crucial problems in one query , and you ask for it OPTIMIZATION then!

madno 2020-02-27 12:44:28
MasterZiv 2020-02-27 12:13:52
All is much worse then I expected first, you don’t only have wrong group by, but you also have forgotten to mention one join condition between tables t4 and t1, !
This is because you use non-ANSI joins, don’t use them ever!

This is how the query should be like
SELECT
t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
COUNT(distinct(t4.id)) AS No_of_incidents
FROM `t_incidents` t4
JOIN `t_user_departments` t1 on t1.archive=FALSE
JOIN `ta_user_department` t2 on t2.department_id=t1.id
JOIN `t_core_users` t3 on t2.user_id= t3.id and t4.s17 = CONCAT(t3.display_name,’-‘,t3.id) AND t3.archive=FALSE
WHERE t4.archive=FALSE
AND t4.s17 IS NOT NULL
AND t4.s17 <>”
AND t4.is_template=FALSE
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,t4.s17 DESC
LIMIT 10;
Problems:
0) you don’t have any join condition on table t_user_departments t1
1) you have very bad join condition on t_core_users t3 — condition by expression, index will never be used,
2) you have wrong GROUP BY.
3) and you cover all this with LIMIT , just for you not to notice any problems in data.

This is wonderful result, I can say!
3 crucial problems in one query , and you ask for it OPTIMIZATION then!

Sorry for my misunderstanding. Thank you so much for your help..I will give this a try and come back with results

MasterZiv 2020-02-27 12:53:12
madno 2020-02-27 12:44:28
Sorry for my misunderstanding. Thank you so much for your help..I will give this a try and come back with results

This query above is still WRONG !

Nabeel 2020-02-27 13:08:58
How to create a db with capital letter like NewDatabase
MasterZiv 2020-02-27 13:10:03
Nabeel 2020-02-27 13:08:58
How to create a db with capital letter like NewDatabase

create database “NewDatabse”
?

Nabeel 2020-02-27 13:11:16
After creating the db it always shows small letter db name
Nabeel 2020-02-27 13:11:46
Like newdatabase
MasterZiv 2020-02-27 13:31:32
Nabeel 2020-02-27 13:11:16
After creating the db it always shows small letter db name

Then my guess was wrong…

madno 2020-02-27 15:18:51
MasterZiv 2020-02-27 12:53:12
This query above is still WRONG !

SELECT t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
Count(t4.id) AS No_of_incidents
FROM t_user_departments t1,
t_incidents t4,
ta_user_department t2,
t_core_users t3
WHERE t2.department_id = t1.id
AND t2.user_id = t3.id
AND t1.archive = false
AND t4.archive = false
AND t4.s17 = Concat(t3.display_name, ‘-‘, t3.id)
AND t4.s17 IS NOT NULL
AND t4.s17 <> ”
AND t4.is_template = false
AND t3.archive = false
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,
t4.s17 DESC
LIMIT 10;

madno 2020-02-27 15:19:08
madno 2020-02-27 15:18:51
SELECT t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
Count(t4.id) AS No_of_incidents
FROM t_user_departments t1,
t_incidents t4,
ta_user_department t2,
t_core_users t3
WHERE t2.department_id = t1.id
AND t2.user_id = t3.id
AND t1.archive = false
AND t4.archive = false
AND t4.s17 = Concat(t3.display_name, ‘-‘, t3.id)
AND t4.s17 IS NOT NULL
AND t4.s17 <> ”
AND t4.is_template = false
AND t3.archive = false
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,
t4.s17 DESC
LIMIT 10;

Here i removed joins

MasterZiv 2020-02-27 15:20:08
madno 2020-02-27 15:19:08
Here i removed joins

you’d better leave them

MasterZiv 2020-02-27 15:21:13
madno 2020-02-27 15:18:51
SELECT t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
Count(t4.id) AS No_of_incidents
FROM t_user_departments t1,
t_incidents t4,
ta_user_department t2,
t_core_users t3
WHERE t2.department_id = t1.id
AND t2.user_id = t3.id
AND t1.archive = false
AND t4.archive = false
AND t4.s17 = Concat(t3.display_name, ‘-‘, t3.id)
AND t4.s17 IS NOT NULL
AND t4.s17 <> ”
AND t4.is_template = false
AND t3.archive = false
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,
t4.s17 DESC
LIMIT 10;

this is WRONG select, either.
NO join condition for t1 and t4

madno 2020-02-27 15:26:33
MasterZiv 2020-02-27 15:21:13
this is WRONG select, either.
NO join condition for t1 and t4

There is no direct relationship between t4 and t1.

MasterZiv 2020-02-27 15:29:08
madno 2020-02-27 15:26:33
There is no direct relationship between t4 and t1.

This is very bad, then these tables shouldn’t be together in a select

madno 2020-02-27 15:30:34
MasterZiv 2020-02-27 15:29:08
This is very bad, then these tables shouldn’t be together in a select

Hey llia I appreciate for your patience..Can you guide me to a tutorial to learn about this.

MasterZiv 2020-02-27 15:30:48
madno 2020-02-27 15:26:33
There is no direct relationship between t4 and t1.

There’s no join condition for table t1 at all, do you understand?

MasterZiv 2020-02-27 15:32:32
madno 2020-02-27 15:18:51
SELECT t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
Count(t4.id) AS No_of_incidents
FROM t_user_departments t1,
t_incidents t4,
ta_user_department t2,
t_core_users t3
WHERE t2.department_id = t1.id
AND t2.user_id = t3.id
AND t1.archive = false
AND t4.archive = false
AND t4.s17 = Concat(t3.display_name, ‘-‘, t3.id)
AND t4.s17 IS NOT NULL
AND t4.s17 <> ”
AND t4.is_template = false
AND t3.archive = false
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,
t4.s17 DESC
LIMIT 10;

You have 4 tables, then you should have at least 3 join conditions between them, you have got only 2

MasterZiv 2020-02-27 15:34:06
madno 2020-02-27 15:30:34
Hey llia I appreciate for your patience..Can you guide me to a tutorial to learn about this.

This is just skill of writing SQL, there is no tutorials on this

madno 2020-02-27 15:50:33
MasterZiv 2020-02-27 15:34:06
This is just skill of writing SQL, there is no tutorials on this

How to rewrite the query if that table is unrelated?

MasterZiv 2020-02-27 15:51:10
madno 2020-02-27 15:50:33
How to rewrite the query if that table is unrelated?

I don’t know, I don’t know tables structure

madno 2020-02-27 17:02:15
In this all table structures are there
MasterZiv 2020-02-27 17:08:39
madno 2020-02-27 17:02:15
In this all table structures are there

I so it, but the structure is not understandable.

madno 2020-02-27 17:09:03
Ok I’ll send again
MasterZiv 2020-02-27 17:10:01
madno 2020-02-27 17:09:03
Ok I’ll send again

I can read it , I can’t understand it

MasterZiv 2020-02-27 17:18:30
madno 2020-02-27 17:09:03
Ok I’ll send again

So what you should do now is studying the DB and writion the query from scratch again.
There are more errors there.

|