How to make SQL many-to-many same-type relationship table

by

Create a User table then a Relationships table where you store the id of the two friend and any kind of information about their relationship.

SQL diagram

SQL diagram

MySQL code

CREATE TABLE `Users` (
  `id` TINYINT NOT NULL AUTO_INCREMENT DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `Relationships` (
  `id` TINYINT NOT NULL AUTO_INCREMENT DEFAULT NULL,
  `userid` TINYINT NULL DEFAULT NULL,
  `friendid` TINYINT NULL DEFAULT NULL,
  `friended` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

ALTER TABLE `Relationships` ADD FOREIGN KEY (userid) REFERENCES `Users` (`id`);
ALTER TABLE `Relationships` ADD FOREIGN KEY (friendid) REFERENCES `Users` (`id`);

SQL selection

After you fill up the tables with data, you can create your SQL SELECT query to get all of your friends. Your friends are those whose id is in one side side while your id is in the other side. You check both sides for your id so you don’t need to store relationships twice. Also you have to exclude your id, because you can’t be your own friend (in a normal, healthy world).

SELECT *
FROM Users u
   INNER JOIN Relationships r ON u.id = r.userid
   INNER JOIN Relationships r ON u.id = r.friendid
WHERE
   (r.userid = $myid OR r.friendid = $myid)
   AND r.friended >= $startdate
   AND r.friended <= $enddate
   AND u.id != $myid;

Where $myid, $startdate and $enddate can be PHP variables so in double quotes you can pass this code directly to your database.

More Related Contents:

  1. SQL function PIVOT with 2 column
  2. SQL query for display one field only once which having multiple record
  3. SQL/mysql – Select distinct/UNIQUE but return all columns?
  4. Convert UTF-8 String Classic ASP to SQL Database
  5. Pivot on Multiple Columns using Tablefunc
  6. Dynamically create columns sql
  7. Can table columns with a Foreign Key be NULL?
  8. Multiple CTE in single query
  9. Return pre-UPDATE column values using SQL only
  10. Selecting data into a Postgres array
  11. SQL syntax term for ‘WHERE (col1, col2) < (val1, val2)'
  12. Oracle: loading a large xml file?
  13. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  14. How to get the numeric part from a string using T-SQL?
  15. MySQL: How to copy rows, but change a few fields?
  16. How to renumber primary index
  17. Query that ignore the spaces
  18. How to enable Ad Hoc Distributed Queries
  19. Sleep function in ORACLE
  20. Postgres function returning table not returning data in columns
  21. SQL query to select distinct row with minimum value
  22. SQL Server SELECT LAST N Rows
  23. update columns values with column of another table based on condition [duplicate]
  24. how to select even records from a table in oracle?
  25. SQL constraint minvalue / maxvalue?
  26. How to generate date series to occupy absent dates in google BiqQuery?
  27. T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition
  28. Oracle RAC and sequences
  29. Removing duplicate rows (based on values from multiple columns) from SQL table
  30. What is “Connect Timeout” in sql server connection string?

Leave a Comment