mysql - I have written query to sort name starting with S and V in give database , error coming up is below

Question

select * from cricket_1111 , cricket_999 
where first_name LIKE "s%v" 
LIMIT 0, 1000   

Error Code: 1052. Column 'first_name' in where clause is ambiguous 0.000 sec

question from:https://stackoverflow.com/questions/65599723/i-have-written-query-to-sort-name-starting-with-s-and-v-in-give-database-error

Answer 1

You have a first_name column in both tables, so you have to specify which table you want to use like

select * 
from cricket_1111 , cricket_999 
where cricket_1111.first_name LIKE "s%v" 
LIMIT 0, 1000   

It would also be very productive if you took a look at the JOIN syntax rather than using from cricket_1111 , cricket_999

Something like

select * 
from cricket_1111 c1
    JOIN cricket_999 c9 on c9.link_column = c1.link_column
where c_1.first_name LIKE "s%v" 
LIMIT 0, 1000 
ORDER BY c_1.first_name

Also a limit clause without a sort ORDER BY can be very unpredictabe