Difference On Condition In JOIN && WHERE

May 3, 2007

I appreciate how difficult it is to resolve a problem without all the
information but maybe someone has come across a similar problem.

I have an 'extract' table which has 1853 rows when I ask for all rows where
period_ = 3. The allocation table for info has 210 rows.

I have two scripts below. The first script where I specify a period on a
join, brings back 1853 lines and works. The second script where I specify
the period in the where clause only brings back 1844 rows. I have located
the missing 9 rows and they don't look any different to the other 1844 rows.

Can someone educate me as to the difference between specifying a condition
on a join and a condition in a where clause.

SELECT
a.costcentre_,
b.nett_,
a.*,
b.*

FROMextract a

LEFT OUTER JOIN
allocation b

ONa.e_reg_ = b.reg_no_
ANDb.period_ = 3
WHERE
a.period_ = 3


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


SELECT
a.costcentre_,
b.nett_,
a.*,
b.*

FROMextract a

LEFT OUTER JOIN
allocation b

ONa.e_reg_ = b.reg_no_


WHERE
a.period_ = 3
ANDb.period_ = 3

View 4 Replies


ADVERTISEMENT

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

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

Return Difference Of Two Stamps In Table Based On Some Condition

Aug 10, 2015

I have table that contains below data

CreatedDate                ID             Message
 2015-05-29 7:00:00      AOOze            abc
 2015-05-29 7:05:00      AOOze            start
 2015-05-29 7:10:00      AOOze            pqy
 2015-05-29 7:15:00      AOOze            stop
 2015-05-29 7:20:00      AOOze            lmn   

 and so on following the series for every set of same ID with 5 entries for each ID

I need to Find Maximum interval time for each ID and for condition in given message (between message like Start and Stop) I used below query and it works fine

select Id, max(CreatedDate) AS 'MaxDate',min(CreatedDate) AS 'MinDate',
DATEDIFF(second,min(CreatedDate),max(CreatedDate)) AS 'MaxResponseTimeinSeconds' from Table where Id in (
SELECT distinct Id
from Table
where Message like  'stop')
group by Id

Above query displays max response time for ID A00ze as 20 minutes, but stop message has occured at 7.15. I would need to modify the query to return max response time  as 15 min(from 7.00 to 7.15).

Difference of starttime(where A00ze id started) and stoptime(where stop string is found in message).

View 7 Replies View Related

IF Condition In Join??

Jul 20, 2005

Dear GroupI'd be grateful if you can send me on the right track in achieving this.I have three tables A,B,C outlined as follows:Table: AField: RowIDField: EntityIDField: TypeIdentifierTable: BField: RowIDField: NameTable: CField: RowIDField: NameLet's assume I've the following records:Table A:1,1,02,1,1Table B:1,SmithTable C:1,XYZCorporationThe table joins are as follows:A.EntityID = B.RowIDA.EntityID = C.RowIDI would like to select all records from Table A and display the Names fromeither Table B or Table C, depending on the Field TypeIdentifier.E.g.: SELECT Name FROM A JOIN B ON (A.EntityID = B.RowID) JOIN C ON(A.EntityID = C.RowID) IF TypeIdentifier = 0 SELECT Name FROM B IFTypeIdentifier = 1 SELECT Name FROM CResultset:SmithXYZCorporationIs this somehow possible?Thanks very much for your time & efforts!Martin

View 3 Replies View Related

JOIN Based On LIKE Condition

Apr 6, 2008

I'm trying to join two tables based on a like condition. The first table contains the full IP, e.g. '166.27.12.24' and the second contains a 2 octet range, e.g. '166.27', which I need to join.

Table 1 -> TRAFFIC (Time, SourceIP)
Table 2 -> IP_ROSTER (IP2OctetRange, Administrator)

I've tried the following, but it does not seem to work:


SELECT TOP 100
SOURCE_IP,
r.IP2OctetRange,
r.Administrator
FROM TRAFFIC
LEFT JOIN IP_ROSTER AS r
ON SOURCE_IP LIKE RTRIM(LTRIM(IP2OctetRange))+'%'

View 8 Replies View Related

SQL-Question: Natural Join With Condition

Nov 10, 2007

 Hello! I have two tablestable1:                     table2:person                     Manager----------                     -----------------*id                           *id*Manager_id             *Name*Branch                   *Title Now I want to select person.* and Mangager.Name, where person.Manager_id=Manager.id.Ok, that would be easy for me, but the point is, that the column person.Manager_id might be empty or =0 (as integer), and in that case I want to have the person.* data too.So, something like Select person.*, Manager.id From person, Manager Where person.id=4 AND person.Manager=id=Manager.id doesn't work. Can somebody explain me how to fix it? I'm using MS-SQL2000 if it is important.... Best regards! 

View 1 Replies View Related

Condition In Left Outer Join

Jan 22, 2008

Hi, 
I want to get join of two tables A and B in such a way that all the Data from A for a particular Date should be extracted and then from table B Reqired column should selected against that data (it can be null). But if i use the following query:
1) Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same Where A.Date = '1/22/2008'
it first joins the Table A and B (A contains millions of records) which takes a lot of time and then where is applied which takes a lot of time.
And if I use the following query:
2)Select A.*,B.Reqired from A Left Outer Join B on A.Same=B.Same AND A.Date = '1/22/2008'
in this case A.Date = '1/22/2008'  has no effect and all the data from the table A comes.
I have also tried first making the temp table for A.Date = '1/22/2008' and the joining with B but its cost is same as 1.
For this purpose Oracle allows the use of (+) operator like:
3)Select A.*,B.Reqired from A,B 
Where A.Same=B.Same(+) AND A.Date = '1/22/2008'  which means all data from A for '1/22/2008' Plus B.Required against it whether its null or not.
Please let me know if there is a way around in MS SQL SERVER 2005.
Thanx.
 
 

View 8 Replies View Related

Opinion/Standard For Join Condition

Jul 30, 2007

Hi,

I was having a conversation with another developer about the order that the join conditions are placed. More specifically, about the order the tables are referenced in the join condition. There are two ways to do it, for example:-- Here the table that is referenced first was "declared" first.
SELECT*
FROM
Logon
LEFT OUTER JOIN
Thread
ON Logon.LogonID = Thread.LogonID
LEFT OUTER JOIN
Message
ON Thread.ThreadID = Message.ThreadID

-- Here the table that is referenced first is the table being joined directly above it.
SELECT*
FROM
Logon
LEFT OUTER JOIN
Thread
ON Thread.LogonID = Logon.LogonID
LEFT OUTER JOIN
Message
ON Message.ThreadID = Thread.ThreadID
I realize this is not that big of a deal, but I was wondering if anyone had a good/valid reason for doing one versus the other.

Just curious,

-Ryan

View 10 Replies View Related

Some Question About Left Join On Condition?

Oct 31, 2007

Left join on condition has no effect in the left table itself?
I have two tables:
one named student

studentID studentName
-------------------------------------------------- --------------------------------------------------
1 Jom
2 Jim
3 Tom
4 Kate
5 LinDa
6 DaiAnna


The other is grade table

studentID subject grade
-------------------------------------------------- -------------------------------------------------- -----------
1 math 90
2 Chinese 90
1 Chinese 80
3 math 98
4 math 76


And I write two SQLs
1:
select students.studentName , grades.grade as math_grade
from student as students
left outer join grade as grades on students.studentID = grades.studentID and grades.subject = 'math'
2:
select students.studentName , grades.grade as math_grade
from grade as grades
left outer join student as students on students.studentID= grades.studentID and grades.subject = 'math'

After execute, the answer is
1:
studentName math_grade
-------------------------------------------------- -----------
Jom 90
Jim NULL
Tom 98
Kate 76
LinDa NULL
DaiAnna NULL

2:
studentName math_grade
-------------------------------------------------- -----------
Jom 90
NULL 90
NULL 80
Tom 98
Kate 76


The math_grade of the second answer is all the grade rows of the grade table. on grades.subjec='math' has no effect.
Why SQL will act it like this? I know it's not a bug. But I really wanna know the inside work.
Thanks in advance, with very appreciation.

View 4 Replies View Related

UPDATE Statement With A JOIN Condition

Jun 30, 2006

Hi all,

HERE IS MY UPDATE STATEMENT:

Update WACTS_Inventory_Part_Loc_Tbl SET WIPLT_Part_New_Qty = WIPLT.WIPLT_Part_New_Qty + tmp.MATIN_QTY FROM

WACTS_Inventory_Part_Loc_Tbl WIPLT

INNER JOIN

Temp_MatIn_Data tmp

ON

WIPLT.WIPLT_Part_Number_MSBA=tmp.PARTS_ShortID

WHERE

WIPLT.WIPLT_Location_Code='MF'



I have a Problem with this satment becoz my Temp_Matin_Data_Tmp table contains two records with the same PartNumber that is (ShortId) and the two records have two different quantities.

The WACTS_Inventory_Part_Loc_Tbl has only one Part NUmber Record and the key is the PartNUmber and Location_Code.



Now when the Update Satement runs it updates the qty only from the first record of the temp table and does not update the quantity of the second record from tne source table.

I woould appreciate any help on this.



Thanks,

Jothi

View 4 Replies View Related

Set Difference (I Think) Join

Jul 20, 2005

I needed to get a list of rows from a table that is not present inanother table.My problem lies in the fact that I only want SOME of the rows in table2 used in determining existance. This happens because table 2 containshistorical data (based on report date). Table 1 contains my productionlist.I am able to get most of the code done but cannot seem to incorporatethe reportdate column.Based on the code below my output should be as follows:ReportDate = '20031229'Output = 0 rowsReportDate = '20031230'Output = DIA, 123456789ReportDate = anything elseOutput = QQQ, abcdefghiDIA, 123456789CREATE TABLE [Indices] ([Symbol] VARCHAR(10),[Identity] VARCHAR(10))CREATE TABLE [ClearingIndices] ([ReportDate] DATETIME,[Symbol] VARCHAR(10),[Identity] VARCHAR(10))GOINSERT INTO [Indices] VALUES ('QQQ', '123456789')INSERT INTO [Indices] VALUES ('DIA', 'abcdefghi')INSERT INTO [ClearingIndices] VALUES ('20031229', 'QQQ', '123456789')INSERT INTO [ClearingIndices] VALUES ('20031229', 'DIA', 'abcdefghi')INSERT INTO [ClearingIndices] VALUES ('20031230', 'QQQ', 'abcdefghi')GOSELECTI.[Symbol], I.[Identity]FROM[Indices] ILEFT OUTER JOINClearingIndices CIONCI.[Symbol] = I.[Symbol]AND CI.[Identity] = I.[Identity]WHERE--CI.[ReportDate] = '20031230'CI.[Symbol] IS NULLAND CI.[Identity] IS NULL

View 1 Replies View Related

SQL Server 2008 :: Inner Join With Complex Condition

Mar 23, 2015

I have Two tables @master and @child

Master Table :

MasterID EntryNumber BranchId IsstockIn
1 1 1 1

2 1 1 0

Child Table:

CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4

My Query:

Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)

My Result:

EntryQty
10
20
-5
-4

Expected Result:

10
20

View 6 Replies View Related

SQL Server 2008 :: Adding Condition Within Inner Join

Jul 13, 2015

I am looking for a query where in I can have a conditional statement within inner join after ON statement as shown below.

Declare @roleid int
select @roleid = roleid from Role where Name ='Admin'
select empid,empName,deptName from employee em
inner department dm on CASE when @roleid>0 then 1=dm.RoleId else em.RoleId=dm.RoleId end

View 1 Replies View Related

Diff Between LEFT OUTER JOIN And *= When Condition

Jul 23, 2005

Using SqlServer :Query 1 :SELECT def.lID as IdDefinition,TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetourFROM serveur.Data_tblDEFINITIONTABLEDECODES def,serveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeurwhere def.TYPEDETABLEDECODES = 4and TDC_AUneValeur.PERIODE_ANNEEFISCALE_ID = 2and def.lID *= TDC_AUneValeur.DEFINITIONTABLEDECODES_DEFINITION_I DQuery 2 :SELECT def.lID as IdDefinition,TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetourFROM serveur.Data_tblDEFINITIONTABLEDECODES def LEFT OUTER JOINserveur.Data_tblTABLEDECODEAUNEVALEUR TDC_AUneValeurON def.lID = TDC_AUneValeur.DEFINITIONTABLEDECODES_DEFINITION_I Dwhere def.TYPEDETABLEDECODES = 4and TDC_AUneValeur.PERIODE_ANNEEFISCALE_ID = 2The query 1 returns :IdDefinition ValeurDeRetour23 null24 null25 null29 36The query 2 returns :IdDefinition ValeurDeRetour29 36The first result is the good one.How is it that the second query doesn't return the same resultSet ?I've been told about problems comparing NULL ???What is the solution ???Thanks a lot.Damien

View 3 Replies View Related

Difference Between LEFT N RIGHT JOIN

Dec 6, 2005

Hi,
I have got a query for you guys. Can any one temme the difference between

"FROM Table_A LEFT JOIN Table_B" and "FROM Table_B RIGHT JOIN Table_A"



;)
Thanks,
Rahul Jha

View 2 Replies View Related

Difference Between Inner Join And Intersection

Aug 14, 2006

Hi Can anybody explain me what is the difference between inner join and intersection?

I prepare a query but it shows the same results then why we need two functions like this to perform same operation

Thanx-Nagu

View 4 Replies View Related

Difference Between Explicit Inner Join And Implicit

Mar 31, 2008

Is there any difference between explicit inner join and implicitinner joinExample of an explicit inner join:SELECT *FROM employeeINNER JOIN departmentON employee.DepartmentID = department.DepartmentIDExample of an implicit inner join:SELECT *FROM employee, departmentWHERE employee.DepartmentID = department.DepartmentID

View 11 Replies View Related

SQL Server 2012 :: Generate Flag To Check Whether Join Condition Match Or Not

Oct 12, 2015

I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?

**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.

example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia

example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
5 USA America

Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N

View 3 Replies View Related

SQL 2012 :: Difference Between Logical And Physical Join?

Jul 9, 2014

What is the difference between Logical Join and Physical Join and there Types?

View 6 Replies View Related

Transact SQL :: How To Get First Table All Rows In Left Join If Condition Is Applied On Second Table

Aug 15, 2015

I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-

Faculty_Subject.Class_Id=@Class_Id

Then i don't get all subjects from subject table, how this can be achieved.

Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN

[code] ....

View 9 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 I Print The Results Of A Condition Based On The Condition?

Feb 9, 2006

For example..

select * from mytable where MyNum = 7

If this brings back more than 1 row, I want to display a message that says,

Print 'There is more than one row returned'

Else (If only 1 row returned), I don't want to print anything.

Can I do this? Thx!

View 1 Replies View Related

COndition Spli - Error Date Condition

Apr 19, 2007

Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...



I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)





[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.



What is wrong??

Regards,

Pedro

View 4 Replies View Related

Reporting Services :: SSRS IIF One Condition Or IIF Another Condition

Jun 22, 2015

I  am trying to write an visibility function to have message shown based on two different IIF  conditions:

If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",    otherwize return " NAME CREATED")If behavior is to  Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",    otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME  CREATED"),
IIF((UCase(First(Fields!Message.Value,  "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")

View 6 Replies View Related

Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?

Oct 24, 2007

Question is in the subject.

Thanks in advance
-Jamie

View 7 Replies View Related

Transact SQL :: How To Add Condition In Where Clause According To Another Condition

Oct 17, 2015

I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :

create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc

[Code] .....

View 3 Replies View Related

Difference Between &"join&" And &"exists&"

Jul 30, 2007

hi,

what is the main difference of using join or using exists.

e.g.1.
select t1.id, t1.name from table1 t1
join table2 t2
t2.id = t1.id

join table3 t3
t2.id = t3.id

where
t1.name like '%something'

e.g.2.

select t1.id, t1.name from table t1

where t1.name like '%something'
and (select t2.id from table2 t2
join table3 t3
t2.id = t3.id)

what is the main difference and how does this impact on the output?

thanx

View 2 Replies View Related

An Easy Way To Reference The Nth Row From A Table Meeting A Condition X, And The Mth Row From The Same Table Meeting Condition Y

May 10, 2006

Hi

I am developing a scientific application (demographic forecasting) and have a situation where I need to update a variety of rows, say the ith, jth and kth row that meets a particular condition, say, x.

I also need to adjust rows, say mth and nth that meet condition , say y.

My current solution is laborious and has to be coded for each condition and has been set up below (If you select this entire piece of code it will create 2 databases, each with a table initialised to change the 2nd,4th,8th and 16th rows, with the first database ignoring the condition and with the second applying the change only to rows with 'type1=1' as the condition.)

This is an adequate solution, but if I want to change the second row meeting a second condition, say 'type1=2', I would need to have another WITH...SELECT...INNER JOIN...UPDATE and I'm sure this would be inefficient.

Would there possibly be a way to introduce a rank by type into the table, something like this added column which increments for each type:







ID
Int1
Type1
Ideal Rank by Type

1
1
1
1

2
1
1
2

3
2
1
3

4
3
1
4

5
5
1
5

6
8
2
1

7
13
1
6

8
21
1
7

9
34
1
8

10
55
2
2

11
89
1
9

12
144
1
10

13
233
1
11

14
377
1
12

15
610
1
13

16
987
2
3

17
1597
1
14

18
2584
1
15

19
4181
1
16

20
6765
1
17

The solution would then be a simple update based on an innerjoin reflecting the condition and rank by type...

I hope this posting is clear, albeit long.

Thanks in advance

Greg

PS The code:

USE

master

GO

CREATE DATABASE CertainRowsToChange

GO

USE CertainRowsToChange

GO

CREATE TABLE InitialisedValues

(

InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE Initialise

AS

BEGIN

INSERT INTO InitialisedValues (Int1 )

SELECT 2

INSERT INTO InitialisedValues (Int1 )

SELECT 4

INSERT INTO InitialisedValues (Int1 )

SELECT 8

INSERT INTO InitialisedValues (Int1 )

SELECT 16

END

GO

EXEC Initialise

/*=======================================================*/

CREATE TABLE AllRows

(

AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE TABLE RowsToChange

(

RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE InitialiseRowsToChange

AS

BEGIN

INSERT INTO RowsToChange (Int1 )

SELECT 2

INSERT INTO RowsToChange (Int1 )

SELECT 4

INSERT INTO RowsToChange (Int1 )

SELECT 8

INSERT INTO RowsToChange (Int1 )

SELECT 16

END

GO

EXEC InitialiseRowsToChange

GO

CREATE PROCEDURE PopulateAllRows

AS

BEGIN

INSERT INTO AllRows (Int1 )

SELECT 1

INSERT INTO AllRows (Int1 )

SELECT 1

INSERT INTO AllRows (Int1 )

SELECT 2

INSERT INTO AllRows (Int1 )

SELECT 3

INSERT INTO AllRows (Int1 )

SELECT 5

INSERT INTO AllRows (Int1 )

SELECT 8

INSERT INTO AllRows (Int1 )

SELECT 13

INSERT INTO AllRows (Int1 )

SELECT 21

INSERT INTO AllRows (Int1 )

SELECT 34

INSERT INTO AllRows (Int1 )

SELECT 55

INSERT INTO AllRows (Int1 )

SELECT 89

INSERT INTO AllRows (Int1 )

SELECT 144

INSERT INTO AllRows (Int1 )

SELECT 233

INSERT INTO AllRows (Int1 )

SELECT 377

INSERT INTO AllRows (Int1 )

SELECT 610

INSERT INTO AllRows (Int1 )

SELECT 987

INSERT INTO AllRows (Int1 )

SELECT 1597

INSERT INTO AllRows (Int1 )

SELECT 2584

INSERT INTO AllRows (Int1 )

SELECT 4181

INSERT INTO AllRows (Int1 )

SELECT 6765

END

GO

EXEC PopulateAllRows

GO

SELECT * FROM AllRows

GO

WITH Temp(OrigID)

AS

(

SELECT OrigID FROM

(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows) AS FromTable

INNER JOIN

RowsToChange AS ToTable

ON FromTable.RowScore = ToTable.Int1

)

UPDATE AllRows

SET Int1=1000

FROM

Temp as InTable

JOIN Allrows as OutTable

ON Intable.OrigID = OutTable.AllRowsID

GO

SELECT * FROM AllRows

GO

USE

master

GO

CREATE DATABASE ComplexCertainRowsToChange

GO

USE ComplexCertainRowsToChange

GO

CREATE TABLE InitialisedValues

(

InitialisedValuesID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL

)

GO

CREATE PROCEDURE Initialise

AS

BEGIN

INSERT INTO InitialisedValues (Int1 )

SELECT 2

INSERT INTO InitialisedValues (Int1 )

SELECT 4

INSERT INTO InitialisedValues (Int1 )

SELECT 8

INSERT INTO InitialisedValues (Int1 )

SELECT 16

END

GO

EXEC Initialise

/*=======================================================*/

CREATE TABLE AllRows

(

AllRowsID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL,

Type1 int NOT NULL

)

GO

CREATE TABLE RowsToChange

(

RowsToChangeID int identity(1 ,1) NOT NULL PRIMARY KEY,

Int1 int NOT NULL,

Type1 int NOT NULL

)

GO

CREATE PROCEDURE InitialiseRowsToChange

AS

BEGIN

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 2, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 4, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 8, 1

INSERT INTO RowsToChange (Int1,Type1 )

SELECT 16, 1

END

GO

EXEC InitialiseRowsToChange

GO

CREATE PROCEDURE PopulateAllRows

AS

BEGIN

INSERT INTO AllRows (Int1, Type1 )

SELECT 1, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 1, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 2, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 3, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 5, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 8, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 13, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 21, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 34, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 55, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 89, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 144, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 233, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 377, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 610, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 987, 2

INSERT INTO AllRows (Int1, Type1 )

SELECT 1597, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 2584, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 4181, 1

INSERT INTO AllRows (Int1, Type1 )

SELECT 6765, 1

END

GO

EXEC PopulateAllRows

GO

SELECT * FROM AllRows

GO

WITH Temp(OrigID)

AS

(

SELECT OrigID FROM

(SELECT Row_Number() OVER (ORDER BY AllRowsID Asc ) AS RowScore, AllRowsID AS OrigID, Int1 AS OrigValue FROM Allrows WHERE Type1=1) AS FromTable

INNER JOIN

RowsToChange AS ToTable

ON FromTable.RowScore = ToTable.Int1

)

UPDATE AllRows

SET Int1=1000

FROM

Temp as InTable

JOIN Allrows as OutTable

ON Intable.OrigID = OutTable.AllRowsID

GO

SELECT * FROM AllRows

GO

View 3 Replies View Related

Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation

May 22, 2015

I have two xml source and i need only left restricted data.

how can i perform left restricted join?

View 2 Replies View Related

Warning - The Join Order Has Been Enforced Because A Local Join Hint Is Used

Dec 23, 2014

I have two select statements, in between select statement taking UNION ALL . I need to avoid the error

Warning: The join order has been enforced because a local join hint is used.

View 9 Replies View Related

'Left Outer Merge Join' Failing To Join Valid Row

Aug 10, 2007

Scenario:

OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC


OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC

These two tasks are followed immediately by a MERGE JOIN

All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)

result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...


result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...

All other rows are joining as expected.
Why is it failing for this one row?

View 1 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related







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