[RESOLVED] a simple query problem regarding joins

[raghunadhs]

Hi All,
i am practising Sql Server 2000. i try to solve a query but failed. can u help me? the problem is ......I have a table called "student". my table is like this...
student_no, student_name,student_country are my three fields.

Student_no , Student_Name , Student_Country
1000 Williams China
2000 Stuvart U.S
2500 Willams China
3000 Stuvart Malesia
2200 Rob U.S

now my question is how can i get the records from the table such that the records should have Student_name,Student_country as same values.
egg:in the above example Williams with China has two records but the student_no are different. i want those 2 records. here Student_no is the primary key.

Thanks & Regards,
raghunadhs.v

[szlamany]

There is no JOIN problem here - as JOINS are part of how you relate two tables and you only have STUDENT as a table to deal with - right??

If you do

Select Student_name,Student_country From Student

you will get two rows for Williams / China - right?

If you want only one row you would do either

Select Distinct Student_name,Student_country From Student

or

Select Student_name,Student_country From Student Group by Student_name,Student_country

[raghunadhs]

Hi szlamany,
i am sorry if my post could not give you clarity. in that table in the previous Quote, there are 5 records. In that 5 records, there are two records with the same student_name, student_country. ie; there are two students with the same name "williams" and those two students belong to the same country "China".
now my question is how to extract those two records having the same Student_name, Student_country? and finally my out put should be like this....

Student_no, student_name, Student_country
1000, williams, china
2500, williams, china

i hered that it is possible through self join. i tried also but could not achive it.
i tried like the following. i don't know whether it is correct or not...

Select * from S1.Student_name,S1.Student_country from Student S1, Student S2 where S1.Student_name = S2.Student_name and S1.Student_country = S2.Student_country

but unfortunately the above query is not giving proper results.
pleas help me if you can.

Thanks & Regards,
raghunadhs

[QUOTE=szlamany]There is no JOIN problem here - as JOINS are part of how you relate two tables and you only have STUDENT as a table to deal with - right??

[szlamany]

Ok - you only want those two rows.

That is the purpose of the HAVING clause - which must follow a GROUP BY

Select Student_name,Student_country
    From Student
    Group by Student_name,Student_country
    Having Sum(1)>1

Each group is loaded by the engine into a working resultset - and the Sum(1) is incremented for each row inserted.

The HAVING clause is assessed only after all rows are inserted - as a final filter.

HAVING is not the same as WHERE - keep that in mind. WHERE is used to determine if a row gets into the working resultset. HAVING is a final filter after all rows are inserted.

You example stated that you want the student id of these rows - my example only returns a single row of those name/countries with more then 1 entry.

Do you need to see the "separate" rows as well??

[raghunadhs]

Wonder ful eplenation szlamany!
its working. And i added another statement to do remaining thing.. (printing those two rows...)
what i did was......

declare @varName varchar(50)
declare @varCountry varchar(50)
declare @varSql varchar(100)
Select @varName=Student_Name,@varCountry=Student_Country
From Student
Group by Student_name,Student_Country
Having Sum(1)>1
if @@rowcount>0
begin
set @varSql= 'select * from Student where Student_Name= ''' + @varName + '''
and Student_country ='''+ @varCountry + ''''
exec(@varSql)
end

I checked it and found working well. if there is any simple method to print these type of records, pls let me know.
Thanks & Regards,
raghunadhs

[QUOTE=szlamany]Ok - you only want those two rows.

That is the purpose of the HAVING clause - which must follow a GROUP BY

Select Student_name,Student_country
    From Student
    Group by Student_name,Student_country
    Having Sum(1)>1

Each group is loaded by the engine into a working resultset - and the Sum(1) is incremented for each row inserted.

[szlamany]

You can do all this with a single query. It's sometimes hard to drop the old iterative/looping/conditional type logic we are used to - but try to think instead in set based logic

This query

Select Student_name,Student_country
    From Student
    Group by Student_name,Student_country
    Having Sum(1)>1

Gives you name and country of the people you want.

It could be put into a sub-query and checked that way

Select * From Student S1
    Where Exists (Select S2.Student_name,S2.Student_country
                       From Student S2
                       Where S2.Student_name=S1.Student_name
                           and S2.Student_country=S1.Student_country
                       Group by S2.Student_name,S2.Student_country
                       Having Sum(1)>1)

Here we are grabbing a row from Student with an alias of S1 and then seeing if the sub-query (student with S2) exists.

You could also JOIN from the sub-query - it might be faster.

Select S1.*
From (Select Student_name,Student_country
    From Student
    Group by Student_name,Student_country
    Having Sum(1)>1) as "S2"
Left Join Student S1 on S1.Student_name=S2.Student_name
               and S1.Student_country=S2.Student_country

[edit]Fixed typo with misplaced parenthesis[/edit]

[raghunadhs]

Hi szlamany,
thanks for your immediate correspondence. Two examples what u have shown are so good.this type of examples will make the new lerners(like me )how to use different clauses in different situations.

and i think the last brace ')' shoud be at end(after sum(1)>1) in the following query.
why i am specially saying is... it will be useful for new learners.

Thank you so much....
regards,
raghunadhs.


It could be put into a sub-query and checked that way

Select * From Student S1
    Where Exists (Select S2.Student_name,S2.Student_country
                       From Student S2
                       Where S2.Student_name=S1.Student_name
                           and S2.Student_country=S1.Student_country
                       Group by S2.Student_name,S2.Student_country)
                       Having Sum(1)>1

Here we are grabbing a row from Student with an alias of S1 and then seeing if the sub-query (student with

[szlamany]

Thanks...

I edited my post to correct the placement of the parenthesis.

Why don't you do the same since you quoted the error in the query in your last post as well.