Showing posts with label joins. Show all posts
Showing posts with label joins. Show all posts

Monday, March 19, 2012

Conditional Table Joins

Hi all,
I'm having a little trouble with producing a query for MS SQL Server
2000 with a conditional join in.
The basis of the structure is that when an order is active, the
OrderID of any book in the Order is stored in the book record in the
Books table. If an order is cancelled, the OrderID is removed from the
record in the Books table, to enable the book to be put back on sale.
At that point, for each book cancelled, a record is appended to the
CancelledRefundedBooks table, with just the book reference number
(foreign key of the Books table), and the OrderID it belonged to. This
is to us to still see which books were in a cancelled order. I hope
that made sense! i've posted the table defs for these tables at the
bottom of this message.
My problem is when using the following stored procedure query to
search for orders based on different criteria, included a reference
number for a book in the order, the join with the books table is
causing cancelled orders not to be returned. For example, if i search
for active order 1, because one of the possible search criteria is
book reference, the Books table is joined to the query. Because order
1 is active, the book records in the Books table have orderID's
stored, and the order is returned. However, if i search for cancelled
order 2, because the books are lacking the OrderID field, the join is
causing the order record not to be returned. I have tried using an
INNER JOIN and a LEFT OUTER JOIN, and neither help. The ideal
situation would be if the join for the Books table could be
conditional on the OrderStatusID field in the Orders table, so that
the query would be joined to the Books table if the order was active,
and if the order was cancelled, the join would change to the
CancelledRefundedBooks table.
An additional complication is that if the join was to the
CancelledRefundedBooks table, in the event of a cancelled order, the
CancelledRefundedBooks table holds only the book reference numbers.
This means that table would then need a further join onto the Books
table to retrieve author, title etc. fields.
The stored proc as it stands is as follows:
ALTER PROCEDURE sp_searchorders
@.refnumber int = NULL,
@.surname nvarchar(100) = NULL,
@.orderid int = NULL,
@.postcode nvarchar(50) = NULL,
@.booktitle nvarchar(500) = NULL
WITH RECOMPILE
AS
SELECT DISTINCT
Orders.OrderID, Staff.Surname AS StaffSur,
Staff.FirstName AS StaffFir, Orders.CustomerID, Customers.Forenames,
Customers.Surname,
Customers.Telephone, Customers.Email,
Customers.Forenames, Customers.Surname, Customers.TownCity,
Websites.Name AS Website, Orders.OrdDate
FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
Staff ON Orders.StaffID = Staff.StaffID INNER
JOIN
Websites ON Orders.WebsiteID = Websites.WebsiteID LEFT OUTER JOIN
Books ON Orders.OrderID = Books.OrderID
WHERE CASE @.refnumber
WHEN 0 THEN @.refnumber
ELSE Books.Ref
END
= @.refnumber
AND Customers.Surname LIKE COALESCE(@.surname, '%')
AND Books.Title LIKE COALESCE(@.booktitle, '%')
AND Customers.Postcode LIKE COALESCE(@.postcode, '%')
AND CASE @.orderid
WHEN 0 THEN @.orderid
ELSE Orders.OrderID
END
= @.orderid
My sincerest apologies for posting such a long narrative about the
problem, but I can't think of any other way of describing it! I have
included the CREATE statements for all the mentioned tables below, and
if i can provide any more information to help come up with a solution
plz ask. I am a relative newbie at SQL server, so please be gentle!!
Many thanks in advance
James Currer
CREATE TABLE [Books] (
[Ref] [int] NOT NULL ,
[Author] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Title] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[PlacePubDate] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[Keywords] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Catalogues] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
[Cost] [money] NULL ,
[DealerCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Notes] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[Price] [money] NULL ,
[OrderID] [int] NULL ,
[Weight] [numeric](6, 3) NULL ,
[ISBN] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Row] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Shelf] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[LocationID] [int] NULL ,
[StatusID] [int] NULL ,
[DealerID] [int] NULL ,
[BoxID] [int] NULL ,
[LastUpdatedBy] [int] NULL ,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[Ref]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [CancelledRefundedBooks] (
[BookRef] [int] NOT NULL ,
[RefundID] [int] NULL ,
[OrderID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[StaffID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[OrdDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[WebsiteID] [int] NOT NULL ,
[PackingID] [int] NULL ,
[PaymentID] [int] NULL ,
[InvoiceNumber] [int] NULL ,
[InvoiceDate] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[OrderStatusID] [int] NOT NULL CONSTRAINT [DF_Orders_OrderStatusID]
DEFAULT (4),
[TotalPricePaid] [money] NOT NULL CONSTRAINT
[DF_Orders_TotalPricePaid] DEFAULT (0),
[CustomersOwnRef] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[ShippingPaid] [money] NOT NULL CONSTRAINT [DF_Orders_ShippingPaid]
DEFAULT (0.00),
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
) ON [PRIMARY]
GOJames,
I did not go beyond your second paragraph. I believe your db design is
flawed. If you keep an OrderID in your Book table for an active order, how
do you deal with the situation that there more than one orders that buy the
same book?
"James Currer" <phaser2001@.hotmail.com> wrote in message
news:57df26d.0307300626.667496bf@.posting.google.com...
> Hi all,
> I'm having a little trouble with producing a query for MS SQL Server
> 2000 with a conditional join in.
> The basis of the structure is that when an order is active, the
> OrderID of any book in the Order is stored in the book record in the
> Books table. If an order is cancelled, the OrderID is removed from the
> record in the Books table, to enable the book to be put back on sale.
> At that point, for each book cancelled, a record is appended to the
> CancelledRefundedBooks table, with just the book reference number
> (foreign key of the Books table), and the OrderID it belonged to. This
> is to us to still see which books were in a cancelled order. I hope
> that made sense! i've posted the table defs for these tables at the
> bottom of this message.
> My problem is when using the following stored procedure query to
> search for orders based on different criteria, included a reference
> number for a book in the order, the join with the books table is
> causing cancelled orders not to be returned. For example, if i search
> for active order 1, because one of the possible search criteria is
> book reference, the Books table is joined to the query. Because order
> 1 is active, the book records in the Books table have orderID's
> stored, and the order is returned. However, if i search for cancelled
> order 2, because the books are lacking the OrderID field, the join is
> causing the order record not to be returned. I have tried using an
> INNER JOIN and a LEFT OUTER JOIN, and neither help. The ideal
> situation would be if the join for the Books table could be
> conditional on the OrderStatusID field in the Orders table, so that
> the query would be joined to the Books table if the order was active,
> and if the order was cancelled, the join would change to the
> CancelledRefundedBooks table.
> An additional complication is that if the join was to the
> CancelledRefundedBooks table, in the event of a cancelled order, the
> CancelledRefundedBooks table holds only the book reference numbers.
> This means that table would then need a further join onto the Books
> table to retrieve author, title etc. fields.
> The stored proc as it stands is as follows:
> ALTER PROCEDURE sp_searchorders
> @.refnumber int = NULL,
> @.surname nvarchar(100) = NULL,
> @.orderid int = NULL,
> @.postcode nvarchar(50) = NULL,
> @.booktitle nvarchar(500) = NULL
> WITH RECOMPILE
> AS
> SELECT DISTINCT
> Orders.OrderID, Staff.Surname AS StaffSur,
> Staff.FirstName AS StaffFir, Orders.CustomerID, Customers.Forenames,
> Customers.Surname,
> Customers.Telephone, Customers.Email,
> Customers.Forenames, Customers.Surname, Customers.TownCity,
> Websites.Name AS Website, Orders.OrdDate
> FROM Orders INNER JOIN
> Customers ON Orders.CustomerID => Customers.CustomerID INNER JOIN
> Staff ON Orders.StaffID = Staff.StaffID INNER
> JOIN
> Websites ON Orders.WebsiteID => Websites.WebsiteID LEFT OUTER JOIN
> Books ON Orders.OrderID = Books.OrderID
> WHERE CASE @.refnumber
> WHEN 0 THEN @.refnumber
> ELSE Books.Ref
> END
> = @.refnumber
> AND Customers.Surname LIKE COALESCE(@.surname, '%')
> AND Books.Title LIKE COALESCE(@.booktitle, '%')
> AND Customers.Postcode LIKE COALESCE(@.postcode, '%')
> AND CASE @.orderid
> WHEN 0 THEN @.orderid
> ELSE Orders.OrderID
> END
> = @.orderid
> My sincerest apologies for posting such a long narrative about the
> problem, but I can't think of any other way of describing it! I have
> included the CREATE statements for all the mentioned tables below, and
> if i can provide any more information to help come up with a solution
> plz ask. I am a relative newbie at SQL server, so please be gentle!!
> Many thanks in advance
>
> James Currer
>
> CREATE TABLE [Books] (
> [Ref] [int] NOT NULL ,
> [Author] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Title] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [PlacePubDate] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
> [Keywords] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Catalogues] [nvarchar] (80) COLLATE Latin1_General_CI_AS NULL ,
> [Cost] [money] NULL ,
> [DealerCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Notes] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [Price] [money] NULL ,
> [OrderID] [int] NULL ,
> [Weight] [numeric](6, 3) NULL ,
> [ISBN] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Row] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Shelf] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [LocationID] [int] NULL ,
> [StatusID] [int] NULL ,
> [DealerID] [int] NULL ,
> [BoxID] [int] NULL ,
> [LastUpdatedBy] [int] NULL ,
> CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
> (
> [Ref]
> ) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [CancelledRefundedBooks] (
> [BookRef] [int] NOT NULL ,
> [RefundID] [int] NULL ,
> [OrderID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [StaffID] [int] NOT NULL ,
> [CustomerID] [int] NOT NULL ,
> [OrdDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [WebsiteID] [int] NOT NULL ,
> [PackingID] [int] NULL ,
> [PaymentID] [int] NULL ,
> [InvoiceNumber] [int] NULL ,
> [InvoiceDate] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [OrderStatusID] [int] NOT NULL CONSTRAINT [DF_Orders_OrderStatusID]
> DEFAULT (4),
> [TotalPricePaid] [money] NOT NULL CONSTRAINT
> [DF_Orders_TotalPricePaid] DEFAULT (0),
> [CustomersOwnRef] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [ShippingPaid] [money] NOT NULL CONSTRAINT [DF_Orders_ShippingPaid]
> DEFAULT (0.00),
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
> [OrderID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

Thursday, March 8, 2012

Conditional Joins

Hi all,

I have 4 tables with the structure shown below

Main Table :

Create Table TestMain
(TestMainId INT , TestCompanyID INT )

Other Tables :

Create Table TestCompany1
(Id INT , TestCompanyID INT )

Create Table TestCompany2
(Id INT , TestCompanyID INT )

Create Table TestCompany3
(Id INT , TestCompanyID INT )

In this above tables.. I would have a record in the table TestMain and a entry for that specific record would be in any of the tables like TestCompany1,TestCompany2,TestCompany3

Sample Records :

In the table TestMain

1 1000
2 2000
3 3000
4 4000
5 5000
6 6000
7 7000

In the table TestCompany1

1 1000
2 6000

In the table TestCompany2

1 3000
2 4000
3 5000

In the table TestCompany3

1 7000

How do I join those tables and fetch the main record with its subsequent entry from the other tables ?

Thanks in advance,

HHADo you have defined any relationship between the tables..? It's basic requirement for data integrity.

Anyway you can join the two tables this way...

Select Testmain.TestMainId, TestMain.TestCompanyID From TestMain
JOIN TestCompany1 ON TestMain.TestCompanyID = TestCompany1.TestCompanyID

You can join more than two tables using different join types...|||Hi

You would need to use left joins and maybe COALESCE but it is hard to know without more details. The fact that you are doing this hints that your design may not be sound too (although it may be - this looks like a mock up yes?).

HTH|||I think your best bet would be to inner join against each table and UNION or UNION ALL the results - your design does look dubious though, why are you segmenting companies across 3 tables - do they have different attributes per collection or is there another reason?|||Hi all,

There is a main table say COMPANY and they other tables CompanyA , CompanyB , CompanyC.

The main table COMPANY has the general info about the company ( like address , contact info) and there are 3 BIT columns to indicate what all type of company it falls under.If it falls under A & B , then the relevant information
are stored in CompanyA & CompanyB.

Now I need to write a proc which gets few input parameters and searches
for the company details.

1. If no parameters where passed , I need to get all the company from the
COMPANY with relevant information from the CompanyA, CompanyB,CompanyC.

2.If I get a parameter which says I should fetch only companys falling under
CompanyA , I should be able to get them too.

Still , the DB is in production , I cant touch the design.

Thanks for all your help ,

HHA|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71565

Terrible design - I'm sure you know or are at least becoming aware of.

Playing with post three from the link (not efficient - there may be better solutions):

CREATE TABLE #TESTMAIN
(TESTMAINID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY1
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY2
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY3
(ID INT , TESTCOMPANYID INT )

INSERT #TESTMAIN
SELECT 1, 1000 UNION ALL
SELECT 2, 2000 UNION ALL
SELECT 3, 3000 UNION ALL
SELECT 4, 4000 UNION ALL
SELECT 5, 5000 UNION ALL
SELECT 6, 6000 UNION ALL
SELECT 7, 7000

INSERT #TESTCOMPANY1
SELECT 1, 1000 UNION ALL
SELECT 2, 6000

INSERT #TESTCOMPANY2
SELECT 1, 3000 UNION ALL
SELECT 2, 4000 UNION ALL
SELECT 3, 5000

INSERT #TESTCOMPANY3
SELECT 1, 7000

DECLARE @.CompanyOneOnly AS Bit
SET @.CompanyOneOnly = 1

SELECT *
FROM -- Relvent companys
(SELECT X1.TESTMAINID,
X1.TESTCOMPANYID,
ISNULL(X2.ID,0) AS Comp1,
ISNULL(X3.ID,0) AS Comp2,
ISNULL(X4.ID,0) AS Comp3
FROM #TESTMAIN X1
LEFT JOIN #TESTCOMPANY1 X2 ON X1.TESTCOMPANYID = X2.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY2 X3 ON X1.TESTCOMPANYID = X3.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY3 X4 ON X1.TESTCOMPANYID = X4.TESTCOMPANYID) AS DerT
WHERE CAST(Comp1 AS Bit) = @.CompanyOneOnly OR @.CompanyOneOnly = 0

DROP TABLE #TESTMAIN
DROP TABLE #TESTCOMPANY1
DROP TABLE #TESTCOMPANY2
DROP TABLE #TESTCOMPANY3

Conditional Joins

Hi, I have a query that needs to filter on certain data only if needed. eg.
(Assume tblStock and tblFilter are 1-to-1 on StockID)
DECLARE @.Min TINYINT , @.Max TINYINT
SELECT @.Min = 5 , @.Max = 10
SELECT
StockID
FROM
tblStock
INNER JOIN
tblFilter
ON
tblStock.StockID = tblFilter.StockID
AND
(
tblFilter.Value BETWEEN @.Min AND @.Max
OR
@.Min IS NULL OR @.Max IS NULL
)
Preferably I'd like to not do the join at all if either variable is null,
and I'd like it in a stored procedure so it's precompiled.
This is a simplified version of the query, in our system there are
aproximately 8 filters, which may or may not be required - and in any
combination.
I could, in theory create a stored proc for each combination of filter
inclusion - but this will be hard to maintain - especially if/when more
filters are introduced.
Thanks.
Rebecca.Please always include DDL with questions like this. It also helps to
include sample data and required results.
If Value is not nullable then maybe you just want to set @.min and @.max
to the min and max values for INT. Otherwise, use EXISTS:
SELECT stockid
FROM tblstock
WHERE EXISTS
(SELECT *
FROM tblfilter
WHERE tblStock.StockID = tblFilter.StockID
AND (stockid BETWEEN @.min AND @.max
OR @.min IS NULL
OR @.max IS NULL)) ;
David Portas
SQL Server MVP
--|||I have included the full ddl for the sample i've included.
Essentially what I want to do is put an IF around the Inner joins to exclude
the join if the parameters filtering the value are null.
Currently, we have the query dynamically built in the client, but this gives
compaliation overheads when various combinations are excluded/inculded and
we are investigating a pre-compiled version.
USE tempdb
/* Create tables */
IF OBJECT_ID('dbo.tblFilter2') IS NOT NULL DROP TABLE dbo.tblFilter2
IF OBJECT_ID('dbo.tblFilter1') IS NOT NULL DROP TABLE dbo.tblFilter1
IF OBJECT_ID('dbo.tblStock') IS NOT NULL DROP TABLE dbo.tblStock
CREATE TABLE dbo.tblStock ( StockID INT PRIMARY KEY CLUSTERED , Other
VARCHAR(5) NULL , Fields VARCHAR(5) )
CREATE TABLE dbo.tblFilter1 ( StockID INT PRIMARY KEY NONCLUSTERED , Value
INT , CONSTRAINT FK_dbo_tblFilter1_dbo_tblStock FOREIGN KEY ( StockID )
REFERENCES dbo.tblStock ( StockID ) )
CREATE TABLE dbo.tblFilter2 ( StockID INT PRIMARY KEY NONCLUSTERED , Value
DATETIME , CONSTRAINT FK_dbo_tblFilter2_dbo_tblStock FOREIGN KEY ( StockID )
REFERENCES dbo.tblStock ( StockID ) )
CREATE CLUSTERED INDEX CIX_dbo_tblFilter1 ON dbo.tblFilter1 ( Value )
CREATE CLUSTERED INDEX CIX_dbo_tblFilter2 ON dbo.tblFilter2 ( Value )
/* Populate sudo-random data */
INSERT INTO
dbo.tblStock
SELECT
N AS StockID
, NULL AS Other
, NULL AS Fields
FROM
dbo.tblNumbers /* contains n = 1 to 1,000,000 */
WHERE
N BETWEEN 1 AND 5000
INSERT INTO
dbo.tblFilter1
SELECT
StockID
, ( 5000 - StockID ) + 1 AS Value
FROM
dbo.tblStock
INSERT INTO
dbo.tblFilter2
SELECT
StockID
, DATEADD( d , -StockID , [Now] ) AS Value
FROM
dbo.tblStock
CROSS JOIN
( SELECT GETDATE() AS [Now] ) vwDate
/* Search Query */
GO
CREATE PROCEDURE
dbo.prRunFilter
@.Min INT
, @.Max INT
, @.FilerMonth DATETIME /* assumes midnight, first of month */
AS
SET DATEFORMAT YMD
DECLARE @.StartOfMonth DATETIME , @.EndOfMonth DATETIME
SELECT @.StartOfMonth = @.FilerMonth , @.EndOfMonth = DATEADD( ms , -3 ,
DATEADD( m , 1 , @.FilerMonth ) )
SELECT
tblStock.StockID
, tblFilter1.Value
, tblFilter2.Value
FROM
dbo.tblStock tblStock
INNER JOIN
(
SELECT
StockID
, Value
FROM
dbo.tblFilter1
WHERE
Value BETWEEN @.Min AND @.Max
) tblFilter1
ON
tblStock.StockID = tblFilter1.StockID
INNER JOIN
(
SELECT
StockID
, Value
FROM
dbo.tblFilter2
WHERE
Value BETWEEN @.StartOfMonth AND @.EndOfMonth
) tblFilter2
ON
tblStock.StockID = tblFilter2.StockID
GO
EXEC dbo.prRunFilter @.Min = 4490 , @.Max = 4500 , @.FilerMonth = {d
'2004-05-01'}|||I would create a view:
create view t1
as
SELECT
StockID
FROM
tblStock
INNER JOIN
tblFilter
ON
tblStock.StockID = tblFilter.StockID
in the procedure, I would
if @.Min IS NULL OR @.Max IS NULL
-- the plan for this may be simpler
select * from t1
else
-- this query might need a different plan
select * from t1
where tblFilter.Value BETWEEN @.Min AND @.Max
this way both the maintenance of the query is easy, as it's in a view,
and you have 2 plans|||Except that, as stated in my original post, we have 8 such filters...
This would result in 64 if/else statements and views to handle all the
combinations.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1128437238.893216.20870@.o13g2000cwo.googlegroups.com...
> I would create a view:
> create view t1
> as
> SELECT
> StockID
> FROM
> tblStock
> INNER JOIN
> tblFilter
> ON
> tblStock.StockID = tblFilter.StockID
> in the procedure, I would
> if @.Min IS NULL OR @.Max IS NULL
> -- the plan for this may be simpler
> select * from t1
> else
> -- this query might need a different plan
> select * from t1
> where tblFilter.Value BETWEEN @.Min AND @.Max
> this way both the maintenance of the query is easy, as it's in a view,
> and you have 2 plans
>|||no - just one view, and yes, some if ... else statements.
Believe me or not, if you are selecting from big tables, then the
performance penalty of running the generic plan may be pretty high.
That, of course, has been said assuming that there are indexes that
could be used for some of your filters.

Conditional Join on Data Flow?

Hi,

Can we make conditional joins on the data flow?

Imagine i want to join 2 tables based on a value and an interval... Imagine i have a positioning number in one table and in the other i have a price, from_position, to_position and i want to join the to tables like position >= from_position and position <= to_position

Can we do this in SSIS?

Best Regards,

You can't do a join like that using a single component. To achieve this you might try doing a full join and then using a conditional split or a custom script to drop the rows that don't meet the desired join criteria.