In this scenario, I was attempting to Left Join a table to another table and Inner Joining them to a third table. I ended up getting no results from the query.
A bit of research found this link:
In short, you have to perform the Inner Joins first before you Left Join.
My solution was to Left Join the entire way. My reason for the Inner Join in the first place, and the reason why I’m unsure of this method was that it seemed like a good way to kill performance. However, I’m not at the stage where I need to kill time performance tuning the system so I’ll have to ignore it for now.
I’m probably wrong anyway, but it wouldn’t hurt to check when I finally get the time.
When joining, I like to use ON + AND a lot. I heard that this tends to have better performance than running a WHERE query. And it worked well as long as I stayed with INNER JOINS. Imagine my surprise when I needed to do a LEFT JOIN while performing a conditional match. I expected a single row back, but SQL returned every single possible row instead.
After searching around, I found this link:
Currently, I’m building up a website, so I can’t begin using Explain to tune. In the meantime, I’m going to use WHERE to filter the list whenever I have an Outer join. If this ends up killing performance, I’ll have to find a way to alter the queries later.
Did you know that if you define an alias when selecting columns, you probably can’t use it inside of a WHERE clause without getting an Unknown Column error?
The standard explanation given by This site (http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html) and used by many of the sites that I found with google explains it by saying that:
Standard SQL disallows references to column aliases in a
WHERE clause. This restriction is imposed because when the
WHERE clause is evaluated, the column value may not yet have been determined.
I think I found a layman’s explanation for the problem here:
SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name as not yet occurred.
And I believe a workable solution can be found here:
So…instead of using WHERE, use HAVING.
Here’s the conclusion of when to use either as found by:
Here’s the rule. If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.
Here’s another rule: You can’t use HAVING unless you also use GROUP BY.
INSERT INTO tableNew (col1, col2)
SELECT tbl1.col1, tbl2.col2
FROM tbl1 JOIN tbl2
DELETE users, comments
FROM users JOIN comments ON comments.author=users.id
Join tableB B on B.id=C.bid
Join tableA A on A.id=B.aid
Where A.id=1 or A.id=2 or A.id=3
Here’s an interesting SQL function:
Given a list, returns the first non-null value in the list. If all values are null, returns null.
This link had some good arguments against exposing auto-incrementing numbers on a web page.
But generally, web developers feel uncomfortable exposing IDs that allow guessing other IDs by just incrementing or decrementing a number. Many resort to random, multi-digit IDs instead.
On a more obscure note, numeric IDs may also allow competitors to estimate your growth by keeping track of how the incremental value increases.
Here’s the query to troubleshoot:
UPDATE `Items` SET ( `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') ) WHERE ( `Serial`=33 );
When you run the above query, you’ll get a funny message:
<span style="color: #ff0000;"> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') ) WHERE ( `Serial`=33 )' at line 1</span>
So where’s the syntax error?
It turns out that the parenthesis within the SET statement is a syntax error. I’m currently not sure why the parenthesis cause problems, but you need to take them out in order to run the above query.
So, the working query would be:
UPDATE `Items` SET `Date_Acquired`=STR_TO_DATE('1/22/2012', '%c/%e/%Y') WHERE ( `Serial`=33 );
Following the spirit of atomic operations, I found a link on starting and ending a transaction.
And this might be a good link for figuring out where to place the commit statement when using prepared statements.
A peeve of mine is trying to purchase something online from an e-commerce site which specifically states In Stock, and then waiting for weeks only to discover I’ve been back-ordered. It’s doubly bad when I’m trying to buy myself a christmas present. Yup, myself.
I haven’t tried the method listed in the below link yet, but I’m setting it aside in case I need it in the future.
It’s easier if you don’t have to use a query to insert the current time and date. This is particularly useful for “date created” and “date modified” columns. MySQL doesn’t let you do both at the same time for one table, but there are ways around it.
I used the second option, inserting a null into the column. I read somewhere that it’s sort of hacky, however it appears to be legitimate behavior as seen in below link.
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html (Scroll to the bottom)
Personally, I prefer to insert a NOW() value instead of NULL since it’s more intuitive.