0

I have the following SQL statement. Which is working okay. Except in some cases the processing of the query takes a lot of time, resulting in a system time out. So I need to convert the statement. Probably with a JOIN.

I can't figure out how to convert this statement:

SELECT table1.id as id, 
       table1.firstname, 
       table1.lastname, 
       table3.name, 
       table4.name 

FROM table1, table2, table3, table4, table5 

WHERE table1.id = table2.tabel1id 
AND table2.table2id = table3.table2id 
AND table3.table3id = table5.table3id
AND table5.somecode = '5' 
AND table3.table3id = table4.name 
AND table1.firstname LIKE '%John%' 

GROUP BY table1.id 
ORDER BY table3.name, table1.firstname, table1.lastname

I have the nesting of the tables and I also want to use the results of subqueries in the final SELECT (table3.name and table4.name)

Vincent
  • 4,342
  • 1
  • 38
  • 37

3 Answers3

0

You already have JOINs, but you use the implicit version (comma-delimited table list in FROM plus join-conditions in WHERE) instead of the explicit (JOIN plus ON).

So rewriting should not improve performance (otherwise MySQL is more crappy than I thought).

You should better check if you created all neccessary indexes.

luchaninov
  • 6,792
  • 6
  • 60
  • 75
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • According to SQL documentation there is a difference between JOINs and subqueries (as I'm using above). Based on the select statement another execution path is used. So I'm afraid that your conclusion is not correct. – Vincent Jan 27 '14 at 11:07
  • You're not using subqueries. A **subquery** is a SELECT in a WHERE-condition like **WHERE col IN (SELECT col1 FROM ....)**, sometimes **Derived Tables**, i.e. SELECT in a FROM like **SELECT ... FROM (SELECT ...) AS dt** are also called subqueries. – dnoeth Jan 27 '14 at 17:27
0
SELECT table1.id as id, 
       table1.firstname, 
       table1.lastname, 
       table3.name, 
       table4.name 

FROM table1 INNER JOIN table2
ON table1.id = table2.tabel1id 
INNER JOIN  table3 
ON table2.table2id = table3.table2id
INNER JOIN  table4
ON table3.table3id = table4.name 
INNER JOIN table5 
ON table3.table3id = table5.table3id
WHERE 
     table5.somecode = '5' 
AND table1.firstname LIKE '%John%' 

GROUP BY table1.id as id, 
       table1.firstname, 
       table1.lastname, 
       table3.name, 
       table4.name 
ORDER BY table3.name, table1.firstname, table1.lastname

Read Here for more information about sql server JOIN syntax

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • 1
    All the columns in the `select clause` must be present in the `group by` too or else use `distinct` – praveen Jan 17 '14 at 09:18
  • @praveen why would that be necessary? There's no aggregate and the OP's question doesn't state that. Answer seems correct to me. – NickyvV Jan 17 '14 at 09:23
  • @NickyvV: The query won't even compile in sql server http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e – praveen Jan 17 '14 at 09:27
0
select t1.id,t1.firstname,t3.name,t4.name 
from table1 as t1 
inner join table2 as t2 on t1.id=t2.tabel1id 
inner join on table3 as t3 on t2.table2id=t3.table2id
inner join on table4 as t4 on t3.table3id=t4.name
inner join on table5 as t5 on t5.table3id=table3.table3id 
where t1.firstname like '%John%' and t5.somecode='5'
group by t1.id
order by t3.name,t1.firstname,t1.lastname
user3036372
  • 24
  • 7
  • So basically the JOINs are just to implement the relation with the tables and all the real dependencies are listed in the WHERE. But that way all the tables are JOINed fully without a subquery? – Vincent Jan 27 '14 at 11:09
  • 1
    join makes it easier to relate any two tables. if there is a common field between two tables then inner join is used else outer join.sub queries can always be used but as far as i know it complicates the whole query. as far as there is any way out to fire a query without a sub query you should use it. – user3036372 Jan 28 '14 at 06:00