This week's book giveaway is in the Security forum.
We're giving away four copies of Securing DevOps and have Julien Vehent on-line!
See this thread for details.
Win a copy of Securing DevOps this week in the Security forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Finding a Disjoint set between two tables  RSS feed

 
Ranch Hand
Posts: 174
  • 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: 174
  • 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: 37898
596
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: 174
  • 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:

 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!