selecting different columns from different tables 2

[simanek]

Hi all,

I'm fairly new to this so please be patient if this is a dumb question.

I want to select all the columns in two different tables that have some columns in common and some that are not in common. I would like to view all of the columns in both tables but I want to eliminate some of the records depending on what some of these 'extra' columns are equal to. Is it possible to do this?

the columns in table1:
column1, column2, column3, column4

the columns in table2:
column3, column4, column5, column6

What I'm trying to do:
SELECT * FROM table1 table2 WHERE column1=someval and column3=otherval and column6=anotherval;

But of course, that doesn't work. Any help would be greatly appreciated. Thanks. Mike
~~~~
simanek@uiuc.edu
"It's a Swingline!"
~~~~

 

[Jerrycurl]

What are the columns that the tables have in common?
You also need to specify which table the columns are from if they're named the same thing in both tables.
For example, if table1 and table2 had column2 in common, your select statement would look like this:

select * from table1 join table2
on table1.column2 = table2.column2
where table1.column1 = someval and table1.column3 = otherval and table1.column6 = anotherval

This assumes that all the columns in the where clause belong to table1. You can substitute table2 there where applicable.

 

[karluk]

A first attempt would be to join the two tables on the common columns and then add the additional restrictions to your "where" clause:

select a.column1, a.column2, a.column3, a.column4, b.column5, b.column6
from table1 a, table2 b
where a.column3=b.column3
and a.column4=b.column4
and a.column1='someval'
and a.column3='otherval'
and b.column6='anotherval'

 

[simanek]

thanks for the help Jerrycurl and karluk. Is it possible to do a multiple table join assuming you have a common column? Mike
~~~~
simanek@uiuc.edu
"It's a Swingline!"
~~~~

 

[Jerrycurl]

Sure:

select * from table1 join table2
on table1.column2 = table2.column2
join table3 on table3.column2 = table2.column2
join table4 on table3.column2 = table4.column2
join...(and so on)

 

[Jerrycurl]

I might also point out that

select * from table1 join table2
on table1.column2 = table2.column2
join table3 on table3.column2 = table2.column2
join table4 on table3.column2 = table4.column2

and

select * from table1,table2,table3,table4
where table1.column2 = table2.column2 and
table3.column2 = table2.column2 and table3.column2 = table4.column2

are equivalent. But the ANSI standard is to use the JOIN keyword as in the first example.