CODEWITHSUNDEEP
c#asp.netsqlmysql
Ishan
Ishan

Reputation: 4028

Modifying sql query,dont want to use join operation

The following query uses join operation, i want the same query to execute without using join operation. Is it possible to do so? If yes than how can i do it?

select jname, jcode from heardt inner join judge ON heardt.jud1 = jcode

The reason i am trying to do this is because i am using odbc connection for mysql and join operations are not getting executed at all as web page is loading for infinitely long and not giving any output. That is the reason why i want to try without using join operation

Upvotes: 0

Views: 205

Answers (7)

mariana soffer
mariana soffer

Reputation: 1853

As lieven pointed out I think his solution is a good one

select  jname
        , jcode 
from    heardt 
        , judge 
where   heardt.jud1 = judge.jcode

But you should create indexes in the fields you are joining, therefore the result will be provided much quickly so add

Create index a1 on heardt(jud1); Create index a2 on judge(jcode);

I think this is the better possible option

Upvotes: 0

Tom 'Blue' Piddock
Tom 'Blue' Piddock

Reputation: 2210

If you are able to run it SQL Manager you should be able to run it on the ODBC Connection, if not there is something wrong with the way you are instantiating that connection in C#.

Can you post the c# code you are using so we can make a better judged answer for you.

Upvotes: 0

littlegreen
littlegreen

Reputation: 7420

i am using odbc connection for mysql and join operations are not getting executed as web page is loading for infinitely long and not giving any output. That is the reason why i want to try without using join operation

That's probably not because your JOIN is not getting executed, but because your JOIN query is taking too long. And that's probably because you don't have the correct index defined (an index, preferably a clustered index, on judge.jcode).

If the join is still taking too long after adding such an index you could consider precaching the query with a table or indexed view (latter however not supported in MySQL).

Upvotes: 0

Heiko Hatzfeld
Heiko Hatzfeld

Reputation: 3197

There is no additional filter on that query. It might cause the query to return many rows. This could cause a slowdown, depending on the number of records in your table.

You should consider limiting the number of returned records from the query.

Something else you need to check, if there is an index on the JCode field

Upvotes: 1

jvanrhyn
jvanrhyn

Reputation: 2824

The query Select jname, jud1 from heardt where not jud1 is null shows that jud1 has a value, but not that that value is valid. The join or where validates the relationship between the tables.

If your query takes a very long time to execute with the join in place it is most likely that you do not have correct indexes on the tables causing a table scan to take place instead of and indexed search.

Upvotes: 0

Rudi
Rudi

Reputation: 3228

Select jname, jud1 from heardt where not jud1 is null

EDIT: Ok, this was quick. So: Why do you need the 'join'?

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58451

I don't know your rationale, I find JOINS much easier to read but you can replace them by joining (no pun intented) the tables in the where clause.

select  jname
        , jcode 
from    heardt 
        , judge 
where   heardt.jud1 = judge.jcode

Upvotes: 2

Related Questions