sql query

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

nicemanishAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dany BalianCTOCommented:
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

0
nicemanishAuthor Commented:
but here it will distinct all the fields i want to be distinct only region name
0
Dany BalianCTOCommented:
then the select should be without distinct.. and we need to group the data accordingly..
can you provide me a sample of data in CCareStageTbl table so that i can try a final sql?

you need something like this:

Select cs.Region_Key ,max(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_Nam
group by cs.region_key, cl.client_key, cl.upd_date,cl.user_id

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
This is an exam?
0
PortletPaulfreelancerCommented:
>>"but here it will distinct all the fields i want to be distinct only region name"

then "select distinct" is not the right technique, because this always does the whole row (no exceptions)

see: Select Distinct is returning duplicates ...

You probably need to use group by instead, but this requires you to decide what to do for every column (do you want MIN(), MAX() etc)

Sometimes using row_number() can be effective instead of distinct or group by - but we don't know if this is true for you here.

----------------

You are asking us to to be precise in our answers (e.g. "could you plz check my joins are accurate or not")

But we know NOTHING about your data, not a single thing!
Every query here could be quite wrong and we would not know.

Please provide some sample data (for each table), private data can be changed. (not images of data! data in a reusable format)

Then also provide "expected results".

Form these 2 items we can advise you on the queries.
0
nicemanishAuthor 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
0
PortletPaulfreelancerCommented:
@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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
0
nicemanishAuthor 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

0
PortletPaulfreelancerCommented:
it is a good lesson to have learned, nice job
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.