October 25, 2011 at 10:48 am
I have 3 tables. One is SiteSchool, one is School, the other is Site.
The 3 tables are as below, my question is in schSchool table we have a permSiteID, now we want to add another tempSiteID in the schSchool table. We want to use it like if tempsiteID is not null, we use it to join the SchSite table to find out address, if it is null, we want use PermsiteId to join the other tables to find more information about site.
How can we do this?
In the schSchoolSite table, ( the school this year is in remodeling or construction, so we have a temp location), we then have two rows in this table, one is for temp, one is for permenant.
CREATE TABLE [dbo].[schSchool](
[SchoolYear] [int] NOT NULL,
[SchoolID] [int] NOT NULL,
[SchoolNm] [varchar](40) NOT NULL,
[SchoolLongNm] [varchar](80) NOT NULL,
[AttendanceAreaID] [int] NULL,
[CategoryID] [int] NULL,
[OrgUnitID] [int] NULL,
[StartTime] [time](0) NOT NULL,
[DismissalTime] [time](0) NOT NULL,
[SchoolPhoneNbr] [char](10) NOT NULL,
[PermSiteID] [int] NULL,
[IsAssignmentSchool] [bit] NOT NULL,
[IsEnrollmentSchool] [bit] NOT NULL,
[GeographicZoneID] [int] NULL,
[IsClosed] [bit] NOT NULL,
[ClosureDt] [date] NULL,
[CreateDt] [datetime2](7) NOT NULL,
[CreatedBy] [varchar](128) NOT NULL,
[ChangeDt] [datetime2](7) NULL,
[ChangedBy] [varchar](128) NULL,
[SchoolTypeID] [int] NULL,
[MultiSiteInd] [bit] NULL,
[EnrollmentType] [varchar](25) NULL,
[StateSchoolID] [int] NULL
CONSTRAINT [PK_schSchool] PRIMARY KEY CLUSTERED
(
[SchoolYear] ASC,
[SchoolID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[schSite](
[SiteID] [int] NOT NULL,
[SiteNm] [varchar](40) NOT NULL,
[SiteHouseNbr] [int] NULL,
[SiteHouseNbrModifier] [varchar](10) NULL,
[SiteStreetPrefix] [char](2) NULL,
[SiteStreetNm] [varchar](40) NULL,
[SiteStreetType] [char](4) NULL,
[SiteStreetSuffix] [char](3) NULL,
[SiteZipCd] [char](5) NULL,
[SiteX] [decimal](10, 2) NULL,
[SiteY] [decimal](10, 2) NULL,
[CreateDt] [datetime2](7) NOT NULL,
[CreatedBy] [varchar](128) NOT NULL,
[ChangeDt] [datetime2](7) NULL,
[ChangedBy] [varchar](128) NULL,
CONSTRAINT [PK_schSite] PRIMARY KEY CLUSTERED
(
[SiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[schSiteSchool](
[SchoolYear] [int] NOT NULL,
[SiteID] [int] NOT NULL,
[SchoolID] [int] NOT NULL,
[CreateDt] [datetime2](7) NOT NULL,
[CreatedBy] [varchar](128) NOT NULL,
[ChangeDt] [datetime2](7) NULL,
[ChangedBy] [varchar](128) NULL,
[GeneralOfficePhoneNbr] [varchar](15) NULL,
[GeneralOfficeFaxNbr] [varchar](15) NULL,
[MailStop] [varchar](15) NULL,
[AttendancePhoneNbr] [varchar](15) NULL,
[SiteAbbrevCd] [varchar](15) NULL,
[HasReportEnrollCount] [bit] NULL,
CONSTRAINT [PK_schSiteSchool] PRIMARY KEY CLUSTERED
(
[SchoolYear] ASC,
[SiteID] ASC,
[SchoolID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[schSiteSchool] WITH CHECK ADD CONSTRAINT [FK_schSiteSchool_schSchool_SchoolIDSchoolYear] FOREIGN KEY([SchoolYear], [SchoolID])
REFERENCES [dbo].[schSchool] ([SchoolYear], [SchoolID])
GO
ALTER TABLE [dbo].[schSiteSchool] CHECK CONSTRAINT [FK_schSiteSchool_schSchool_SchoolIDSchoolYear]
GO
October 25, 2011 at 10:50 am
just join on tablea = coalesce(tempsiteID, permSiteID)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2011 at 10:52 am
Thanks, that is what I think too.
Could you direct me to more examples that use coalesce in join?
I cannot find many examples about this, thanks much.
October 25, 2011 at 10:55 am
join school on school.SiteID = coalesce(tempsiteID, permsiteID)
Not sure what else can be said. If tempsiteID is not null then it will join on that, otherwise it will join on the value of permsiteID.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2011 at 1:10 pm
Using a coalesce in a joining or filtering condition is almost always a bad idea. You're going to end up with a full scan on the table in question. I made a simple mock-up to demonstrate this.
I'm making a simple table and populating it with 400 records. Some of the records will have a MatchValueTemp and the others will remain NULL. Also, notice that I'm putting indexes on the table.
use tempdb
go
create table dbo.Table1
( id int identity primary key
, MatchValue int null
, MatchValueTemp int null );
with cteBase as
(
select N from ( values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20) ) as sq(N)
)
insert into dbo.Table1
select row_number() over(order by b2.N),
case when (b1.N * b2.N) % 3 = 0 then (b1.N * b2.N) + 11 end
from cteBase b1
cross join cteBase b2;
create index ix_Table1_1 on dbo.Table1 (MatchValue) include (id, MatchValueTemp);
create index ix_Table1_2 on dbo.Table1 (MatchValueTemp) include (id, MatchValue);
Just to get an idea of what the data data looks like:
select *
from dbo.Table1
order by id;
Now I'm going to create a little table to hold the data I want to match up:
create table dbo.Table2
( MatchValue int primary key );
insert into dbo.Table2
values
(14),--found in both regular and temp
(17),--found in both regular and temp
(23);--match temp, regular value is overwritten by temp
Now we can run the coalesce query:
select t1.id, t1.MatchValue, t1.MatchValueTemp
from dbo.Table2 t2
join dbo.Table1 t1
on t2.MatchValue = coalesce(t1.MatchValueTemp, t1.MatchValue)
order by t1.id;
What we get is a scan of our little table, which is expected, but also a full scan of the bigger table. In this example it's only 400 records, but even so, it's noticeable expensive. We also have a compute scalar for the coalesce function and then an expensive hash match operation to join the full 400 records with our small table. Keep in mind, when you use coalesce, it's going to have to perform the logic on all records in the table and its only use of an index will be to pick the most narrow one to perform the full scan it is going to have to do.
Here is demonstrated an alternate method:
select t1.id, t1.MatchValue, t1.MatchValueTemp
from dbo.Table2 t2
join dbo.Table1 t1
on t2.MatchValue = t1.MatchValueTemp
union
select t1.id, t1.MatchValue, t1.MatchValueTemp
from dbo.Table2 t2
join dbo.Table1 t1
on t2.MatchValue = t1.MatchValue
order by t1.id;
In this case, 2 indexes are used separately and instead of a 400 record scan we end up with one seek on 3 records and another on 12 records. Then, because the result sets are much smaller, more efficient nested loops are performed instead of the hash match from the coalesce query.
Coalesce can be great in the latter portions of query processing (like building the select list) but it's generally pretty terrible for joins and filters.
October 26, 2011 at 12:13 am
Apart from the technical reasons not to use coalesce (or isnull, which does the same thing) in join or where clauses, this problem is better addressed by a little remodeling of your SiteSchool table. You currently have a key of SchoolYear, SiteID, SchoolID. How will you store a school that has 2 (or even more) locations during a particular schoolyear? And what if -heaven forbid- a site burns down somewhere half way a school year, how will you store the new location for that school for the remainder of the year?
If you would move the following attributes into the site table or into some new table with school contact information:
[GeneralOfficePhoneNbr] [varchar](15) NULL,
[GeneralOfficeFaxNbr] [varchar](15) NULL,
[MailStop] [varchar](15) NULL,
[AttendancePhoneNbr] [varchar](15) NULL,
[HasReportEnrollCount] [bit] NULL
Then replace the SchoolYear column by 2 date columns to represent a validity date range. Plus give the table it's own ID column: SiteSchoolID. Create a (non-unique) index on SchoolID, ValidFromDt, ValidToDt, SiteID.
Now you can store all sorts of changes in the sites for your schools: multiple sites for a school, a school that moves sites during a school year, a school that moves site between school years and your temp site.
Something like this (I haven't tested it, but it should give you an idea of what I mean):
CREATE TABLE [dbo].[schSiteSchool](
[SiteSchoolID] [int] NOT NULL,
[ValidFromDt] [Date] NOT NULL,
[ValidToDt] [Date] NOT NULL,
[SiteID] [int] NOT NULL,
[SchoolID] [int] NOT NULL,
[CreateDt] [datetime2](7) NOT NULL,
[CreatedBy] [varchar](128) NOT NULL,
[ChangeDt] [datetime2](7) NULL,
[ChangedBy] [varchar](128) NULL,
[SiteAbbrevCd] [varchar](15) NULL,
CONSTRAINT [PK_schSiteSchool] PRIMARY KEY CLUSTERED
(
[SiteSchoolID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [FK_schSiteSchool_schSchool] FOREIGN KEY (SchoolID) REFERENCES dbo.[schSchool] (SchoolID),
CONSTRAINT [FK_schSiteSchool_schSite] FOREIGN KEY (SiteID) REFERENCES dbo.[schSite] (SiteID)
) ON [PRIMARY]
GO
CREATE INDEX ixSiteSchool_SchoolSite ON [dbo].[schSiteSchool]([SchoolID], [ValidFromDt], [ValidToDt], [SiteID])
GO
CREATE INDEX ixSiteSchool_SiteAbbrev ON [dbo].[schSiteSchool]([SiteAbbrevCd], [ValidFromDt], [ValidToDt])
GO
October 26, 2011 at 10:09 am
Thanks for all the help, I will think it over to see what is the best solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply