We help IT Professionals succeed at work.

sql query

388 Views
Last Modified: 2014-08-18
Hi I am not sure that my solution is correct or not as we need only one distinct field(region_name).plz provide the solution


Problem Statement 1.      Populate the fields in RegionDetails table with all distinct Region_Name based on the fields CCareStageTbl.Client_Name and Client.Client_Name

My Solution 1.

INSERT INTO RegionDetails ( 
	Region_Key ,
	Region_Name ,
	Client_Key ,
	Upd_Date ,
	User_Id 
)
Select Distinct Region_Key ,Region_Name,Client_Key ,Upd_Date ,User_Id 
FROM CCareStageTbl CS INNERJOIN Client CL
ON CS.Client_Name = CL.Client_Name 

//could you plz check my joins are accurate or not ,here are also distinct Facility_Name
Problem Statement 2.	Populate the fields in FacilityDetails table with all distict Facility_Name based on the join on the fields from CCareStageTbl.Client_Name & Client.Client_Name, CCareStageTbl.Region_Name & RegionDetails.Region_Name and RegionDetails.Client_Key & Client.Client_Key and CCareStageTbl.Facility_Type_Desc & FacilityType.Facility_Type_Desc. 

My Solution 2: 
INSERT INTO FacilityDetails ( 
	Facility_Key ,
	FacilityType_Key ,
	Facility_Name ,
	Region_Key ,
	Upd_Date ,
	User_Id )
Select Distinct Facility_Key ,FacilityType_Key ,Facility_Name ,Region_Key ,Upd_Date ,User_Id 
FROM CCareStageTbl CS INNERJOIN Client CL
ON CS.Client_Name = CL.Client_Name 
LEFT Join RegionDetails RD
ON CS.Region_Name = RD.Region_Name
AND RD.Client_Key = CL.Client_Key
LEFT JOIN FacilityType FT
ON CS.Facility_Type_Desc = FT.Facility_Type_Desc

Open in new window

Comment
Watch Question

Commented:
1. use table.field in your select (of course depending on in which table the fields are found)
2. inner join should have space between inner and join
...
Select Distinct cs.Region_Key ,cs.Region_Name,cl.Client_Key ,cl.Upd_Date ,cl.User_Id
FROM CCareStageTbl CS INNER JOIN Client CL
ON CS.Client_Name = CL.Client_Name 

Open in new window


same for solution 2 (you should see which field exists in which table)

Select Distinct ft.Facility_Key ,ft.FacilityType_Key ,ft.Facility_Name ,rd.Region_Key ,cl.Upd_Date ,cl.User_Id
FROM CCareStageTbl CS 
INNER JOIN Client CL ON CS.Client_Name = CL.Client_Name
LEFT Join RegionDetails RD ON CS.Region_Name = RD.Region_Name AND RD.Client_Key = CL.Client_Key
LEFT JOIN FacilityType FT ON CS.Facility_Type_Desc = FT.Facility_Type_Desc

Open in new window

Author

Commented:
but here it will distinct all the fields i want to be distinct only region name
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
This is an exam?
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi vitlor ,
 this is not a exam... dont demotivate people in experts exchange ... i made query then after i ask in experts exchage.
for better clarification i provided problem state.

i know that experts exchange having the people who are master in technology.they can provide me help.

well i respect you and all member in expert exchange.

thanks
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
@nicemanish

thanks for clearing-up the matter of an exam.

However, what you need to do is:

Provide some sample data (for each table), private data can be changed.
(not images of data! data in a reusable format)
Then also provide the "expected results"

Form these 2 items we can advise you on the queries.

I promise you this is the most accurate way to explain your needs to us and we can then give you informed advice
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Nicemanish, I needed that clarification because we shouldn't resolve exams but point the user how to resolve it.
Now I know how should I help you. Will try to find a solution for your problem and will be back.

Cheers

Author

Commented:
Hi All, Thanks For your co-operation
plz find the correct way something as below using row number and partition

INSERT INTO Patient  ( 
	Patient_Id ,MPI ,First_Name ,Middle_Name ,Last_Name ,
	Patient_Suffix ,Age ,Birth_Date ,Gender ,SSN ,Upd_Date ,User_Id )
SELECT CCST.Patient_Id , CCST.MPI , CCST.First_Name , CCST.Middle_Name , CCST.Last_Name , CCST.Patient_Suffix , CCST.Age , CCST.Birth_Date , CCST.Gender , CCST.SSN , CCST.Upd_Date , CCST.User_Id 
FROM (
       SELECT Patient_Id ,MPI ,First_Name ,Middle_Name ,Last_Name ,
	Patient_Suffix ,Age ,Birth_Date ,Gender ,SSN ,Upd_Date ,User_Id
              ROW_NUMBER() OVER (PARTITION BY Patient_Id  ORDER BY Upd_Date DESC
                     ) rownumber
       FROM CCareStageTbl
       ) CCST
WHERE rownumber = 1

Open in new window

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
it is a good lesson to have learned, nice job

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.