Shantanu's Blog

1) Why doesn't the following query work?

SELECT col1, col2, concat_ws( ' ', firstname, lastname ) AS fullname
FROM players
WHERE fullname LIKE 'The Name'

The reason you are getting the error is because you can't refer to a column alias in a WHERE clause. Take your main part of the query:

SELECT concat_ws( ' ', firstname, lastname ) AS fullname
FROM players

and wrap another select around it:

SELECT col1, col2 FROM (
SELECT concat_ws( ' ', firstname, lastname ) AS fullname
FROM players)

now in order to use this you need to assign an alias to your inner query because it is considered a derived table. you can use whatever you like but using DT or dt is easy to remind you it is a Derived Table

SELECT col1, col2 FROM (
SELECT concat_ws( ' ', firstname, lastname ) AS fullname
FROM players
) as DT

You can now use your WHERE clause to refer to fullname.

SELECT col1, col2 FROM (
SELECT concat_ws( ' ', firstname, lastname ) AS fullname
FROM players
) as DT
WHERE fullname LIKE 'The Name'
LIMIT 0,30

_____

2) Why am I getting the wrong results?

SELECT ROUND(( NOW() - last_accessed ) /60)
FROM sessions
WHERE user_id = '1'

Sometimes your query may show unexpected results. For e.g. in the query you may get correct results if the difference is 1 or 2 minutes but if the difference is 20 minutes or so, you get the incorrect value of 30 minutes and if actual result is 45, you get 58 minutes and so on. The correct way to write this query is to convert the time to seconds and then divide by 60.

SELECT ROUND( TIME_TO_SEC( NOW() - last_accessed ) /60 ) AS minutes_ago
FROM sessions
WHERE user_id =171

And if you are using latest version of MySQL then you can use timediff function.

SELECT ROUND( TIME_TO_SEC(TIMEDIFF(NOW(), last_accessed) ) / 60 ) as minutes_ago
FROM sessions
WHERE user_id = 1

_____

3) What is a Theta Join?

Theta Joins are not joins which employ comma syntax, theta joins are joins which involve inequality comparisons in the join conditions. For e.g.

select a.foo, b.bar
from a, b where b.id < a.id

select a.foo, b.bar
from a inner join b on b.id < a.id

Labels: mysql FAQ

If you are really fed up with powerpoint organization charts, you can try this nifty tool called mindmeister web utility.

http://www.mindmeister.com/maps/show_public/1274157

Check out the example given above. It's child's play to create such nice org. charts. You can register on the site and create professional looking charts within minutes, export as a gif image, share and work with your colleagues on the same chart at the same time.

Labels: mysql, mysql case study

Hello all. I have an interesting idea for a query but i have no idea how to make it work. I'm pretty decent at SQL so i'm no noob, but this one has me stumped. Maybe it can't be done with a query, or maybe it's right in front of my nose and i cant see it. Anyway here it is:

You have a table that lists part numbers and their price, but the price changes over time. So you end up having multipe costs for each part number, each with a start and end date. Looks something like this:

partnum cost start end
-------- ---- ----- ---
001 5.00 2006-1-1 2006-5-31
001 5.25 2006-6-1 2006-11-30
001 5.20 2006-12-1
002... etc...

You can see when one cost ends the next starts the next day. Now what i need to do is check this table for errors in which there are 2 simultaneous costs listed. In other words, find all partnums where the end date for one overlaps the start date of the other.

Here is what an error would look like:

partnum cost start end
-------- ---- ----- ---
005 2.00 2007-4-1 2007-6-30
005 2.25 2007-5-1

See how the start date of the second one is before the first one ended? How can i write a query to find these? Anyone have any ideas?

http://forums.devshed.com/mysql-help-4/an-interesting-query-puzzle-can-anyone-solve-463651.html

Labels: mysql, mysql case study

I use the following query to determine the average score by teams taking a particular test.

SELECT teams.teamname, teams.id, AVG(game_sessions.score) as score
FROM teams, game_sessions
WHERE teams.id = game_sessions.team_id
GROUP BY teams.teamname
ORDER BY score DESC LIMIT 0,25

What I'd like to change now is instead of averaging all of the scores from each team I'd like to only average the top 50% of scores from each team.

Is this possible?

http://forums.mysql.com/read.php?10,166707,166707#msg-166707

Labels: mysql, mysql case study

I have written a very useful add-on for firefox users.
It will convert the script from one Indian language to another.
It means you can read that interesting gujarati blog in Marathi by clicking on a single button on the toolbar.


I hope some of you will find this useful and write a review so that it will be available to all the people. Currently it's available only to the registered users.

1)Go to
https://addons.mozilla.org/en-US/firefox/

2) Register from link at upper right
https://addons.mozilla.org/en-US/firefox/users/register

3) Login
https://addons.mozilla.org/en-US/firefox/users/login

4) Go to "My Account" (upper right)
https://addons.mozilla.org/en-US/firefox/users/edit

5) Check Show Sandbox in Profile and Save

6) Click on Sandbox on upper left

7) Search for "girgit" get the following result page:
https://addons.mozilla.org/en-US/firefox/addon/5483

You can check out the source code by clicking on the "View the source" link.

Write a nice review. Thanks.

Labels: firefox

I would like to now if there's any way through mysql to retrieve the position
of a specific record in a query.

My question might be a little confusing but let me explain through an example
Take following example table

ID Name Datum
1 Pierre 2007/07/07
2 Paul 2007/07/06
3 Rubens 2007/07/04
4 Paul 2007/07/05
5 Pattrick 2007/07/17

with following sql
SELECT * order by datum desc

Obviously this the result
5 Pattrick 2007/07/17
1 Pierre 2007/07/07
2 Paul 2007/07/06
4 Paul 2007/07/05
3 Rubens 2007/07/04

What i want to know now is which row,for instance, the name Paul would be found in that result

in this example it would be row 3 and 4

http://forums.mysql.com/read.php?10,165113,165113#msg-165113

Labels: mysql, mysql case study