Multiple Outer and Inner joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple Outer and Inner joins

Author

Topic

vijeyashobana
Starting Member

9 Posts

Posted - 2008-09-05 : 12:56:41

I have a UDF I need to call twice. UDF name is dbo.ufn_splitToStringTable. I call the UDF to match the results based on zipcode.so inner join here.

left outer JOIN PI_Common AS p ON p.accountID = a.accountID

LEFT OUTER JOIN ABI_customerFlag ac on ac.abinum = a.abinum

inner join dbo.ufn_splitToStringTable(@zipcodes) z on z.itemList = a.zipcode

inner join dbo.ufn_splitToStringTable(@zipcodes1) z1 on z1.itemList = a.zipcode

here @zipcodes, @zipcodes1 may or may not be empty.

If @zipcodes is empty, matching should goto 2nd inner join,

if @zipcodes1 is empty, matching should be in 1st inner join.

If @zipcodes, @zipcodes1 are both not empty, matching should be done by calling the UDF both times.

If I use one inner join I get the required results. If I use both these inner joins and set @zipcodes1 is empty, I get 0.
I cannot modify the UDF so I have to find some way with joins here.

So how to fix this issue? I mean I need the results matching the UDF. How can I have 2 inner joins and still get the results??

Any ideas/suggestions welcome and appreciated!!!

Thanks,
Vijeya Shobana

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-05 : 13:07:34

I just did a quick read, so forgive me if I'm missing something... But, I think what you want to do is create a table variable the load that with the results of the two UDF calls and simply join to the table variable.

vijeyashobana
Starting Member

9 Posts

Posted - 2008-09-05 : 13:17:49

here i want to join a few tables and extract the results. The LEFT OUTER JOINS are used on PI_Common p , ABI_Customer ac and Account a tables. Now I have to filter the resulting records based on zipcodes.

UDF ufn_splitToStringTable is used to split the zipcodes. @zipcodes, @zipcodes are set dynamically in the stored proc.

If @zipcodes is empty, the 1st inner join should be skipped and results should be matched on @zipcodes1
If @zipcodes1 is empty, the 2nd inner join should be skipped and results should be matched on @zipcodes.
If @zipcodes, @zipcodes1 both exist, the inner joins should not be skipped and results should be matched on both @zipcodes and @zipcodes1.

I don't see a best way for this problem, I am sure it should be very simple but right now, i'm missing it.

Thanks & Regards

vijeyashobana
Starting Member

9 Posts

Posted - 2008-09-05 : 13:28:05

quote:
Originally posted by Lamprey

I just did a quick read, so forgive me if I'm missing something... But, I think what you want to do is create a table variable the load that with the results of the two UDF calls and simply join to the table variable.

no i'm not using table variable here. i'm displaying the results of this query.

Thanks & Regards

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-05 : 13:43:52

quote:
Originally posted by vijeyashobana

quote:
Originally posted by Lamprey

I just did a quick read, so forgive me if I'm missing something... But, I think what you want to do is create a table variable the load that with the results of the two UDF calls and simply join to the table variable.

no i'm not using table variable here. i'm displaying the results of this query.

I know I am suggesting that you use a table variable. For Example:

DECLARE @ZipCode TABLE (ZipCode VARCHAR(10) PRIMARY KEY)

INSERT 
	@ZipCode
SELECT 
	DISTINCT itemList
FROM 
(
	SELECT ItemList
	FROM dbo.ufn_splitToStringTable(@zipcodes)
	
	UNION ALL
	
	SELECT ItemList
	FROM dbo.ufn_splitToStringTable(@zipcodes1)
) AS T

-- Your code:

left outer JOIN PI_Common AS p ON p.accountID = a.accountID

LEFT OUTER JOIN ABI_customerFlag ac on ac.abinum = a.abinum

inner join @ZipCode AS Zip ON Zip.ZipCode = a.zipcode

vijeyashobana
Starting Member

9 Posts

Posted - 2008-09-05 : 13:59:04

Thanks for your prompt reply Lamprey. I get your point. Will reply back after I've modified the query.

Thanks

Thanks & Regards

vijeyashobana
Starting Member

9 Posts

Posted - 2008-09-05 : 14:06:52

:) ya got it. I'm using that particular inner join in at least a dozen other places, sometimes with other joins. So placing this UNION in a separate variable makes it reusable and I don't have to change in other places too. Thanks for your help Lamprey. I appreciate it.

Thanks
VijeyaShobana

Thanks & Regards

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-05 : 14:39:19

You are welcome, I'm gald it helped! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources