Multiple joins in SQL Server

Hi I have a requirement where in i haev to convert the SQL from Oracle
to 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 2
table2 inner join with table3
table2 inner join with table4
table2 left join with table5
table5 left jin with table6
table6 left jin with table7
table7 left jin with table8
table8 left jin with table9
Any idea how to achieve this'
Tia
naren"narendra vuradi" <nvuradi@.gmail.com> wrote in message
news:aad31742-b563-4da4-8a37-4be71ebf81c7@.e23g2000prf.googlegroups.com...
> Hi I have a requirement where in i haev to convert the SQL from Oracle
> to 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 2
> table2 inner join with table3
> table2 inner join with table4
> table2 left join with table5
> table5 left jin with table6
> table6 left jin with table7
> table7 left jin with table8
> table8 left jin with table9
> Any idea how to achieve this'
> Tia
> naren
Using standard SQL (rather than the proprietary Oracle syntax) it should
look pretty close to the way you wrote it:
SELECT ... /* columns */
FROM table1
INNER JOIN table2
ON table1.col = table2.col
INNER JOIN table3
ON table2.col = table3.col
INNER JOIN table4
ON table2.col = table4.col
LEFT OUTER JOIN table5
ON table2.col = table5.col
LEFT OUTER JOIN table6
ON table5.col = table6.col
LEFT OUTER JOIN table7
ON table6.col = table7.col
LEFT OUTER JOIN table8
ON table7.col = table8.col
LEFT OUTER JOIN table9
ON table8.col = table9.col ;
--
David Portas|||Hi
In addition to David's suggestion , I'd note you that JOIN 13 tables does
not look good in terms of database/application design as well as performance
"narendra vuradi" <nvuradi@.gmail.com> wrote in message
news:aad31742-b563-4da4-8a37-4be71ebf81c7@.e23g2000prf.googlegroups.com...
> Hi I have a requirement where in i haev to convert the SQL from Oracle
> to 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 2
> table2 inner join with table3
> table2 inner join with table4
> table2 left join with table5
> table5 left jin with table6
> table6 left jin with table7
> table7 left jin with table8
> table8 left jin with table9
> Any idea how to achieve this'
> Tia
> naren

Multiple joins in For XML Auto, Elements

Hi,
I'm trying to join a number of tables to produce an xml from a stored
procedure. The trouble is that the results from every new table I join get
produced as a child node to the previous table's node.
Select BG.*, CA.*, NA.*, ET.*
From BG (nolock)
Left Outer Join CA (nolock) On BG.ID = CA.BGID
Left Outer Join ET (nolock) On ET.CAID = CA.ID
Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
Results in the follwing structure
<BG>
<CA>
<NA>
<ET></ET>
</NA>
</CA>
</BG>
Is there a way I can make the xml structure come out as
<BG>
<CA>
<NA></NA>
<ET></ET>
</CA>
</BG>
Regards."Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> Hi,
> I'm trying to join a number of tables to produce an xml from a stored
> procedure. The trouble is that the results from every new table I join get
> produced as a child node to the previous table's node.
> Select BG.*, CA.*, NA.*, ET.*
> From BG (nolock)
> Left Outer Join CA (nolock) On BG.ID = CA.BGID
> Left Outer Join ET (nolock) On ET.CAID = CA.ID
> Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
> Results in the follwing structure
> <BG>
> <CA>
> <NA>
> <ET></ET>
> </NA>
> </CA>
> </BG>
> Is there a way I can make the xml structure come out as
> <BG>
> <CA>
> <NA></NA>
> <ET></ET>
> </CA>
> </BG>
> Regards.
It gets even worse than that if NA or ET return multiple rows. I finally
gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the structure
I wanted. I expect you will need to do the same.|||This is by design (and is explained in the documentation).
The automode looks at the data lineage and not the query string. Thus it
does not know how the data has been joined in. So it assumes a single
nesting hierarchy.
To get sibling trees, you need to use the explicit mode in SQL Server 2000,
or you can use the much simpler nested FOR XML expressions if you can move
to SQL Server 2005.
Best regards
Michael
"Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
> "Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
> news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> It gets even worse than that if NA or ET return multiple rows. I finally
> gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the
> structure I wanted. I expect you will need to do the same.
>|||thanks Andy / Michael,
will do, till i migrate to 2005
"Michael Rys [MSFT]" wrote:

> This is by design (and is explained in the documentation).
> The automode looks at the data lineage and not the query string. Thus it
> does not know how the data has been joined in. So it assumes a single
> nesting hierarchy.
> To get sibling trees, you need to use the explicit mode in SQL Server 2000
,
> or you can use the much simpler nested FOR XML expressions if you can move
> to SQL Server 2005.
> Best regards
> Michael
> "Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
> news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
>
>sql

Multiple joins in For XML Auto, Elements

Hi,
I'm trying to join a number of tables to produce an xml from a stored
procedure. The trouble is that the results from every new table I join get
produced as a child node to the previous table's node.
Select BG.*, CA.*, NA.*, ET.*
From BG (nolock)
Left Outer Join CA (nolock) On BG.ID = CA.BGID
Left Outer Join ET (nolock) On ET.CAID = CA.ID
Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
Results in the follwing structure
<BG>
<CA>
<NA>
<ET></ET>
</NA>
</CA>
</BG>
Is there a way I can make the xml structure come out as
<BG>
<CA>
<NA></NA>
<ET></ET>
</CA>
</BG>
Regards.
"Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> Hi,
> I'm trying to join a number of tables to produce an xml from a stored
> procedure. The trouble is that the results from every new table I join get
> produced as a child node to the previous table's node.
> Select BG.*, CA.*, NA.*, ET.*
> From BG (nolock)
> Left Outer Join CA (nolock) On BG.ID = CA.BGID
> Left Outer Join ET (nolock) On ET.CAID = CA.ID
> Left Outer Join NA (nolock) On NA.CAID = CA.ID FOR XML AUTO, ELEMENTS
> Results in the follwing structure
> <BG>
> <CA>
> <NA>
> <ET></ET>
> </NA>
> </CA>
> </BG>
> Is there a way I can make the xml structure come out as
> <BG>
> <CA>
> <NA></NA>
> <ET></ET>
> </CA>
> </BG>
> Regards.
It gets even worse than that if NA or ET return multiple rows. I finally
gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the structure
I wanted. I expect you will need to do the same.
|||This is by design (and is explained in the documentation).
The automode looks at the data lineage and not the query string. Thus it
does not know how the data has been joined in. So it assumes a single
nesting hierarchy.
To get sibling trees, you need to use the explicit mode in SQL Server 2000,
or you can use the much simpler nested FOR XML expressions if you can move
to SQL Server 2005.
Best regards
Michael
"Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
> "Himanshu" <Himanshu@.discussions.microsoft.com> wrote in message
> news:DE807BF9-0FD3-4E76-B617-219E3F96F752@.microsoft.com...
> It gets even worse than that if NA or ET return multiple rows. I finally
> gave up on FOR XML AUTO and used FOR XML EXPLICIT to generate the
> structure I wanted. I expect you will need to do the same.
>
|||thanks Andy / Michael,
will do, till i migrate to 2005
"Michael Rys [MSFT]" wrote:

> This is by design (and is explained in the documentation).
> The automode looks at the data lineage and not the query string. Thus it
> does not know how the data has been joined in. So it assumes a single
> nesting hierarchy.
> To get sibling trees, you need to use the explicit mode in SQL Server 2000,
> or you can use the much simpler nested FOR XML expressions if you can move
> to SQL Server 2005.
> Best regards
> Michael
> "Andy Walldorff" <andy.walldorff@.daytonrcs.REMOVE.com> wrote in message
> news:e58nbLWMGHA.720@.TK2MSFTNGP14.phx.gbl...
>
>

Multiple joins in a filter

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

The recommended way would be:

1. On T1: myFilterColumn=HOST_NAME()

2. On T2: add a join filter with sp_addmergefilter - T2.c1=T1.c1

3. On T3: add a join filter with sp_addmergefilter - T3.c3=T2.c2

Multiple Joins - Need Help

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

CTR.ReqID, CTR.SpecimenID, CTR.LabID, CTR.ProcedureID, CTR.TestID, CTR.Isolate, CTR.Problem,

CTR.ProblemComments, CP.ProcedureID, CP.Description AS CPProcedureDescription

FROM ClinicalTestsRequested CTR inner join ClinicalProcedures CP

ON CTR.ProcedureID=CP.ProcedureID

WHERE (CTR.SpecimenID = @.Accession)

I want to add another Join to the above where:

Table = ClinicalTests CT
Fields = CT.TestID, CT.Description AS CTTestDescription

and Compare = CTR.TestID to CT.TestID

Thanks !!

after your on clause for the first inner join add:

inner join clinicalTests ct on ctr.testid = ct.testid

add your columns to the list in the select.

Better yet, use sql server management studio express to design the query for you (it doensn't matter if your database is sql2005 or 2000). Create a new query for one of your tables and then use the query designer. This will allow you to drag and drop joins, make them into outer joins, add columns, etc, using a design tool.

--JJ

Multiple Joins

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
ColinIf there are no detail Product & Years which are not present in the Header table, then this sounds like a left outer join. The rest sound like inner joins.

If I'm understanding your intention, the following (untested) should work :

select h.*,
d.*,
s.*,
de.*
from Header h left outer join
Detail on h.product = d.product and h.year = d.year inner join
sales s on s.product = d.product and s.year = d.year and s.week = d.week inner join
Orders o on o.product = d.product and o.year = d.year and o.week = d.week inner join
Deliveries de on de.product = d.product and de.year = d.year and de.week = d.week

Perhaps I'm misunderstanding what you're wanting to see...

Multiple Join Query Problem

I am trying to update a field in TblA based on the contents of a field in
TblB where 4 conditions match but I can't get it to work. ere is what I
tried:
Update TblA t Set [Show Name] =
(Select Contest From TblB s
Where s.Date = t.Date,
s.RegionalCode = t.Regional,
s.unit = t.unit,
s.PF = t.[P or F])
How should that be coded?Update TblA Set [Show Name] =
(Select Contest From TblB s
Where s.Date = TblA.Date and
s.RegionalCode = TblA.Regional and
s.unit = TblA.unit and
s.PF = TblA.[P or F])|||Thanks. I was off on the wrong track.
Wayne
<markc600@.hotmail.com> wrote in message
news:1144491686.935456.163560@.v46g2000cwv.googlegroups.com...
> Update TblA Set [Show Name] =
> (Select Contest From TblB s
> Where s.Date = TblA.Date and
> s.RegionalCode = TblA.Regional and
> s.unit = TblA.unit and
> s.PF = TblA.[P or F])
>|||You might want a where clause in your query:
Update TblA Set [Show Name] =
(Select Contest From TblB s
Where s.Date = TblA.Date and
s.RegionalCode = TblA.Regional and
s.unit = TblA.unit and
s.PF = TblA.[P or F])
where exists
(Select Contest From TblB s
Where s.Date = TblA.Date and
s.RegionalCode = TblA.Regional and
s.unit = TblA.unit and
s.PF = TblA.[P or F])sql