Simple join issue...

[Milamber]

Simple join issue:


right now i have two tables:
table1 { CA, NJ, WY }
table2 { CA, NY, WY }

and the result i want is an array of { CA, NY, NJ, WY } with the use of a single query.

How the heck do you do that? I know it involves "JOIN"s of some sort, but right now I simply can't seem to wrap my head around that concept.

Also, any one that can point me in the direction of some good literature on the subject would be greatly appreciated.

[pippo]

Hi,
could you be more specific about the tables layout ( fields ) ?
What kind of relation do you want to make between table1 and table2 ?



pippo

[Milamber]

sure:

Table1
+----------------+
| id    |  state |
+----------------+
| 1     |  CA    |
| 2     |  NY    |
| 3     |  WY    |
+----------------+

Table2
+----------------+
| id    |  state |
+----------------+
| 1     |  CA    |
| 2     |  NJ    |
| 3     |  WY    |
+----------------+

What i want back is:

Table
+----------------+
| id    |  state |
+----------------+
| 1     |  CA    |
| 2     |  NJ    |
| 2     |  NY    |
| 3     |  WY    |
+----------------+

That all clear?

[pippo]

Damn,
your question was a bit complicated...for me at least
It broken my head but I found a solution:

DROP TABLE IF EXISTS pippo;
CREATE TABLE pippo select * from table1;
INSERT INTO pippo select * from table2;
SELECT * FROM pippo GROUP BY state;

I tested it and it worked using mysql_front.
That'sn't as single query...

Maybe it could work using a temporary table too,
but I'm not sure about that.

I think that moving this thread in the database/mysql forum you will have more nice solutions from sql guru there.

I'm not a sql guru...

Mine works.



pippo


p.s.
I was assumming that you were using mysql...

[weirdbeardmt]

off the top of my head...

SELECT table1.* from table1 LEFT JOIN table2 ON table1.state=table2.state WHERE table1.state=table2.state 

something like that...

[seanf]

Moved to a more appropriate forum. I could have put this in the MySQL forum, but I don't want to guess what db you are using

Sean

[r937]

milamber, what you want is a UNION, not a JOIN...

select id, state from table1
union
select id, state from table2
order by id, state

notice UNION instead of UNION ALL

UNION removes duplicate rows

if you are using mysql, UNION is not supported until version 4