Solved

SQL JOIN

Posted on 2013-06-13
6
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

617 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