Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL JOIN

Posted on 2013-06-13
6
Medium Priority
?
200 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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

564 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