CODEWITHSUNDEEP
mysqlsql
user2093576
user2093576

Reputation: 3092

How to optimize my query joining two tables in Mysql

My requirement is as below

SQL1: sel Names, Numbers from Table1 where condition = FilterName

the output of this will be like:
    aa,11
    bb,22
     cc,33

SQL2: I need to write a sql like this

 Sel * from table2 where condition = 'Something' 
         And names & Numbers in ([aa,11],[bb,22],[cc,33])

I have written the query like 

Select * from table2 where condition = 'SOmething' 
         And names in (select Names from Table1 where condi = 'FilterName')
         And Numbers in (select Numbers from Table1 where condi = 'FilterName')

Is there any other better way to write this query? I cannot use procedure here

Upvotes: 0

Views: 54

Answers (2)

Vatev
Vatev

Reputation: 7590

From my interpretation of the problem, something like this should do it:

SELECT *
FROM table2 t2
JOIN table1 t1 ON t1.Names = t2.names AND t1.Numbers = t2.numbers
WHERE t1.condi = 'FilterName'
AND t2.condition = 'Something'

MySQL does have syntax for (a,b,c) IN ((a1,b1,c1),(a2,b2,c2)...), but I haven't yet found a way to use indexes with it, so it's not very useful.

Upvotes: 2

sekky
sekky

Reputation: 834

You can try to use JOINs here:

Select table2.* from table2
join table1 on table2.names = table1.names and table2.numbers = table1.numbers
where table1.condi = 'FilterName'
and table2.condition = 'Something'

I hope this helps!

Upvotes: 3

Related Questions