Joining table UDFs in queries

Hi,
I've got a table UDF which takes two parameters and returns a table, as
follows:
CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
datetime)
RETURNS @.tblPeriodYear TABLE
(
Period tinyint,
Year smallint
)
AS
BEGIN
<snipped to save space>
RETURN
END
That works fine. However, is it possible to use this UDF as part of a query
where the input parameters come from another table?
E.g. the two input parameters I want to pass to the function are contained
within the Sales table, and I could output them as follows:
SELECT
CompanyID,
SaleDate,
<other fields>
FROM
Sales
Ideally, I'm looking for some way of combining the query on the table with
the UDF e.g.
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM
Sales
Is this even possible?
Any assistance gratefully received.
MarkI'm afraid not in SQL Server 2000. This is new functionality added in SQL
Server 2005 via the APPLY table operator, e.g.,
SELECT ...
FROM Sales AS S
CROSS APPLY ftblPeriodYear(S.CompanyID, S.SaleDate) AS F;
You can find more details here:
http://www.windowsitpro.com/Article...47145.html?Ad=1
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Mark Rae" <mark@.mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:eueZ2kxyFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've got a table UDF which takes two parameters and returns a table, as
> follows:
> CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
> datetime)
> RETURNS @.tblPeriodYear TABLE
> (
> Period tinyint,
> Year smallint
> )
> AS
> BEGIN
> <snipped to save space>
> RETURN
> END
> That works fine. However, is it possible to use this UDF as part of a
> query where the input parameters come from another table?
> E.g. the two input parameters I want to pass to the function are contained
> within the Sales table, and I could output them as follows:
> SELECT
> CompanyID,
> SaleDate,
> <other fields>
> FROM
> Sales
> Ideally, I'm looking for some way of combining the query on the table with
> the UDF e.g.
> SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
> FROM
> Sales
>
> Is this even possible?
> Any assistance gratefully received.
> Mark
>|||That :
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM=20
Sales=20
doesn=B4t work. :-(|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1128675411.933739.210250@.g14g2000cwa.googlegroups.com...

>That :
>SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
>FROM
> Sales
>
>doesnt work. :-(
Er, yeah I know - that was the reason for my post...|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:unelksxyFHA.3720@.TK2MSFTNGP14.phx.gbl...

> I'm afraid not in SQL Server 2000. This is new functionality added in SQL
> Server 2005 via the APPLY table operator, e.g.,
Thanks - I was vaguely aware that there was something like this in SQL
Server 2005, but wondered if it had an equivalent in 2000...

joining a varchar with a int column

just noticed this, wonder if there is a performance hit
got results when executing the following
select AgendaID
from ObjectiveAgenda
Inner Join Objective On Objective.ObjectiveID =
ObjectiveAgenda.ObjectiveID
problem is that the ObjectiveID column in one table is of SQL type
INTEGER, while in the other table is of type VARCHAR.
wonder if it is doing a translation behind the scenes, with some
performance hit.Yes, SQL Server will have to convert the value and this can cause table scan
.
Thus this can cause major negative impact.
Here is a script I have used for testing. In the Query Analyser, please
'Display Execution Plan' for the 2 queries at the end of my script. One will
cause Table Scan and another will go for Index S.
set nocount on
create table #test111
(scode int,
sdesc varchar(30))
create index idx1_test111
on #test111(sdesc)
declare @.val1 int
set @.val1 = 1
while @.val1 < 10000
begin
insert into #test111
values(@.val1, convert(varchar(30),(@.val1 * @.val1)))
set @.val1 = @.val1 + 1
end
select * from #test111 where sdesc = 25
go
select * from #test111 where sdesc = '25'
"arzewski@.hotmail.com" wrote:

> just noticed this, wonder if there is a performance hit
> got results when executing the following
> select AgendaID
> from ObjectiveAgenda
> Inner Join Objective On Objective.ObjectiveID =
> ObjectiveAgenda.ObjectiveID
> problem is that the ObjectiveID column in one table is of SQL type
> INTEGER, while in the other table is of type VARCHAR.
> wonder if it is doing a translation behind the scenes, with some
> performance hit.
>

Joining 2 tables...

Hi. I'm new to SQL, and need to join 2 tables... any hints?

table1:
id (int)
title(varchar(50))
body(text)

table2:
id (int)
title(varchar(50))
body(text)

somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information:

table1:
id title body
1 "first title" "first body"
2 "second title" "second body"
3 "third title" "third body"

table2:
id title body
1 "first title" "first body"
2 "second title" "second body"
3 "third title" "third body"

I would like to get...

id table title body
3 1 "third title" "third body"
3 2 "third title" "third body"
2 1 "second title" "second body"
2 2 "second title" "second body"
1 1 "first title" "first body"
1 2 "first title" "first body"

Does anyone know how to get this? I am fairly flexible if i need to change things...

cheers, eh!

SELECT 'A', Id, Title, Body FROM TABLE1
UNION
SELECT 'B', Id, Title, Body FROM TABLE2
ORDER BY Id DESC, 1 ASC

JOIN question and NOT JOIN

Hi,

This is a sample database table

TableA

============================

aID int identity(1,1) primary key

aName varchar(30)

TableB

===========================

bID int identity(1,1) primary key

bTitle varchar(30)

aID int references TableA(aID)

TableC

===========================

cID int indentity(1,1) primary key

cCategory varchar(30)

bID int references TableB(bID)

Here I got two query, are them the same?

Select A.aName, B.bTitle, C.cCategory

From TableA A, TableB B, TableC C

Where A.aID = B.aID And B.bID = C.cID

and

Select A.aName, B.bTitle, C.cCategory

From TableA A Join TableB B On A.aID=B.aID

Join TableC On B.bID=C.cID

Are those two the same?

And what is the different of JOIN and LEFT OUTER JOIN? Any other JOIN?

Millions Thanks!

Usually they are the same.

However, the first form is 'old' and will be soon deprecated.

Use the second form.

For more details about JOIN, refer to Books Online, Topics: Using Joins, JOIN

|||

hi ,

If you measure this, you will most likely discover that the two versions
use the exact same access plan. SQL Server tries very hard to optimize a
query, and in that process, a where clause which equates columns from two
tables will be converted to an inner join.

please check this link for the second question.

http://en.wikipedia.org/wiki/Join_(SQL)

hope, it clear

|||Thanks for leading me to the source.|||

The definitive 'source' is Books Online.

Wikipedia is often a good source of information also, but I would trust Books Online more than a wiki -especially if a job or exam was dependent upon the 'answer'.

|||

Books online can sometimes be complicated, but I agree with your answer.

Join query

I have two tables Table1 and Table2

Table1 columns:
CustomerNum varchar(12)
InvDate smalldatetime

Table2 columns
CustomerNum varchar(12)
MaintDate smalldatetime
Dis float

Table1 Data (sample):
CustomerNum InvDate
995169 2/4/2002
995169 11/4/2002
995169 1/1/2003
995169 3/4/2003
995169 10/4/2003
995169 1/4/2005
995169 5/4/2005
995169 11/15/2005

Table2 Data (sample):
CustomerNum MaintDate Dis
995169 10/3/2001 1.07
995169 10/3/2002 1.1
995169 5/16/2003 1.7
995169 9/30/2003 2.9
995169 1/1/2005 2.8
995169 3/31/2005 2.95
995169 10/31/2005 2.85

I want to display customerNum, InvDate from Table1 along with applicable Dis from Table2. Dis for a customer changes form time to time. We need to pick the right one based on Table1.InvDate and Table2.MaintDate. For example, the applicable Dis for customner 995169 invoice date 2/4/2002 is 1.07
After joining the tables, I want the data to be displayed as under:

CustomerNum InvDate Dis
995169 2/4/2002 1.07
995169 11/4/2002 1.1
995169 1/1/2003 1.1
995169 3/4/2003 1.7
995169 10/4/2003 2.9
995169 1/4/2005 2.8
995169 5/4/2005 2.95
995169 11/15/2005 2.85

Any help in constructing sql qury will be appreciated. Thanks in advance.

do you want the closest invdate from table 1 that is before maintdate for each customer. it is not clear how you are wanting the data joined, please explain further.|||What I want is get the applicable Dis from Table2 for the customer's invoice date. So after joining the table2 to table1, I should display table1.customernum, table1.invdate, table2.Dis.|||

Lots of subquery hell unless you add a todate on to your second table.

You can then join where invdate between table2.fromdate and table2.todate

|||What you want is described in my request http://sqljunkies.com/WebLog/simons/archive/2006/02/06/Upper_join_request.aspx|||can you not use the maintdate as the to date?|||

try:

SELECT sub.CustomerNum, sub.invdate, table2.Dis
FROM table2
INNER JOIN
(
SELECT t1.CustomerNum, MAX(t1.invdate) invdate, t2.maintdate
FROM table2 t2
JOIN table1 t1
ON t1.InvDate < t2.maintdate
AND t1.CustomerNum = t2.CustomerNum
GROUP BY t1.CustomerNum, t2.maintdate
) AS sub
ON table2.CustomerNum = sub.CustomerNum
AND table2.maintdate = sub.maintdate

|||

You can do below in SQL Server 2000 onwards:

select t1.CustomerNum, t1.InvDate

, (select top 1 t2.Dis from table2 as t2

where t2.CustomerNum = t1.CustomerNum and t2.MaintDate <= t1.InvDate

order by t2.MaintDate desc) as Dis

from table1 as t1

join problem

Hi,
I have problem with joining.
DDL:
CREATE TABLE Items (
item_code INTEGER NOT NULL,
item_description VARCHAR(50) NOT NULL,
PRIMARY KEY (item_code)
);
CREATE TABLE VAT_Groups (
vat_group CHAR(1) NOT NULL,
vat_description CHAR(20) NOT NULL,
PRIMARY KEY (vat_group)
);
CREATE TABLE VAT_Percents (
vat_group CHAR(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
vat_percent NUMERIC(5,2) NOT NULL,
PRIMARY KEY (vat_group, start_date),
FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
);
CREATE TABLE Items_VAT_History (
item_code INTEGER NOT NULL,
vat_group CHAR(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
PRIMARY KEY (item_code, vat_group, start_date),
FOREIGN KEY (item_code) REFERENCES Items (item_code),
FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
);
Sample data:
INSERT INTO Items VALUES (1, 'Vegetables');
INSERT INTO Items VALUES (2, 'Vine');
INSERT INTO Items VALUES (3, 'Milk');
INSERT INTO VAT_Groups VALUES ('E', 'Common VAT');
INSERT INTO VAT_Groups VALUES ('C', 'Lower VAT');
INSERT INTO VAT_Percents VALUES ('E', '2004-01-01', '2004-12-31', 20.00);
INSERT INTO VAT_Percents VALUES ('E', '2005-01-01', NULL, 18.00);
INSERT INTO VAT_Percents VALUES ('C', '2004-01-01', NULL, 8.00);
INSERT INTO Items_VAT_History VALUES (1, 'E', '2004-01-01', '2005-06-30');
INSERT INTO Items_VAT_History VALUES (1, 'C', '2005-07-01', NULL);
INSERT INTO Items_VAT_History VALUES (2, 'E', '2004-01-01', NULL);
INSERT INTO Items_VAT_History VALUES (3, 'C', '2004-01-01', NULL);
Desired Result:
item_code start_date end_date vat_percnt
---
1 2004-01-01 2004-12-31 20.00
1 2005-01-01 2005-06-30 18.00
1 2005-07-01 NULL 8.00
2 2004-01-01 2004-12-31 20.00
2 2005-01-01 NULL 18.00
3 2004-01-01 NULL 8.00
or equally good result:
item_code start_date end_date vat_percnt
---
1 2004-01-01 2004-12-31 20.00
1 2005-01-01 2005-06-30 18.00
1 2005-07-01 NULL 8.00
2 2004-01-01 2004-12-31 20.00
2 2005-01-01 NULL 18.00
3 2004-01-01 2004-12-31 8.00
3 2005-01-01 NULL 8.00
Thanks
Srdjan MijatovHi
Try:
SELECT i.item_code,
CONVERT(CHAR(10),CASE WHEN h.start_date >= p.start_date THEN h.start_date
ELSE P.start_date END, 121) AS start_date,
CONVERT(CHAR(10),CASE WHEN ISNULL(h.end_date,'29991231') <=
ISNULL(p.end_date,'29991231') THEN h.end_date ELSE p.end_date END,121) AS
end_date,
p.vat_percent
FROM Items i
JOIN Items_VAT_History h on i.item_code = h.item_code
JOIN VAT_Percents P on h.vat_group = p.vat_group
AND ( ( h.start_date >= p.start_date AND h.start_date <=
ISNULL(p.end_date,'29991231') )
OR ( h.end_date <= ISNULL(p.end_date,'29991231') and
ISNULL(h.end_date,'29991231') >= p.start_date )
OR ( h.start_date <= p.start_date AND ISNULL(h.end_date,'29991231') >=
ISNULL(p.end_date,'29991231') )
)
ORDER BY i.item_code, start_date
John
"Srdjan Mijatov" wrote:

> Hi,
> I have problem with joining.
>
> DDL:
> CREATE TABLE Items (
> item_code INTEGER NOT NULL,
> item_description VARCHAR(50) NOT NULL,
> PRIMARY KEY (item_code)
> );
> CREATE TABLE VAT_Groups (
> vat_group CHAR(1) NOT NULL,
> vat_description CHAR(20) NOT NULL,
> PRIMARY KEY (vat_group)
> );
> CREATE TABLE VAT_Percents (
> vat_group CHAR(1) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE,
> vat_percent NUMERIC(5,2) NOT NULL,
> PRIMARY KEY (vat_group, start_date),
> FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
> );
> CREATE TABLE Items_VAT_History (
> item_code INTEGER NOT NULL,
> vat_group CHAR(1) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE,
> PRIMARY KEY (item_code, vat_group, start_date),
> FOREIGN KEY (item_code) REFERENCES Items (item_code),
> FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
> );
>
> Sample data:
> INSERT INTO Items VALUES (1, 'Vegetables');
> INSERT INTO Items VALUES (2, 'Vine');
> INSERT INTO Items VALUES (3, 'Milk');
> INSERT INTO VAT_Groups VALUES ('E', 'Common VAT');
> INSERT INTO VAT_Groups VALUES ('C', 'Lower VAT');
> INSERT INTO VAT_Percents VALUES ('E', '2004-01-01', '2004-12-31', 20.00);
> INSERT INTO VAT_Percents VALUES ('E', '2005-01-01', NULL, 18.00);
> INSERT INTO VAT_Percents VALUES ('C', '2004-01-01', NULL, 8.00);
> INSERT INTO Items_VAT_History VALUES (1, 'E', '2004-01-01', '2005-06-30');
> INSERT INTO Items_VAT_History VALUES (1, 'C', '2005-07-01', NULL);
> INSERT INTO Items_VAT_History VALUES (2, 'E', '2004-01-01', NULL);
> INSERT INTO Items_VAT_History VALUES (3, 'C', '2004-01-01', NULL);
>
> Desired Result:
> item_code start_date end_date vat_percnt
> ---
> 1 2004-01-01 2004-12-31 20.00
> 1 2005-01-01 2005-06-30 18.00
> 1 2005-07-01 NULL 8.00
> 2 2004-01-01 2004-12-31 20.00
> 2 2005-01-01 NULL 18.00
> 3 2004-01-01 NULL 8.00
> or equally good result:
> item_code start_date end_date vat_percnt
> ---
> 1 2004-01-01 2004-12-31 20.00
> 1 2005-01-01 2005-06-30 18.00
> 1 2005-07-01 NULL 8.00
> 2 2004-01-01 2004-12-31 20.00
> 2 2005-01-01 NULL 18.00
> 3 2004-01-01 2004-12-31 8.00
> 3 2005-01-01 NULL 8.00
>
>
> Thanks
> Srdjan Mijatov
>|||Thank you, its working.
I tried to figure out that complex join condition

> AND ( ( h.start_date >= p.start_date AND h.start_date <=
> ISNULL(p.end_date,'29991231') )
> OR ( h.end_date <= ISNULL(p.end_date,'29991231') and
> ISNULL(h.end_date,'29991231') >= p.start_date )
> OR ( h.start_date <= p.start_date AND ISNULL(h.end_date,'29991231') >=
> ISNULL(p.end_date,'29991231') )
> )
Then I run query without it and it is working agian.
Srdjan

Join Problem

Hi,

I have 2 tables:

CREATE TABLE [dbo].[TBL_CONDITION](
[CONDITIONID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](500) NULL)

CREATE TABLE [dbo].[TBL_CONDITION_CUSTOMER](
[CCAN] [varchar](10) NOT NULL,
[CONDITIONID] [int] NOT NULL,
[FOLLOW-UP_DATE] [nchar](10) NOT NULL)

Sample data is as follows:

INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of statements')
INSERT INTO [tbl_condition] ([NAME])VALUES('Satisfactory review')
INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of latest interim')

INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',1,'03/09/2007')
INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',2,'04/09/2007')

Inrespective of data in child table ([tbl_condition_customer] ) I want all the rows from tbl_condition - I am using outer join for that but am not getting the required output:

SELECT dbo.TBL_CONDITION.CONDITIONID, dbo.TBL_CONDITION.NAME, dbo.TBL_CONDITION_CUSTOMER.CCAN
FROM dbo.TBL_CONDITION
LEFT OUTER JOIN dbo.TBL_CONDITION_CUSTOMER ON dbo.TBL_CONDITION.CONDITIONID = dbo.TBL_CONDITION_CUSTOMER.CONDITIONID
WHERE (dbo.TBL_CONDITION_CUSTOMER.CCAN = '52410')

But I am getting output as that of Inner Join?

What am I missing. I want output as follows:

ConditionId Name CCAN
--
1 Receipt of statements 52410
2 Satisfactory review 52410
3 Receipt of latest interim NULL

Hi JayaC

You could change your select statement to the statement shown below.

Chris

SELECT dbo.TBL_CONDITION.CONDITIONID, dbo.TBL_CONDITION.NAME, dbo.TBL_CONDITION_CUSTOMER.CCAN
FROM dbo.TBL_CONDITION
LEFT OUTER JOIN dbo.TBL_CONDITION_CUSTOMER ON dbo.TBL_CONDITION.CONDITIONID = dbo.TBL_CONDITION_CUSTOMER.CONDITIONID AND (dbo.TBL_CONDITION_CUSTOMER.CCAN = '52410')

|||Thanks Chris. That worked :)

join on column with different data types

Hi.
I have a query where a single column is used as join key.
In TableA the column is nvarchar and in TableB it is varchar.
TableB has a clustered index on that column but the join will do a full
table scan
on TableB.
When I convert the coresponding column in TableA to varchar the query use
the index.
The join query will not do an implicit convert between varchar and
nvarchar.
Perhaps as designed.
Where can I find documentation on this issue.
I have been searching books online and Googles but haven't found it.
Sqlserver 2000 or 7.0
--
/dg
----
Dan van Ginhoven
SchlumbergerSema AB
S-412 97 GÖTEBORG Sweden
Phone +46 317 51 44 13
Mob/Cell +46 708 51 44 13convert(varchar(10, column_tableA) = column_tableB
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||There is a chart which shows which data types are implicitly or explicitly
convertable in books on line search for convert... The chart indicates that
SQL can implicitly convert between nchar/nvarchar and char/varchar
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||Hi Wayne!
Yes I have seen the chart. It surprises me a bit that the query didn't use
the index.
It may be bug.
I´m looking for a text that describes in what situations the Query Planner
will not use an
index, but will do a full table scan. I think I have seen it once.
One example is when a query contains <column> like '%value%'
it won´t use an index on that column to solve the query.
/dg|||> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
Nope, it is how SQL Server works. Although you don't have to write any code
for an implicit conversion SQL Server still converts one datatype to another
when it creates the execution plan. If you run the following code in Query
Analyzer and look at the execution plan you will see that #B.B is converted
before the two tables are joined:
CREATE TABLE #A (A nvarchar(20))
GO
CREATE TABLE #B (B varchar(20))
GO
SELECT * FROM #A
INNER JOIN #B
ON #A.A = #B.B
GO
DROP TABLE #A, #B
GO
Why is #B.B converted and not #A.A? That is determined by the Data Type
Precedence. varchar has a lower Data Type Precedence than nvarchar, so
varchar gets converted. (You can find the complete list of the data type
precedence in Books Online under Data Type Precedence).
Because #B.B is used in a function (when it is converted), the Query
Optimizer can't use any indexes on the column and has to use a table scan.
As you saw when the other (nvarchar) column in the join is explicitly
converted, the varchar column won't be implicitly converted and the index on
the varchar column can be used.
About a text: Kalen Delaney has written a series of articles for SQL Server
Magazine (www.sqlmag.com) about which search conditions can make use of
indexes and which don't, and there is also a bit about it in her book Inside
SQL Server 2000.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uPHxWevPDHA.1720@.TK2MSFTNGP11.phx.gbl...
> Hi Wayne!
> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
> I´m looking for a text that describes in what situations the Query
Planner
> will not use an
> index, but will do a full table scan. I think I have seen it once.
> One example is when a query contains <column> like '%value%'
> it won´t use an index on that column to solve the query.
> /dg
>

Join Issue

my scenario is given below

createtable #product (prodID int, subproductid varchar(20))

createtable #subproduct (subproductid varchar(20),description varchar(40))

Insert #product select 1,'1001/2002'

Insert #product select 1,'3003/4004'

Insert #product select 1,'5005/6006'

insert subproduct select 1001 ,'aaa'

insert subproduct select 2002 ,'bbb'

insert subproduct select 3003 ,'ccc'

insert subproduct select 4004 ,'ddd'

insert subproduct select 5005 ,'eee'

insert subproduct select 6006 ,'fff'

this is how our two tables is related. i know its a bad design . but i can't help it.

my question is how can i join these two table ?

thanks in advance

Leena S

S Leena,

IF you know that the table design is bad, why can't you fix it? Do you need help in understanding why it is so bad?

Apparently, from the way the data is put together, and looking at this query, someone made some boneheaded decisions about how to store data in a database. You can be the 'hero' and correct the 'mistake'.

And then life, with queries such as this, will be so much easier...

|||

Hi,

One of the solutions would be a UDF that splits the text and join with that.

But I too suggest the above remark because the design is against the 'rules' of normalization imho ;-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||You could join using:

SELECT * --Change this
FROM #product JOIN #subproduct
ON '/' + #product.subproductid + '/' like '%/' + #subproduct.subproductid + '/%'

But as suggested already, you should consider changing your structure.

Rob|||

This is really bad design.

Anyhow if you are not authorized to change the design, the following approach may help you..

Code Snippet

create table #product(

prodID int,

subproductid varchar (20)

);

Insert #product select 1,'1001/2002'

Insert #product select 2,'3003/4004'

Insert #product select 3,'5005/6006'

create table #subproduct(

subproductid varchar(20),

description varchar(40)

);

Insert #subproduct select 1001 ,'aaa'

Insert #subproduct select 2002 ,'bbb'

Insert #subproduct select 3003 ,'ccc'

Insert #subproduct select 4004 ,'ddd'

Insert #subproduct select 5005 ,'eee'

Insert #subproduct select 6006 ,'fff'

--Generating Number Tables;

Select Identity(int,1,1) as Number Into #Numbers From #subproduct A Cross Join #subproduct B;

--Getting the results

Select ProdId,description From

(

Select

prodId

,Case When Number <= Len(subproductid) Then Substring('/' + subproductid + '/', Number+1, CharIndex('/',subproductid + '/', Number+1)-Number) End subproductid

from

#product P

Cross Join #Numbers N

Where

Number <= Len(subproductid) AndSUBSTRING('/' + subproductid + '/', number, 1) = '/'

) as Product

Join #subproduct sub on sub.subproductid = Product.subproductid