Behavior, Content, Money – 3 Things you should never give away for free!!!

BCmoney MobileTV

Using MySQL to JOIN data in two tables, where part of the data is NULL

Posted by bcmoney on January 25, 2009 in SQL with No Comments


No Gravatar
InnoDB_int

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