Combining two tables to make a third

Need help combining two tables into a third with corresponding fields
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:

>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns from e
ach table instead of using [*],
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mngong@.gmail.com> wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com
..
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
>
> Your help is appreciated have been on this
>

Combining LEFT JOIN and MAX

i've got two tables:

table: PEOPLE
columns: ID, NAME

table: TESTS
columns: ID, TEST_NUM, RESULT

rules:
1. one person can have from zero to many tests.
2. i want to see one row for each person, regardless of whether they have any tests or not.
3. i only want to see the most recent (highest numbered) test.

SQL:
i'm able to return all the rows using a LEFT JOIN, but i still need a way to only return the most recent test.

SELECT A.ID, A.NAME,
B.TEST_NUM, B.RESULT
FROM PEOPLE A
LEFT JOIN TESTS B
ON A.ID = B.ID

here's what's in the tables:

PEOPLE:
ID NAME
-- --
1 JOE
2 BOB
3 JIM
4 JOHN
5 RALPH
6 STEVE
7 MARY
8 BETH
9 SUE
10 KIM

TESTS:
ID TEST_NUM RESULT
-- --- --
1 1 PASS
1 2 PASS
2 1 FAIL
3 1 FAIL
4 1 PASS
4 2 FAIL
4 3 FAIL
6 1 FAIL
8 1 PASS
9 1 PASS

here's what the query currently returns:

ID NAME TEST_NUM RESULT
-- -- --- --
1 JOE 1 PASS
1 JOE 2 PASS
2 BOB 1 FAIL
3 JIM 1 FAIL
4 JOHN 1 PASS
4 JOHN 2 FAIL
4 JOHN 3 FAIL
5 RALPH
6 STEVE 1 FAIL
7 MARY
8 BETH 1 PASS
9 SUE 1 PASS
10 KIM

here's what i want it to return:

ID NAME TEST_NUM RESULT
-- -- --- --
1 JOE 2 PASS
2 BOB 1 FAIL
3 JIM 1 FAIL
4 JOHN 3 FAIL
5 RALPH
6 STEVE 1 FAIL
7 MARY
8 BETH 1 PASS
9 SUE 1 PASS
10 KIMtry this --select a.id
, a.name
, b.test_num
, b.result
from people a
left
join tests b
on a.id = b.id
and b.test_num
= (select max(test_num)
from tests
where id = a.id)|||i get a syntax error with that.

it doesn't tell me why, maybe it doesn't like the 'and' in the 'join'?|||what is the syntax error?

oh, and perhaps you could also let us know which database you're using|||the real database will be DB2, but i'm testing on a mock access db for the time being. i get the same results in each.

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression ''.

i only got this error after i added this:

and b.test_num
= (select max(test_num)
from tests
where id = a.id)|||try this --

on (
a.id = b.id
and b.test_num
= (select max(test_num)
from tests
where id = a.id)
)
you realize that sql is never portable between different databases, right?|||it's been my experience that basic SQL is portable. it's just the more complicated functions that aren't.

DB2 Error:

ODBC Error Code = 37000 (Syntax error or access violation)
[StarQuest][StarSQL ODBC Driver]SQL syntax error, .

Access Error:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression ' a.id = b.id and b.test_num = (select max(test_num) from tests where id = a.id) '.

it doesn't seem to like those outer parenthesis.|||select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id)
union all
select a.id
, a.name
, null
, null
from people a
left
join tests b
on a.id = b.id
and b.id is null|||DB2 doesn't seem to like the nulls.

access had a problem with the join, but who cares about access?

ODBC Error Code = 42703 ()

[StarQuest][StarSQL ODBC Driver][DB2]NULL IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE|||okay, for the second subselect, try:select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from ...|||oops, change the and in the last line to where|||hey, i think that worked.

there's no error at least.

some of my test data got purged overnight, so i'm going to repopulate the tables and give it a shot.

i'll post back when i do.

akosz

ps - don't bother with that devshed forum. i cross posted when this first started to see if anybody anywhere had any answers. apparently you do :)|||well that did the trick!

thanks a million for getting me through that one. :)

the bad news is, it looks like we're allowing duplicate id's in the PEOPLE table. a timestamp will differentiate between them and allow for a primary key. i really don't know how they want me to accomodate for this as it relates to the application i'm developing.

it's going to be a long afternoon...

akosz

btw - there are actually quite a few more columns and tables than what you see here, i just scaled it down to get at the syntax.|||now i need to add a WHERE (or an AND) clause to this so that i only get certain matches in the PEOPLE table.

here's the query:

select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id)
union all
select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from people a
left
join tests b
on a.id = b.id
where b.id is null

here's what i need to add:

WHERE a.id IN (123,999,343)

i tried adding this as an AND clause after the first WHERE but it only brough back one row instead of 3.|||nevermind, i just needed some coffee :)

it's:

select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id
and a.id IN (123,999,343))
union all
select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from people a
left
join tests b
on a.id = b.id
where b.id is null