Trouble with select statement that combines 2 columns from 2 tables

[jbrack321]

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) :(



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

[bluewalrus]

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

[jbrack321]

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

[james438]

I am very interested in the solution to this problem as well.

[sniperman]

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

$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"

[jbrack321]

Hi sniperman,

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




$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

[jbrack321]

Got it !!!!

Looks like this gets the job done




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

[equill]

There are still more many variants

[james438]

Much appreciated, thanks jbrack321 :)