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


ADVERTISEMENT

T-SQL (SS2K8) :: Converting OUTER APPLY To LEFT JOIN In A Query

Oct 10, 2014

I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)

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

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

Order Of Inner Joins And Cross Apply

Sep 29, 2015

Will the order of inner joins and cross apply effect the results of a query?

Example:
FROM dbo.vw_Info v
CROSS APPLY dbo.usf_LastFee(v.StoreID, v.AgreementID, v.CustomerID, ABS(v.PrePaymentBalance), cp.ConfirmationNo) lf
INNER JOIN dbo.Customers c

[Code] ....

I want to change the position of doing "CROSS APPLY". Will it effects query results?

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

T-SQL (SS2K8) :: String Occurrence Count With Cross Apply

Jun 17, 2014

See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.

DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))

INSERT INTO @t
SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL
SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL

[Code] .....

View 9 Replies View Related

T-SQL (SS2K8) :: Most Updated Records From Multiple Join Query

Mar 28, 2014

i have Two tables... with both the table having LastUpdated Column. And, in my Select Query i m using both the table with inner join.And i want to show the LastUpdated column which has the maxiumum date value. i.e. ( latest Updated Column value).

View 5 Replies View Related

T-SQL (SS2K8) :: Select Query With Multiple Where Conditions And OR Operator

Feb 6, 2015

I have the got below T SQL query to fetch the matching record

DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int

[Code] .....

-- result - 'Orange'

This is an exact matching record and straight forward

Is it possible to identify the record using T SQL query based on the following scenarios

1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match

-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values

SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1

-- result - 'Orange'

View 4 Replies View Related

T-SQL (SS2K8) :: How To Show Multiple Values In Column Of Join Query

Nov 6, 2014

in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').

i tried in my join query

these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')

its taking so much of time how t o solve ?

View 1 Replies View Related

T-SQL (SS2K8) :: How To Vary Column Names In Cross Apply Based On Different Columns In Each Table

Feb 26, 2015

I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:

select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank

[code]....

Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:

GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)

[Code] ....

These are imported from an Excel Workbook so that's why all the columns with spaces for now.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Split One Record Into Multiple Records In Query Based On Start And End Date

Aug 27, 2014

I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

A random record in my Absences table shows (as an example):

resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)

I would like to have 4 lines in my query:

resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4

Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.

What I now get is:

resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...

... instead of the correct hours per date (8, 8, 8, 4).

A very simplified extract of my code is:

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.

View 1 Replies View Related

Multiple Cross Apply

Nov 14, 2006

Good Afternoon,

I'm attempting to leverage SQL's new 'APPLY" operator and I guess I don't fully understand it proper usage.

This is a relatively simple request, first i want to count the models produced within a valid period of time. The first 'Cross Apply' gets the valid starting and ending dates and looks ups the number of models produced for the period of time. This section of code works perfectly fine.

The problem appears to be with the second "Cross Apply".  What I'm attempting to accomplish is to count all models produced, regardless of time frame.

When executed the query appears to go into an loop and I end up canceling out the request.

Any ideas where I went wrong?? Any help is greatly appreciated!

 

select b1.model                            as Model 
      ,b1.MinDate                            as Mfg_Str_Date
      ,b1.MaxDate                           as Mfg_End_Date
      ,Count(b2.Model+B2.Serial) as Mfg_Date_Valid
      ,Count(b3.Model+B3.Serial) as All_Units

from        (select b.model, min(b.build_date) as MinDate ,max(b.build_date) as MaxDate
             from etbl_models_Serial as b
             group by b.model) as b1

--These are Units produced within Valid Window
cross apply (select b2.model,b2.Serial
             from etbl_Production as b2
             where b2.Model = b1.Model
               and b2.Mfg_Date between b1.MinDate and b1.MaxDate) as b2

--These are all units produced
cross apply (select b3.model,b3.Serial
             from etbl_Production as b3
             where b3.Model = b2.Model) as b3
      
Group by b1.Model, b1.MinDate, b1.MaxDate
Order by b1.Model

View 4 Replies View Related

How To Apply Multiple Insert Statement

Jun 16, 2008

Hello everyone

I am using MS SQL Server Management Studio Express. When I apply multiple insert statement, it doesn't allow me to do so. I have to do it one by one, which is time consuming.

Sample
INSERT INTO table (col_a,col_b) VALUES (1,1)
INSERT INTO table (col_a,col_b) VALUES (2,2)
INSERT INTO table (col_a,col_b) VALUES (3,3)

Any ideas? I want to do it in one go. Thank you very much.

View 1 Replies View Related

Multiple Left Joins (2 Left Joins 1 Big Headache)

Sep 1, 2005

Hi All,

Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.


My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx

I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.

View 2 Replies View Related

Analysis :: How To Pass Apply SSRS Parameters On Multiple Dimension Attributes

Oct 13, 2015

How @StartDate and @EndDate parameters must added to the MDX query for usage in SSRS data set. strtomember can be used like

SELECT (
strtomember(@StartDate) : strtomember(@EndDate)
) ON COLUMNS FROM [Cube]

How can i specify that sub-select must work on the [Fact A] and [Fact B] rundate? strtomember(@StartDate) does not specify on which attribute this sub select is going to work. Any pointers ?

FROM
(
SELECT
(
[Fact B].[Rundate].&[2015-01-02T00:00:00] : [Fact B].[Rundate].&[2015-01-15T00:00:00]
) ON COLUMNS FROM
(
SELECT
( [Fact A].[Rundate].&[2015-01-02T00:00:00] : [Fact B].[Rundate].&[2015-01-15T00:00:00] ) ON COLUMNS FROM [Cube]
)
)

View 2 Replies View Related

Multiple Joins - Need Help

Aug 23, 2007

have the following code for ONE Inner Join, but I want to add another join for another Table and Fields.... can you help me with the syntax:
SELECT DISTINCT

View 1 Replies View Related

Multiple Inner Joins

Feb 19, 2008

I cant find the problem with this query for the life in me
 SELECT ForumTopic.*, websiteinfo.shortdomainname AS author, MemberInfo.postcount AS pc, MemberInfo.joined AS jd FROM ForumTopic INNER JOIN websiteinfo ON ForumTopic.domaininfoid=websiteinfo.domaininfoid INNER JOIN websiteinfo websiteinfo2 ON MemberInfo.domaininfoid=websiteinfo2.domaininfoid WHERE ForumTopic.id = 1
 the error message is:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.domaininfoid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.postcount" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "MemberInfo.joined" could not be bound.
 
Can anyone help with this error? Thanks for any  responses

View 3 Replies View Related

Multiple Joins

Feb 3, 2006

Hello,

I need to create a view which links 5 tables as follows:
I have a Header Table which is keyed on Product and Year which I want to join to a Detail Table which is keyed on Product and Year and Week. I want to see all of the rows from each table, which I think is a FULL OUTER JOIN.

I then have three subsidiary tables for Sales, Orders and Deliveries which are all keyed on Product and Year and Week - I want to join each of these tables separately to the Detail table above so that again I see all of the rows from the Detail Table, the Sales Table, the Orders Table and the Deliveries table. For any Product/YearWeek there may or may not be a row on any of the Sales, Order or Deliveries table, but there will not be any rows on these tables which are not on the Detail Table.

Can I do this in the FROM clause andnif so how, or do I need to do a series of separate SELECTs for the Sales, Orders & Deliveries table with UNION clauses.
Best regards
Colin

View 1 Replies View Related

Multiple Self Joins

Mar 28, 2012

Application called Filemaker, I'm now trying to use that experience to teach myself SQL (using Microsoft SQL is SQL 2008 r2).My question is can you have multiple self joins.

I have a table which has two columns (for this example)

spell | spellindicator | episode

the spell is not unique e.g. there could be several records with the spell 'A1234' the spellindicator column will contain a number 1 or 2. the data could look like
this:

spell-------spellindicator---------episode
A1234-----------1-----------------2
B5674-----------1-----------------1
C8739-----------2-----------------3
A1234-----------1-----------------1
B5674-----------2-----------------1
A123------------2-----------------2
A1234-----------1-----------------3

I want to create two left self joins:

1) to count the records that have the same order number with the spellindicator being 1.
2) to find the max and min episode

For 1) I've created two keys:

pk_indicator = spell +':1'
fk_indicator = spell + ':' + spellindicator

example data:
pk_indicator------fk_indicator
A1234:1-----------A1234:1
B5674:1-----------B5674:1
C8739:1-----------C8739:2
A1234:1-----------A1234:1
B5674:1----------- B5674:2
A1234:1-----------A1234:2
A1234:1-----------A1234:1

For2) The join is on the spell

Part 1) works fine on its own
Part 2) works fine on its own

But put both joins in the same statement and the count function no longer works correctly. I realise that there is an issue with the self joins but I thought it would be possible to have multiple self joins.

use leis
go
select l1.spell, l1.spellindicator, l1.episode, l1.pk_Indicator, l1.fk_indicator
,maxepisode = MAX(e2.episode)
,minepisode = MIN(e2.episode)

[code]....

View 2 Replies View Related

Multiple Inner Joins

Oct 23, 2007

I have a customer table which has customer billing addresses; an orders table which has payment and shipping method info; and an items table which has line items for each order. The customer table has a custnum index field. The orders table has a custnum and orderid field. And the items table has an orderid field.

I'd like to be able to retrieve, in one query, the customer's billing address, all of their orders, with each of their order's line items.

What would the query look like in order to accomplish this?

Thanks in advance.

View 1 Replies View Related

T-SQL (SS2K8) :: Selecting Data From Table With Multiple Conditions On Multiple Columns

Apr 15, 2014

I am facing a problem in writing the stored procedure for multiple search criteria.

I am trying to write the query in the Procedure as follows

Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3

I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.

View 4 Replies View Related

CROSS APPLY Vs OUTER APPLY Example Messed Up?

Nov 27, 2007

Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.

On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:




Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END



and then run a sample T-SQL statement





Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc

My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?

When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.

Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.

So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?

(Please refrain from introducing another example into this question.)

View 8 Replies View Related

I'm Having Trouble With Doing Multiple Joins

Mar 7, 2001

Here is what I am currently doing:
********************************************
SELECT Hours.Hours, Hours.Comments
FROM Hours INNER JOIN Employee
ON Employee.UserID = Hours.UserID
INNER JOIN Task
ON Hours.TaskID = Task.TaskID
INNER JOIN Project
ON Hours.ProjID = Project.ProjID
WHERE Hours.Date <= EndDate
AND Hours.Date >= StartDate
AND Hours.Date <= EndDate;
********************************************
Am I doing something wrong here?
Any help would be greatly appreciated!

View 2 Replies View Related

Multiple Joins To The Same Table

Oct 29, 2006

I'm trying, with little success, to achieve something that should be quite easy (I think!) and any advice would be appreciated.

I have a leagues table structured so:

LeagueID | Name | Player1 | Player 2 ... Player6

and the data in the player columns is a userid from the users table and I'm trying to display the Leagues but with the player names rather than player IDs.

I'm working along the lines of


Code:

select
u1.displayname as Player1,
u2.displayname as Player2
from DCMLeagues as L
inner join Users as u1 on L.player1 = u1.userid
inner join Users as u2 on L.player2 = u2.userid



but with little success so far. Any thoughts would be appreciated! Thanks very much in advance.

-- Chris

View 5 Replies View Related

Multiple Joins To Same Column

Apr 9, 2012

I'm putting together the site for a local darts league, essentially each of the teams completes an 'e-scorecard' for that particular game. This is then stored in a table named 'scorecardIndex' :

scxUID(PK)scxTeam1(int)scxTeam2(int)scxTeam1Capt(int)scxTeam2Capt(int)scxDate(nvarchar 50)scxSubmitBy(int)

So each 'index' points off to the UID of the player who was captain, and the UID of of each team.

What I'm struggling with is joining this all together, as I'm joining both scxTeam1Capt and scxTeam2Capt to the same columns in the 'players' table (plFirstName, plSurname)

plUID(PK)plFirstName(nvarchar 50)plSurname(nvarchar 50) plTeamUID(int)plEmail(nvarchar80)plPassword(nvarcharMAX)

And equally joining scxTeam1 & scxTeam2 to the Teams table to pull back the teams actual name.

Using several inner joins I can pull back the entire row for each team, but the columns have the same name..

SELECT *
FROM scorecardIndex AS S
INNER JOIN teams AS T1
ON T1.teamUID = S.scxTeam1
INNER JOIN teams AS T2
ON T2.teamUID = S.scxTeam2

I'm using MS SQL Server 2008 R2.

I've tried to be as detailed as possible without overdoing it.

View 3 Replies View Related

Multiple Left Joins Help?

Apr 27, 2004

Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong.



These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this



emailDetails table ( emailID_PK,emailName,emailText,emailSubject,emailN otificationTypeID)

emailRecipients table (emailID_PK, RecipientID, SentToEmail)

luEmailNotificationTypes look up table (emailNOtificationTypeID_PK, emailNotificationTypeName)



Thanks

View 2 Replies View Related

Joins In Multiple Tables

Dec 7, 2013

Table 1:

id amount
1 100
2 200
3 300
4 400

Table 2:

id amount
1 100
1 100
2 200
3 300
4 null

Table 3:

id amount
1 null
2 200
2 200
3 300
3 200
4 null

id is common for each tables , how can i get output like this:

Collapse | Copy Code

id t1 t2 t3
1 100 200 null
2 200 200 200
3 300 300 500
4 400 null null

I am stuck with this query .

View 1 Replies View Related

New Column In Multiple Joins

Aug 21, 2014

In the below query

with cte1
as (select siteid
,productcategory
,sum(isnull(netsales,0)) as netsales
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())

[Code] ....

I need to add routeid column so that i can finally display sum of sales grouped by routeid in addition to the siteid that i am already displaying.

View 4 Replies View Related

Joins Between Multiple Tables?

Feb 4, 2015

I have a general question concerning joins. Below is a table scenario:

SELECT *
FROM TABLE_A T0
INNER JOIN TABLE_B T1 ON T1.[Some_Column] = T0.[Some Column]
LEFT JOIN TABLE_C T2 ON T2.[Some_Column] = T0.[Some Column]

Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned, then the records from TABLE_C will be joined to the initial 'result set' (that is the result of joining TABLE_A & TABLE_B), or will TABLE_C simply be joined to TABLE_A regardless of the inner join between TABLE_A & TABLE_B?

View 1 Replies View Related

Multiple Joins In SQL Server

Jan 4, 2008

Hi I have a requirement where in i haev to convert the SQL from Oracleto the one which will run on the SQL server.in the Oracle Query i am doing multiple joins, between some 13 tables.and some of these joins are inner joins and some are Left outer joins.table1 inner joined with table 2table2 inner join with table3table2 inner join with table4table2 left join with table5table5 left jin with table6table6 left jin with table7table7 left jin with table8table8 left jin with table9Any idea how to achieve this??Tianaren

View 2 Replies View Related

Problem With Multiple Joins.

Jul 20, 2005

Hi All Database Gurus,I am trying to write code which will produce all the possible validqueries, given tables and join information for tables.Right now i am just trying to construct all the sequential joins.eg. if i have 4 tables A, B, C, D and the join conditions areA Inner join B,B Inner Join C,C Left Outer join Dthen i am constructing joins as :1. A IJ B IJ C LJ D2. B IJ A IJ C LJ D3. B IJ C IJ A LJ D4. B IJ C LJ D IJ A5. C IJ B IJ A LJ D6. C IJ B LJ D IJ A7. C LJ D IJ B IJ AI am not placing any paranthesis to specify the join order. And manyof them are giving me same output.Can anybody tell me how to detect the joins which will give the sameoutput ?here in this case the number of combinations are 7 but for 8 tables iam getting 420 combinations and many of them are same.so please help me to reduce the number of combinations.eagerly waiting for suggetions.Thanking you.Prem.(premratan@hotmail.com)

View 1 Replies View Related

Multiple Joins In A Filter

Feb 15, 2007

I'm using Sql 2005 and merge replication.

I want to create a dynamic merge filter on a table but the data I filter on is only reachable by a few extra joins. For example:

I want to filter Table3. Table3 is joined to Table2 which in joined to Table1. Table1 contains the host_name column that I use to filter i.e. host_name = HOSTNAME().

In order to get filtered data in Table3 do I have to also filter Table 2 and Table1, or in my filter clause for Table3 can I say something like: Table3.FKid = Table2.Id and Table2.FKId = Table1.Id and Table1.host_name = HOSTNAME()?

I'm pretty sure I can't use AND in my dynamic filter clause but I just wanted to make sure.

Thanks for your help

Graham

View 1 Replies View Related







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