#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0

    Help needed finding data from 4 tables


    Hi There

    I have searched and tried to locate this answer before posting.

    I have a MYSQL problem

    I have 4 tables with lots of different columns.

    All 4 tables have the same ID column, a unique reference number:
    Each table has one Row that point to the same ID

    My problem is:
    I need query all 4 tables and get the row back using the ID column

    How do i do this.

    Im using
    PHP Code:

    SELECT 
    FROM table1table2table3table4 WHERE table1.ID '$record' 
    This pulls other rows i don't want.

    I just want the 4 rows with the same ID


    How can i do this?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Hm, where to begin...

    OK. In your own time read up on JOINs and UNIONs. Then find out what DDLs are in relation to MySQL. Then, if you're still stuck, come back to us with a more structured question, and an indication of what the output should actually look like.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Originally Posted by cafelatte
    Hm, where to begin...

    OK. In your own time read up on JOINs and UNIONs. Then find out what DDLs are in relation to MySQL. Then, if you're still stuck, come back to us with a more structured question, and an indication of what the output should actually look like.

    Honest ive been reading and trying everything, im stuck.
    I ve hit a brick wall.

    All the JOIN answers relate to specific columns in each table,
    but i have so many columns i can possible add all columns to the query. There has to be a simpler way.

    I need a one liner to look up all 4 tables, and bring me back all the column data for that record: ID=165456

    So something like:

    Get ALL COLUMN DATA with ROW ID = 165456 from all the 4 TABLES

    *****

    There is only one row in each table with the same ID Number
    Still trying to solve this one.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    All of your tables share one, and only one, column in common. That's the criteria upon which they should be JOINed.

    An inability to name all the columns in your query points to either poorly designed tables or chronic laziness.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    normally i don't like to just provide a solution to what is potentially a homework assignment, but this one is rather obviously not a homework assignment, and since you say you've already read up on joins and the penny still hasn't dropped, here ya go...
    Code:
    SELECT table1.*
         , table2.* 
         , table3.* 
         , table4.* 
      FROM table1
    INNER
      JOIN table2
        ON table2.id = table1.id
    INNER
      JOIN table3
        ON table3.id = table1.id
    INNER
      JOIN table4
        ON table4.id = table1.id
     WHERE table1.id = '$record'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    I tried this, and Computer Says No

    Is there an error in your syntax?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Marko2012
    I tried this, and Computer Says No


    perhaps you would be so kind as to post the actual query that you ran to which your computer made this fascinating reply

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo