Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL JOIN

Posted on 2013-06-13
6
Medium Priority
?
199 Views
Last Modified: 2013-06-15
Hello All,
I have a query as below:
select KeyIdentifiers.GPIDrugGroup, facname,reorders.RoNo,LastRxNo,Qty,dayssupply,reorders.ndc,
cutoffdt,DrugLabelName,patlname+', '+PatFName PatName,p.NsID,bed,room,PhName
,d.ExpandedSig,reorders.lastfilldt,reorders.OrigDt,REORDERS.CutoffDt
 from Reorders 
left join KeyIdentifiers on reorders.NDC  = KeyIdentifiers.NDC
left join Facilities on Reorders.FacID = Facilities.FacID
left join Patients p on reorders.PatID = p.PatID and reorders.FacID = p.FacID
left JOIN "FwReports"."dbo"."vwReorderDirectionsExpandedSig" d on Reorders.RoNo = d.RoNo and reorders.PatID = d.PatID
where reorders.FacID in('292')and (KeyIdentifiers."GPIDrugGroup"='57')
and
(reorders.CutoffDt is null or reorders.CutoffDt >GETDATE()) 
order by PatName

Open in new window

Is there any way I can innerjoin the following with the keyidentifiers only  something like below:
INNER JOIN "FwReports"."dbo"."GPIDrugGroups" "GPIDrugGroups" ON "KeyIdentifiers"."GPIDrugGroup"="GPIDrugGroups"."GPIDrugGroup"

Open in new window

and in my select I be able to select GPIDrugGroups.groupname along with other fields
0
Comment
Question by:Star79
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:BurundiLapp
ID: 39244953
What have you tried so far, what errors are you getting?

Is FwReports a different database?

If so I would try something like:

INNER JOIN FwReports..GPIDrugGroups FwDG ON KeyIdentifiers.GPIDrugGroup = FwDG.GPIDrugGroup

Open in new window


And then use the following in your select statement

FwDG.groupname

Open in new window


to pull out the groupname.
0
 

Author Comment

by:Star79
ID: 39244990
this is an innerjoin between keyidentifiers and gpidruggroups and not the selection table re-orders.so can I just write
select KeyIdentifiers.GPIDrugGroup, facname,reorders.RoNo,LastRxNo,Qty,dayssupply,reorders.ndc,
cutoffdt,DrugLabelName,patlname+', '+PatFName PatName,p.NsID,bed,room,PhName
,d.ExpandedSig,reorders.lastfilldt,reorders.OrigDt,REORDERS.CutoffDt
 from Reorders 
left join KeyIdentifiers on reorders.NDC  = KeyIdentifiers.NDC
INNER JOIN "FwReports"."dbo"."GPIDrugGroups" "GPIDrugGroups" ON "KeyIdentifiers"."GPIDrugGroup"="GPIDrugGroups"."GPIDrugGroup"
left join Facilities on Reorders.FacID = Facilities.FacID
left join Patients p on reorders.PatID = p.PatID and reorders.FacID = p.FacID
left JOIN "FwReports"."dbo"."vwReorderDirectionsExpandedSig" d on Reorders.RoNo = d.RoNo and reorders.PatID = d.PatID
where reorders.FacID in('292')and (KeyIdentifiers."GPIDrugGroup"='57')
and
(reorders.CutoffDt is null or reorders.CutoffDt >GETDATE()) 
order by PatName

Open in new window

0
 
LVL 6

Expert Comment

by:BurundiLapp
ID: 39245015
So long as you are happy that there is a definite match in fwReports for every entry in KeyIdentifiers and it won't falsely restrict your results set then yes, you should be able to perform that join.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39245070
Why you could not use [] insted of ""?

INNER JOIN "FwReports"."dbo"."GPIDrugGroups" "GPIDrugGroups" ON "KeyIdentifiers"."GPIDrugGroup"="GPIDrugGroups"."GPIDrugGroup"

should be like below

INNER JOIN [FwReports].[dbo].[GPIDrugGroups]  AS GPIDrugGroups
ON [KeyIdentifiers].[GPIDrugGroup]=[GPIDrugGroups].[GPIDrugGroup]

Open in new window

0
 

Author Comment

by:Star79
ID: 39247869
burundi,Iam not sure if there is a definite match for every entry in keyidentifiers.If thats the case how can it be handled.
0
 
LVL 6

Accepted Solution

by:
BurundiLapp earned 1500 total points
ID: 39247888
It would have to be a left outer join rather than an inner join, it would then return nulls for records where there was no match.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question