Click here to Skip to main content
15,947,957 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Updated 22-Sep-15 1:16am
v2
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

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900