Using MySQL to JOIN data in two tables, where part of the data is NULL
InnoDB_int (Photo credit: Fenng(dbanotes))
MySQL can be incredibly useful and simplistic by times, while other times its lack of full Joining (i.e. LEFT OUTER JOIN gets evaluated the same as LEFT JOIN), and Referrential Integrity can become frustrating when you are trying to provide these functionalities within the Database Server.
(I know, I know InnoDB solves most of these problems, but still not complete)
So the problem is this:
Table 1 – ACCOUNT
account_ID account_username
Table 2 – ACCOUNT TYPE
account_type_ID account_name start_url end_url
Sparing too many details, I wanted to store a list of a user’s accounts on other popular websites all over the web, while still keeping a normalized database (could get out of hand otherwise, if you had a large number of users and crammed both accounts and account types into one table). Thus the solution is to of course normalize to two tables, one for the various users’ account names and one for the account types (i.e. Amazon, eBay, YouTube, Facebook, MySpace, etc…)
This brings about one little problem in that you’ll need to perform a JOIN to present each separate user with “their accounts” plus all the other available accounts that they have yet to create/add to your site for merging. Should be a basic JOIN, no problem right?
Wrong… good luck trying to solve that smoothly without multiple different forms, one for accounts they have added, one for accounts they haven’t added, and one for accounts that they can still possibly add. This can (and should) be reduced down to 2 screen, one for managing all their accounts, and one for displaying all the accounts they have added thus far. A far better solution… but in order to do that we need to JOIN on existing and non-existing data.
Read on for the solution…
BC$ = Behavior, Content, Money
The goal of the BC$ project is to raise awareness and make changes with respect to the three pillars of information freedom - Behavior (pursuit of interests and passions), Content (sharing/exchanging ideas in various formats), Money (fairness and accessibility) - bringing to light the fact that:
1. We regularly hand over our browser histories, search histories and daily online activities to companies that want our money, or, to benefit from our use of their services with lucrative ad deals or sales of personal information.
2. We create and/or consume interesting content on their services, but we aren't adequately rewarded for our creative efforts or loyalty.
3. We pay money to be connected online (and possibly also over mobile), yet we lose both time and money by allowing companies to market to us with unsolicited advertisements, irrelevant product offers and unfairly structured service pricing plans.