Help with SELECT statement

Hi,
I have two tables with a UserID column and need to construct a query that
lists all UserIDs from Table A that is not present in Table B.
Any help with this select statement would be appreciated
NiclasSelect A.* from TableA as A where Not Exists (select * from TableB as B
where B.UserId = A.UserId)
You can also go with a Left Outer Join but it's a little more complicated to
understand:
Select A.* from TableA as A Left Outer Join TableB as B on A.UserId =
B.UserId
Where B.UserId is Null
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||A third possibility would be to use the IN clause:
Select A.* from TableA as A where A.UserId Not IN (select UserId from TableB
Where UserId is not Null)
The condition Where B.UserId is Not Null is a necessity if there is a
possibility that B.UserId can be Null; otherwise the result won't be good if
the IN clause encounter a Null value.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||Many thanks !
Niclas
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:emU3YG65FHA.2888@.tk2msftngp13.phx.gbl...
>A third possibility would be to use the IN clause:
> Select A.* from TableA as A where A.UserId Not IN (select UserId from
> TableB Where UserId is not Null)
> The condition Where B.UserId is Not Null is a necessity if there is a
> possibility that B.UserId can be Null; otherwise the result won't be good
> if the IN clause encounter a Null value.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Niclas" <lindblom_niclas@.hotmail.com> wrote in message
> news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
>

Help with multi Join or multi tier select.

Hello,

I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables.
PlanID is the primary in Plan and

PlanProviderProviderLocationLookups
--------------
PlanIDProviderIDProviderIDLookupType
PlanNamePlanIDProviderStatusLookupKey
RegionIDLastName...LookupValue
...FirstName...

Given a PlanID I want all the Providers with a ProviderStatus = 0

I can get the query to work just fine if there are records but what I
want is if there are no records then I at least want one record with
the Plan information. Here is a sample of the Query:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
left outer JOIN Provider p ON pln.PlanID = p.PlanID
left outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey
WHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

I know the problew the ProviderStatus on the Where clause is keeping
any records from being returned but I'm not good enough at this to
another select.

Can anybody give me some suggestions?

Thanks

DavidTry moving the predicate "AND PL.providerstatus = 0" into the ON clause:

FROM Plans AS PLN
INNER JOIN Lookups L3
ON L3.LookupType = 'REGN'
AND PLN.regionid = L3.lookupkey
LEFT OUTER JOIN Provider AS P
ON PLN.planid = P.planid
LEFT OUTER JOIN ProviderLocation AS PL
ON P.providerid = PL.providerid
AND PL.providerstatus = 0
LEFT OUTER JOIN Lookups AS L1
ON L1.lookuptype = 'PLRM'
AND PL.reasonmain = L1.lookupkey
LEFT OUTER JOIN Lookups AS L2
ON L2.lookuptype = 'PLX1'
AND PL.reasonsub = L2.lookupkey
WHERE PLN.planid = '123456789'

--
David Portas
SQL Server MVP
--|||No that didn't work becase then it all the providers ... and I think
only the locations with with providerstatus = 0.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RpOdnffKDN6GPVfdRVn-sQ@.giganews.com>...
> Try moving the predicate "AND PL.providerstatus = 0" into the ON clause:
> FROM Plans AS PLN
> INNER JOIN Lookups L3
> ON L3.LookupType = 'REGN'
> AND PLN.regionid = L3.lookupkey
> LEFT OUTER JOIN Provider AS P
> ON PLN.planid = P.planid
> LEFT OUTER JOIN ProviderLocation AS PL
> ON P.providerid = PL.providerid
> AND PL.providerstatus = 0
> LEFT OUTER JOIN Lookups AS L1
> ON L1.lookuptype = 'PLRM'
> AND PL.reasonmain = L1.lookupkey
> LEFT OUTER JOIN Lookups AS L2
> ON L2.lookuptype = 'PLX1'
> AND PL.reasonsub = L2.lookupkey
> WHERE PLN.planid = '123456789'|||I understood that you wanted to include rows from Plans which didn't have
corresponding rows from ProviderLocation - in which case they won't have a
ProviderStatus. It may be easier to understand your requirements if you post
DDL, sample data INSERTs and show your required result based on that sample
data. (http://www.aspfaq.com/5006)

--
David Portas
SQL Server MVP
--|||>> I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the other tables. <<

Mind posting some DDL? When see data element names as poorily written
as "LookupType", "LookupKey" and "LookupValue", it is a pretty sure
sign that the basic schema design is wrong. To be is to be something
in particular and those names imply that you have a "One True Lookup
Table" (OTLT) flaw. Yes, like many diseases or disasters, it is
common enough to have a name! Google it; I wrote a column on it in
INTELLIGENT ENTERPRISE magazine.|||[posted and mailed, please reply in news]

David Logan (ibflyfishin@.yahoo.com) writes:
> I can get the query to work just fine if there are records but what I
> want is if there are no records then I at least want one record with
> the Plan information. Here is a sample of the Query:
> SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
> p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
> pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
> l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
> pl.InvalidData
> FROM Plans pln
> INNER JOIN Lookups l3 ON l3.LookupType = 'REGN'
> AND pln.RegionID = l3.Lookupkey
> left outer JOIN Provider p ON pln.PlanID = p.PlanID
> left outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
> left outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'
> AND pl.ReasonMain = l1.LookupKey
> left outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'
> AND pl.ReasonSub = l2.Lookupkey
> WHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0
> ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum
> I know the problew the ProviderStatus on the Where clause is keeping
> any records from being returned but I'm not good enough at this to
> another select.

As David said, it is always a good idea to include CREATE TABLE and
sample data. But I think I have a guess what will work for you:

SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,
p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,
pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,
l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,
pl.InvalidData
FROM Plans pln
JOIN Lookups l3 ON l3.LookupType = 'REGN'
AND pln.RegionID = l3.Lookupkey
LEFT JOIN (Provider p
JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderID
AND pl.ProviderStatus = 0
JOIN Lookups l1 ON l1.LookupType = 'PLRM'
AND pl.ReasonMain = l1.LookupKey
JOIN Lookups l2 ON l2.LookupType = 'PLX1'
AND pl.ReasonSub = l2.Lookupkey)
ON pln.PlanID = p.PlanID
WHERE pln.PlanID = '123456789'
ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNum

The point here is that the thing in parathensis is sort of a logical
table, and you make an outer-join to that logical table.

This is the normal way of doing things when you want to join a
left-joined table with a lookup table (should not be necessary to
left-join the lookup table). In this case it also necessary, to
exclude providers which does not have any location with status = 0.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help with Linked Servers

Hi Everyone,

I am developing a reporting database server that will use multiple data sources to construct its query (SQL Server 2005, Oracle 9i, and DB2). I successfully linked all 3 databases to my SQL Server 2005 and I am ready to create the report queries. I would like to know what is the best method to do this. Should I refer to the linked servers by four part naming or use a OPENQUERY query. When using a OPENQUERY, you cannot specify parameters to pass to the linked server which makes me question its performance. Can someone please suggest the best way to do this?

Thank you in advance

-Sam

The 'best' method is most likely the four part naming. It allows you to create more robust queries.

OPENQUERY is best used when the remote server is rarely used and for security (or other) reasons, having a 'linked server' is not appropriate. If the remote server is frequently used, then, by all means, add it as a linked server and use the four part names.