Let me preface this by saying I am open to design criticism if i am making this harder than it needs to be.

I have 3 tables:

serials (id, number)
columns (id, name)
data (id, serial_id, column_id, value)

There are different amounts of rows in data that correspond to each row of serials.

What i am trying to retrieve is each serials.number with all corresponding data.value fields appended to the end of the result row. This is an example of what i am looking for if all corresponding values were stored in a c-style array:

row 1: serials.number, data.value[0], data.value[1], data.value[2]
row 2: serials.number, data.value[0], data.value[1], NULL
row 3: serials.number, data.value[0], NULL, data.value[2]
row n: etc ...

The reasoning behind doing it this way was to allow the amount of columns to be different for varying implementations without having to ALTER TABLE. I also wanted the column name to support "full text" without having to write a parser for table field names. In addition to all of this, I need to gather all the information in 1 query as to facilitate WHERE clauses to narrow the results.

Any criticism/advice is welcome I'de love to expand my knowledge of SQL to avoid these issues in the future.

do a search for EAV (entity-attribute-value)

this is a design technique with severe shortcomings, and i urge you to consider alternatives

the main problem is that "to gather all the information in 1 query as to facilitate WHERE clauses to narrow the results" turns out to be ~way~ harder than you think

I will simplify the situation (1 table?) and try to only inject complexity when needed When doing development in a vaccuum sometimes it is hard to tell which voice to listen too

Thanks again.

...then you have come to the right place.

Well, no criticism here, but some questions about your reasons:

Why, exactly, is ALTER TABLE such a problem?

Why is the first syntax so much easier or the second so much harder?

Why do you have to parse field names? Are you sure you understand what "parsing" means? If you really do have to parse field names, does your language / standard library not support regular expressions?

Why is one query important? Can't you cache the schema information?

And what, exactly, is stopping you from gathering all this data in one query? Have you looked into stored procedures?

Finally, have you thought about how you're going to handle integrity constraints?

Or views ?

It is not really a problem rather a feeling that i was approaching the situation wrong. Which it appears i was by second guessing myself Fields will be added and removed at a handful of user's whims and it seemed dangerous to have those changes directly affecting all the data (being that it is only 1 table). I realize that it is my responsibility to make it not dangerous by doing thorough validation and utilizing integrity constraints.

With a single table using the ALTER TABLE method, i will be using the field names as headers for the end result. If the field name is more than 1 word i will need to perform some kind of "beautification" on it before displaying it to the screen. For example I don't want the header to show as "last_modified", but instead as "Last Modified".

Yes it does. I will be using such to handle the "beautification" of the header names.

One query was only important because of my lack of experience with SQL. The only other alternative i could come up with at the time was pushing the data from all 3 tables into structures and then manipulating it from there. This seemed like a horrible idea and thought i could utilize SQL queries a little better. That is how i ended up here

I have, and am working that out currently.

Views, yet something else i need to familiarize myself with Quick glance leads me to believe this could have yielded a solution.

I have bookmarked some decent resources on views (as well as stored procedures and EAV). Thanks for all the responses.