asked
left join "on conditions" using outer apply
OK here is some very simple code to demonstrate my example. I do not want to hit the tmp_Contact table again when running the function and please ignore the fact that I'm creating a function that returns the same results as the table, this is a very simplified example: -- **** code starts here **** create table tmp_Base (ID int, Name char(20)) create table tmp_Contact (ID int, ContactType tinyint, Email varchar(50)) insert tmp_Base(ID, Name) values (1, 'A'),(2, 'B'),(3, 'C') insert tmp_Contact(ID, ContactType, Email) values (2,1,
'111@B.com'), (2,2,
'222@B.com'), (2,3,
'333@B.com'), (2,4,
'444@B.com'), (3,1,
'111@C.com'), (3,8,
'888@C.com') go CREATE FUNCTION tmp_ReturnContactData (@UniqueID int) RETURNS TABLE AS RETURN select ID, ContactType, Email from tmp_Contact where ID = @UniqueID go -- **HERE IS THE RESULTSET I WANT** select * from tmp_Base b left join tmp_Contact c on c.ID = b.ID and c.ContactType in (1,2) -- how to make below return the required resultset select t1.ID, t1.Name, t2.ContactType, t2.Email from tmp_Base t1 outer apply tmp_ReturnContactData(t1.ID) t2 /* on t1.ContactType in (1,2) */ drop function tmp_ReturnContactData drop table tmp_Base drop table tmp_Contact -- **** code ends here **** **ORIGINAL POST** This is a greatly simplified version of the problem using SQL server 2008. I have 2 tables: Company and Contact. A simplified version of a query using a left join would be: select c.ID, c.CompanyName, con.ContactType, con.Email from Company c left join Contact con on con.ID = c.ID and con.ContactType in (1,2,3) where c.ID in (1,2) ID is a primary key and ContactType (tinyint) denotes "head office", "executive office" etc. There are around 15 ContactTypes in the ContactType table. My resultset from the above query would look something like this: ID CompanyName ContactType Email 1 ABC Inc. -- ID 1 has no contact information 2 XYZ Inc. 1
info@xyz.com 2 XYZ Inc. 2
exec@xyz.com 2 XYZ Inc. 3
ir@xyz.com I have created a table valued function to return the contact info. Currently it only accepts ID as an input parameter. I now want to return the same resultset. My new query, using the function, looks like this: select c.ID, c.CompanyName, con.ContactType, con.Email from Company c outer apply dbo.fn_Contact(c.ID) con where c.ID in (1,2) The problem with this is it returns me all the ContactTypes for ID 2 (eg ContactTypes 1,2,3 and 4 through to 15). If I try to add a condition in the "where" clause ("where con.ContactType in (1,2,3)") it's like an "inner join" and eliminates the ID = 1 row from the resultset. The only way that I can see to get around this is to pass the ContactType as a parameter to the function "dbo.fn_Contact(c.ID, 1)". But then it becomes problematic requesting multiple ContactTypes. I guess I could pass the ContactType in as a string and parse the string in the function: dbo.fn_Contact(c.FPID, '1,2'). Am I missing something obvious here? I've spent a considerable amount of time searching the web, but to no avail. Any help would be greatly appreciated. Thanks in advance, Dan
Can you please provide the tables structure & the output which is required?
Try moving the ContactType information from the WHERE clause to the ON clause. In the WHERE clause, as you say, it's turning it into an INNER JOIN.
Since you are asking for a functionality which is not it is made for, you cannot do it. Although there is a workaround something like this ; WITH CTE AS ( SELECT
t1.ID , t1.Name , t2.ContactType , t2.Email FROM tmp_Base t1 OUTER APPLY tmp_ReturnContactData(
t1.ID) t2 ) SELECT * FROM [CTE] WHERE ContactType IN ( 1, 2 ) OR ContactType IS NULL But I would not implement this if I were you. I would have stayed with either LEFT JOIN approach OR a DERIVED TABLE approach (As our Guru Mr. Grant Fritchey have pointed out). This solution is not good performance wise. You would see the difference as the data gets bigger and bigger. This is where I have added it as a comment, not as answer. I would rather ask you to be flexible in your thoughts and stay with either LEFT JOIN OR DERIVED TABLE.
The function that you're calling is the filter. I'm not a fan of functions for this. You can still use OUTER APPLY with a derived table.
Thanks for the reponse and I'm not married to this solution. I'm not sure I understand the "filter" part of your answer. Are you saying that the function SHOULD be the filter or that my function IS the filter (and shouldn't be)? I'll try to explain in more detail, what I'm attempting to do. We have a CompanyContact table which contains email address, phone and fax numbers etc and a CompanyAddress table which contains the street address (please don't ask why this isn't a single table as there's actually a third and fourth table involved, but it all becomes much too complicated to explain). The function uses SQL's MERGE operation to merge the results from the CompanyContact and CompanyAddress tables, based on a Company's unique ID and ContactType. It then spits out the result as a single row. So a company may have an Investor Relations contact and no Investor Relations address, or vice versa, or it may have Investor Relations data in both tables. I don't want to restrict what ContactTypes the function works with as different products have different requirements for which ContactType(s) to use. I thought a FUNCTION would be perfect for this as I don't think I can easily accomplish this with a VIEW or a STORED PROCEDURE. Any thoughts on how to go about this would be appreciated. Here's the function if it helps: USE [CIS] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Function [dbo].[CompanyContactAddress] ( @FPID int--, ) Returns @CompanyContactAddress table( FPID int, TYPE tinyint, PRECEDENCE tinyint, ContactDatakey int, ParentContact bit, FPPID int, Title varchar(16), FirstName char(20), MiddleName char(20), LastName char(26), Kinship char(5), FullName varchar(91), Position smallint, PositionDesc char(60), PositionAbbrev char(30), PositionText varchar(100), FullPosition varchar(8000), AbbrevPosition varchar(8000), Email varchar(120), PhoneAreaCode char(3), PhonePrefix char(3), PhoneSuffix char(4), PhoneExtension char(5), FaxAreaCode char(3), FaxPrefix char(3), FaxSuffix char(4), TollAreaCode char(3), TollPrefix char(3), TollSuffix char(4), OverseasPhoneCountry varchar(3), OverseasPhone varchar(25), OverseasFaxCountry varchar(3), OverseasFax varchar(25), PrivacyContact bit, AddressDataKey int, ParentAddress bit, Line1 varchar(40), Line2 varchar(40), Line3 varchar(40), CityCode smallint, City char(50), ProvCode smallint, Province char(28), ProvAbbrev char(14), ProvPostal char(4), CountryCode smallint, Country char(40), Postal char(12), PrivacyAddress bit Primary Key(FPID, TYPE, PRECEDENCE) ) as begin insert @CompanyContactAddress -- get the contact info select CC.FPID, CC.Type, CC.Precedence, CC.ContactDatakey, ParentContact = CC.Parent, CD.FPPID, Title = isnull(zT.Abbrev,''), FirstName = isnull(Pe.FirstName,CD.FirstName), MiddleName = isnull(Pe.MiddleName,CD.MiddleName), LastName = isnull(Pe.LastName,CD.LastName), Kinship = isnull(zK.Description,''), FullName = case when CD.FPPID is null then ltrim(rtrim(' '+CD.FirstName) + rtrim(' '+CD.MiddleName) + rtrim(' '+CD.LastName)) else ltrim(rtrim(zT.Abbrev) + rtrim(' '+Pe.FirstName) + rtrim(' '+Pe.MiddleName) + rtrim(' '+Pe.LastName) + rtrim(' '+zK.Description)) end, Position = zP.Code, PositionDesc = zP.Description, PositionAbbrev = zP.Abbrev, PositionText = isnull(Po.PositionText,CD.PositionText), FullPosition = ltrim(rtrim(zP.Description) + rtrim(' ' + replace(isnull(Po.PositionText,CD.PositionText),'investor rel.','investor relations'))), AbbrevPosition = ltrim(rtrim(zP.Abbrev) + rtrim(' ' + replace(isnull(Po.PositionText,CD.PositionText),'investor relations','investor rel.'))), CD.Email, CD.PhoneAreaCode, CD.PhonePrefix, CD.PhoneSuffix, CD.PhoneExtension, CD.FaxAreaCode, CD.FaxPrefix, CD.FaxSuffix, CD.TollAreaCode, CD.TollPrefix, CD.TollSuffix, CD.OverseasPhoneCountry, CD.OverseasPhone, CD.OverseasFaxCountry, CD.OverseasFax, CD.Privacy, -- empty address fields AddressDataKey = 0, ParentAddress = 0, Line1 = '', Line2 = '', Line3 = '', CityCode = 0, City = '', ProvCode = 0, Province = '', ProvAbbrev = '', ProvPostal = '', CountryCode = 0, Country = '', Postal = '', PrivacyAddress = 0 from CompanyContact CC inner join ContactData CD on CD.ContactDatakey = CC.ContactDatakey inner join CompanyContact CP on CP.ContactDatakey = CC.ContactDatakey and CP.Parent = 1 left join Personnel Pr on Pr.FPID = CP.FPID and Pr.FPPID = CD.FPPID left join Person Pe on Pe.FPPID = CD.FPPID left join zTitles zT on zT.Code = Pe.Title left join zKinship zK on zK.Code = Pe.Kinship left join Position Po on Po.Datakey = Pr.Datakey and Po.Sequence = (Select top 1 Sequence from Position Po1 inner join zPositions zP1 on zP1.Code = Po1.Position where Po1.Datakey = Pr.Datakey and zP1.Display = 1 and zP1.Director = 0 order by zP1.Sort) inner join zPositions zP on zP.Code = case when CD.FPPID is null then CD.Position else isnull(Po.Position,0) end where CC.FPID = @FPID -- now either insert or update the existing contact data in @ContactAddress with address data merge @CompanyContactAddress as cm using -- select the address data (select A.FPID, A.TYPE, A.PRECEDENCE, A.AddressDataKey, A.Parent, D.Line1, D.Line2, D.Line3, zCi.CityCode, City = zCi.Description, zCi.ProvCode, zPr.Province, zPr.ProvAbbrev, ProvPostal = zPr.Postal, zCi.CountryCode, zCo.Country, D.Postal, D.Privacy from CompanyAddress A inner join AddressData D on D.AddressDataKey = A.AddressDataKey inner join zCity zCi on zCi.CityCode = D.City inner join zProvState zPr on zPr.ProvCode = zCi.ProvCode inner join zCountry zCo on zCo.CountryCode = zCi.CountryCode where FPID = @FPID) as a on cm.FPID = a.FPID and cm.TYPE = a.TYPE and cm.PRECEDENCE = a.PRECEDENCE -- if matched (the ID, TYPE and PRECEDENCE already exist in the @ContactAddress table) then update with the address data when matched then update set cm.AddressDataKey = a.AddressDataKey, cm.ParentAddress = a.Parent, cm.Line1 = a.Line1, cm.Line2 = a.Line2, cm.Line3 = a.Line3, cm.CityCode = a.CityCode, cm.City = a.City, cm.ProvCode = a.ProvCode, cm.Province = a.Province, cm.ProvAbbrev = a.ProvAbbrev, cm.ProvPostal = a.ProvPostal, cm.CountryCode = a.CountryCode, cm.Country = a.Country, cm.Postal = a.Postal, cm.PrivacyAddress = a.Privacy -- if not matched then insert the address data when not matched then insert (FPID, TYPE, PRECEDENCE, AddressDataKey, ParentAddress, Line1, Line2, Line3, CityCode, City, ProvCode, Province, ProvAbbrev, ProvPostal, CountryCode, Country, Postal, PrivacyAddress) values (a.FPID, a.TYPE, a.PRECEDENCE, a.AddressDataKey, a.Parent, a.Line1, a.Line2, a.Line3, a.CityCode, a.City, a.ProvCode, a.Province, a.ProvAbbrev, a.ProvPostal, a.CountryCode, a.Country, a.Postal, a.Privacy ); return; end;
