SQL is FUN - Everything MySQL2024-03-29T13:03:44Zhttp://everythingmysql.ning.com/forum/categories/sql-is-fun/listForCategory?feed=yes&xn_auth=noSQL query helptag:everythingmysql.ning.com,2009-09-14:3993569:Topic:1272009-09-14T03:14:12.000ZZachary Buckholzhttp://everythingmysql.ning.com/profile/ZacharyBuckholz
I am updating my Drinks Dictionary iPhone app which uses a sqlite database. I need to create a query to count how many items begin with the letter 'a', 'b', 'c', etc....<br />
<br />
I can get as far as 'select DISTINCT substr(name,1,1) FROM drinks;<br />
<br />
How can I apply a GROUP BY clause or something else so in one query I can get every unique first character and a count of how many drinks start with that character?<br />
<br />
Thanks<br />
Zach
I am updating my Drinks Dictionary iPhone app which uses a sqlite database. I need to create a query to count how many items begin with the letter 'a', 'b', 'c', etc....<br />
<br />
I can get as far as 'select DISTINCT substr(name,1,1) FROM drinks;<br />
<br />
How can I apply a GROUP BY clause or something else so in one query I can get every unique first character and a count of how many drinks start with that character?<br />
<br />
Thanks<br />
Zach Misusing LEFT JOINtag:everythingmysql.ning.com,2009-09-12:3993569:Topic:1102009-09-12T00:03:31.000ZSarah Sproehnlehttp://everythingmysql.ning.com/profile/SarahSproehnle
SQL is definitely fun, unless it's not. ;) I've seen many cases where developers misuse a LEFT (or RIGHT) JOIN. Either they don't understand what an outer join is, or they needed an outer join at some point, but as the code evolved it wasn't necessary any more.<br />
<br />
Let's say you have a Customer table and a Phone and Address table that reference the Customer table. To query customers and their info:<br />
SELECT fname, lname, number, street<br />
FROM cust JOIN phone<br />
USING (custid)<br />
JOIN address<br />
USING…
SQL is definitely fun, unless it's not. ;) I've seen many cases where developers misuse a LEFT (or RIGHT) JOIN. Either they don't understand what an outer join is, or they needed an outer join at some point, but as the code evolved it wasn't necessary any more.<br />
<br />
Let's say you have a Customer table and a Phone and Address table that reference the Customer table. To query customers and their info:<br />
SELECT fname, lname, number, street<br />
FROM cust JOIN phone<br />
USING (custid)<br />
JOIN address<br />
USING (custid);<br />
+-------+-----------+-----------------+----------------+<br />
| fname | lname | number | street |<br />
+-------+-----------+-----------------+----------------+<br />
| Sarah | Sproehnle | +1 555-555-1212 | 42 Sakila Lane |<br />
| Chris | Schneider | +1 555-555-9898 | 88 MySQL Way |<br />
+-------+-----------+-----------------+----------------+<br />
<br />
However, if you may have a customer without an address or phone number, they wouldn't be listed. So use a LEFT JOIN:<br />
SELECT fname, lname, number, street<br />
FROM cust <b>LEFT JOIN</b> phone<br />
USING (custid)<br />
<b>LEFT JOIN</b> address<br />
USING(custid);<br />
+-------+-----------+-----------------+----------------+<br />
| fname | lname | number | street |<br />
+-------+-----------+-----------------+----------------+<br />
| Sarah | Sproehnle | +1 555-555-1212 | 42 Sakila Lane |<br />
| Chris | Schneider | +1 555-555-9898 | 88 MySQL Way |<br />
| MySQL | User | NULL | NULL |<br />
+-------+-----------+-----------------+----------------+<br />
<br />
Now for the mistake I often see. Someone will do a LEFT JOIN between 2 tables, but then an inner join that makes the left join pointless:<br />
SELECT fname, lname, number, street<br />
FROM cust <b>LEFT JOIN</b> phone<br />
USING (custid)<br />
<b>JOIN</b> address<br />
USING (custid);<br />
+-------+-----------+-----------------+----------------+<br />
| fname | lname | number | street |<br />
+-------+-----------+-----------------+----------------+<br />
| Sarah | Sproehnle | +1 555-555-1212 | 42 Sakila Lane |<br />
| Chris | Schneider | +1 555-555-9898 | 88 MySQL Way |<br />
+-------+-----------+-----------------+----------------+ BAD SQL - I've seen it happen, really!tag:everythingmysql.ning.com,2009-09-09:3993569:Topic:672009-09-09T03:50:41.000ZChrishttp://everythingmysql.ning.com/profile/Chris
So... in the shared hosting a DBA comes across many bad queries. For one reason or another a developer or admin or the guy who someone paid $10/hr writes SQL like this:<br />
<br />
SELECT DISTINCT Resort.ResortID, Resort.Name, Resort.Address1, Resort.Address2, Resort.Address3, Resort.Address4<br />
FROM Resort, ResortAttribute c2, ResortAttribute c3, ResortAttribute c4, ResortAttribute c5, ResortAttribute c6,<br />
ResortAttribute c7, ResortAttribute c8, ResortAttribute c9, ResortAttribute c10, ResortAttribute c11,…
So... in the shared hosting a DBA comes across many bad queries. For one reason or another a developer or admin or the guy who someone paid $10/hr writes SQL like this:<br />
<br />
SELECT DISTINCT Resort.ResortID, Resort.Name, Resort.Address1, Resort.Address2, Resort.Address3, Resort.Address4<br />
FROM Resort, ResortAttribute c2, ResortAttribute c3, ResortAttribute c4, ResortAttribute c5, ResortAttribute c6,<br />
ResortAttribute c7, ResortAttribute c8, ResortAttribute c9, ResortAttribute c10, ResortAttribute c11, ResortAttribute c12,<br />
ResortAttribute c13, ResortAttribute c14, ResortAttribute c15, ResortAttribute c16, ResortAttribute c17, ResortAttribute c18,<br />
ResortAttribute c19, ResortAttribute c20, ResortAttribute c21<br />
WHERE Resort.ResortID = c2.ResortID<br />
AND Resort.ResortID = c3.ResortID<br />
AND Resort.ResortID = c4.ResortID<br />
AND Resort.ResortID = c5.ResortID<br />
AND Resort.ResortID = c6.ResortID<br />
AND Resort.ResortID = c7.ResortID<br />
AND Resort.ResortID = c8.ResortID<br />
AND Resort.ResortID = c9.ResortID<br />
AND Resort.ResortID = c10.ResortID<br />
AND Resort.ResortID = c11.ResortID<br />
AND Resort.ResortID = c12.ResortID<br />
AND Resort.ResortID = c13.ResortID<br />
AND Resort.ResortID = c14.ResortID<br />
AND Resort.ResortID = c15.ResortID<br />
AND Resort.ResortID = c16.ResortID<br />
AND Resort.ResortID = c17.ResortID<br />
AND Resort.ResortID = c18.ResortID<br />
AND Resort.ResortID = c19.ResortID<br />
AND Resort.ResortID = c20.ResortID<br />
AND Resort.ResortID = c21.ResortID<br />
AND Resort.Name like '%%'<br />
AND c2.AttributeID = 273<br />
AND c3.AttributeID = 357<br />
AND c9.AttributeID = 182<br />
AND c10.AttributeID = 256<br />
AND c11.AttributeID = 258<br />
AND c12.AttributeID = 243<br />
AND c13.AttributeID = 225<br />
AND c14.AttributeID = 208<br />
AND c15.AttributeID = 446<br />
AND c16.AttributeID = 191<br />
AND c17.AttributeID = 204<br />
AND c18.AttributeID = 462<br />
AND c19.AttributeID = 211<br />
AND c20.AttributeID = 216<br />
AND c21.AttributeID = 217<br />
AND (c4.AttributeID = 173<br />
OR c5.AttributeID = 181<br />
OR c6.AttributeID = 184<br />
OR c7.AttributeID = 215<br />
OR c8.AttributeID = 234)<br />
ORDER BY Resort.Name;<br />
<br />
*************************** 1. row ***************************<br />
Table: Resort<br />
Create Table: CREATE TABLE `Resort` (<br />
`ResortID` int(11) NOT NULL auto_increment,<br />
`Name` varchar(255) NOT NULL default '',<br />
`Address1` varchar(100) default NULL,<br />
`Address2` varchar(100) default NULL,<br />
`Address3` varchar(100) default NULL,<br />
`Address4` varchar(100) default NULL,<br />
`Phone1` varchar(30) default NULL,<br />
`Phone2` varchar(30) default NULL,<br />
`Phone3` varchar(30) default NULL,<br />
`URL` varchar(255) default NULL,<br />
`Description` text,<br />
`NumberOfUnits` mediumint(11) default NULL,<br />
`AvgPrice` float default NULL,<br />
`RatingOverall` float default NULL,<br />
`RatingGuestRoom` float default NULL,<br />
`RatingService` float default NULL,<br />
`RatingCleanliness` float default NULL,<br />
`RatingDiningExperience` float default NULL,<br />
`RatingRecommendation` float default NULL,<br />
`RatingCommercial` float default NULL,<br />
PRIMARY KEY (`ResortID`)<br />
) TYPE=MyISAM<br />
1 row in set (0.00 sec)<br />
<br />
*************************** 1. row ***************************<br />
Table: ResortAttribute<br />
Create Table: CREATE TABLE `ResortAttribute` (<br />
`ResortID` int(11) NOT NULL default '0',<br />
`AttributeID` int(11) NOT NULL default '0',<br />
PRIMARY KEY (`ResortID`,`AttributeID`)<br />
) TYPE=MyISAM<br />
1 row in set (0.00 sec)<br />
<br />
Now, I love a good SQL puzzle but this is ridiculous! I sometimes give the SQL above as an interview question and you would not believe how many DBAs cannot solve it.<br />
<br />
Here is some background:<br />
1. This was a real query<br />
2. The person who wrote it made it incredible inefficient<br />
3. It is easy to solve if you just sit back and look at it<br />
4. The query took forever to return a result<br />
<br />
Ready for the solution? Not so fast! I need someone to comment on this before I reveal the "What where they thinking" and "How to solve it".