interesting sql problem

• jrajkhowa

  SSC Enthusiast

  Points: 190

  More actions

  April 5, 2010 at 2:16 pm

  #231381

  Hi,

  I hav a table which contains student info. such as student name and course name. Its structure is like this:

  Name courseno

  ---------- ---------

  a 1

  a 2

  b 1

  b 3

  c 6

  d 1

  d 2

  I hav to find the students that have taken the exactly same courses. here for example a and d as they hav taken the courses 1 and 2. thanks for any help

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 5, 2010 at 2:40 pm

  #1146011

  Looks like homework.

  I'd join the table to itself on the course ID, and the count(distinct) on it per student ID.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• jrajkhowa

  SSC Enthusiast

  Points: 190

  More actions

  April 5, 2010 at 3:43 pm

  #1146050

  your idea wont give me a and b, but all a,b and d.

  you solve this part, then next part will continue.

  and this is not homework but work

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  April 5, 2010 at 6:11 pm

  #1146132

  (The next time you post, please post the table definition and sample data as I have shown in the first code block. It will most likey get you a tested answer quicker)

  --==Preferred forum posting format

  CREATE TABLE #T (Name VARCHAR(2), courseno INT)

  INSERT INTO #T

  SELECT 'a', 1 UNION ALL

  SELECT 'a', 2 UNION ALL

  SELECT 'b', 1 UNION ALL

  SELECT 'b', 3 UNION ALL

  SELECT 'c', 6 UNION ALL

  SELECT 'd', 1 UNION ALL

  SELECT 'd', 2

  Is this what you are looking for?

  SELECT DISTINCT(t1.courseno),t1.name FROM #T t1

  JOIN #T t2

  ON (t1.courseno = t2.courseno) GROUP BY t1.name, t1.courseno

  HAVING COUNT(t1.courseno) > 1

  --results:

  courseno name

  1 a

  1 b

  1 d

  2 a

  2 d

  .

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 7, 2010 at 8:16 am

  #1147359

  jrajkhowa (4/5/2010)

  ---

  your idea wont give me a and b, but all a,b and d.

  you solve this part, then next part will continue.

  and this is not homework but work

  No, my idea gives a and d, which are the only ones that are complete matches.

  I'm not sure if it's intended, but your statement comes across as rude and arrogant.

  Here's a sample solution:

  CREATE TABLE #T (

  ID INT IDENTITY PRIMARY KEY,

  Name CHAR(1),

  CourseNo TINYINT);

  INSERT INTO #T (Name, CourseNo)

  VALUES ('a',1),('a',2),('b',1),('b',3),('c',6),('d',1),('d',2);

  SELECT T1.Name, T2.Name

  FROM #T T1

  INNER JOIN #T T2

  ON T1.CourseNo = T2.CourseNo

  AND T1.Name < T2.Name

  CROSS APPLY (SELECT COUNT(*) AS T1Qty FROM #T T3 WHERE T3.Name = T1.Name) T1Qty

  CROSS APPLY (SELECT COUNT(*) AS T2Qty FROM #T T4 WHERE T4.Name = T2.Name) T2Qty

  WHERE T1Qty = T2Qty

  GROUP BY T1.Name, T2.Name, T1Qty

  HAVING COUNT(*) = T1Qty;

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon