select within select or nested select??

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
yourguide
Forum Newbie
Posts: 2
Joined: Fri Apr 10, 2009 9:23 pm

select within select or nested select??

Post by yourguide »

Hi all... I have searched the forum, but I am either missing it or I don't understand enough to know how it applies to my situation so here goes.

I have one table, just one.
That table has product information in that includes spec data on each product.
the columns are like "specalum", "specwatt", "specfoo", "specbar", etc.

I want to return items that have spec data within ANY spec% column as long as that data is not empty like '' or 'N' since thats a flag meaning it doesn't apply to that product.

what I have so far is:
SELECT COLUMN_NAME FROM information_schema.`COLUMNS` C WHERE table_name = 'webitemmaster' AND COLUMN_NAME LIKE 'Spec%' ;

Which gives me a list of all the "Spec%" columns.... now I need to somehow search within each of those columns in the list for any value that is NOT '' or 'N'

I am new to SQL, so please bear with me... my table name is webitemmaster
Could some kind soul please point me in the right direction. I think a select within a select is right, but I dont know exactly how to express it.

Thank you so much for your time.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: select within select or nested select??

Post by califdon »

Your basic problem is that you have a table whose structure is unsuitable for a relational database. That sounds harsh, but let me explain. Relational databases (and SQL, the language that allows you to perform queries on relational tables) are based on a mathematical model developed by Dr. E. F. Codd around 1970. The mathematical rules that SQL uses are based on tables that conform to a set of rules of "normalization." If your tables don't conform (as yours does not), it may be possible to cobble together something that would work in a specific circumstance, but I, for one, wouldn't even try to work with such tables. The specific rule of normalization that your table violates is "repeating groups", which is the very first rule, called First Normal Form or 1NF. You need to read about normalization, and a good place to start would be Wikipedia: http://en.wikipedia.org/wiki/Database_n ... rmal_forms. It is for precisely the kind of operation that you are trying to perform that these rules were developed and must be followed.

Bottom line: normalize your data (you will end up with 2 tables) and your solution will be simple.
yourguide
Forum Newbie
Posts: 2
Joined: Fri Apr 10, 2009 9:23 pm

Re: select within select or nested select??

Post by yourguide »

Thank you for the reply.
I didn't build this database... I have just inherited it... it gets updated every night via some vbscripts and scheduled tasks.
Since I have full access to the code I think perhaps I will try and figure out how to split up the imported data (Comes in one big .CSV file) into a more normalized database.
I do appreciate your input. thank you.
Post Reply