query problem

[cwong1]

I'm having some problem creating a SELECT query (hopefully, with one or two SQL statement only) that would output the contents of two tables. Example, given the table1 and table2 as follows:

table1
------------------------
ID data1 data2
------------------------
1 100 aaa
2 200 bbb
3 300 ccc
4 400 ddd
5 500 eee
------------------------
table2
------------------------
ID info1 info2
------------------------
1 11 xyz
2 22 abc
3 33 123
7 77 456
8 88 opq
------------------------
the output of the query from table1 and table2 should be as follows:
---------------------------------------------------
ID data1 data2 info1 info2
---------------------------------------------------
1 100 aaa 11 xyz
2 200 bbb 22 abc
3 300 ccc 33 123
4 400 ddd
5 500 eee
7 77 456
8 88 opq
---------------------------------------------------

as you could see, there are some records present in both tables and some don't, but I need all the data to be returned by the SELECT query. How will I do this?

Thanks in advance.

 

[snowboardr]

try..

strSQL = "Select data1.table1, data2.table1, info1.table2, info2.table2 from table1, table2"

just a guess. haha www.vzio.com
ASP WEB DEVELOPMENT

 

[dixondwayne]

select a.id, a.data1, a.data2, b.info1, b.info2
from table1 a left outer join table2 b on
a.id = b.id

This gives you EVERY record in table1

even if there isn't a match record in table2

meaning... like the above id=4 is in table1 but not in table2, you'll still get the record from table1 even though it isn't in table2.

 

[cwong1]

I'm using version 3.23. Follow up question, what if I had two primary keys in the table1 and table2 aside from ID, say P_ID. Example:

table1
-----------------------------
P_ID | ID | data1 | data2
-----------------------------
P1 | 1 | 100 | aaa
P1 | 2 | 200 | bbb
P1 | 3 | 300 | ccc
P1 | 4 | 400 | ddd
P1 | 5 | 500 | eee
P2 | 1 | 100 | bbb
-----------------------------
table2
-----------------------------
P_ID | ID | info1 | info2
-----------------------------
P1 | 1 | 11 | xyz
P1 | 2 | 22 | abc
P1 | 3 | 33 | 123
P1 | 7 | 77 | 456
P1 | 8 | 88 | opq
P2 | 1 | 11 | xyy
P2 | 5 | 55 | xyy
------------------------------

but this time, I'm getting all the records which has a P_ID equal to P1.
First, I executed the query:

SELECT table1.ID, table1.data1, table1.data2, table2.info1, table2.info2 FROM table1 LEFT OUTER JOIN table2 ON table1.ID AND table2.ID WHERE table1.P_ID="P1" ;

-> i was able to get all the records that exist on both tables and also the records that only exist in table1. Afterwards, I executed another query in order to get the records that only exist in table2. so, I run the following SQL:

SELECT table2.ID, table1.data1, table1.data2, table2.info1, table2.info2 FROM table1 RIGHT OUTER JOIN table2 ON table1.ID=table2.ID where table1.ID IS NULL AND table2.P_ID="P1" ;

-> but there's no result. I also tried rearranging the statement to something like:

SELECT table2.ID, table1.data1, table1.data2, table2.info1, table2.info2 FROM table2 LEFT OUTER JOIN table1 ON table1.ID=table2.ID where table1.ID IS NULL AND table2.P_ID="P1" ;

-> I got wrong result... it seems that the problem is in the "where" clause.. any suggestions?

Thanks in advance.