on SQL and the pancake in the middle.
April 15, 2006 1:52 PM   Subscribe

SQL WTH: various examples given on websites and in a small stack of books don't work.

An example: inner joins, listed on a website as done by enclosing the join conditions in parentheses, don't work. One of the books listed them in quotation marks instead. That also did not work. MS Access 2003 wanted them with an equals sign between the two conditions and no space.

A second: CONCAT ==> undefined function. CONTAINS ==> undefined function.

A third: SELECT x,y,z AS a FROM b. Concatenation returns a blank line on any record with a blank field. So a result set of authors with no listed middle name returns a blank line. For better or worse, using this to search authors won't turn up Homer, Cher, or Bono.

There's yet another example with SELECT AS FROM WHERE. The book lists it as retrieving results matching the WHERE condition; but the program thinks that it's for user authentication instead. (Typing in the intended match will return *all* results, regardless of whether they match; typing in something else--or nothing at all--will return no results.)

What's going on? How do I correct it? Or, alternatively, what sources do I need to be consulting, as the ones I have are confusing me more than they're helping?
posted by Tuwa to Computers & Internet (9 answers total)
Best answer: inner joins - you need to post your working.
concat - non-standard but common. have you tried || instead?
contains - non-standard (i think). what are you trying to do?
SELECT x,y,z AS a FROM b - selects x, y and "a" (with z's values). no concatenation anywhere. don't understand.
WHERE - most definitely not user authentication.

there is a standard (ansi) for sql, but every db extends it, in incompatible ways. so the best source for information is the manual that came with the database you are using.

sounds like you're confused with documentation from non-standard databases and also making mistakes.

if you want help with specific examples you need to post exactly what you typed, at least, and some idea of what the tables are would also be useful.
posted by andrew cooke at 2:04 PM on April 15, 2006

Best answer: Note SQL implementations differ by db. There's some basic standards but really for things like joins you're going to need to use documentation for your db.

SQL for Access
posted by bitdamaged at 2:12 PM on April 15, 2006

Best answer: Is MS Access 2003 your platform? If so, just accept that the Jet SQL engine in Access does not hold very closely to SQL standards.

If you want to learn to write queries in "SQL" in Access, generate some starter ones with the query design tool and then flip over to the "SQL view" to see how Access represents the actions you wish perform. I'm sure there are references showing particular ways Access's Jet SQL is broken compared to ANSI, but experimenting and watching Access's code generation is probably the fastest way to get Access's version of the basics down.
posted by NortonDC at 2:18 PM on April 15, 2006

Yes, what others have said, there are different versions of SQL, depending which database it is and which version. You're more likely when searching to get code for MySQL, it'd say. I've even got Google saying "did you mean MySQL?" when I'd specifically searched for "MSSQL" to get code for Microsoft SQL Server 2000.

Derail/sub-question -- what is the core of SQL? As in, what functions are there that anyone can be sure will work? "SELECT FROM TABLE WHERE FIELD = VALUE" and the basics presumably work in all SQL implementations, but is there a core list of function we could refer to -- on which things like CONCAT and random selection and sub-selects and so on aren't listed?
posted by AmbroseChapel at 3:39 PM on April 15, 2006

I think you're just going to have to live with the fact that you write SQL statements against a certain DB's dialect of SQL. There is no way to write robust applications that can be trivially transported between databases. There is an ANSI standard for SQL but I think referring to it is mostly useless (or perhaps just academic) because to do anything non-trivial you will be back to using a particular dialect of the DB that you're working with.
posted by Rhomboid at 4:06 PM on April 15, 2006

Response by poster: I figured the inner joins out by using the wizard and then looking at the SQL, as NortonDC suggested. Part of the problem was the syntax and the rest was that I had tables with numerical names which needed to be enclosed in brackets.

As for the concatenation:
In a table "directors" with field names "givenName", "middleName", and "familyName", the query
SELECT givenName + " " + familyName AS name;
Francis Coppola
Ingmar Bergman
Martin Scorsese
Paul Anderson

The query
SELECT givenName + " " + middleName + " " + familyName AS name
FROM directors;

returns the following:
Francis Ford Coppola

Paul Thomas Anderson.

Adding the middle name for Bergman and Scorsese solves that, but I'd rather have a query that can pull whatever information is available rather than failing when any field is empty.

I'm having this problem any time a field is empty, regardless of which field in the query it is.

The wizard is not helping me figure this one out.

What I was aiming for was to have a convenient sort order (family name) but also to be able to concatenate to search all known names for a given person (I intend to add fields for pseudonyms, alternate spellings or transliterations, etc.)

It's quite possible I'm just going about this wrong. Again, the books I have are often more puzzling than helpful.
posted by Tuwa at 5:12 PM on April 15, 2006

Tuwa, you'll appreciate Access's NZ function to deal with null's. It's very handy. The help system will explain the (very simple) syntax for using NZ.
posted by NortonDC at 5:17 PM on April 15, 2006

Response by poster: Thanks, everyone. This one was actually palm/forehead simple: queries including results with fields with null values have to use & rather than +.


To piggyback on my own question, suggestions for a good Access 2003 book are very welcome. None of the ones I got from the library address this so I wonder what other important things I must be missing.
posted by Tuwa at 6:48 PM on April 15, 2006

You might want to check out Microsoft's MSDN newsgroups.

I learned a lot about MSAccess SQL and VBA in the Access newsgroup. Lots of MSAccess-heads there who can answer your specific questions and point you to other good resources.
posted by syzygy at 4:39 AM on April 16, 2006

« Older Mercury in ancient Cosmetics?   |   2 cool 4 pie? Newer »
This thread is closed to new comments.