Skip to content

Tag: mysql

Speed up joins on thousands of rows

I have two tables that look something along the lines of: And a query to get data from t1. The query runs fine and takes a few ms to get data from it, although problems start appearing when I use joins. Here’s an example of one of my queries: Which can return a few thousand rows, that might look somethi…

Count from 4 tables with join and null values

Tables: What I am expecting to query is this: However this is what I get: With this query: I wonder, in what way the query above could be modified to return the expected results? What would be a good approach to achieve this? Answer You are joining along two dimensions, so you are getting a Cartesian products…

SQL select query retrieval blank return

I have to retrieve distinct entities from a column, all of which start with a vowel. The query looks like this : It’s not giving compilation errors, but it’s not returning anything. Any ideas? Answer You can use regular expressions: LIKE wildcard patterns do not support character classes, except i…

Weird behavior of MySQL Server when ordering by 2 conditions

Say a database table my_table has the following structure: and some of the rows in the database have total_votes == 0. I want to get the rows ordered like this, except I also want to include the rows that have total_votes == 0. I tried using the following query, and it worked like a charm: My question is why …

How to left join when only matched one record from right table

I want to know How to left join when only matched one record from right table. For example, tableA id value 1 34 2 42 3 60 tableB id value tableA_id 1 20 1 2 31 1 3 50 2 I want to get result like below using left outer join. tableA_id tableA_value tableB_value 1 34 null 2 42 50