sql query

nicemanish
nicemanish used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017

Commented:
This is an exam?
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
>>"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.

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
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ãoMSSQL Senior Engineer
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
Most Valuable Expert 2014
Awarded 2013

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial