UnHoly Compound SELECT from Hell

Those of you who have faced similar problems with MySQL might find this amusing.


This was ultimately the only thing that Access would accept in order to do the kind of query I wanted.. and that's after I lowered my standards. It could've been much, much worse..

God what I wouldn't give for views....

**Shudders** Access **Shudders**

I had to take a business class once that the professor wanted team to build a database for an example business to handle inventory tracking and everyone used Access since that is what they learned in their "Computers for Business" class. I, having skipped the class since I was a BETA tester for MS office, installed a copy of OSCommerce Suite on my webserver and turned in a one page word document to a link and username/password. Made a C+ on the project for being a smart *** about it. Story of my at college. Always did things my own way because it was better.

However at the dnd of the day: does it work?

Sometimes its not if its "how it should" be coded as much as so long as it works. After all, that is what the end user wants. They don't give a flying leap about whether the statements conform to ANSI '92 SQL syntax.

How "well" it is coded really is nothing more than honour amoung geeks.

Oh quit your wining!

I work for the state and am currently working with the state DOP (Department of Personel) to pull information about employees for reports. Their database is not only NOT normalized, but they mix information about different departments into the same tables using overlapping codes with different meanings.

Doing a word count (in MS word) on one SQL quiry I'm currently using, it's over 1,400 characters w/o spaces. This quiry simply looks up general information on all employees at my work but pulls from 4 different tables, and has a 7 item WHERE clause on it. (All joined by "AND"s)

*WARNING, THOSE WEAK OF HEART SHOULDN'T READ THE FOLLOWING*

Washington State's DOP stores all their personel info in an Access database, available through an ODBC connection to any computer on their network. If I'm not carefull with my select statements, I'll accidently pull information about other departments.

But I'm not too worried about anyone getting my info since half the info they have in there is incorrect anyways. (or more) Plus trying to map the darn thing reminds me of where the term "spaghetti code" came from.

You've never heard of IN() ?

Big SQL statements aren't uncommon, but compounding a bunch of SELECTs like that is just stupid. If I had VIEWs that would be no issue at all (are you listening MySQL... ARE YOU LISTENING???). And, I don't think you CAN normalize relations in an Access DB most of the time. I've tried with this one, and I just simply can't do it. There's very minimal data duplication (integer ids are assigned to text terms just to save disk space), but it's tough.

Hmmm... storing employee information in a spreadsheet organizer (Access) that's not transaction safe and can be accessed by pretty much anyone on the network.... that's smart.

They're listening.

http://www.mysql.com/doc/en/ANSI_diff_Views.html

Actually... that page was sort of my point.



I love their BS excuse for not having them too

Someday MySQL; someday you will be a real database server just like all the other little database servers.

LMAO... totally. I agree, but oh well... at least theres postgresql.

That's my one gripe with mySQL too.

As far as the DOP database being unnormal, they repeat the same information in several tables, (ie: Soc. Sec. Number, Name, etc.) they have FAR too many 1 to 1 relationships between tables, and don't have any unique fields.

The employee id is only unique per agency and sub-agency codes. So to get an employee name, I would have to use something like:

Select employeeName from <table name> where AgencyCode='300' and SubAgencyCode='C33' and personID=123456;

LOL

Sounds like a typical Access spreadsheet to me.

You know, it's kind of funny to hear the complaints about mysql. Essentially it is a website database meant for basic/intermediate use. It didn't have support for transactions, etc.

Now, everyone wants this feature and that feature, pretty soon it's going to be like the upper end db's. And I'd bet the performance will suffer because of that. The quick, small footprint db will be replaced by the bigger, feature loaded, resource using, somewhat slower db.

I always worked under the assumption that if mysql didn't have all the features you needed you just used a different db.

Just a thought

Hey, I'm not complaining too loudly. I love mySQL and I use it for my own web sites. There's only been a few times I've ran into problems with it, but they're pretty easy to work around.

I've even been trying to get my work to let me setup a linux box with mysql and perl on it. That'd be SO much better then what we have right now!

The problem is that people try to pass it off as a RELATIONAL database management system. If it's good at what it does, that's fine, but if what it does doesn't involve being a RDBMS, don't try to pass it off as one. Same thing that irks me about Access. It makes a nice SQL-enabled (sort of) Spreadsheet tool. But, don't try to pass it off as a RDBMS or I WILL lay into you (not you in particular - whoever tries to pull that crap)... I'm not so passionate about cutting MySQL down to size, however... it's a lot closer to one than Access and it's still moving forward.