Conflicting Joins in Derived Table

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)

Conflicting Joins in Derived Table

Author

Topic

jono
Starting Member

6 Posts

Posted - 2003-01-15 : 15:52:38

Hi,

I’m having a problem with joins in a derived table. I work at a health plan and am trying to identify one primary location for mailing purposes for each of our Primary Care Providers, who may each practice at multiple locations. The three tables I’m working with are:

PCPLocs, which identifies all active PCP/Location pairs and contains the associated physical addresses, and is unique on provider_id and loc_no;

PCPLocs_MailingAddr, which lists a mailing address for certain provider locations where it has been identified that the Post Office won’t deliver to the physical address, and is also unique on provider_id and loc_no;

and PCPLocsPrimaryAddr, which is unique on provider_id and contains only the provider_id and loc_no for providers with multiple addresses if one of the addresses has been identified and verified as the primary address.

What I want is a query that has one mailing address for each distinct provider_id from the PCPLocs table. If an address has been identified as primary in PCPLocs_PrimaryAddr, use that location; else, use the minimum location number from the PCPLocs table. Then, for each of those provider locations identified, use a mailing address if present, else use the physical address from PCPLocs.

Here’s what I have so far. I identify unique Provider IDs and the location number I want to use in the derived table, dt_pa (short for preferred_address, not to be confused with primary address), then pull in the appropriate mailing or physical address in the upper query.

select distinct dt_pa.provider_id,
dt_pa.loc_no,
pl.lname,
pl.fname,
pl.mname,
pl.name_suffix,
pl.specialty,
pl.clinic,
pl.mso_id,
pl.center,
case
when ma.mailingaddr1 is not null
then ma.mailingaddr1
else pl.addr1
end as addr1,
case
when ma.mailingaddr1 is not null
then ma.mailingaddr2
else pl.addr2
end as addr2,
case
when ma.mailingcity is not null
then ma.mailingcity
else pl.city
end as city,
case
when ma.mailingzip is not null
then ma.mailingzip
else pl.zip
end as zip
from (select b.provider_id,
'loc_no' =
case
when a.provider_id is null
then b.loc_no
else a.loc_no
end
from pcplocsprimaryaddr a,
(
select provider_id,
min(loc_no) as loc_no
from pcplocs
group by provider_id
) b
where b.provider_id *= a.provider_id
and b.loc_no *= a.loc_no
) dt_pa,
pcplocs pl,
pcplocsmailingaddr ma
where dt_pa.provider_id = pl.provider_id
and dt_pa.loc_no = pl.loc_no
and dt_pa.provider_id *= ma.provider_id
and dt_pa.loc_no *= ma.loc_no
order by dt_pa.provider_id,
dt_pa.loc_no

The derived table query runs fine by itself. If I run the derived table into a temp table and then run the main query off the temp table, the main query will work too. But when I try to run it as written, I get message 303: pcplocsprimaryaddr is the inner member of an outer-join clause, which is not allowed if the table also participates in a regular join clause. I was hoping to be able to do this without the use of temp tables. Any recommendations would be greatly appreciated.

Cheers,
Jono

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-16 : 07:21:55

Wouldn't your query be so much easier if you normalized and kept all your address in one table?

Jay White
{0}

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-01-16 : 07:54:40

Don't use the *= syntax for outer joins. That format was deprecated years ago and is no longer ANSI standard. SQL Server will no longer recognize it in its next version (Yukon). Its use today is problematic when join syntaxes are mixed - for example, if you used the old outer join syntax in a view and a newer one in a parent view.

In terms of normalization, explain the reason for the table "PCPLocs_MailingAddr". I agree with Page in wondering what this denormalization gets you (other than more complex query writing).

Jonathan
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-16 : 08:08:06

Quick solution:

Try re-writing your query using JOIN syntax; it makes finding errors like this much easier and is more standard.

It clearly indicates table relationships and also seperates criteria from join conditions, and also clearly indicates outer joins vs. inner joins.

I suspect you currently have a mix of = and *= on the same relationship somewhere.

Theoritical Easier Solution:

If you have the same data spread out in multiple tables, try something like:

SELECT Provider_ID, 10000 as priority, Address1, Address2, City, ...
FROM SQL1 ( -- the main place to get addresses)
UNION ALL
SELECT Provider_ID, 20000 + Location_Number as priority, Address1, Address2, City, ....
FROM SQL2 ( -- secondary place, where you want min of location #)
UNION ALL
SELECT Provider_ID, 30000 as Priority, Address1, Address2, City, ....
FROM SQL3
etc...

Each "SQL#" part is the SQL or tablename of each address. Put them all in the same format with the same field names, and make sure "Priority" is unique for each part of the UNION. Note the second one includes the logic so the "Priority" will end up including the lowest location number for the table in which you indicated that needs to be the case.

Then, you can GROUP BY Provider_ID and take MIN(Priorty) as that if the place you want to get your address data from for each provider.

The final result would be something like:

SELECT A.*
FROM
(aboveSQL) A
INNER JOIN
(
SELECT Provider_ID, Min(Priority) as MinPriority
FROM (above SQL) A
GROUP BY Provider_ID
) B
ON A.Provider_ID = B.Provider_ID and A.Priorty = B.MinPriority

- Jeff

Edited by - jsmith8858 on 01/16/2003 08:09:29

jono
Starting Member

6 Posts

Posted - 2003-01-16 : 13:10:28

quote:

Wouldn't your query be so much easier if you normalized and kept all your address in one table?

Jay White
{0}

jono
Starting Member

6 Posts

Posted - 2003-01-16 : 13:11:09

quote:

Wouldn't your query be so much easier if you normalized and kept all your address in one table?

Jay White
{0}

jono
Starting Member

6 Posts

Posted - 2003-01-16 : 13:25:52

I suppose I should have explained about why the mailing addresses are being kept separately in the first place. We have a sister company that maintains our claims system, which houses our providers' physical addresses. The system is on Oracle, and once a month they send us a frozen data extract, which we convert and pump into SQL Server for reporting purposes. The claims system does not store a mailing address that is associated with each of the physical addresses. It does house vendor address(es), but often times the vendor is in a different location, or even another state, than the provider.

This has been a nightmare for mailing purposes, when we want to send things to PCPs. So folks in our company started tracking providers' mailing addresses for those where mail to the physical address was undeliverable. We've kept the mailing addresses separate since the PCPLocs table is re-created every month and the mailing addresses are not. Ultimately, this query will end up being a view in an Access application that will allow users to print mailing labels - one for each provider, regardless of how many locations they have (which is why they also want to be able to identify a primary practice - another thing of which our "claims system" isn't capable).

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-16 : 13:32:47

A business need should never be justification for throwing away the relational model. You can expose the data to your organization however you please, but your physical implementation should concern itself with dri and normalization first and foremost.

Example ... put a type char(1) column (or some such thing) on your addr table. Then your query becomes much simpler and you can present whichever type of address you wish to the data consumer.

Jay White
{0}

jono
Starting Member

6 Posts

Posted - 2003-01-16 : 16:10:39

quote:

Each "SQL#" part is the SQL or tablename of each address. Put them all in the same format with the same field names, and make sure "Priority" is unique for each part of the UNION. Note the second one includes the logic so the "Priority" will end up including the lowest location number for the table in which you indicated that needs to be the case.

Then, you can GROUP BY Provider_ID and take MIN(Priorty) as that if the place you want to get your address data from for each provider.

The final result would be something like:

SELECT A.*
FROM
(aboveSQL) A
INNER JOIN
(
SELECT Provider_ID, Min(Priority) as MinPriority
FROM (above SQL) A
GROUP BY Provider_ID
) B
ON A.Provider_ID = B.Provider_ID and A.Priorty = B.MinPriority

- Jeff

Edited by - jsmith8858 on 01/16/2003 08:09:29

Thanks Jeff, that works beautifully.

Jay, fyi, it's not as much about putting a business need ahead of the relational model as it is about a political climate over which I have no control. I'll spare you the details, but in a nutshell: I can't change how addresses are captured in our claims system, I can't tamper with our frozen data and I don't administer our servers. I just write queries and go home. I appreciate your thoughts, and I don't disagree, but there are severe limits to what I can do around here and I just have to work within them.

As for the non-ANSI joins, I learned to use them from a co-worker who, ironically, was chastised for bothering to use the ANSI joins the last place she worked. Neither of us were aware that they'll no longer be supported, so we'll be sure to take them out of all our programs. Thanks for the alert.

Cheers,
Jono

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources