• Post Reply Bookmark Topic Watch Topic
  • New Topic

Finding a Disjoint set between two tables  RSS feed

 
Ranch Hand
Posts: 175
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

  Please find the SQLfiddle link  http://sqlfiddle.com/#!9/89c76/2


How can I do this in a single query for all the games, there can be many types of games. I cant specify the games explicitly, instead, I want to write the query to fetch all the playerids who are not playing a game for each of the game some thing like this



the above query is giving only one record. I need like this

Result:
------------------------
game1 | 4,5,6 
------------------------
game2 | 4,6
------------------------
game3 | 1,2,3,5,6
------------------------
....
 
Chandra shekar M
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need something like a forloop, which can loop over the games and find the players not playing that game. Can I do this in SQL or should I write a procedure
 
author & internet detective
Marshal
Posts: 38257
623
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, this can be done in raw SQL. It's not particular efficient; doing a loop behind the scenes. But for small datasets, that is fine.



The nested query gets the games a player played. If there aren't any for that player/match combo, the outer query includes the data. The distinct is because the cartesian product of the outer query creates duplicate rows.
 
Chandra shekar M
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne Boyarsky, it helped
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This may prove to be a bit cheaper than Jeanne's solution:

 
This cake looks terrible, but it tastes great! Now take a bite out of this tiny ad:
Rocket Oven Kickstarter - from the trailboss
https://coderanch.com/t/695773/Rocket-Oven-Kickstarter-trailboss
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!