?
Solved

sql query

Posted on 2014-08-11
10
Medium Priority
?
371 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

0
Comment
Question by:nicemanish
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40252744
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
 

Author Comment

by:nicemanish
ID: 40252769
but here it will distinct all the fields i want to be distinct only region name
0
 
LVL 11

Accepted Solution

by:
Dany Balian earned 450 total points
ID: 40252787
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40253373
This is an exam?
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 450 total points
ID: 40254973
>>"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
 

Author Comment

by:nicemanish
ID: 40267015
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40267034
@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
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40267069
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
 

Author Closing Comment

by:nicemanish
ID: 40267220
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40267415
it is a good lesson to have learned, nice job
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

839 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