Query Joins Problem

Jul 23, 2005

Hi all,
I have the following tables:
A1
==
HostID Name RunID
------ ---- -----
1 host1 NULL
2 host2 1
3 host3 NULL

A2
==
RunID SessionID
----- ---------
1 4
2 2

A3
==
SessionID Name
--------- ----
4 Session1
2 Session3

I want to show every record from A1 with SessionName from A3, unless
the field RunID in A1 is NULL and then I want to see NULL, like this:
HostID Name RunID Name
------ ---- ----- ----
1 host1 NULL NULL
2 host2 1 Session1
3 host3 NULL NULL


when I try the following query:

select A1.*, A3.Name
from A1, A2, A3
where A1.RunId *= A2.RunId and A2.SessionID = A3.SessionID

I get the following error:

The table 'A2' is an inner member of an outer-join clause. This is not
allowed if the table also participates in a regular join clause.

How can I overcome this problem. Please help. (I use this syntax istead
of joins since I have to supprt also Oracle DB and this syntax is
simpler to translate).

Thanks in advance,
Yaron

View 1 Replies


ADVERTISEMENT

Joins Query Help

Jun 4, 2007

Hi all,Just after some help with a query (Stored Procedure) I've managed to get wrapped round my head.The DB is as such:

COMPANY
Company_id
Company_name
COMPANY_GROUP
Group_id
Group_name
USER
User_id
User_name
Bridging tables

COMPANY_GROUP_BRIDGE
company_id
group_id

USER_COMPANY_BRIDGE
user_id
company_id Basically, the only parameter I have for the query is a User_id.I need to get the Group linked to the User and return all the companies within that group. I've
tried reading up on all the join types again but have just got this
completely wrapped round my neck. I keep thinking along the lines of
SELECT all the companies linked to all the groups linked to all the
companies linked to the User_id  :s  I must be able to do
this without using two Company tables...?
Any help much appreciated,Pete 

View 7 Replies View Related

Sub Query AND Inner Joins

Feb 25, 2008

I have 
Categories table with CategoryID, Title, CategoryParentID columns
Questions table with QuestionID, Text columns
CategoriesQuestions table with CategoryID, QuestionID columns
Now I want to make the query which takes QuestionID and return all Categories of that provided QuestionID.
I already have made that query in 2 different styles.
First I make an Inner Join to retrieve the results
Select Categories.* from Categories Inner Join CategoriesQuestiosn on Categories.CategoryID = CategoriesQuestions.CategoryID where CategoriesQuestions.QuestionID = 1
Second I make a sub query to retrive the results.
Select * from Categories where CategoryID In (Select CategoryID From CategoriesQuestions where QuestionID = 1)
Now I am asking what is the better one in respect to performance.
And please provide any other query if you have.

View 5 Replies View Related

Joins In A Query

Feb 26, 2014

Iwrote a query right, and need to get further information out of another table, so did a Join, but then also need to get further information out of another table..So, I need to add this query:

SELECT PERSONNUM as EmployeeCode
,[FIRSTNM] as Firstname
,[LASTNM] as LastName
,[HOMELABORLEVELDSC1] as Site
,[HOMELABORLEVELNM1] as SiteID
FROM [tkcsdb].[dbo].[VP_ALLPERSONV42]where USERACCOUNTSTATUS = 'active'
and homelaborlevelnm1 in ('11', '13', '32')
order by SiteID, LastName

To be included in this query:

SELECTa.PERSONNUM,
a.PERSONFULLNAME,
HOMELABORLEVELDSC1,
[SERVER]

[code]...

View 3 Replies View Related

Sql Query, Several Joins

Mar 6, 2008

I have the following:

select
ad.bank,ad.account_number,ad.account_short_name,
p.security_id,isnull(b.NAME,isnull(tw.name,ms.name)) as SecurityDescription,
p.security_price,mc.classcode,
sum(case
when p.security_price = 9999999.99900 then 0
when mc.classcode between '000'and'299' then (p.quantity * p.security_price) / 100
when mc.classcode between '950'and'999' then (p.security_price * p.quantity) * -1
when mc.classcode between '300' and '324' or mc.classcode between '330' and '399'then p.acm_ctf_mtf_price * p.quantity
when p.security_price = '0' then p.cost
else
(p.security_price * p.quantity)
end) as MV,p.position_date,p.quantity,p.cost,p.acm_ctf_mtf_price
from positions p
left join BloombergEquityData b on p.security_id = b.ID_CUSIP
left join account_detail ad on p.account_id = ad.account_id
left join metavanteclasscodes mc on p.security_id = mc.cusip
left join MacgregorSecurityMaster ms on p.security_id = ms.cusip
left join TobaSecurityWarehouse tw on p.security_id = tw.cusip
where p.position_date = '03/05/2008' and
((ad.investment_authority_id in ('2','3') and ad.bank in ('98','7R')) or ad.bank = '9W')-- and ad.account_number = '41G006012'
group byad.account_number,ad.account_short_name,p.security_id,
ms.name,b.NAME,p.security_price,p.position_date,ad.bank,mc.classcode,p.quantity,p.cost,p.acm_ctf_mtf_price,isnull(b.NAME,isnull(tw.name,ms.name))
order by ad.account_number,p.security_id

For a given day, in this example 3/5 if I were to just select all the records I would get back around 550,000. If I put the filter on where I tell it to just show me one account its snappy. If I remove that filter and have it show me all the accounts it take forever. Here is the layout of my positions table where i am pulling the majority of the information:

CREATE TABLE [dbo].[positions](
[account_id] [int] NOT NULL,
[security_id] [varchar](50) NOT NULL,
[position_date] [smalldatetime] NOT NULL,
[quantity] [decimal](18, 5) NOT NULL,
[cost] [decimal](18, 5) NULL,
[security_price] [decimal](18, 5) NULL,
[acm_ctf_mtf_price] [decimal](18, 9) NULL,
CONSTRAINT [PK_positions] PRIMARY KEY CLUSTERED
(
[account_id] ASC,
[position_date] ASC,
[security_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

My questions is if you can identify any reason why the query might take more than 10 minutes (actually still running after 10 minutes) to complete when I have it bring me back all the records, not sure if its a lack of indexes or what, but was hoping for some suggestions on what I might be missing. I apologize if I havent included enough information, still learning how to troubleshoot the performance side of things.

View 11 Replies View Related

Sql Joins Query Problem

Aug 30, 2006

Not sure if this is right place to ask but here is our problem. We are getting records from more than one table.  Table1 and Table2 for example Both Tables haveid, value table11, 201, 30 Table 21, 501, 40   here is the queryselect table1.id, table1.value, table2.value from Table1inner join Table2on Table1.id = table2.idproblem is this returns 4 rows like this1, 20 , 501, 30 , 501, 20 , 401, 30 , 40 When we need it to return1, 20 , null1, 30, null1, null, 401, null , 50 any help on this would be appreciated.Thank You in advance.  

View 2 Replies View Related

Slow Query With Joins And Where

Feb 5, 2008

Hi all.I want to use the following query in a sp to enable paging using ObjectDataSource.The problem (being EXTREMELEY slow) arises when I add these joins and where statements.
SELECT r.RID AS ReqID, r.Name AS ReqName, r.Family AS ReqFamily,t3.Name AS DistName, t4.Name AS RurName,t5.Name AS VilName, n.Name+' '+n.Family AS NazerName ,ROW_NUMBER() over (order by r.Family) AS RowRankFROM Requests rLEFT OUTER JOIN Nazeran n ON r.nazerID = n.ID LEFT OUTER JOIN t1States t1 ON t1.ID = r.StateID LEFT OUTER JOIN t2Provinces t2 ON t1.ID = t2.StateID AND r.ProvID = t2.ID LEFT OUTER JOIN t3Districts t3 ON t2.ID = t3.provID AND t1.ID = t3.stateID AND r.DistID = t3.ID LEFT OUTER JOIN t4RuralDistricts t4 ON t3.ID = t4.distID AND t2.ID = t4.provID AND t1.ID = t4.stateID AND r.RurID = t4.ID LEFT OUTER JOIN t5Villages t5 ON t4.ID = t5.rurID AND t3.ID = t5.distID AND t2.ID = t5.provID AND t1.ID = t5.stateID AND r.VilID = t5.IDWHERE r.stateid=(case when @StateID is null or @StateID='' then r.stateid else @StateID end)  and r.provid=(case when @provID is null or @provID='' then r.provid else @provID end)  and r.rID=(case when @ReqID is null or @ReqID='' then r.rID else @ReqID end)  and isnull(r.nazerID,'')=(case when @nazerID is null or @nazerID='' then isnull(r.nazerID,'') else @nazerID end)  and r.name+' '+r.family like (case when @ReqName is null or @ReqName='' then r.name+' '+r.family else '%'+@ReqName+'%' end)
**there are 1million rows in [Requests] table ,200000 rows in [t5villages], and about total 5000 rows in other tables.As you can see, this is for a GridView showing list of people requesting a loan allowing users to make alternative searches based on Name, Familyname ,ID ,...
would you please help me optimize and make fast this query.Many thanks..

View 13 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

Complex Query, Many Joins...

May 1, 2008

Ok I have a complex query that works great, but now I need to join yet another table but the problem is, the value that links it to the rest of the query is a value that is returned from a case statement earlier in the query...

For Example...

SELECT var1, var2, var3,
CASE T1.Number WHEN 0 THEN T2.Result ELSE T3.Result END as var4
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 on T2.Value=T1.Value
LEFT OUTER JOIN TABLE3 T3 on T3.Value=T1.Value2

Above is a generalized, much smaller example of my working Query.
Now what I need to do is join TABLE4 but the value I need to connect it to the rest of the query is var4, which can come from 2 different places depending on the value of T1.Number

Basically the logic I need to make it work is to either have a case statement in my JOIN. Like, 'LEFT OUTER JOIN TABLE4 on T4.Value=(CASE T1.Number WHEN 0 THEN T2.Result ELSE T3.Result END)'

Is this even possible and if so, how? I need to keep the logic in the main query to preserve performance...

Thank you in advance,

Casey

View 3 Replies View Related

How To Use Joins To Solve This Sql Query

Mar 27, 2008

My tables and data as follows

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2


AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name
1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw
2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa
3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw
4 03/23/08 AB Plant 1 4.35 -- 1 -- henry
5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa
6 03/23/08 Other Project 1 3.24 -- -- 2 hologa
7 03/23/08 Other Project 2 4.35 -- -- 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.







uday

View 1 Replies View Related

SQL View / Joins Query

Jul 23, 2005

Hi,I have a view(A) and I am trying to do a join on another table (B) toinclude only rows where date values in view A is greater than in tableB. I also want the view to pick up rows in viewA based on date values.Here is what I have so far:SELECT *FROM viewA vwleft JOIN tableB tb ONvw.id = tb.id and(vw.date1 > tb.date1 orvw.date2 > tb.date2 orvw.date3 > tb.date3)WHERE vw.date4 > getdate()-1Not matter what kind of join I use I can get both the rows from theview where dateA > getdate()-1 AND where date1-3 are greate than intableB. Dates 1 - 4 seperate date fields. Could someone please tellme what I am doing wrong.Thanks.

View 3 Replies View Related

Query With Joins Problem

Jun 22, 2006

HelloLet me explain the problem I am having:I have two tables, data_t and a_data_ta_data_t is the archive table of data_tThe two tables are exactly the same.In the table values are stored:Value (A numeric value)Code (A text code to identify a report with data)Line (The line number)Col (The Col Number)EDate (The date of entry)Grp (A number of a group the data belongs to)I want to get the value from data_t minus the value from a_data_t withthe same Code, Line and Col but with a different EDate (To view thevariance).Here is my statement:select d1.line, d1.col, (IsNull(d1.value,0) - IsNull(d2.value,0)) asvalue from data_t d1full outer join a_data_t d2 on d1.Code = d2.Code and d2.line = d2.lineand d1.col = d2.colwhered1.Code = 'XC001' and d1.line between 1 and 20 and d1.grp = 26and d1.EDate = '2006/06' and d2.grp = 26 and d2.EDate = '2006/05'order by d1.line, d1.colIt works fine EXCEPT when there is a value in either of the tables thatisn't in the other one, then a value is not given.Example:data_t doens't have a value for line=1 and col=2 and grp=26 and Code ='XC001' and EDate = '2006/06'a_data_t has the value of 50000 for the same details (Except Edate of'2006/5')Instead of returning -50000 it doesn't return anything.I hope I could explain it correctly.Any help will be greatly appreciated.Thanks.

View 1 Replies View Related

Using Joins How To Solve This Sql Query

Mar 27, 2008

My tables and data as follows

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2


AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name
1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw
2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa
3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw
4 03/23/08 AB Plant 1 4.35 -- 1 -- henry
5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa
6 03/23/08 Other Project 1 3.24 -- -- 2 hologa
7 03/23/08 Other Project 2 4.35 -- -- 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=€™ AB Corporate€™ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=€™ AB Plant€™ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=€™ Other Project€™ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.

<!--[endif]-->

View 1 Replies View Related

Writing Alternative Query For Inner Joins

Jun 19, 2007

Hello....
i have 4 tables in a databse(tab1,tab2,tab3,tab4). id is the attribute of TAB1 and id also foreign key of all the tables.....That mean id attribute exists on all the tables........so when i want to query to more than 1 table , i have to go for Joins...basically i am using innerjoin.........
Can u tell me is there any other way to find the same output as the innerjoin......without using the keyword INNER JOIN
for example ......SELECT * FROM TAB1 INNER JOIN (TAB2 INNERJOIN TAB3 ON TAB2.ID=TAB3.ID) ON TAB1.ID=TAB2.ID
 
another query........
SELECT *FROM TAB1,TAB2,TAB3 WHERE (TAB1.ID=TAB2.ID) AND (TAB2.ID=TAB3.ID) AND (TAB3.ID=TAB1.ID)
above queries are same or not??????????

View 1 Replies View Related

Joins Vs Where Clause - Performance Query

Dec 5, 2006

Hi There !!

To finetune performance for some of our queries,

I have come across suggestions to use

- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases

Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ????

Also,

I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )


USING A WHERE CLAUSE and Alias
-------------------------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null

USING A JOIN
-----------------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL

******

I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:

Any comments/ suggestions.

Thanks,

Have a great time
-Ranjit.

-------------------------------------
It pays to be honest to your DBA

View 4 Replies View Related

Query On Performing Recursive Joins

Apr 14, 2007

I have a single table named PROCESS which contain following three fields

ProcessID
ParentID,
info

* Every process have a unique ProcessID and ave single parent process which is identified by ParentID.

* If a process does not have a Parent then its ParentID value is -1.

*Only single level of Parent-child hierarchy is maintained.


Can anyone please tell me the Query that uses Recursive JOINS to retrieve the ProcessID s based in following Conditions.

1. if the Parent's 'info' field contains given value then retrieve all the process under it.
2. Retrive all the process whose 'info' contains given value and excluding the Processes resulted from 1st conditiion

Thanks in advance

Bharath Booshan L

View 2 Replies View Related

Query Optimization Assistance W/ Joins

Jul 20, 2005

I have a couple of tables that look like this (not excactly but closeenough):[Contact]id intfname varchar(50)lname varchar(50)[ContactPhoneNumber]id intnumber varchar(15)ext varchar(6)contact_id intpriority int (indicates primary, secondary... numbers)type int (indicates type of number: fax, cell, land line)I'm looking for a more optimized method of displaying this informationin this format:fname, primary business phoneUsing a derived column like this works, but seems to be slow with manyrecords, despite tuning indexes:SELECT c.fname AS [First Name],( SELECT TOP 1numberFROM ContactPhoneNumber cpnWHERE cpn.type = 1AND cpn.contact_id = c.idORDER BY cpn.priority) AS NumberFROM Contact cI can get the same results using a join, and it's a lot faster. But I'mnot sure how to select only the primary phone number this way...basically the first phone number whose priority is either NULL or 1.Any suggestions?*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!

View 5 Replies View Related

Query With A Lot Of Left Joins - Rewrite?

Sep 21, 2007

Hello,

I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?

How would I rewrite left joins? Any examples?


Thanks.

View 5 Replies View Related

Complex SQL Query - Joins, Max, Union

Sep 15, 2006

How to find maximum value from two tables have the same field name?

For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has
same field name calcuated_price has max value is 3000.

Nishith

View 9 Replies View Related

Need Suggestion On TSql Query With Joins

Sep 27, 2007

Hi All,
Please suggest me is there any performance/other differences between the below two queries.

----query1

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join T2 on T1.EID = T2.EID

----query2

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join (Select Dept, Desig From T2) As T2 on T1.EID = T2.EID


Thanks
Senthil

View 3 Replies View Related

SQL Query Ignoring Null Values In Joins-- Help

Aug 2, 2006

I would like to populate a grid with data from 2 different tables.
Table1: [PK]id(int), name(nvarchar), areaID(int)
Table2: [PK][FK]areaID(int), areaDescription(nvarchar)

My cerrent query is:
SELECT Table1.id, Table1.name, Table2.areaDescription FROM Table1 INNER JOIN Table2 ON Table1.areaID = Table2.areaID

However, sometimes the areaID in Table1 will only be populated at a later stage and therefore will be NULL in Table1. Table2 is used as a lookup table when inserting into Table1. This query therefore ommits any records in Table1 which do not have an areaID. I would like to view ALL records(ones without an areaID as well) as they would be populated in the grid and selected to be updated on web forms because they are incomplete and then subsequently assigned an areaID.

Any help with this query would be much appreciated...

View 2 Replies View Related

Multi Table Query Using Two LEFT JOINs

Jul 24, 2012

I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY.

SELECT
quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining,
machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes
quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id
FROM quote
LEFT JOIN machining_operations
ON machining_operations.quote_num = quote.quote_id
LEFT JOIN parts
ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name
GROUP BY quote.quote_id

View 10 Replies View Related

SQL Server 2012 :: How To Reduce Joins In Query

Nov 13, 2013

I have select query with 10 Joins in place with different i want to improve the performance of the query how do i join tables with out joins.

View 9 Replies View Related

T-SQL (SS2K8) :: Query Using Multiple APPLY Joins

Mar 20, 2014

I’ve never written a query with multiple APPLY joins before and I’m running into some troubles with my first one. The below SQL statement runs within 10 seconds if I comment out either one of the APPLY joins and its corresponding field columns. However, when I try to execute with both APPLY joins, the query runs indefinitely. The longest I’ve waited before cancelling it is 90 minutes.

Now, I know there are probably other ways I could write this query to get me the results I’m looking for. I’m posting this on the board because I’m curious about finding out why multiple APPLY joins could cause SQL Server to run away. I’m hoping to gain some insight so that I can better understand how APPLY joins work so that in case I have a big need to do this again in the future (without suitable workarounds) I can code it correctly.

Here are some things I’ve tried so far…

1.Changed the States table into a subquery that only returns a single state
2.Change all the references inside the APPLY subqueries so that they had different aliases (just in case they were conflicting with each other).
3.Changed the CROSS applies to OUTER applies. States has 50 records and only 32 have matching permit data so the 18 extra iterations using OUTER APPLY don’t impact performance any when an APPLY is used by itself.

SELECT s.state_name
, COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits
, SUM(DistinctPermits) AS DistinctPermits
FROM States S
CROSS APPLY (SELECT w.StateID, COUNT(*) as DistinctPermits

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Removing Not Necessary Joins In Child Query?

May 23, 2014

I am building a application, which will generates a MASTER query with 15 fields & 5 tables joins.Now, the user selects only some fields from that master fields and generates the CHILD query. This is creating a performance issue.

To minimize this, i would like to remove the not necessary joins in the child query.

View 8 Replies View Related

Query To Find Manager Name From Employee Table Without Joins

Oct 25, 2012

Table structure is very simple as below and I know there are solutions with joins (Left outer joins), need to know if it is possible to get o/p without using joins

Note:- also need records who doesn't have manager (null)

table structure
eid------ename------mgrid
1------Nancy------2
2------Andrew------null
3------Janet ------2
4------Margaret------2
5------Steven------4
6------Michael ------5

o/p
Employee------Manager
Nancy------Andrew
Andrew------Null
Janet ------Andrew
.
.

View 9 Replies View Related

Advance Joins - Adding Another Field To Existing Query

Jan 16, 2015

I have this already existing query and want to add another field to it but its failing. Below is the current query

REPLACE(CONVERT(CHAR(10), A.BATDAT, 1), '/', '') + RIGHT('00000' + CONVERT(VARCHAR, A.BATSEQ), 5
RIGHT('000' + CONVERT(VARCHAR, A.SEQNBR), 3) AS CLIENT_TRACK_NO

I want to add a field from a table name APTTL and the filed name is DTYYY char (2)

View 2 Replies View Related

T-SQL (SS2K8) :: Query With Several Joins Fills Up TempDB And Won't Finish

Aug 14, 2014

I have this query I need for a report. Originally it was 4 queries to be used in Crystal Reports. Now I want to create the same report with SSRS and therefore I incorporated all queries in one in order not to use subreports [URL].....

Tempdb fills up to nearly 90 GB. I am running SQL Server on a local box, so I am sure there is no other traffic. Here is the query:

SELECT AdHaupt.NSprache_ID
,AdHaupt.mengentext AS mengentextHaupt
,AdHaupt.Einzelpreis
,AdHaupt.Anzeigebezeichnung
,AdHaupt.Gesamtpreis

[Code] ...

I ran it with TOP 10 as well, just to see if it will finish at all, but it never did (ran for an hour now).

View 9 Replies View Related

Multiple Table Joins Makes Query Go To Sleep

Jul 23, 2005

Hello All & Thanks in advance for your help!Background:1) tblT_Documents is the primary parent transaction table that has 10fields and about 250,000 rows2) There are 9 child tables with each having 3 fields each, their ownPK; the FK back to the parent table; and the unique data for thattable. There is a one to many relation between the parent and each ofthe 9 child rows. Each child table has between 100,000 and 300,000rows.3) There are indexes on every field of the child tables (though Idon't believe that they are helping in this situation)4) The client needs to be presented a view that has 5 of the mainfields from the parent table, along with any and all correspondingdata from the child tables.5) The client will select this view by doing some pattern-matchingsearch on one of the child records' detail (e.g. field-name LIKE%search-item% - so much for the indexes...)Problem:When I do the simple join of just the parent with one of the children,the search works *fairly* well and returns the five parent fields andthe corresponding matching child field.However, as soon as I add any one of the other child records to simplydisplay it's unique data along with the previously obtained results,the resulting query hangs.Is the overall structure of the tables not conducive to this kind ofquery? Is this a situation where de-normalization will be required toobtain the desired results? Or, more hopefully, am I just an idiotand there is some simpler solution to this problem?!Thanks again for your assistance!- Ed

View 9 Replies View Related

Query Optimizer Problems With Many LEFT/RIGHT OUTER JOINs

Mar 14, 2008

I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).

I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run. This looks exactly like this problem described in http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.


Basically I have a query:

SELECT ....
FROM TABLEA

INNER JOIN TABLEB ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'


The query plan for this is using TABLEA as the "main" table and joining everything else to it. The problem is, TABLEA has 117 MILLION records. TABLEB has 10,000 records which match the WHERE. I stopped this query after it ran for 62 HOURS.

If I simply change the query to:

SELECT ....
FROM TABLEB

INNER JOIN TABLEA ...
LEFT OUTER TABLEC...
LEFT OUTER TABLED...
RIGHT OUTER TABLEF...
LEFT OUTER TABLEJ..
LEFT OUTER TABLEH...
LEFT OUTER TABLEI...
RIGHT OUTER TABLEK...
LEFT OUTER TABLEM..
... 17 joined tables in all......
WHERE TABLEB.field1 = 'abc'

The query runs in 15 mins. The query plan now uses TABLEB and the WHERE clause to join all the other tables.

The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code.

Any ideas how I can fix this?

View 1 Replies View Related

Query Doesn't Return Any Records Unless All Joins Have Matches

Jan 1, 2008

Problem is that if the [Receiving] table doesn't have a match then no records are return. I want all matches from the [Orders Subtable] and any matches from the [Receiving] Table. If no [Receiving] table matches then I still want all matches from the [Orders Subtable]. Attached is the query.

Note: The query has to run in Access 2000 and I will be coding it in VB.



SELECT Orders.[Orders ID],
[Orders Subtable].ID,
[Orders Subtable].Quantity,
Receiving.Quantity,
Receiving.[Component #]

FROM (Orders
LEFT JOIN Receiving ON Orders.[Orders ID] = Receiving.[Orders ID])
INNER JOIN [Orders Subtable] ON Orders.[Orders ID] = [Orders Subtable].[Orders ID]

GROUP BY Orders.[Orders ID], [Orders Subtable].ID,
[Orders Subtable].Quantity, Receiving.Quantity,
Orders.[Project #], [Orders Subtable].On_Order,
[Orders Subtable].[Component #],
Receiving.[Component #]

HAVING (((Orders.[Project #])="Speed1aaaaa") AND
(([Orders Subtable].On_Order)=True) AND
(([Orders Subtable].[Component #])="R02101A") AND
((Receiving.[Component #])="R02101A"));

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

SQL Server 2008 :: Left Joins And Query Plan Compile Times

Mar 8, 2015

We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.

So rather then the following exemplary fragment

select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id

Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.

I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.

View 9 Replies View Related







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