Jason Fel Jason Fel - 1 month ago 12
MySQL Question

How can I get all business data AS WELL as if current user is following them?

In mysql how can I write a query that will fetch ALL business data, and at the same time (or not if it is better another way) check if user is following that business? I have the following relationship table to determine if a user is following a business (status=1 would mean that person is following):

CREATE TABLE IF NOT EXISTS `Relationship_User_Follows_Business` (
`user_id` int(10) unsigned NOT NULL,
`business_id` int(10) unsigned NOT NULL,
`status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '1=following, 0=not following'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `Relationship_User_Follows_Business`
ADD UNIQUE KEY `unique_user_business_id` (`user_id`,`business_id`);


Assume business table just holds data on different businesses like name, phone number, etc. I would want to return all of the business data in my query (Business.*). I want to append the status (0 or 1) to the end of each business row to determine if the user is following that business. I have tried the following query but it does not work because it is narrowing the results to only show a business if there is a relationship row. I wish to show ALL businesses regardless if a relationship row exists or not because I only create the relationship row if a user follows:

SELECT Business.*, Relationship_User_Follows_Business.status FROM Business, Relationship_User_Follows_Business WHERE 104=Relationship_User_Follows_Business.user_id AND Business.id=Relationship_User_Follows_Business.business_id


Note that I am using 104 as a test user id. The user id would normally be dependent on user, not a static 104.

Answer

You are looking for a LEFT JOIN and not an INNER JOIN which keeps all the records from the master table and all the matching rows from the details table . Also, avoid using implicit join syntax(comma separated) and use the proper syntax of a join :

SELECT Business.*, Relationship_User_Follows_Business.status
  FROM Business
  LEFT JOIN Relationship_User_Follows_Business 
   ON Business.id = Relationship_User_Follows_Business.business_id
     AND Relationship_User_Follows_Business.user_id = 104