f



Not grasping outer joins for ms-sqlserver :: 3 tables to join

I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working 
on a three table query.

Assumptions:
-- I have events in the Event table.
-- Each event CAN have one Transaction, but it's not guaranteed
-- Each transaction, ir present, will have one or more Amount records

This would be the pseudo-query without any special joins:

-----------------------------------------
SELECT
	a.Name,
	SUM( c.amount ) as Total
FROM
	Event a,
	Transaction b,
	Amounts c
WHERE
	a.EventID = b.EventID
AND	b.TransID = c.TransID
-----------------------------------------

This is fine if there is a Transaction for the Event.  But, if there's 
no transaction for an event, no record is pulled of course.

What I need is for a record to come back for each event regardless of 
the presence of a Transaction.  If there's no transaction, then the 
"Total" column should be 0.

How would I get an OUTER JOIN to work on this so that each Event gets a 
record?

TIA
-BEP
0
beparker (3)
3/15/2006 5:58:30 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

4 Replies
437 Views

Similar Articles

[PageSpeed] 29

SELECT
        a.Name,
        SUM( isnull(c.amount,0) ) as Total
FROM
FROM
        Event a left outer join
        Transaction b on         a.EventID = b.EventID
left outer join
        Amounts c on  b.TransID = c.TransID

0
drmiller100 (140)
3/15/2006 7:43:10 PM
Appreciate it - worked great.
-BEP

Doug wrote:
> SELECT
>         a.Name,
>         SUM( isnull(c.amount,0) ) as Total
> FROM
> FROM
>         Event a left outer join
>         Transaction b on         a.EventID = b.EventID
> left outer join
>         Amounts c on  b.TransID = c.TransID
> 
0
beparker (3)
3/15/2006 8:09:10 PM
On Wed, 15 Mar 2006 11:58:30 -0600, Brian Parker wrote:

>I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN working 
>on a three table query.
>
>Assumptions:
>-- I have events in the Event table.
>-- Each event CAN have one Transaction, but it's not guaranteed
>-- Each transaction, ir present, will have one or more Amount records
>
>This would be the pseudo-query without any special joins:
>
>-----------------------------------------
>SELECT
>	a.Name,
>	SUM( c.amount ) as Total
>FROM
>	Event a,
>	Transaction b,
>	Amounts c
>WHERE
>	a.EventID = b.EventID
>AND	b.TransID = c.TransID
>-----------------------------------------
>
>This is fine if there is a Transaction for the Event.  But, if there's 
>no transaction for an event, no record is pulled of course.
>
>What I need is for a record to come back for each event regardless of 
>the presence of a Transaction.  If there's no transaction, then the 
>"Total" column should be 0.
>
>How would I get an OUTER JOIN to work on this so that each Event gets a 
>record?

Hi Brian,

The query posted by Doug will also return events that do have a
transaction, but without any amounts. Your description says that those
don't exisst - but just in case that you want to make sure that these
are excluded, here are two versions that will really only include
transactions with amounts:

SELECT     a.Name, SUM(c.Amount) AS Total
FROM       Transaction AS b
INNER JOIN Amounts AS c
      ON   c.TransID = b.TransID
RIGHT JOIN Event AS a
      ON   a.EventID = b.EventID

Or, if you prefer to include the tables in your query in the "logical"
order (or if you just prefer LEFT outer joins over RIGHT outer joins):

SELECT     a.Name, SUM(c.Amount) AS Total
FROM       Event AS a
LEFT JOIN (Transaction AS b
INNER JOIN Amounts AS c
      ON   c.TransID = b.TransID)
      ON   a.EventID = b.EventID

Note the order of the join and on clauses. I added parentheses for
clarity, though SQL Server will understand this just as well without the
parentheses.

-- 
Hugo Kornelis, SQL Server MVP
0
hugo5804 (463)
3/15/2006 8:24:31 PM
Hugo Kornelis wrote:
> The query posted by Doug will also return events that do have a
> transaction, but without any amounts. Your description says that those
> don't exisst - but just in case that you want to make sure that these
> are excluded, here are two versions that will really only include
> transactions with amounts:


Thanks, Hugo.  Between all of the examples I'll be able to grasp what's 
going on with the join.

Appreciate it!

-BEP
0
beparker (3)
3/15/2006 8:57:18 PM
Reply:

Similar Artilces:

Re: SQL Left Join Warning
While it makes sense that this would work, when I tried it I got an error stating that variable name tmp2.n_time and tmp3.n_time were not valid variable names. So, I just went with using the explicit naming instead of the star. Thanks. Warren Schlechte From: Catley, Dennis [mailto:dcatley@exchange.vt.edu] Sent: Wednesday, November 05, 2008 12:29 PM To: Warren Schlechte Subject: RE: SQL Left Join Warning - Join 3 tables That warning results from the use of the *, not a big deal. SAS is just letting you know that you're building redundancy into your created table, by constructing it with 3 columns of the same name. But you can code a drop after your table name such as create table ld50_parms (drop = tmp2.n_time tmp3.n_time) as The alternative is to be explicit Re which columns you want/need from the 'right side tables'. The two drops columns are still able to be used in the syntax as you now have it, they're just not kept in the result set. --- Dennis Dennis Catley Associate Director - Institutional Research Virginia Tech 128 Smyth Hall - Mailcode 0433 Blacksburg, VA. 24061 dcatley@vt.edu (540) 231-2623 -----Original Message----- From: Warren Schlechte [mailto:Warren.Schlechte@TPWD.STATE.TX.US] Sent: Wednesday, November 05, 2008 10:10 AM Subject: SQL Left Join Warning - Join 3 tables Here is the log from some sql steps I have coded. I am selecting specific elements from specific rows from a dataset named ld50_est, and then join the data I want in...

query sql server/oracle table from Db2 or join db2 table with sqlserver table
My application connects to a DB2 V8.2 EE database running on AIX. I need to now lookup a table in SQL SERVER via the DB2 connection. I also need to run an sql that joins a DB2 table with an Oracle table. How can I accomplish this ? Do i need a seperate product ? I also have license for Db2 v8 connect that I use for connecting to db2 on the mainframe. TIA Roger ...

query sql server/oracle table from Db2 or join db2 table with sqlserver table #2
My application connects to a DB2 V8.2 EE database running on AIX. I need to now lookup a table in SQL SERVER via the DB2 connection. I also need to run an sql that joins a DB2 table with an Oracle table. How can I accomplish this ? Do i need a seperate product ? I also have license for Db2 v8 connect that I use for connecting to db2 on the mainframe. TIA Roger Roger wrote: > My application connects to a DB2 V8.2 EE database running on AIX. I > need to now lookup a table in SQL SERVER via the DB2 connection. I > also need to run an sql that joins a DB2 table with an Oracle table. > How can I accomplish this ? Do i need a seperate product ? > I also have license for Db2 v8 connect that I use for connecting to > db2 on the mainframe. You need to look at Websphere Information Integrator along with the relational wrappers for MS SQL server and Oracle. Ian, its a canned CRM application that currently connects to DB2. I cannot run the app on Websphere. A few years could use relational connect and create federated nickname on db2. Can I still do that ? TIA On Apr 27, 6:01 pm, Ian <ianb...@mobileaudio.com> wrote: > Roger wrote: > > My application connects to a DB2 V8.2 EE database running on AIX. I > > need to now lookup a table in SQL SERVER via the DB2 connection. I > > also need to run an sql that joins a DB2 table with an Oracle table. > > How can I accomplish this ? Do i n...

joining table from oracle db to a table in ms sql db
I am using following query to get data from a table in oracle db: SELECT * FROM OPENQUERY(ARADMIN, 'select Column1 from Oracle_Table WHERE status not in (''a'','b'',''c'') ' Order by OPENEDDATE DESC I want to join a table from MS SQL 2000 DB in the above query, can any one let me know how this can be done? thankx, rushi "hrushi" <hbhome@gmail.com> wrote in message news:1171386377.803275.145700@v45g2000cwv.googlegroups.com... >I am using following query to get data from a table in oracle db: > > SELECT * > FROM OPENQUERY(ARADMIN, 'select Column1 from Oracle_Table WHERE > status not in (''a'','b'',''c'') ' > Order by OPENEDDATE DESC > > I want to join a table from MS SQL 2000 DB in the above query, > > can any one let me know how this can be done? > > thankx, > rushi > Look up Heterogeneous Connectivity. -- Terry Dykstra You can consider using Oracle Business Intelligence Enterprise Edition (OBIEE) for this purpose. OBIEE was designed for querying data from different data sources in a hetergenious environment. You first defined the ODBC connectivity and load the physical data definitions into the OBIEE repository file. You can define the join criteria amoung loaded "table" in the repository. Defining the logical to phytical mapping can be simply done using drag and drop interface. The BI wil...

SQL Left Join Warning
Here is the log from some sql steps I have coded. I am selecting specific elements from specific rows from a dataset named ld50_est, and then join the data I want into a new table based on the variable n_time. The code works as I want, but I get a warning. I have tried several things, but cannot seem to get clean running code without the warning. I would appreciate your help. 31 proc sql; 32 create table tmp1 (drop=_type_)as 33 select n_time, intercept, trtmnt, ld50, _type_ 34 from ld50_est 35 where _type_="PARMS"; NOTE: Table WORK.TMP1 created, with 4 rows and 4 columns. 36 37 create table tmp2 as 38 select n_time, var_b0 39 from ld50_est 40 where var_b0 ^=.; NOTE: Table WORK.TMP2 created, with 4 rows and 2 columns. 41 42 create table tmp3 as 43 select n_time, var_b1, cov_b0b1 44 from ld50_est 45 where var_b1 ^=.; NOTE: Table WORK.TMP3 created, with 4 rows and 3 columns. 46 47 create table ld50_parms as 48 select * 49 from (tmp1 50 left join tmp2 51 on tmp1.n_time=tmp2.n_time) 52 left join tmp3 53 on tmp2.n_time=tmp3.n_time 54 ; WARNING: Variable N_Time already exists on file WORK.LD50_PARMS. WARNING: Variable N_Time already exists on file WORK.LD50_PARMS. Warren Schlechte HOH Fisheries Science Center 5103 Junction Hwy Mt. Home, TX 78058 Phone 830.866.3356 x214 Fax 830.866.3549 You have...

Re: SQL Left Join Warning
The variable in all the datasets is the variable I am matching on, so it has to be in all the datasets. Given that, what can I do to stop from getting the WARNING note. Warren Schlechte -----Original Message----- From: bigD [mailto:diaphanosoma@GMAIL.COM] Sent: Wednesday, November 05, 2008 10:39 AM Subject: Re: SQL Left Join Warning - Join 3 tables You have the same variable name in two different data sets. Sas will drop the values from the data set on the "right" in this case, and only keep the variables on the left. Usually not a problem, unless variables from the two datasets contain different information. On Nov 5, 10:10 am, Warren.Schlec...@TPWD.STATE.TX.US (Warren Schlechte) wrote: > Here is the log from some sql steps I have coded. > > I am selecting specific elements from specific rows from a dataset named > ld50_est, and then join the data I want into a new table based on the > variable n_time. > > The code works as I want, but I get a warning. > > I have tried several things, but cannot seem to get clean running code > without the warning. > > I would appreciate your help. > > 31 proc sql; > 32 create table tmp1 (drop=_type_)as > 33 select n_time, intercept, trtmnt, ld50, _type_ > 34 from ld50_est > 35 where _type_="PARMS"; > NOTE: Table WORK.TMP1 created, with 4 rows and 4 columns. > > 36 > 37 create table tmp2 as > 38 ...

Converting database to MS-SQLServer from PostGRESQL
Forgive me if this question is a bit too generic, if it is, feel free to just not respond. I have a database which has been running in PostgreSQL for a number of years at this stage which I want to port into MS SQL server. It seems that the SQL that Postgre outputs when I do a backup is not syntactically correct within MS-SQL server. My question is, does anyone have any documentation on how to convert a database from the Postgre platform to SQL server? Is it possible using an ODBC connection to import a database structure including table definitions, views etc into SQL Server? Failing this, does anyone have any suggestions on where I might start - I did attempt to go through the SQL code and modify it to suit SQL server, but it's about 3,500 lines of code excluding the insert statements (which themselves are also wrong) and almost every line needs something changed when comparing SQL syntax from Postgre to MSSQL server Thanks in advance for any comments/suggestions. Engada. -- Posted via a free Usenet account from http://www.teranews.com Engada wrote: > I have a database which has been running in PostgreSQL for a number of > years at this stage which I want to port into MS SQL server. > > It seems that the SQL that Postgre outputs when I do a backup is not > syntactically correct within MS-SQL server. pg_dump has a number of flags that may help, e.g. --inserts What specific types of syntax errors do you encounter? Googling (Postgr...

Re: SQL Left Join Warning
On Wed, 5 Nov 2008 12:46:05 -0600, Warren Schlechte <Warren.Schlechte@TPWD.STATE.TX.US> wrote: >While it makes sense that this would work, It may seem to make sense at first glance, but actually it doesn't. The prefix-dot construct is part of SQL, whereas the DROP data set option is not. The implementation of DROP works in both SQL and non-SQL contexts. DROP does not recognize the prefix-dot construct. >when I tried it I got an >error stating that variable name tmp2.n_time and tmp3.n_time were not >valid variable names. > >So, I just went with using the explicit naming instead of the star. > >Thanks. >Warren Schlechte > >From: Catley, Dennis [mailto:dcatley@exchange.vt.edu] >Sent: Wednesday, November 05, 2008 12:29 PM >To: Warren Schlechte >Subject: RE: SQL Left Join Warning - Join 3 tables > >That warning results from the use of the *, not a big deal. SAS is just >letting you know that you're building redundancy into your created >table, by constructing it with 3 columns of the same name. > >But you can code a drop after your table name such as > >create table ld50_parms (drop = tmp2.n_time tmp3.n_time) as > >The alternative is to be explicit Re which columns you want/need from >the 'right side tables'. > >The two drops columns are still able to be used in the syntax as you now >have it, they're just not kept in the result set. > >--- Dennis > >Dennis ...

Re: SQL Left Join Warning
I truly appreciate all the assistance in understanding the WARNING message and in how to avoid it. Warren Schlechte -----Original Message----- From: Sigurd Hermansen [mailto:HERMANS1@WESTAT.com] Sent: Wednesday, November 05, 2008 2:17 PM To: Warren Schlechte; SAS-L@LISTSERV.UGA.EDU Subject: RE: SQL Left Join Warning - Join 3 tables Warren: While I would prefer to see explicit naming of variables in a SELECT list, at times I take the easy route and ignore the warning. SAS SQL actually maintains multiple attribute names in a join until it writes the yield of the join to a dataset. At that point it selects the first attribute referenced by the name and drops any referenced subsequently. I stress "referenced" because an implicit (*) reference may precede an explicit reference. The 'L has a long tradition of using simple programs to demonstrate how a compiler interprets programs. The extended examples below demonstrate how SAS SQL handles duplicates of attribute names. I've included a counterexample to disprove your assertion that a SAS SQL join on x has to include x in the yield of the query. S data R1; x=1; y=2; run; data R2; x=1; y=3; run; proc sql; /* Explicitly displaying different attributes with same name. */ select R1.x,R1.y,R2.x,R2.y from R1 inner join R2 on R1.x=R2.x ; /* Implicitly displaying different attributes with same name. */ select * from R1 inner join R2 on R1.x=R2.x ; /* Realizing attribute conflicts in a SAS dataset. */ create ta...

Re: SQL Left Join Warning
On Wed, 5 Nov 2008 21:30:05 -0800, Kevin Y <kevin77711@YAHOO.COM> wrote: >You have to use coalesce to remove the warnings: What you *have to* do is avoid telling SAS to create a table or view having multiple columns with the same name. COALESCE is one way of doing that. � Proc sql; create table ld50_parms as select � coalesce(tmp1.n_time, tmp2.n_time,tmp3.n_time) as n_time, tmp1.intercept, tmp1.trtmnt, tmp1.ld50, tmp1._type_, tmp2.ld50_es, tmp3.var_b1, tmp3.cov_b0b1 tmp3.ld50_est � from (tmp1 left join tmp2 on tmp1.n_time=tmp2.n_time) left join tmp3 on tmp2.n_time=tmp3.n_time �; quit; � ________________________________ From: Warren Schlechte <Warren.Schlechte@TPWD.STATE.TX.US> To: SAS-L@LISTSERV.UGA.EDU Sent: Wednesday, November 5, 2008 11:10:00 PM Subject: SQL Left Join Warning - Join 3 tables Here is the log from some sql steps I have coded. I am selecting specific elements from specific rows from a dataset named ld50_est, and then join the data I want into a new table based on the variable n_time. The code works as I want, but I get a warning. I have tried several things, but cannot seem to get clean running code without the warning. I would appreciate your help. 31� � � � proc sql; 32� � � � create table tmp1 (drop=_type_)as 33� � � � select n_time, intercept, trtmnt, ld50, _type_ 34� � � � from ld50_est 35� � � � where _type_="PARMS"; NOTE: Table WORK.TMP1 created, with 4 rows and 4 columns. 36 37� � � � create table tmp2 as 38� � �...

Re: SQL Left Join Warning
Try the following: create table ld50_parms as select * from tmp1 natural left join tmp2 natural left join tmp3 ; Ed Edward Heaton, Senior Systems Analyst, Westat (An Employee-Owned Research Corporation), 1650 Research Boulevard, TB-286, Rockville, MD 20850-3195 Voice: (301) 610-4818 Fax: (301) 294-2085 mailto:EdHeaton@Westat.com http://www.Westat.com -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Warren Schlechte Sent: Wednesday, November 05, 2008 12:37 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: SQL Left Join Warning - Join 3 tables The variable in all the datasets is the variable I am matching on, so it has to be in all the datasets. Given that, what can I do to stop from getting the WARNING note. Warren Schlechte -----Original Message----- From: bigD [mailto:diaphanosoma@GMAIL.COM] Sent: Wednesday, November 05, 2008 10:39 AM Subject: Re: SQL Left Join Warning - Join 3 tables You have the same variable name in two different data sets. Sas will drop the values from the data set on the "right" in this case, and only keep the variables on the left. Usually not a problem, unless variables from the two datasets contain different information. On Nov 5, 10:10 am, Warren.Schlec...@TPWD.STATE.TX.US (Warren Schlechte) wrote: > Here is the log from some sql steps I have coded. > > I am selecting specific elements from specific rows from a dataset named > ld50_est, and then j...

Re: SQL Left Join Warning
Thanks Ed. I knew there had to be a way to avoid the WARNING, but the things I tried wouldn't do it. Warren Schlechte -----Original Message----- From: Ed Heaton [mailto:EdHeaton@westat.com] Sent: Wednesday, November 05, 2008 12:01 PM To: Warren Schlechte; SAS-L@LISTSERV.UGA.EDU Subject: RE: SQL Left Join Warning - Join 3 tables Try the following: create table ld50_parms as select * from tmp1 natural left join tmp2 natural left join tmp3 ; Ed Edward Heaton, Senior Systems Analyst, Westat (An Employee-Owned Research Corporation), 1650 Research Boulevard, TB-286, Rockville, MD 20850-3195 Voice: (301) 610-4818 Fax: (301) 294-2085 mailto:EdHeaton@Westat.com http://www.Westat.com -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Warren Schlechte Sent: Wednesday, November 05, 2008 12:37 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: SQL Left Join Warning - Join 3 tables The variable in all the datasets is the variable I am matching on, so it has to be in all the datasets. Given that, what can I do to stop from getting the WARNING note. Warren Schlechte -----Original Message----- From: bigD [mailto:diaphanosoma@GMAIL.COM] Sent: Wednesday, November 05, 2008 10:39 AM Subject: Re: SQL Left Join Warning - Join 3 tables You have the same variable name in two different data sets. Sas will drop the values from the data set on the "right" in this case, and only keep the variables on the left. U...

Re: SQL Left Join Warning
Warren: While I would prefer to see explicit naming of variables in a SELECT list, at times I take the easy route and ignore the warning. SAS SQL actually maintains multiple attribute names in a join until it writes the yield of the join to a dataset. At that point it selects the first attribute referenced by the name and drops any referenced subsequently. I stress "referenced" because an implicit (*) reference may precede an explicit reference. The 'L has a long tradition of using simple programs to demonstrate how a compiler interprets programs. The extended examples below demonstrate how SAS SQL handles duplicates of attribute names. I've included a counterexample to disprove your assertion that a SAS SQL join on x has to include x in the yield of the query. S data R1; x=1; y=2; run; data R2; x=1; y=3; run; proc sql; /* Explicitly displaying different attributes with same name. */ select R1.x,R1.y,R2.x,R2.y from R1 inner join R2 on R1.x=R2.x ; /* Implicitly displaying different attributes with same name. */ select * from R1 inner join R2 on R1.x=R2.x ; /* Realizing attribute conflicts in a SAS dataset. */ create table R1xR2 as select R1.x,R1.y,R2.x,R2.y from R1 inner join R2 on R1.x=R2.x ; proc print data=R1xR2; run; /* Joining on equivalence of one pair of attributes while displaying a second pair of attributes. */ proc sql; select R1.y,R2.y from R1 inner join R2 on R1.x=R2.x ; /* Realizing attribute name conflicts into a SAS dataset. */ cre...

Re: SQL Left Join Warning
You have to use coalesce to remove the warnings:=0A=A0=0AProc sql;=0Acreate= table ld50_parms as=0Aselect =0A=A0=0Acoalesce(tmp1.n_time, tmp2.n_time,tm= p3.n_time) as n_time,=0Atmp1.intercept,=0Atmp1.trtmnt,=0Atmp1.ld50,=0Atmp1.= _type_,=0Atmp2.ld50_es,=0Atmp3.var_b1, =0Atmp3.cov_b0b1=0Atmp3.ld50_est=0A= =A0=0Afrom (tmp1=0Aleft join tmp2=0Aon tmp1.n_time=3Dtmp2.n_time)=0Aleft jo= in tmp3=0Aon tmp2.n_time=3Dtmp3.n_time=0A=A0;=0Aquit;=0A=A0=0A=0A=0A=0A=0A_= _______________________________=0AFrom: Warren Schlechte <Warren.Schlechte@= TPWD.STATE.TX.US>=0ATo: SAS-L@LISTSERV.UGA.EDU=0ASent: Wednesday, November = 5, 2008 11:10:00 PM=0ASubject: SQL Left Join Warning - Join 3 tables=0A=0AH= ere is the log from some sql steps I have coded.=0A=0AI am selecting specif= ic elements from specific rows from a dataset named=0Ald50_est, and then jo= in the data I want into a new table based on the=0Avariable n_time.=0A=0ATh= e code works as I want, but I get a warning.=0A=0AI have tried several thin= gs, but cannot seem to get clean running code=0Awithout the warning.=0A=0AI= would appreciate your help.=0A=0A31=A0 =A0 =A0 =A0 proc sql;=0A32=A0 =A0 = =A0 =A0 create table tmp1 (drop=3D_type_)as=0A33=A0 =A0 =A0 =A0 select n_ti= me, intercept, trtmnt, ld50, _type_=0A34=A0 =A0 =A0 =A0 from ld50_est=0A35= =A0 =A0 =A0 =A0 where _type_=3D"PARMS";=0ANOTE: Table WORK.TMP1 created, wi= th 4 rows and 4 columns.=0A=0A36=0A37=A0 =A0 =A0 =A0 create table tmp2 as= =0A38=A0 =A0 =A0 =A0 selec...

Outer Joins in MS ACCESS Query Wizard.
I did some research on the Net and in response to my previous post I'm thinking the problem is a result of needing to set an Outer Join instead of a inner... However, how do you specify an Outer Join in the Microsoft Access Query Creator? I haven't done SQL in years so it's plenty rusty and would rather not go that route. adel.ather@sympatico.ca (ninjaking) wrote in message news:<66700bb5.0404201120.71317321@posting.google.com>... > I did some research on the Net and in response to my previous post I'm > thinking the problem is a result of needing to set an Outer Join > instead of a inner... However, how do you specify an Outer Join in the > Microsoft Access Query Creator? I haven't done SQL in years so it's > plenty rusty and would rather not go that route. Hello, Go into design view of your query, which according to your question, should have at least two sources your querying form and are linked together. Double click on the link, or right click on it, and a popup dialog box should appear. Change the selection from a 1 to a 2, or what will fit your needs. This should change the link from an Inner Join to an Outer Join. Or what SQL uses, a Left join to a Right join. Regards, Ray ...

inner join and outer join
what's the difference between inner join and outer join in sql? See: The Query Lost My Records! at: http://allenbrowne.com/casu-02.html If you are asking what the difference looks like in the SQL statement, the FROM clause will contain the words: LEFT JOIN or RIGHT JOIN instead of: INNER JOIN -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. <bob@coolgroups.com> wrote in message news:1144769706.614863.9510@i39g2000cwa.googlegroups.com... > what's the difference between inner join and outer join in sql? > ...

comparing & then joining 2 tables in ms-access
I've original version of a table called PMM (Product Material Master). Thro' a web interface, user can change that table contents. Once changed, i need to raise an ECN (Engineering CHange Note) specifying what changes happened to original PMM table whether rows are deleted, new rows are added or existing rows are modified etc. I've both old and new version of PMM tables. The difference between two PMM tables are captured in a third table called ECN and it has both original & new PMM table entries which are not same. PMM Table structure is as follows. SbPartNo char(50) PartDesc char(200) manPartNo char(200) manufacturer char(100) vendor char(100) refDesi char(200) qty char(5) My requirement is as follows: table 1 (original version of PMM table) 1111 partDesc1 111111 xyz1 vendor1 refdesi1 2 2222 partDesc2 222222 xyz2 vendor2 refdesi2 4 3333 partDesc3 333333 xyz3 vendor3 refdesi3 6 table 2 (Modified version of PMM table) 2222 partDesc2 222222 xyz2 vendor2 refdesi2 4 3333 partDesc3 343434 xyz3 vendor3 refdesi3 6 4444 partDesc4 444444 xyz4 vendor4 refDesi4 8 ECN Table structure is as follows: old_SbPartNo char(50) old_PartDesc char(200) old_manPartNo char(200) old_manufacturer char(100) old_vendor char(100) old_refDesi char(200) old_qty char(5) new_SbPartNo char(50) new_PartDesc char(200) new_manPartNo char(200) new_manufacturer char(100) new_vendor char(100) new_refDesi char(200) new_qty char(5) After comparing the above 2 PMM tables, join two PMM tab...

Re: proc sql outer join with more than two tables #3
Thanks for the help, this did the trick. ...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100617.6f7b9f3e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? > > > Thanks for your help > &...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril On 10 Aug 2004, jewelk@free.fr wrote: > Hello, > > I need to copy a table from an 8i oracle database to a > sqlserver 2000 database. A few options exist. If this is one-off, just use sqlldr to drop the data to a file and then bcp to get it into SQLServer. > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? Well, I'm sure SQLServer has connectivity to Oracle? If you want to go this route, use that and do this from SQLServer. -- Galen Boyer On 10 Aug 2004 07:15:16 -0700, jewelk@free.fr (Cyril) wrote: >Hello, > >I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > >Is it possible to use the command "COPY FROM ... TO ..." ? >So, what is the correct syntax ? > > >Thanks for your help > >Cyril Read up on using the Heterogeneous Gateway to Sqlserver. -- Sybrand Bakker, Senior Oracle DBA "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100615.6371b40e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "...

Outer joins in MS SQL 2005: why is it this way? better yet, any resources on these nuances?
I am writing a simple query involving three tables (see below). I would like to display all customers along with their industry regardless of whether or not the customer has been assigned an industry. Table 'Customers' has 426 unique rows Table 'CustomerAttribute' is linked with/to 'Customers' where Customers.custid = CustomerAttribute.custid Table 'Industry' is linked with/to 'CustomerAttribute' where CustomerAttribute.industryid = Industry.industryid. Furthermore, CustomerAttribute.industryid is defined where CustomerAttribute.attributeid = 6 The following query returns 214 row, ie. not what I need: select cust.code, cust.name, I.code, I.Name from customer cust left join customerAttribute CA on Cust.custid = CA.custid left join Industry I on CA.industryID = I.industryID where CA.attributeid = 6 Order by cust.name The following two queries returns 426 row, ie what I want. Why is this? Better yet, does anyone have a good resource on these subtleties with MS SQL?(as far as I am concerned the queries below make sense but I have never had to use this notation) select cust.code, cust.name, I.code, I.Name from customer cust left join (select * from customerAttribute where attributeid=6) CA on Cust.custid = CA.custid left join Industry I on CA.industryID = I.industryID Order by cust.name select cust.code, cust.name, I.code, I.Name from customer cust left join customerAttribute CA on (Cust.custid = CA.custid AND CA.attributeid = 6) left ...

3 table outer join with where caluse colc='P' question
I have distilled my question down to this. I have three table and I normally query tables x and y to get my passed results something like select x.cola,x.colb,y.colc from x,y where x.cola=y.cola and y.colc = 'P'; COLA COLB C ---------- ----- - 1 A P 2 B P 4 D P 5 E P 6 F P Now I want to have a table with all possible values of x.colb and then do an outer joing to get output like I would like the output to look like z.colc x.colb A A B B C D D E E F F G H So i know that I don't have passed results form C,G,H everything works as expected if I don't have the where clause y.colC='P' this is the query I have so far but I can't get the where clause y.colc='P' to work correctly select z.colb,x.colb from z left outer join x on z.colb=x.colb left outer join y on x.cola=y.cola and y.colc='P' order by z.colb / drop table x; drop table y; drop table z; create table x (cola number, colb varchar2(1)); create table y (cola number, colc varchar2(1)); create table z (colb varchar2(1)); insert into x values (1,'A'); insert into x values (2,'B'); insert into x values (3,'C'); insert into x values (4,'D'); insert into x values (5,'E'); insert into x values (6,'F'); insert into y values (1,'P'); insert into y values (2,'P'); insert into y values (3,'F'); insert into y values (4,'P...

Self-Join combined with Outer Join?
I guess this is probably a simple problem, but I can't for the life of me figure it out. Say I have the following table: job_id batch_id action date ------------ ------------- ------------- -------- 1 1 sent 1/1/01 2 1 received 1/2/01 3 2 sent 1/3/01 4 2 received 1/4/01 5 3 sent 1/5/01 I want to write a query that will display as follows: batch_id sent received ------------ ------------ ------------ 1 1/1/01 1/2/01 2 1/3/01 1/4/01 3 1/5/01 I can self-join the table so that it gives me a query displaying batch_id 1 and 2 no problem. I just can't make it display rows for which there is no received action. Any thoughts?? I'm desperate!! Sorry, I forgot to mention: I'm using Oracle 10g on Linux r13l24r2@gmail.com wrote: > I guess this is probably a simple problem, but I can't for the life of > me figure it out. Say I have the following table: > > job_id batch_id action date > ------------ ------------- ------------- -------- > 1 1 sent 1/1/01 > 2 1 receiv...

Re: joining tables- one table below another #3
On Wed, 1 Nov 2006 08:27:48 -0500, Peter Crawford <Peter.Crawford@BLUEYONDER.CO.UK> wrote: >sorry to be following up on my own posting, but a logical flaw >was pointed out in the sql union code I offered. It worked but >not in the required way. >Since the original poster may lack the confidence to challenge >the advice, please allow me to correct the flaw. > >As written the results are ordered by the data columns. To achieve >all the rows of "one table below" all the rows of the other, that >"union" needs the qualifier "all" as in > >>>>>>>>>>>>>>>>>>>>>>>>> >proc sql ; > create table c as > select * from a > union all > select * from b > ; >quit; >>>>>>>>>>>>>>>>>>>>>>>>> Also, the CORRESPONDING option may be of use. It's important to understand that without it, the columns are aligned according to position, left to right, and not according to name. To people used to combining data sets in the DATA step, this is strange and unusual. In the example here, it would not matter. Sometimes neither mode of automatic alignment (position-based or name-based) is appropriate; then one has to either use RENAME= options or toss out the "*" shortcut and list the columns explicitly. > >Documentation is usually m...

Web resources about - Not grasping outer joins for ms-sqlserver :: 3 tables to join - comp.databases.ms-sqlserver

Resources last updated: 3/22/2016 4:23:48 AM