Results 1 to 9 of 9

Thread: Trouble with select statement that combines 2 columns from 2 tables

  1. #1
    Join Date
    Dec 2010
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default Trouble with select statement that combines 2 columns from 2 tables

    Hello,

    very new to MYSQL. Having trouble with select statement that combines 2 columns from 2 tables (summing column2 for all similar states in column1).

    1st table = tracker
    column1 = state
    column2 = data

    example below:
    state data
    TX 1000
    ----------------------------

    2nd table = precaution
    column1 = state
    column2 = data

    example below:

    state data
    TX 100
    LA 10000
    ---------------------------

    Result should be:
    state datap
    TX 1100
    LA 10000

    I'm hung up on the following select statement (close but no cigar)

    Code:
    SELECT distinct tracker.state, sum(tracker.data) datap From tracker group by state Union select distinct precaution.state, sum(precaution.data) datap From precaution group by state
    Result of code above does not combine similar states and their related data

    Any help would be appreciated.

    Thanks,

    JB

  2. #2
    Join Date
    May 2007
    Location
    Boston,ma
    Posts
    2,127
    Thanks
    173
    Thanked 207 Times in 205 Posts

    Default

    Are you getting an error message or no result? I don't think you can run a select on 2 tables I think you have to use a join and link them via a column with matching values.

    http://en.wikipedia.org/wiki/Join_%28SQL%29
    Corrections to my coding/thoughts welcome.

  3. #3
    Join Date
    Dec 2010
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Thanks for the reply.

    I'm learning as I go...very green right now...but no errors with the select statement.

    Here's what is retrieved:

    state datap
    TX 1000
    LA 10000
    TX 100

    Thanks for the link. I will read up on how to join and see if that is the answer.

    JB

  4. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,702
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    I am very interested in the solution to this problem as well.
    To choose the lesser of two evils is still to choose evil. My personal site

  5. #5
    Join Date
    Apr 2009
    Location
    Sydney, Australia
    Posts
    110
    Thanks
    15
    Thanked 1 Time in 1 Post

    Default

    i'm also very new to MySQL and have a similar problem.

    I have two tables that I want the output of.

    TABLE 1 = users
    user_id (auto increment)
    username
    password

    TABLE 2 = objects
    user_id
    obj1
    obj2

    The query I use so far in PHP is
    Code:
    $result = mysql_query ("SELECT 'users.username', 'users.password', 'objects.obj1', 'objects.obj2'
    FROM 'users' INNER JOIN 'ruler' ON 'users.user_id' = 'objects.user_id');")
    but it fails with an error "You have an error in your SQL syntax"

  6. #6
    Join Date
    Dec 2010
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default Trouble with select statement that combines 2 columns from 2 tables

    Hi sniperman,

    VERY new to MYSQL.....you might try below to see if it works.

    Code:
    $strQuery = "SELECT users.username, users.password, objects.obj1, objects.obj2 FROM users INNER JOIN ruler ON users.user_id = objects.user_id";
    
    $result = mysql_query($strQuery) or die(mysql_error());



    JB

  7. #7
    Join Date
    Dec 2010
    Posts
    4
    Thanks
    0
    Thanked 1 Time in 1 Post

    Default

    Got it !!!!

    Looks like this gets the job done

    Code:
    select state, sum(datap) as total from (select tracker.state, tracker.data datap from tracker union all select precaution.state, precaution.data datap from precaution) as d group by state

    JB

  8. The Following User Says Thank You to jbrack321 For This Useful Post:

    james438 (01-07-2011)

  9. #8
    Join Date
    Jan 2011
    Location
    Liberia
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    There are still more many variants

  10. #9
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,702
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    Much appreciated, thanks jbrack321
    To choose the lesser of two evils is still to choose evil. My personal site

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
  •