SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Picking from multiple tables

    Hi,
    I am trying to select parts data from 15 different tables

    Tables are like this;
    Table A
    Id….Group….Desc……..Cost
    1……1………Bolt………0.5
    1……1………Nut…….…0.6
    3……2………Screw….…0.4
    4……3………Washer.….0.1

    Table B
    Id….Group….Desc……..Cost
    1……1………Log……..1.5
    2……2………Stick…….2.6
    3……2………Leaf……..2.4
    4……4………Branch…..1.1

    Table C
    Id….Group….Desc……..Cost
    1……3………Plug……….1.9
    2……4………Socket……2.8
    3……4………Pin………..3.4
    4……1………Needle……2.1

    etc, etc

    What I want to do is to pick out rows in each table by group number.
    The group number may or may not exist in each table.
    I don't want to combine the tables into one large table so it would be easy as each table has numerous columns that are unique to that particular table.
    I know this is an old subject, but I can't find an answer here that fits my situation.
    Any help or direction is appreciated.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,491
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Do a UNION of 15 SELECT statements...
    Or take a look at supertype/subtype.

  3. #3
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the advice Guido2004
    I tried UNION but it could not pick the data when a table had nothing to pick, except for the last table in list of SELECT statements.
    In my example above tablec could have no data to pick and everything worksfine, it will pick from tablea and tableb, but if tablea or tableb had no data then it picks nothing even if the other tables have something to pick.

    My codelooks like this

    $result = mysql_query("SELECT * FROM tablea WHERE group = $groupcodenum
    UNION
    SELECT * FROM tableb WHERE group = $groupcodenum
    UNION
    SELECT * FROM tablec WHERE group = $groupcodenum")
    or die('Error - Unable to pick from table data');


    Am I missing something ?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,491
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    If the tables have different formats, don't use the * in your SELECT statements, specify the columns you want to select. From your example in your first post, I understood that you only want four columns that are the same in each table.

    Did you do some research on the supertype/subtype construction?

  5. #5
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The four columns are the same in each table, they have the same names, it is subsequent columns in each table that vary.
    I will try without the * and specify column names using the construction tablename.colname
    Have been looking at supertype/subtype, but as this is first time I have looked at this subject I don't fully get it at the moment - have you got a good link/ref that I can look at ?
    Thanks for the help so far.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,491
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by maxdba View Post
    Have been looking at supertype/subtype, but as this is first time I have looked at this subject I don't fully get it at the moment - have you got a good link/ref that I can look at ?
    Ehm, nope
    Let's ask @r937 ;

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •