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

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

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

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

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"

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

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

There are still more many variants

Much appreciated, thanks jbrack321 :)