16104

SQL view for acquaintance from table

I have a table for friends

PLAYER_ID FRIEND_ID ---------------------- 1 2 1 3 // 1 knows 3 3 1 // 3 knows 1 2 4

How can I create a view containing those acquaintances(know each other) for given player?

PLAYER_ID FRIEND_ID ---------------------- 1 3 3 1

thank you.

Answer1:

Here's the needed select:

select t1.PLAYER_id, t1.FRIEND_ID from tablename t1 inner join tablename t2 on t1.PLAYER_id = t2.FRIEND_ID and t2.PLAYER_id = t1.FRIEND_ID

Answer2:

select p1.player_id, p1.friend_Id from player p1 inner join player p2 on p1.player_id = p2.friend_id and p2.player_id = p1.friend_id

Recommend

  • ActiveRecord is not aware of timezones?
  • Select the users are not friend of anyone
  • Get game_id of a players MAX(score) in sql
  • Hibernate user and friend relationship add other column
  • How can I train my Python based OCR with Tesseract to train with different National Identity Cards?
  • Counting process instances with grep & wc [duplicate]
  • More dynamic page with sitecake?
  • More dynamic page with sitecake?
  • Complexity Of Dijkstra's algorithm
  • use preg_replace to replace whole words using associative array
  • Get rid of ^@ in vim output from system(…)
  • Add onClickListener to listview item
  • Get MAX value from one column and MIN from another column
  • How can I use COALESCE() in WHERE clause optimally?
  • Why doesn't this jquery code work?
  • How to get a checkbox value using custom adapter and listview
  • Using sed with the EDITOR variable in a script for virsh or sudoers
  • Link_to remote => true causing strange routing problems, Rails 3
  • Attaching to processes in C
  • Returning multiple rows from a single row
  • SQL union of two tables with different columns
  • set onClickListener in getview() with ViewHolder class
  • Vim key mapping works in command editor, not in .vimrc - why?
  • php is_file always return false
  • Is there any way to call saveCurrentTurnWithMatchData without sending a push notification?
  • HTML5 video only works in IE. The other browsers shows the black screen
  • Reading a file into a multidimensional array
  • Object and struct member access and address offset calculation
  • Hardware Accelerated Image Scaling in windows using C++
  • Magento Fatal error: Maximum execution error solution, on WAMP
  • MySQL WHERE-condition in procedure ignored
  • Adding custom controls to a full screen movie
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Unanticipated behavior
  • Comma separated Values
  • How do you join a server to an Active Directory (domain)?
  • Trying to get generic when generic is not available
  • How does Linux kernel interrupt the application?
  • playing video using jmf