CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
user3451821
user3451821

Reputation: 123

Can I use OR when joining two tables?

SELECT Notifications.message, BusinessAccounts.employee_username
FROM Notifications, BusinessAccounts
WHERE Notifications.notification_by_business_account_id = BusinessAccounts.account_id AND Notifications.notification_business_id=5

In this situation, I am trying to retrieve notification message alongside the username of the employee who sent out the notification. The problem is it is possible for notification_by_business_account_id to be 0 in Notifications table if the notification was not sent by an employee (but instead it was sent by the business owner themselves). If sent by the business owner, there would be no username, so my query does not retrieve these notifications.

My goal is to retrieve all notifications for business id 5. If Notifications.notification_by_business_account_id is set to 0 (meaning the notification was sent by the owner), I would simply like to leave the value in account_username column blank. How can I do this? Would I use an OR somewhere? Here are my tables:

TABLE `Notifications` (
  `notification_id` mediumint(8) unsigned NOT NULL,
  `notification_business_id` mediumint(8) unsigned NOT NULL,
  `notification_by_business_account_id` int(10) unsigned NOT NULL COMMENT 'This id represents the id of the employee account that sent the notification. Set 0 if from business owner.',
  `notification_message` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notification_date_sent` datetime NOT NULL
)

TABLE `BusinessAccounts` (
  `account_id` int(10) unsigned NOT NULL,
  `account_username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_business_id` mediumint(8) unsigned NOT NULL
)

There are multiple businesses. I am just testing for business with id 5 for now.

Upvotes: 1

Views: 30

Answers (1)

Stephan
Stephan

Reputation: 6018

This is textbook example of Left Outer Join, so I'd recommend you read up on joins in particular outer joins. Note the word "outer" is not always spelled out in code but "left join" is the same as "left outer join".

Here's the query:

Select *
From Notifications As N
Left Outer Join BusinessAccounts As B
    On      N.notification_by_business_account_id = B.account_id 
Where N.notification_business_id In (5 /*List your business ID's separated by commas here if/when you have multiple*/)

Upvotes: 2

Related Questions