BCmoney MobileTV

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…

After scouring the web for hours yet again trying to find the solution to a fairly common problem; one that in fact, I believe I have solved myself several times before, but for the life of me could not find these previous projects and could again not find the relevant information through searches (is it just me or are we approaching the “Tipping Point” of the usefulness for Search on the web?)

Anyway, the point is its a very simple JOIN operation, and it looks something like the following:

SELECT *
FROM <your_first_table>
LEFT JOIN <your_second_table>
USING (common_id)
WHERE <user_id>=116790 OR <user_id> IS NULL

• The number 116790 represents some user’s unique ID number which will be used to sort a large table of records, for example Music albums that user owns, Videos they’ve watched, or, in this case, Web Accounts they have on other websites.

• common_id is the ID for some data record you want to JOIN the tables on (for example, Video’s ID or Musical Album’s ID)
• <your_second_table> is the name of the second table whose data you’d like to merge
  
• <your_first_table> is the name of thefirst table whos data you’d like to merge

Related Articles

• MySQL IF NOT NULL, then display 1, else display 0 (stackoverflow.com)
• My favorite features in MySQL 5.6 (xaprb.com)
• MySQL high availability comparison: service versus data (xaprb.com)
• PHP MySql – Little challenge here inserting data into table (stackoverflow.com)
• Problems getting MySql data into an index using Zend_Search_Lucene (stackoverflow.com)
• MySQL’s latest InnoDB engine can now do extensive, high-performance, full text search (ersanjeev.wordpress.com)
• PHP and MySQL echo string only in certain conditions (stackoverflow.com)