Solved

SQL JOIN

Posted on 2013-06-13
6
187 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL server 2008 and after encryption method 32 46
Problem with SQL Script - Cannot call methods on char 2 21
SSRS 2013 - Creating a summarized report 19 35
Help Required 3 96
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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