Not Grasping Outer Joins For Ms-sqlserver :: 3 Tables To Join

Mar 15, 2006

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
ANDb.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

View 4 Replies


ADVERTISEMENT

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

I was writing a query using both left outer join and inner join.  And the query was ....

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
        (Production.Products AS P
         INNER JOIN Production.Categories AS C

[code]....

However ,the result that i got was correct.But when i did  the same query using the left outer join in both the cases

i.e..

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';

The result i got was same,i.e

supplier     country    productid    productname     unitprice    categorynameSupplier QOVFD     Japan     9     Product AOZBW    97.00     Meat/PoultrySupplier QOVFD    Japan   10     Product YHXGE     31.00     SeafoodSupplier QOVFD     Japan   74     Product BKAZJ    10.00     ProduceSupplier QWUSF     Japan    13     Product POXFU     6.00     SeafoodSupplier QWUSF     Japan     14     Product PWCJB     23.25     ProduceSupplier QWUSF    Japan     15    Product KSZOI     15.50    CondimentsSupplier XYZ     Japan     NULL     NULL     NULL     NULLSupplier XYZ     Japan     NULL     NULL     NULL     NULL

and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.

View 5 Replies View Related

Outer Joins Using More Than Two Tables.

Jun 14, 2001

Is it possible to utilize more than two tables in a single outer join?
I have one table that I want every row and 18 others where I only want an entry if one is present meeting the conditions of "1.customerid = 2.Customerid" etc. I haven't run across this before and would appreciate any help.

View 2 Replies View Related

OUTER JOIN MORE THAN 2 TABLES

Jun 7, 2007

Is it possible to do a full outer join on more than 2 tables, i've been looking for the syntax online but can't seem to find it, so far i have

FROM dbo.TRNEWL_RTENTN_DETL RR
FULL OUTER JOIN VBVT_FIN_DIV_TRANSF
ON RR.X_POLICY_NO = VBVT_FIN_DIV_TRANSF.X_POLICY_NO and
RR.X_POLICY_EFCTV_DT= VBVT_FIN_DIV_TRANSF.X_POLICY_EFCTV_DT and
RR.X_ASCO_CD = VBVT_FIN_DIV_TRANSF.X_ASCO_CD and
RR.PRODUCT_RENWL_ABBR = VBVT_FIN_DIV_TRANSF.PRODUCT_RENWL_ABBR

Now i need to do another Full outer join to RR with table VBVT_FIN_RGN_TRANSF, is that possible?

View 4 Replies View Related

How To Use Left Outer Join In Two Tables?

Nov 8, 2007

Hi all,

I need a small help in creating a join query in sql.

My two tables are A and B. A has got columns id, dname, counter and
date. B has got two columns link and date. The primary key of A
is the column Id whereas in table B, both are primary keys(composite key).

I need to get the records (id,dname,counter) from A which has no
corresponding link in table B.

To be precise, If the table A has fields (id,dname,counter) (1,abc,2000)
and if table B has no record for abc, this row should be returned from A.I hope using left outer join will help me in getting my desired result.I hope someone will be able to help me out.

Thanking all in advance for your valuable time.

View 1 Replies View Related

Need Help Creating Outer Join On Multiple Tables

Nov 2, 2005

I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.

The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?

I'm a SQL code novice so any help would be greatly appreciated!

SELECT
Article.articleId
, Article.articleName
, Article.articleStatus
, Articlegroup_2.ArticlegroupId
, Articlegroup_2.g2_key
, Articlegroup_2.g2_name
, articleGroup.articleGroupId
FROM
HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2
WHERE
articleGroup.articleGroupId = Article.articleGroupId AND
Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id

View 5 Replies View Related

Need Help Creating Outer Join On Multiple Tables

Dec 14, 2007

I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.

I'm a SQL code novice so any help would be greatly appreciated!

Here is my existing SQL query (without the new outer join table):

SELECT
Lead_.Country, Lead_.Company_Name, Employee.Full_Name, Lead_.Rn_Create_Date, Lead_.Marketing_Project_Name, Employee_1.Full_Name, Lead_.Comments

FROM
ProductionED.dbo.Employee Employee, ProductionED.dbo.Employee Employee_1, ProductionED.dbo.Lead_ Lead_

WHERE
Employee.Employee_Id = Lead_.Account_Manager_Id AND Employee_1.Employee_Id = Lead_.Created_By_Employee_Id AND ((Lead_.Market_Segment='new'))

View 5 Replies View Related

Two Tables With Left Outer Join && Where Clause

Oct 29, 2007

Hello,I'm trying to link two tables... one for Employees and the other forTimecardsI need to get a list of employees that do not have timecards on anSPECIFIC DATEI tried the follonwingSELECT Employess.EmployeeIDFROM Employees LEFT OUTER JOIN Timecards on Employees.EmployeeID =Timecards.lmpEmployeeIDWHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'But it doesn't work. However, when I comment the date condition out(lmpTimecardDate = '10/24/2007') it works all right but It's not whatI needAnother interesting point... if I use the following query... it worksall rightSELECT Employess.EmployeeIDFROM EmployeesWHERE Employees.EmployeeID not in (select Timecards.EmployeeID fromTimecardswhere TimecardDate = '10/24/2007')I'd like to be able to use the Left Outer Join option.... Am I doingsomething wrong?... or is it that if It doesn't like the condition I'musgin in the WHERE clause (TimecardDate = '10/24/2007')Thanks for your helpPablo

View 3 Replies View Related

OUTER JOIN With Multiple Tables And A Plus Sign?

Jul 20, 2005

I am trying to select specific columns from multiple tables based on acommon identifier found in each table.For example, the three tables:PUBACC_ACPUBACC_AMPUBACC_ANeach have a common column:PUBACC_AC.unique_system_identifierPUBACC_AM.unique_system_identifierPUBACC_AN.unique_system_identifierWhat I am trying to select, for example:PUBACC_AC.namePUBACC_AM.phone_numberPUBACC_AN.zipwhere the TABLE.unique_system_identifier is common.For example:----------------------------------------------PUBACC_AC=========unique_system_identifier name1234 JONES----------------------------------------------PUBACC_AM=========unique_system_identifier phone_number1234 555-1212----------------------------------------------PUBACC_AN=========unique_system_identifier zip1234 90210When I run my query, I would like to see the following returned as oneblob, rather than the separate tables:-------------------------------------------------------------------unique_system_identifier name phone_number zip1234 JONES 555-1212 90210-------------------------------------------------------------------I think this is an OUTER JOIN? I see examples on the net using a plussign, with mention of Oracle. I'm not running Oracle...I am usingMicrosoft SQL Server 2000.Help, please?P. S. Will this work with several tables? I actually have about 15tables in this mess, but I tried to keep it simple (!??!) for the aboveexample.Thanks in advance for your help!NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAILADDRESS.Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!(Rhetorical question only.)

View 1 Replies View Related

SQL-92 Outer Join Vs T-SQL Outer Join (6.5 Or 7.0) - Test Script Included

Apr 26, 2002

Take the following scenario:

We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results.

Thanks in advance for any help.
-David Edelman

Test script below, followed by results
===========================================
create table parent (p_id int NOT NULL)
go
create table child (p_id int NOT NULL, c_type varchar(6) NULL)
go
insert parent values (1)
insert parent values (2)
insert parent values (3)
insert parent values (4)
insert parent values (5)
insert parent values (6)
insert parent values (7)
insert parent values (8)
insert parent values (9)
insert parent values (10)
go

insert child values (1, 'AAA')
insert child values (1, 'BBB')
insert child values (1, 'CCC')
insert child values (2, 'AAA')
insert child values (4, 'AAA')
insert child values (4, 'DEF')
insert child values (4, 'AAA')
insert child values (4, 'BBB')
insert child values (5, 'AAA')
insert child values (5, 'AAA')
insert child values (6, 'AAA')
insert child values (7, 'AAA')
insert child values (7, 'BBB')
insert child values (7, 'CCC')
insert child values (7, 'DDD')
insert child values (10, 'AAA')
insert child values (10, 'CCC')
go

select p.p_id, count(c.p_id) as num_rows
from parent p, child c
where p.p_id *= c.p_id
and c.c_type in ('AAA', 'BBB')
group by p.p_id

select p.p_id, count(c.p_id) as num_rows
from parent p left outer join child c on p.p_id = c.p_id
where c.c_type in ('AAA', 'BBB')
group by p.p_id

=========================================
Results:
(T-SQL *= outer join)
p_id num_rows
----------- -----------
1 2
2 1
3 0
4 3
5 2
6 1
7 2
8 0
9 0
10 1

(SQL-92 outer join)
Warning: Null value eliminated from aggregate.
p_id num_rows
----------- -----------
1 2
2 1
4 3
5 2
6 1
7 2
10 1

View 1 Replies View Related

Outer Join Syntax Problems (Multiple Tables)

Sep 13, 2005

Hello all--

I'm trying to run a SELECT on 3 tables: Class, Enrolled, Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.

My current query...

SELECT     Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM         Class LEFT OUTER JOIN
                     
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
                     
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name

...results in identical counts for enrolled and waiting, which I know
to be incorrect. Furthermore, it appears that the counts are being
multiplied together (in one instance, enrolled should be 14, waiting
should be 2, but both numbers come back as 28).

If I run this query without one of the joined tables, the counts are
accurate. The problem only occurs when I try to pull counts from both
the tables.

Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?

Thanks very much for your time,
--Jeremy

View 2 Replies View Related

Joins On Views That Are Formed With Outer Joins

Nov 3, 2000

We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.

If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.


625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.

Error 4425 text would imply that joins with view formed by outer joins should be avoided.

Any ideas on the principles involved here.

View 1 Replies View Related

Transact SQL :: Difference Between Outer Apply And Outer Join

May 10, 2010

what is difference between outer apply and outer join ,both return rows from left input as well as right input . isnt it?

View 3 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses


Thank You

View 1 Replies View Related

LEFT OUTER JOIN Or RIGHT OUTER JOIN?

Nov 4, 2003

Hello

I've a table with these values:

Cod_Lingua - Des_Lingua
------------------------------
ITA Italian
GER German
ENG English
FRA French

and another table with product/description

ProductID - Cod_Lingua - Description
-------------------------------------------
1 ITA Mia Descrizione
1 ENG My Description

I've this SELECT:

SELECT Tab_Lingue.Cod_Lingua, Descrizioni_Lingua.Description
FROM Descrizioni_Lingua RIGHT OUTER JOIN Tab_Lingue ON Tab_Lingue.Cod_Lingua=Descrizioni_Lingua.Cod_Lingua
WHERE Descrizioni_Lingua.ProductID=1

I get these results:
ITA - Mia Descrizione
ENG - My Description

I don't want this. I'd like to have this:
ITA - Mia Descrizione
ENG - My Description
GER - (null)
FRA - (null)

How can I get the second result set?

Thanks for your support.

View 3 Replies View Related

Outer Joins

Sep 2, 1999

I have been using an outer join to display information in a form. I would like to edit the rows that are returned. The join shows all of the rows in the 1st table, and the corresponding rows in the 2nd other. If there are no corresponding rows in the 2nd table, a NULL is displayed. The problem is when I try to edit one of the fields that is "NULL", I get an error. I know why the error is happening, because the NULL field is a row that does not exist, yet. The jet database engine will automatically insert rows into the 2nd table, but SQL server will not. I have tried to set primary key and foreign key properties for the tables in question, but it still does not work. Does anyone know how to do this?

Thanks for the help.
Davant

View 1 Replies View Related

Outer Joins

Apr 29, 1999

I have been reading about outer joins and how they are much faster to process on larger data sets than NOT EXISTS are..Did anyone find any significant performance gain..Logically, using an outer join does not make sense in this context..Can someone shed some light on this subject?

View 2 Replies View Related

Outer Joins

Jul 20, 2005

I am trying to create an outer join between two tables in a query thatincludes several other tables.When I double-click on the Join line, it presents three join options:1) ONLY records from table1 and table2 where join fields are equal2) ALL values from table1 and ONLY records from table2 where joinfields are equal3) ALL values from table2 and ONLY records from table1 where joinfields are equalIn my case, I want option 2 - all values from table1, and if there isno match to table2, I want a blank to appear in the output.When I select this option, I get the following error:"Can't have outer joins if there are more than two tables in thequery."How can I get around this, since there are other tables in my query?Thanks.Dennis HancyEaton CorporationCleveland, OH

View 1 Replies View Related

Help Withe SQL Inner And Outer Joins.

Nov 25, 2003

Hello-

I am confused with inner and outer joins. Can someone explain to me exactly what the differences are? The reason I am asking is I am trying to populate a datagrid from multiple tables. The SQL query currently pulls out all customers that have revenue only, When in fact I need it to pull out all customers regardless of revenue so that I can see which customers have "0" revenue.

Here is my sql query which sorts each day into a new datagrid column.

SQL = "SELECT revenue_forecast.oct_03 AS 'forecast', pb_customers.customer_name AS 'customer', sum(case when day(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost else 0 end) as day1, sum(case when day(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost else 0 end) as day2, FROM pb_report_shippers Left outer JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id left outer JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id left outer JOIN revenue_forecast ON pb_customers.customer_id = revenue_forecast.customer_id WHERE pb_report_shippers.shipper_date_time between cast('11/01/03' as datetime) and cast('11/2/03' as datetime) AND pb_report_shippers.job_completed IN('1','0') AND pb_customers.customer_deleted <> '1' GROUP by pb_customers.customer_name, revenue_forecast.oct_03 Order BY pb_customers.customer_name"


Any help would be appreciated. Thanks

View 3 Replies View Related

Two Outer-joins In One Query

Oct 5, 2003

I wrote a SQL query that firstly do an outer-join for two table and after I add another
table with outer-join as well.
I'm getting the following message:
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.
May be someone knows what is it?
If I remove the second join- it work....

View 2 Replies View Related

Outer Joins In SQL Server

Aug 6, 2002

Hi,
I need to change some SQL statements in Oracle to to SQL Server syntax.
For example, the following is a query in Oracle SQL syntax.
================================================== ===========================
SELECT
COUNT(*)
FROM TABLE1 T1,
TABLE2 T2,
TABLE3 T3,
TABLE4 T4,
TABLE5 T5
WHERE
(
T1.NO(+)=T3.NO
AND T3.NO=T2.NO(+)
AND T4.NO(+)=T3.NO
AND T3.NO=T5.NO(+)
)
AND
(
T1.CODE(+)='VALUE1'
AND T2.CODE(+)='VALUE2'
AND T4.CODE(+)='VALUE3'
AND T5.CODE(+)='VALUE4'
)
================================================== =========================
Can you anyone please tell me the SQL Server equivalent? I am having doubts in the outerjoin part.
Thanks in advance
Regards
R

View 7 Replies View Related

Creating Outer Joins

Dec 30, 2004

I have a query:

select * from furn_piece_master fpm, furn_piece_detail fpd, furn_detail fd
where fpd.fpd_master_id = fpm.fpm_id
and fpm.ymr = '200411U'
and fpd.ymr = fpm.ymr
and fpd.chain = fpm.chain
and fpd.store = fpm.store
and fpd.week = fpm.week
and fd.ymr = fpd.ymr
and fd.chain = fpd.chain
and fd.store = fpd.store
and fd.report_type = fpd.report_type
and fd.week = fpd.week
and fd.item_key = fpd.item_key

Now I want to do an outer join on the furn_detail (fd) store only, but leave the rest of the furn_detail inner joins. How would I do this?

View 1 Replies View Related

SQL Outer Joins Problem

Aug 17, 2006

Hi,

I'm upgrading from SQL 2000 -> 2005 at the moment. Having an issue with some old Outer Join SP's which use the =* and *= operators.

The issue i am having is this. Using this Procedure on the Pubs DB:
SELECT title, qty FROM titles, sales
WHERE titles.title_id *= sales.title_id
ORDER BY qty

Works fine. I get 23 rows with 2 of them having NULL in qty.

This also works, giving the same result:
SELECT title, qty
FROM titles
LEFT OUTER JOIN sales
ON titles.title_id = sales.title_id
ORDER BY qty

All looking good. Now, just say i want to limit the rows from the Right table. Lets just select Store #6380. Using the old *= operator:
SELECT title, qty
FROM titles, sales
WHERE titles.title_id *= sales.title_id
AND Stor_ID = 6380
ORDER BY qty

This works fine, giving me all 18 titles, with sales amounts next to two of them, the rest are null, which is the response I want. However, if I insert the same WHERE clause into the OUTER JOIN code:
SELECT title, qty
FROM titles
RIGHT OUTER JOIN sales
ON titles.title_id = sales.title_id
WHERE Stor_Id = 6380
ORDER BY title

It seems to act like an Inner Join, not an Outer Join, giving me only rows that match on both sides. I can't seem to work out why this would do this. Am I overlooking something simple? Can someone give me some idea why it's not working?

Thanks,

Josh

View 6 Replies View Related

OUTER LEFT JOINs On SQL CE

Feb 17, 2008

Hi,

First of all, is this an appropriate place to get answers related to SQL CE? If not, do you have any recomended forums elsewhere?

I'm trying to get a list of various related tables using ResultSets on SQLCE 3. The query is something like this:

SELECT A.*
, F.Descricao AS FamiliasDescricao
, M.Descricao AS MarcasDescricao
, I.Descricao AS IVADescricao
FROM Artigos AS A
LEFT OUTER JOIN Familias AS F ON A.FamiliasUID = F.UID
LEFT OUTER JOIN Marcas AS M ON A.MarcasUID = M.UID
LEFT OUTER JOIN IVA AS I ON A.IVAUID = I.UID
INNER JOIN ArtigosTipos AS AT ON A.ArtigosTiposUID = AT.UID;

The column ArtigosTiposUID cannot be NULL, so an INNER JOIN is used, but the other UID columns can have a NULL value, and I need all the rows on Artigos to show up even if these other UIDs are NULL. The query runs fine like this in VS2005, returning NULL values for the columns if there are no rows on the other tables, both on the SQL Server 2005 database and the .sdf database used on the Windows Mobile device. But on Windows Mobile SQL CE gives me an "Unspecified error [7]", Native error 25607, an the stack trace ends with:

em System.Data.SqlServerCe.SqlCeCommand.ProcessResult s()
em System.Data.SqlServerCe.SqlCeCommand.CompileQueryP lan()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteComman d()
em System.Data.SqlServerCe.SqlCeCommand.ExecuteResult Set()

If I replace all the LEFT OUTER JOINs with INNER JOINs only the rows where all UIDs have a value show up, but as I said, I want all rows on tabela Artigos. Even if I remove all JOINs except the last one and replace it with a LEFT OUTER JOIN I get the same erro, all rows having the column ArtigosTiposUID defined... it seems as if the simple presence of LEFT OUTER JOIN makes SQL CE return an error.

Is there a way to run the queries on VS using the SQL CE engine so that one can check whether the query will run successfuly on Windows Mobile?

Any other sugestions?

Thank you,
Hugo Lopes

View 6 Replies View Related

Mixing INNER And OUTER Joins

Sep 26, 2006

I've encountered this problem multiple times in the past and I have a solution but wonder if there might be a more elegant method of achieving the same result...

Take the following example:SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

Users may or may not have a profile (but never more than one). A profile may or may not have a department (but never more than one).

Now, this will return only users that have a profile even though an outer join has been used. What I really want is to return all users and include their profile and department details but only when the profile has a department.

The solution I have used in the past is:

SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]

The trouble here is that I've lost the ability to reference department and profile independantly in the outer query. Also, more complex scenarios can also become horribly complex if this needs to be done multiple times in the same query.

I could do this:SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]

But again I feel that the intention is not at all clear. I want to inner join department to profile because I'm only interested in profiles with a department and departments referenced by a profile.

I would like to be able to specify that the departments should be inner joined to profiles and whichever profiles remain get outer joined to users whilst retaining department and profile as seperate entities within the query.

Is there any way to use brackets to indicate an order of precedance to the logical joins within the from clause?

Daniel

View 3 Replies View Related

Views &&amp; Outer Joins

Sep 18, 2006

I'm having problems with queries that use a view in an outer join. It's just sits there and grinds. Below is some queries to illustrate.

SELECT EvalID,RecIndex
FROM dbo.vCurrentEvalInfo

Executed in 1 second in SQL Query Analyzer
Returns 29,820 Rows
----

SELECT RecIndex
FROM dbo.tblCNAKeyInformation

Executed in 1 second in SQL Query Analyzer
Returns 47,978 Rows
----

SELECT C.RecIndex, E.EvalID
FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E
ON C.RecIndex=E.RecIndex

Executed in 1 second in SQL Query Analyzer
Returns 47,978 Rows
----

SELECT C.RecIndex, E.EvalID, E.DATE_COMP, E.EvalDate, E.EvalCode, E.EvalResults
FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E
ON C.RecIndex=E.RecIndex

Executed in 1 second in SQL Query Analyzer
Returns 47,978 Rows
----

Never Finishes Executing. If I include any column from the dbo.tblCNAKeyInformation and the query just grinds.

exampe:
SELECT C.RecIndex, E.EvalID, E.DATE_COMP, E.EvalDate, E.EvalCode, E.EvalResults, C.LastName
FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E
ON C.RecIndex=E.RecIndex


Also, the below query doesn't finish executing either.
SELECT RecIndex
FROM dbo.tblCNAKeyInformation
WHERE (RecIndex NOT IN
(SELECT RecIndex
FROM dbo.vCurrentEvalInfo))

Any I missing something Here? I seems like it's recalcualting the view with each scan of the outer table on the join instead of using a cached copy for x number of micro seconds ago.

I could solve this my dumping the view to a temp table, however that seems like an ugly hack to me.

Any help would be appreciated.

View 1 Replies View Related

How Can We Trust Outer Joins

Apr 26, 2006

In the future =* or *= will not be suportted but I found more then one examples that show outer join does not bring all the rows =* does.

Can any one tell me what is wrong? Market table has 19 markets Budget table has rows for BudgetYear=2006 but none for 2007

BTW the same is true in SQL2005 and 2000

----------------------------------------------------------------

SELECT Market_code, Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )
FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_code
Where Budget.BudgetYear=2007 or Budget.BudgetYear is null
GROUP BY Market_code, Description
ORDER BY Description

-- ------------------GIVES only 12 results -------------------------------



SELECT Market_code, Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )
FROM Budget, Market
Where Budget.Customer_Market_code =* Market.Market_code
AND Budget.BudgetYear=2007
GROUP BY Market_code, Description
ORDER BY Description

-- ------------------GIVES 19 results -the true------------------------------

View 1 Replies View Related

Informix OUTER Joins To T-SQL ?

Jan 4, 2008



Hello,

I am currently in the process of converting an ASP.net app from an Informix backend to SQL Server 2005.

Informix has an OUTER keyword which wraps a table in a query and allows it to return NULL.

i.e. this:

SELECT * FROM Table1 OUTER(Table2) WHERE Table1id = Table2id

is the T-SQL equivalent of :


SELECT * FROM Table1 LEFT JOIN Table2 ON Table1ID = Table2ID

However Informix has something called a 'Nested Simple Join' which looks like the following :

SELECT * FROM Table1 OUTER(Table2, Table3) WHERE Table1id = Table2id AND Table2id=Table3id


Would anyone know the T-SQL equivalent of the above - any help would be greatly appreciated.

THANKS

View 1 Replies View Related

Right/left Outer Joins && Performance

Nov 13, 2003

Other than being much less readable, is there a downside to combining left and right outer joins in the same SELECT? I'm reviewing some generally poor code done by a contractor and it's peppered with queries with both left and right joins. I've always thought it was just a semantic difference, but I was just wondering if, other than readability, there were any performance issues.

Thanks,
Pete

View 1 Replies View Related

Outer Joins Does Not Work As Expected

Jul 27, 2000

Hi,


I am trying to extract the non matching records and the matching ones
in two tables from two servers, one a linked server in one go.
For example if table A has records,
Rec1, Rec2, Rec3 and Rec6
AND Table B has
Rec1, Rec2, Rec3 and Rec7
I need to get in the result set
Rec1, Rec2, Rec3, Rec6 and Rec7.

The real query I ran is as follows. I want to know the list of all tables
in GlobalDB database in sg_qt1 and sg_q5 servers.
NOTE : sg_q5 is a Linked server to sg_qt1.

Select Substring(a.name,1,30), Substring(user_name(a.uid),1,19)
from sysobjects a
full outer JOIN sg_q5.globaldb.dbo.sysobjects b
ON a.name = b.name
where a.xtype = 'u' and b.xtype = 'u'
and a.name not in ('dtproperties','rowcounts','globalDBrowcounts')

If I run it from sg_qt1, the result I get contain all tables from sg_qt1
but not the non-matching ones from sg_q5.

I am obviously doing something wrong, but what is it?

Thanks.
If possible please reply to, r.wimalaratne@iaea.org

Ranjit

View 1 Replies View Related

Outer Joins And Substring Error

Nov 28, 2005

My problem exists on the last line - TOAD doesn't seem to like the two sets of parenthesis together. Am I supposed to put something in between? It's OK in the line above because the substring is on the other side.

SELECT distinct fp.active_fl, fp.empl_id, fp.proj_mgr_name, pm.project, fp.subctr_id, rd.CONT_CRNCY
FROM planner.rbws_proj_mgr pm, finance.fin_proj fp, finance.rbws_detl rd
WHERE SUBSTR(fp.proj_id, 1, 9) = pm.project (+)
AND pm.PROJECT = substr(rd.PROJ_ID,1,9) (+)

View 2 Replies View Related

SQL Server 2005 - Help With Outer Joins

Mar 26, 2007

Hello,

I'm currently trying to rewrite some existing non-ANSI joins and have come across one that I'm not sure how to handle.

Here is a sample of the existing code:

SELECT receipts_payment_method.transaction_number,
receipts_detail.terms_amt,
receipts_detail.allowed_amt,
isNull(receipts_payment_method.check_number,'n/a') check_number,
isNull(receipts_payment_method.credit_card_type,'n/a') credit_card_type,
isNull(paymnt_method.payment_desc,'n/a') payment_desc,
isNull(cc_type.credit_card_desc,'n/a') credit_card_desc
FROM receipts,
receipts_detail,
receipts_payment_method,
paymnt_method,
cc_type
WHERE (receipts_payment_method.payment_id *= paymnt_method.payment_id) and (cc_type.credit_card_id =* receipts_payment_method.credit_card_type) and (receipts_payment_method.transaction_number = receipts.transaction_number);

I'm not sure how to rewrite the two joins since there are 3 tables involved (receipts_payment_method, paymnt_method, and cc_type) along with an "and".

Any help would be appreciated! Thanks in advance!

View 1 Replies View Related

How Do You Do Multiple Left Outer Joins???

May 31, 2006

I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys.  I bolded the three IDs that could be NULL or have a foreign key for a value.  An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet.  Right now I only get profiles that have foreign key values and it misses the rest in the search.   So NULL MakeID or ModelID no result on that item my SQL statement below.  Using SQL Server 2005.  Pretty new this to SQL and databases but so far this has been the only trying part.

Thanks

String dbsql = "SELECT a.EquipmentID " +
                         "     , a.SerialNo " +
                         "     , b.Category " +
                         "     , c.Subcategory " +
                         "     , d.Make " +
                         "     , e.Model " +
                         "     , f.Status " +
                         "  FROM tblEquipInfo a " +
                         "     , tblEquipCat b " +
                         "     , tblEquipSubcat c " +
                         "     , tblEquipMake d " +
                         "     , tblEquipModel e " +
                         "     , tblStatus f " +
                         " WHERE b.Category = '" + val + "' " +
                         "   AND a.CategoryID = b.CategoryID " +
                         "   AND a.SubcategoryID = c.SubcategoryID " +
                         "   AND a.MakeID = d.MakeID " +
                         "   AND a.ModelID = e.ModelID " +
                         "   AND a.StatusID = f.StatusID";

 

 

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved