Solved

SQL JOIN

Posted on 2013-06-13
6
186 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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 Error Log - logging period 1 33
Join vs where 2 38
Linking a DMV to a database id/sql text in SQL server 2008 8 46
Max Consumption Rate (MCR) 3 34
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now