#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    2
    Rep Power
    0
    I have four tables (let's call them small tables), and in each are four identically named fields (ID, LName, FName, EMail). There is also other information that varies between tables.

    I want to combine some of the data from each of the small tables into one big table to allow searches on all individuals. But I also want to create links in the search return where users can access the richer, more detailed information in the underlying small table.

    Strategy to date:
    <OL TYPE=A>
    <LI>if big table exists, drop it
    <LI>if big table not exist, create it
    <LI>query small table, using INSERT INTO SELECT to populate big table
    <LI>repeat query for each small table
    </OL>

    Problem: have not figured out how to add the source small table name into what is INSERTed into big table. Can get the unique id from small table, but getting table name and putting it into big table has me stumped .

    Would appreciate some advice...

    Tom
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    23
    Rep Power
    0
    Your database structure is not optimal.

    You should create one table with all the products and common details. Let's say:
    Prod_id, name, description, price. And for the specific details you make different tables (four different tables in you example). Let's say:
    detailTable1: "Prod_id, 1detail1, 1detail2, 1detail3"
    detailTable2: "Prod_id, 2detail1, 2detail3, 2detail3"
    etc.

    Mzzls
    Dave
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    2
    Rep Power
    0
    Thanks, Dave. I agree, my table structure is not optimal. We started with one table, and as we incorporated very different groups of people, we let their differences as groups influence us, resulting in creating tables that are not normalized. Should have normalized then -- and this is on my long-range set of tasks -- but no time right now.

    In the meantime, the workaround I would prefer would be a UNION of the tables, but that is not supported in my version of MySQL. Any other options or ideas will be much appreciated.

    Tom

Similar Threads

  1. select from table with parameter from another
    By PHPme in forum MySQL Help
    Replies: 9
    Last Post: January 14th, 2004, 08:26 PM
  2. sql multi table select
    By SeenGee in forum PostgreSQL Help
    Replies: 2
    Last Post: May 10th, 2003, 07:53 PM
  3. select one or/and another table
    By johnn in forum MySQL Help
    Replies: 1
    Last Post: December 4th, 2002, 02:02 AM
  4. create table with select
    By bhu73 in forum MySQL Help
    Replies: 1
    Last Post: May 17th, 2001, 02:41 AM
  5. Setting USER & PWD for *tables*??
    By Nightspirit in forum MySQL Help
    Replies: 3
    Last Post: September 26th, 2000, 06:04 AM

IMN logo majestic logo threadwatch logo seochat tools logo