pretty basic question here.



i am by no means a sql jock, so here is the question.

is there a way that i can execute a sql statement that will contain a field

that is from a different table? for example



i have tbl1 that contains:

first

last

officeid



i have tbl2 that contains:

officeid

officename

...



i want to open tbl1 and get all the rows in it, but also have an extra field

that contains the correct value. i was thinking of a view, but that will

only get the ones that have a match, i want it all regardless of wether or

not office id contains a value.



thanks

daniel

well i do this everytime.... i found a answer. i dont know if it is the

answer. but anyway



here is the sql query i used. it works. is this a correct approach?



Select tbl1.first, tbl1.last, tbl1.officeid, tbl2.officename AS oName FROM

tbl1, tbl2 WHERE first='george' AND tbl1.officeid = tbl2.officeid



daniel



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 2:46 PM

To: ASP Databases

Subject: [asp_databases] sql queries





pretty basic question here.



i am by no means a sql jock, so here is the question.

is there a way that i can execute a sql statement that will contain a field

that is from a different table? for example



i have tbl1 that contains:

first

last

officeid



i have tbl2 that contains:

officeid

officename

...



i want to open tbl1 and get all the rows in it, but also have an extra field

that contains the correct value. i was thinking of a view, but that will

only get the ones that have a match, i want it all regardless of wether or

not office id contains a value.



thanks

daniel

You can use a Left Outer Join, something like so:



Select * From tbl1 Left Outer Join tbl2 on officeID



This syntax IS incorrect, so I suggest you look up an example of a left

outer join yourself.



Hope this got you pointed in the right direction



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 2:46 PM

To: ASP Databases

Subject: [asp_databases] sql queries





pretty basic question here.



i am by no means a sql jock, so here is the question.

is there a way that i can execute a sql statement that will contain a field

that is from a different table? for example



i have tbl1 that contains:

first

last

officeid



i have tbl2 that contains:

officeid

officename

...



i want to open tbl1 and get all the rows in it, but also have an extra field

that contains the correct value. i was thinking of a view, but that will

only get the ones that have a match, i want it all regardless of wether or

not office id contains a value.



thanks

daniel

try this...



SELECT tb1.*

FROM tb1 LEFT JOIN tbl2 ON tb1.officeid =3D tbl2.officeid



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 2:46 PM

To: ASP Databases

Subject: [asp_databases] sql queries





pretty basic question here.



i am by no means a sql jock, so here is the question.

is there a way that i can execute a sql statement that will contain a 

field

that is from a different table? for example



i have tbl1 that contains:

first

last

officeid



i have tbl2 that contains:

officeid

officename

...



i want to open tbl1 and get all the rows in it, but also have an extra 

field

that contains the correct value. i was thinking of a view, but that 

will

only get the ones that have a match, i want it all regardless of wether 

or

not office id contains a value.



thanks

daniel

i knew there was something i was missing. thanks. that works better then the

thing i was doing.



daniel



-----Original Message-----

From: Edward Olshansky [mailto:Eolshansky@w...]

Sent: Wednesday, August 22, 2001 3:07 PM

To: ASP Databases

Subject: [asp_databases] RE: sql queries





try this...



SELECT tb1.*

FROM tb1 LEFT JOIN tbl2 ON tb1.officeid = tbl2.officeid



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 2:46 PM

To: ASP Databases

Subject: [asp_databases] sql queries





pretty basic question here.



i am by no means a sql jock, so here is the question.

is there a way that i can execute a sql statement that will contain a field

that is from a different table? for example



i have tbl1 that contains:

first

last

officeid



i have tbl2 that contains:

officeid

officename

...



i want to open tbl1 and get all the rows in it, but also have an extra field

that contains the correct value. i was thinking of a view, but that will

only get the ones that have a match, i want it all regardless of wether or

not office id contains a value.



thanks

daniel

Using that query, you will not be returned rows that have null values in

OfficeID from Table 2. I believe that this was one of your initial

requirements. The outer joins are a way to get around null values in linked

tables.



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 3:09 PM

To: ASP Databases

Subject: [asp_databases] RE: sql queries





well i do this everytime.... i found a answer. i dont know if it is the

answer. but anyway



here is the sql query i used. it works. is this a correct approach?



Select tbl1.first, tbl1.last, tbl1.officeid, tbl2.officename AS oName FROM

tbl1, tbl2 WHERE first='george' AND tbl1.officeid = tbl2.officeid



daniel



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 2:46 PM

To: ASP Databases

Subject: [asp_databases] sql queries





pretty basic question here.



i am by no means a sql jock, so here is the question.

is there a way that i can execute a sql statement that will contain a field

that is from a different table? for example



i have tbl1 that contains:

first

last

officeid



i have tbl2 that contains:

officeid

officename

...



i want to open tbl1 and get all the rows in it, but also have an extra field

that contains the correct value. i was thinking of a view, but that will

only get the ones that have a match, i want it all regardless of wether or

not office id contains a value.



thanks

daniel

yeah you are right, after i tested it a little more i figured that out.



thanks

daniel



-----Original Message-----

From: Tony Diana [mailto:tony@v...]

Sent: Wednesday, August 22, 2001 3:15 PM

To: ASP Databases

Subject: [asp_databases] RE: sql queries





Using that query, you will not be returned rows that have null values in

OfficeID from Table 2. I believe that this was one of your initial

requirements. The outer joins are a way to get around null values in linked

tables.



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 3:09 PM

To: ASP Databases

Subject: [asp_databases] RE: sql queries





well i do this everytime.... i found a answer. i dont know if it is the

answer. but anyway



here is the sql query i used. it works. is this a correct approach?



Select tbl1.first, tbl1.last, tbl1.officeid, tbl2.officename AS oName FROM

tbl1, tbl2 WHERE first='george' AND tbl1.officeid = tbl2.officeid



daniel



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 2:46 PM

To: ASP Databases

Subject: [asp_databases] sql queries





pretty basic question here.



i am by no means a sql jock, so here is the question.

is there a way that i can execute a sql statement that will contain a field

that is from a different table? for example



i have tbl1 that contains:

first

last

officeid



i have tbl2 that contains:

officeid

officename

...



i want to open tbl1 and get all the rows in it, but also have an extra field

that contains the correct value. i was thinking of a view, but that will

only get the ones that have a match, i want it all regardless of wether or

not office id contains a value.



thanks

daniel

The SQL may be executed successfully but does not comply with the SQL92

standard. In case you want to read it (all x pages of it)

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt



To comply with the standard JOINs are made using the JOIN statement, 

rather

than the WHERE clause. As many DBMS's support your syntax this is not a

major problem, but personally I find the SQL92 Standard's way of doing

things it much easier to read.



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Thursday, 23 August 2001 5:09 AM

To: ASP Databases

Subject: [asp_databases] RE: sql queries





well i do this everytime.... i found a answer. i dont know if it is the

answer. but anyway



here is the sql query i used. it works. is this a correct approach?



Select tbl1.first, tbl1.last, tbl1.officeid, tbl2.officename AS oName 

FROM

tbl1, tbl2 WHERE first=3D'george' AND tbl1.officeid =3D tbl2.officeid



daniel



-----Original Message-----

From: Daniel O'Dorisio (Work) [mailto:dodorisio@h...]

Sent: Wednesday, August 22, 2001 2:46 PM

To: ASP Databases

Subject: [asp_databases] sql queries





pretty basic question here.



i am by no means a sql jock, so here is the question.

is there a way that i can execute a sql statement that will contain a 

field

that is from a different table? for example



i have tbl1 that contains:

first

last

officeid



i have tbl2 that contains:

officeid

officename

...



i want to open tbl1 and get all the rows in it, but also have an extra 

field

that contains the correct value. i was thinking of a view, but that 

will

only get the ones that have a match, i want it all regardless of wether 

or

not office id contains a value.



thanks

daniel