sql query to display all the fields with no duplicate records on one field

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I need to join two tables namely Schools and Survey and display the records without duplicate of the field [SchoolID] in the second. till I have queried as

SQL

select * from
(Select * from [School] where [BoroughID]=19 and [CurrentAccreditationTypeID]=2)st
 join 
(select * from(select distinct [SchoolID] from [Survey] where [SurveyDate]>='2014-01-09 00:00:00:000' and [SurveyDate]<='2015-31-08 00:00:00:000')as [ID], [SurveyID]) s
on st.[SchoolID]= s.[SchoolID]

But is not working. I manipulated the query as

SQL

select * from
(Select * from [School] where [BoroughID]=19 and [CurrentAccreditationTypeID]=2)st
 join 
(select distinct [SchoolID] from [Survey] where [SurveyDate]>='2014-01-09 00:00:00:000' and [SurveyDate]<='2015-31-08 00:00:00:000') s
on st.[SchoolID]= s.[SchoolID]

Then the other fields in table 'Survey table are not displaying.

Can anyone help me query to display all the fields of both the table without the duplicate on the field [SchoolID] of the table [Survey] alone.

Posted 22-Sep-15 1:15am

user 3008

Updated 22-Sep-15 1:16am

v2

Add a Solution

Comments

CHill60 22-Sep-15 8:51am

I'm not convinced that the solution posted is of any use to you.
By "duplicate of the field" do you mean you don't want that column returned from both tables or do you mean that you only want a single row per SchoolId?
Some sample data and expected results are always useful things to provide in questions like this

user 3008 22-Sep-15 9:35am

Want only one single row per schoolid.

SurveyID | SchoolID | SurveyDate | Year | ClassSize | ClassName
168293 | 4968 | 2015-05-20 00:00:00:000 | 13 | 18 | 13b
168294 | 4968 | 2015-05-19 00:00:00:000 | 13 | 20 | 13c
168321 | 4895 | 2015-02-17 00:00:00:000 | 15 | 45 | 14a

I think this example data from survey would give you an idea.

CHill60 22-Sep-15 10:06am

Ok - looking at that data you have 2 surveys for SchoolID 4968 - which one of the surveys do you want returned in the result?
An example of the results you expect might help

user 3008 23-Sep-15 7:02am

SurveyID | SchoolID | SurveyDate | Year | ClassSize | ClassName
168294 | 4968 | 2015-05-19 00:00:00:000 | 13 | 20 | 13c
168321 | 4895 | 2015-02-17 00:00:00:000 | 15 | 45 | 14a
This must be the result.

If I apply distinct as

Select distinct [SchoolID], [SurveyID] from [Survey]

then it is applying as to remove the duplicate records for the combination of the SchoolID and SurveyID that occurs more than one. I do not want that to happen. It must only check for the duplicate records of the [SchoolID] and remove the entire records of the duplicate record in all fields and display the rest of the records with all the fields. Hope I am clear now.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

vinay kumar0164 · Answer 1 · 2015-09-22T02:48:00

Solution 1

SELECT EMP2.EMPLOYID,emp1.FRSTNAME FROM EMP2
INNER JOIN emp1
ON EMP2.EMPLOYID=emp1.EMPLOYID

select those column name only which you want in your result,hope this example can help you.

Posted 22-Sep-15 2:48am

vinay kumar0164