SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select query based on column names stored in another table

    Names of the tables are stored in my another table due to the fact i have quite a big MySQL table having more than 100 columns. Now i want to get the column names like:

    select (select a,b,c) from tableB from tableA How can i do it by using one select query.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you please post the output of a SHOW CREATE TABLE for both the tables?

    What information is required from the tables?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply.
    My one table has more than 100 columns which are difficult to paste here but i can explain in bit more details with the below example:

    Table A has physical fields:

    Columns

    Column1, Column2, Column3, Column4, Column5 and so on

    Table B has the names of these columns stored as records.

    Query should be Select Column1, Column2, Column3 from Table A if i want to select 3 columns from table A but i don't know the column names from Table A as my column names map table is Table B so what i want is to get the column names from Table B and then use in the select of Table A as:

    Select (select * from Table B where id>5) from Table A

    Query works find with one row but in case of multiple rows it doesn't as due to the fact that it needs commas in between columns names and i can make it workable as:

    Select (select Column1 from Table B),(select Column2 from Table B),(select Column3 from Table B) from Table A

    But i want to make it workable like:

    Select (select * from Table B where id>5) from Table A

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you can't do it in one query, you will have to use two -- one to retrieve the colulmn names, and then your actual query with the column names incorporated

    but before you get too far down this road, let me caution you that this design is fraught with danger, and there are probably other ways of accomplishing whatever it is you're trying to accomplish, which you didn't explain
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    If table B is just being used to store the field names for table A then you should rename all the fields in A to what they're meant to be (the names in table B) so that instead of table A having fields like:

    Column1, Column2, Column3, Column4, Column5....

    it should be like:

    id, name, email... (or whatever the field names are meant to be)
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •