Selecting what’s NOT there in MySQL
Friday, May 16th, 2008Relational databases are a fantastic way to organize data. Sometimes though, I have to come up with contrived solutions to render results that should, in my mind, be much simpler generate.
Here was my problem:
I’m working on a client project right now that is basically a glorified upload/download center with users. He can upload documents and then assign permission as to whether or not each user can view the document on a user by user basis. It’s small-scale and simple and, up until this dilemma, has been a piece of cake to implement.
Because I’m so nice, on a the document overview pages, I’ve provided an “at-a-glance” list of his users sorted into two columns - those who can view this particular document, and those who can’t.
Building the list of users who can view it was easy. I looked at two tables - my users table and my permissions table. To build this result set I just query: