programming

MySQL LEFT JOIN: selecting records with no corresponding entries in second table

Book page

Syntax:

SELECT u.uid, u.name, m.id, p.value FROM mpulreg m, users u LEFT JOIN profile_values p ON p.uid = u.uid WHERE u.uid = m.regnum AND p.uid = u.uid AND p.fid='3' AND m.status != 'D' ORDER BY u.name

This says, select the fields from the tables and select profile_values.value where profile_values.uid = users.uid, even if it's empty. So, you'll get an empty value for the profile_values.value if it doesn't exist.

Detailed explanation

You have two or more tables and would like to get entries from all the tables in one SELECT statement. However, one of the tables doesn't have any entries that correspond to another table with entries in it.

For example, let's say you have a username table, an event registration table and an user real name table. The users table contains a few users; the event contains information about who has signed up for an event; and the real name table shows the username's real names:

+------------------+  +----------------+  +---------------------+
| users            |  | events         |  | names               |
+-----+------------+  +----+-----+-----+  +----+-----+----------+
| uid | name       |  | id | eid | uid |  | id | uid | realname |    
+-----+------------+  +----+-----+-----+  +----+-----+----------+
| 1   | kristofer  |  | 1  | 1   | 3   |  | 1  | 1   | Kris McQ |
+-----+------------+  +----+-----+-----+  +----+-----+----------+
| 2   | kitt       |  | 2  | 1   | 1   |  | 2  | 2   | Kate McQ |
+-----+------------+  +----+-----+-----+  +----+-----+----------+
| 3   | elizabeth  |  | 3  | 1   | 2   |  
+-----+------------+  +----+-----+-----+  

In this case, 'kitt' is going to event 1 (with eid = 1), as are 'kristofer' and 'elizabeth'. Similarly, kristofer's real name is 'Kris McQ', and kitt's real name is 'Kate McQ'. We don't know elizabeth's real name.

Now, we want to list all the people and see if they are coming to the event, what their real names are:

To do so, first SELECT on the users and events, and we see that everyone is going.

SELECT u.*, e.* FROM users u, events e WHERE u.id = e.uid;
...

If we just put in the real names table, we'll get the wrong information:

SELECT u.*, e.*, r.* FROM users u, events e, realnames r WHERE u.id = e.uid;
...

Instead we want to use a LEFT JOIN to join two tables, then use that result set on the selection of data from the third table:

SELECT u.*, e.*, r.* FROM users u, events e LEFT JOIN realnames r ON r.uid = e.uid WHERE u.id = e.uid;

Basic Postgresql Commands

Book page

Command

Result

\d

 

\dt

List all tables

\di

List all indexes

\ds

List all sequences

\dv

List all views

\dS

List all PostgreSQL-defined tables

\d table-name

Show table definition

\d index-name

Show index definition

\d view-name

Show view definition

\d sequence-name

Show sequence definition

\dp

List all privileges

\dl

List all large objects

\da

List all aggregates

\df

List all functions

\df function-name

List all functions with given name

\do

List all operators

\do operator-name

List all operators with given name

\dT

List all types

\l

List all databases in this cluster

MySQL Tricks and Tips

Book page

MySQL tricks and tips.

Also known as information I used frequently and have to look up each time, so why not put it all in one place?

Most of the information on these pages work for any SQL database. Comment structures are different among databases, as are administrative commands ('show tables' in MySQL vs '\d' in PostgreSQL). The rest, however, is the same.

Counting the Number of Bits in a Byte

Book page

From: http://www-db.stanford.edu/~manku/bitcount/bitcount.html

Counting Number of On Bits in an Integer

Fast Bit Counting Routines

Compiled from various sources by Gurmeet Singh Manku

A common problem asked in job interviews is to count the number of bits that are on in an unsigned integer. Here are seven solutions to this problem. Source code in C is available.

Iterated Count

   int bitcount (unsigned int n)  
   {
      int count=0;    
      while (n)
      {
         count += n & 0x1u ;
         n >>= 1 ;
      }
      return count ;
   }
Sparse Ones

   int bitcount (unsigned int n)  
   {  
      int count=0 ;
      while (n)
      {
         count++ ;
         n &= (n - 1) ;
      }
      return count ;
   }
Dense Ones

   int bitcount (unsigned int n)     
   {
      int count = 8 * sizeof(int) ;
      n ^= (unsigned int) -1 ;
      while (n)
      {
         count-- ;
         n &= (n - 1) ;
      }
      return count ;
   }
Precompute_8bit

   // static int bits_in_char [256] ;           
   
   int bitcount (unsigned int n)
   {
      // works only for 32-bit ints
    
      return bits_in_char [n         & 0xffu]
          +  bits_in_char [(n >>  8) & 0xffu]
          +  bits_in_char [(n >> 16) & 0xffu]
          +  bits_in_char [(n >> 24) & 0xffu] ;
   }
Iterated Count runs in time proportional to the total number of bits. It simply loops through all the bits, terminating slightly earlier because of the while condition. Useful if 1's are sparse and among the least significant bits. Sparse Ones runs in time proportional to the number of 1 bits. The line n &= (n - 1) simply sets the rightmost 1 bit in n to 0. Dense Ones runs in time proportional to the number of 0 bits. It is the same as Sparse Ones, except that it first toggles all bits (n ~= -1), and continually subtracts the number of 1 bits from sizeof(int). Precompute_8bit assumes an array bits_in_char such that bits_in_char[i] contains the number of 1 bits in the binary representation for i. It repeatedly updates count by masking out the last eight bits in n, and indexing into bits_in_char.

Precompute_16bit

  // static char bits_in_16bits [0x1u > 16) & 0xffffu] ;
  }

Precompute_16bit is a variant of Precompute_8bit in that an array bits_in_16bits[] stores the number of 1 bits in successive 16 bit numbers (shorts).

Parallel Count

  #define TWO(c)     (0x1u > (TWO(c))) & MASK(c))
     
  int bitcount (unsigned int n)
  {
     n = COUNT(n, 0) ;
     n = COUNT(n, 1) ;
     n = COUNT(n, 2) ;
     n = COUNT(n, 3) ;
     n = COUNT(n, 4) ;
     /* n = COUNT(n, 5) ;    for 64-bit integers */
     return n ;
  }
Parallel Count carries out bit counting in a parallel fashion. Consider n after the first line has finished executing. Imagine splitting n into pairs of bits. Each pair contains the number of ones in those two bit positions in the original n. After the second line has finished executing, each nibble contains the number of ones in those four bits positions in the original n. Continuing this for five iterations, the 64 bits contain the number of ones among these sixty-four bit positions in the original n. That is what we wanted to compute.

Nifty Parallel Count

 #define MASK_01010101 (((unsigned int)(-1))/3)
 #define MASK_00110011 (((unsigned int)(-1))/5)
 #define MASK_00001111 (((unsigned int)(-1))/17)

 int bitcount (unsigned int n)
 {
    n = (n & MASK_01010101) + ((n >> 1) & MASK_01010101) ; 
    n = (n & MASK_00110011) + ((n >> 2) & MASK_00110011) ; 
    n = (n & MASK_00001111) + ((n >> 4) & MASK_00001111) ; 
    return n % 255 ;
 }
Nifty Parallel Count works the same way as Parallel Count for the first three iterations. At the end of the third line (just before the return), each byte of n contains the number of ones in those eight bit positions in the original n. A little thought then explains why the remainder modulo 255 works.

MIT HACKMEM Count

 int bitcount(unsigned int n)                          
 {
    /* works for 32-bit numbers only    */
    /* fix last line for 64-bit numbers */
    
    register unsigned int tmp;
    
    tmp = n - ((n >> 1) & 033333333333)
            - ((n >> 2) & 011111111111);
    return ((tmp + (tmp >> 3)) & 030707070707) % 63;
 }
MIT Hackmem Count is funky. Consider a 3 bit number as being 4a+2b+c. If we shift it right 1 bit, we have 2a+b. Subtracting this from the original gives 2a+b+c. If we shift the original 2 bits right we get a, and so with another subtraction we have a+b+c, which is the number of bits in the original number. How is this insight employed? The first assignment statement in the routine computes tmp. Consider the octal representation of tmp. Each digit in the representation is simply the number of 1's in the corresponding three bit positions in n. The last return statement sums these octal digits to produce the final answer. The key idea is to add adjacent pairs of octal digits together and then compute the remainder modulus 63. This is accomplished by right-shifting tmp by three bits, adding it to tmp itself and ANDing with a suitable mask. This yields a number in which groups of six adjacent bits (starting from the LSB) contain the number of 1's among those six positions in n. This number modulo 63 yields the final answer. For 64-bit numbers, we would have to add triples of octal digits and use modulus 1023. This is HACKMEM 169, as used in X11 sources. Source: MIT AI Lab memo, late 1970's.

     No Optimization         Some Optimization       Heavy Optimization

  Precomp_16 52.94 Mcps    Precomp_16 76.22 Mcps    Precomp_16 80.58 Mcps  
   Precomp_8 29.74 Mcps     Precomp_8 49.83 Mcps     Precomp_8 51.65 Mcps
    Parallel 19.30 Mcps      Parallel 36.00 Mcps      Parallel 38.55 Mcps
         MIT 16.93 Mcps           MIT 17.10 Mcps         Nifty 31.82 Mcps
       Nifty 12.78 Mcps         Nifty 16.07 Mcps           MIT 29.71 Mcps
      Sparse  5.70 Mcps        Sparse 15.01 Mcps        Sparse 14.62 Mcps
       Dense  5.30 Mcps        Dense  14.11 Mcps         Dense 14.56 Mcps
    Iterated  3.60 Mcps      Iterated  3.84 Mcps      Iterated  9.24 Mcps

  Mcps = Million counts per second
Which of the several bit counting routines is the fastest? Results of speed trials on an i686 are summarized in the table on left. "No Optimization" was compiled with plain gcc. "Some Optimizations" was gcc -O3. "Heavy Optimizations" corresponds to gcc -O3 -mcpu=i686 -march=i686 -fforce-addr -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -malign-functions=4.
Gurmeet Singh Manku
Last update: 27 Jul 2002

PHP date/time values

Fmt Description
DAY ===========================================================
d    Day of the month, 2 digits with leading zeros
D    A textual representation of a day, three letters
j    Day of the month without leading zeros
l    A full textual representation of the day of the week
S    English ordinal suffix for the day of the month, 2 characters
w    Numeric representation of the day of the week
z    The day of the year (starting from 0)

WEEK ==========================================================
W    ISO-8601 week number of year, weeks starting on Monday (added in PHP 4.1.0)

MONTH =========================================================
F    A full textual representation of a month, such as January or March
m    Numeric representation of a month, with leading zeros
M    A short textual representation of a month, three letters
n    Numeric representation of a month, without leading zeros
t    Number of days in the given month

YEAR ==========================================================
L    Whether it's a leap year
Y    A full numeric representation of a year, 4 digits
y    A two digit representation of a year

TIME ==========================================================
a    Lowercase Ante meridiem and Post meridiem
A    Uppercase Ante meridiem and Post meridiem
B    Swatch Internet time
g    12-hour format of an hour without leading zeros
G    24-hour format of an hour without leading zeros
h    12-hour format of an hour with leading zeros
H    24-hour format of an hour with leading zeros
i    Minutes with leading zeros
s    Seconds, with leading zeros

TIMEZONE ======================================================
I    Whether or not the date is in daylights savings time
O    Difference to Greenwich time (GMT) in hours
T    Timezone setting of this machine
Z    Timezone offset in seconds. Zones west of UTC are negative, east are positive.

FULL DATETIME =================================================
c    ISO 8601 date (added in PHP 5)
r    RFC 2822 formatted date
U    Seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)

Emacs key bindings

Book page

From: http://www.pdc.kth.se/training/Tutor/Basics/emacs/emacsis.great

--------------------------------------------------------------------
                      Summary of Emacs Commands
 
Starting emacs:
        start up only                       emacs
        start up with a file                emacs filename
 
Leaving emacs:
        suspend Emacs                       C-z
        exit Emacs permanently              C-x C-c
 
Files:
        read a file into Emacs              C-x C-f
        save a file back to disk            C-x C-s
        insert a file into this buffer      C-x i
        replace this file with another      C-x C-v
        write buffer to a specified file    C-x C-w
        run the directory editor            C-x d
 
Getting help:
        starting HELP                       C-h
        get rid of HELP window              C-x 1
        scroll HELP window                  ESC C-v
        apropos                             C-h a
        show function a key runs            C-h c
        describe a function                 C-h f
        get mode-specific information       C-h m
 
Error Recovery:
        abort partially typed command       C-g
        recover file lost by system crash   M-x recover-file
        undo an unwanted change             C-x u  or  C-_
        restore buffer to original          M-x revert-buffer
        redraw garbaged screen              C-l
 
Incremental Search:
        search forward                      C-s
        search backward                     C-r
        regular expression search           C-M-s
        exit incremental search             ESC
        undo effect of last character       DEL
        abort current search                C-g
 
Motion:
      entity to move over                  backward            forward
        character                           C-b                 C-f
        word                                M-b                 M-f
        line                                C-p                 C-n
        go to line beginning/end            C-a                 C-e
        sentence                            M-a                 M-e
        paragraph                           M-[                 M-]
        page                                C-x [               C-x ]
        sexp                                C-M-b               C-M-f
        function                            C-M-a               C-M-e
        go to buffer beginning/end          M-
 
 
        scroll to next screen                      C-v
        scroll to previous screen                  M-v
        scroll left                                C-x 
 
Killing and Deleting:
      entity to kill                       backward            forward
        character(delete, not kill)         DEL                 C-d
        word                                M-DEL               M-d
        line(to end of)                     M-O C-k             C-k
        sentence                            C-x DEL             M-k
        sexp                                M-- C-M-k           C-M-k
 
        kill region                                C-w
        kill to next occurrence of char            M-z char
        yank back last thing killed                C-y
        replace last yank with previous kill       M-y
 
Marking:        
        set mark here                              C-@ or C-SPC
        exchange point and mark                    C-x C-x
        set mark arg words away                    M-@
        mark paragraph                             M-h
        mark page                                  C-x C-p
        mark sexp                                  C-M-@
        mark function                              C-M-h
        mark entire buffer                         C-x h
 
Query Replace:
        interactively replace a string             M-%
        using regular expressions                  M-x query-replace-regexp
 
        Valid responses in query-replace mode are
 
        replace this one, go on to next            SPC
        replace this one, don't move               ,
        skip to next without replacing             DEL
        replace all remaining matches              !
        back up to the previous match              ^
        exit query-replace                         ESC
        enter recursive edit(C-M-c to exit)        C-r
 
Multiple Windows:
        delete all other windows                   C-x 1
        delete this window                         C-x 0
        split window in 2 vertically               C-x 2
        split window in 2 horizontally             C-x 5
        scroll other window                        C-M v
        switch cursor to another window            C-x o
        shrink window shorter                      M-x shrink-window
        grow window taller                         C-x ^
        shrink window narrower                     C-x {
        grow window wider                          C-x }
        select a buffer in other window            C-x 4 b
        find file in other window                  C-x 4 f
        compose mail in other window               C-x 4 m
        run Dired in other window                  C-x 4 d
        find tag in other window                   C-x 4 .
 
Formatting:
        indent current line                        TAB
        indent region                              C-M-\
        indent sexp                                C-M-q
        indent region rigidly arg columns          C-x TAB
        insert newline after point                 C-o
        move rest of line vertically down          C-M-o
        delete blank lines around point            C-x C-o
        delete all white space around point        M-\
        put exactly one space at point             M-SPC
        fill paragraph                             M-q
        fill region                                M-g
        set fill column                            C-x f
        set prefix each line starts with           C-x .
 
Case Change:
        uppercase word                             M-u
        lowercase word                             M-l
        capitalize word                            M-c
        uppercase region                           C-x C-u
        lowercase region                           C-x C-l
        capitalize region                          M-x capitalize-region
 
The Minibuffer:
      The following keys are defined within the minibuffer.
        complete as much as possible               TAB
        complete up to one word                    SPC
        complete and execute                       RET
        show possible completeions                 ?
        abort command                              C-g
      Type C-x ESC to edit and repeat the last command that
      used the minibuffer.  The following keys are then
      defined.
        previous minibuffer command                M-p
        next minibuffer command                    M-n
 
Buffers:
        select another buffer                      C-x b
        list all buffers                           C-x C-b
        kill a buffer                              C-x k
 
Transposing:
        transpose characters                       C-t
        transpose words                            M-t
        transpose lines                            C-x C-t
        transpose sexps                            C-M-t
 
Spelling Check:
        check current word                         M-$
        check region                               M-x spell region
        check buffer                               M-x spell buffer
 
Tags:
        find tag                                   M-.
        find next occurrence of tag                C-u M-.
        specify a new tags file                    M-x visit-tags-table
        regexp search on all files in tags table   M-x tags-search
        query replace on all the files             M-x tags-query-replace
        continue last tags search or query-replace M-,
 
Shells:
        execute a shell command                    M-!
        run a shell command on the region          M-|
        filter region through a shell command      C-u M-|
        start a shell in window *shell*            M-x shell
 
Rmail:
        scroll forward                             SPC
        scroll reverse                             DEL
        beginning of message                       . (dot)
        next non-deleted message                   n
        previous non-deleted message               p
        next message                               M-n
        previous message                           M-p
        delete message                             d
        delete message and back up                 C-d
        undelete message                           u
        reply to message                           r
        forward message to someone                 f
        send mail                                  m
        get newly arrived mail                     g
        quit Rmail                                 q
        output message to another Rmail file       o
        output message in Unix-mail style          C-o
        show summary of headers                    h
 
Regular Expressions:
      The following have special meaning inside a regular expression.
        any single character                       . (dot)
        zero or more repeats                       *
        one or more repeats                        +
        zero or one repeat                         ?
        any char in set                            [ ... ]
        any char not in set                        [^ ... ]
        beginning of line                          ^
        end of line                                $
        backslash                                  \\
        alternative ("or")                         \|
        grouping                                   \( ... \)
        nth group                                  \n
        beginning of buffer                        \`
        end of buffer                              \'
        word break                                 \b
        not beginning or end of word               \B
        beginning of word                          \
        any word-syntax char                       \w
        any non-word-syntax char                   \W
        char with syntax c                         \sc
        char with cyntax not c                     \Sc
 
Registers:
        copy region to register                    C-x x
        insert register contents                   C-x g
        save point in register                     C-x /
        move point to saved location               C-x j
 
Info:
        Enter the Info documentation reader        C-h i
        Moving within a node:
        scroll forward                             SPC
        scroll backward                            DEL
        beginning of node                          . (dot)
 
        Moving between nodes:
        next node                                  n
        previous node                              p
        move up                                    u
        select menu item by name                   m
        select nth menu item by number (1-5)       n
        follow cross reference (return with l)     f
        return to last node you saw                l
        return to directory node                   d
        go to any node by name                     g
 
        Other:
        run Info tutorial                          h
        list Info commands                         ?
        quit Info                                  q
        search nodes for regexp                    s
 
Keyboard Macros:
        start defining a keyboard macro            C-x (
        end keyboard macro definition              C-x )
        execute last-defined keyboard macro        C-x e
        append to last keyboard macro              C-u C-x
        name last keyboard macro                   M-xname-last-kbd-macro
        insert lisp definition in buffer           M-x insert-kbd-macro
 
Commands Dealing with Emacs Lisp:
        eval sexp before point                     C-x C-e
        eval current defun                         C-M-x
        eval region                                M-x eval-region
        eval entire buffer                         M-x eval-current-buffer
        read and eval minibuffer                   M-ESC
        re-execute last minibuffer command         C-x ESC
        read and eval Emacs Lisp file              M-x load-file
        load from standard system directory        M-x load-library
 
                                                 PJV 4/20/1990

Programming articles, tips and tricks

Book page

Bit shifing questions and answers

Book page

From http://beust.com/weblog/, on 26 July 2004.

Fun with bits

When I interview someone, I usually ask at least one bit-shifting question to
the candidate.  It's not a dealbreaker if she fails to answer it, but it
certainly wins her a lot of points if she can, or at least discuss
it in convincing ways.

Interestingly, pretty much every single candidate that I have ever
interviewed who did well on the other questions that I asked (typically at a
higher-level, such as OO design or design pattern) also does well on the
bit-shifting question, so it's usually a pretty strong indicator of someone who
has a good computer science background overall.

There are three categories of bit-shifting questions that I usually pick
from, in increasing order of difficulty:

  1. How can you determine if a number is a power of two?
  2. How can you clear the rightmost bit?
  3. How can you count the number of 1 bits?

This last question is a classic, so I added my own personal twist to it (see
below).

Let's take them in turn.

1.  How can you determine if a number is a power of two?

The easiest way to answer this question is to notice that a power of two
has exactly one 1 followed by only zero's (except for 1, which is also covered by the following
solution).  Therefore, the quickest way is probably:

return x != 0 && (x & (x-1)) == 0;

The idea is that subtracting one from such a number will flip all its bits: 
the 1 becomes a 0 and all the zeros become 1.  "and"ing these two numbers
will therefore equal to zero.

As far as I can tell, this only happens for powers of two:  if the
number has any other 1 than the one we are interested in, it will not be flipped
by the subtraction and will therefore survive the &.

I will accept any variations thereof:  at this point, I am just trying to see
if the candidate has any notion of binary coding.

2.  How can you clear the rightmost bit?

There are several ways to answer this question and ideally, the candidate
will start by giving me the easiest one and then, the bit-oriented one when
pressed.  You can simply observe that clearing the rightmost bit is a no-op
is the number is even and a decrement of the number if it is odd:

return (n % 2 == 0 ? n : (n - 1))

This is not very efficient so I will ask for a faster solutions.  There are
a couple of options:

return n - (n & 1)

or

(n >> 1) << 1

This last solution is mine, and I prefer it over the others because shifting
is much faster than subtracting.  If the candidate doesn't provide it, I
will usually suggest it and ask her to discuss the pros and cons of each.

3. How can you count the number of 1 bits?

And finally, we reach this timeless classic.  If the candidate
has never heard of it, she's in for a tough time.  And even if she does, I
have my own twist to it that guarantees me that I will get to take a close look
at the way she thinks.

Let's start with the naive solution:  shift the number right and
increment a counter each time the rightmost bit is 1:

int counter = 0;
while (n > 0) {
  if (n % 2 == 1) counter++;
  n >>= 1;
}

Next comes the leap of faith:  "This algorithm executes p times, where p is
the size in bits of n.  Can you think of a solution that executes in b
times, where b is the number of 1 bits in n?".

Either she knows the answer or
she doesn't, and if she doesn't, she'll be stumped.  At this point, I will
step in and I will give her a hint:  "Can you describe to me what the
expression n & (n-1) does to n?".

If you've never seen this trick, it's impossible to answer right away: 
you will need to apply the formula on the board on a few numbers before you put
the pieces together (which is already not very easy).  The answer is: 
"It clears the rightmost 1 bit in n".

With this in mind, the solution to the puzzle becomes:

int counter = 0;
while (n != 0) {
  counter++;
  n = n & (n-1);
}

If she gave me this answer without help, I will strongly suspect that she
already knew the trick, so I will throw in my own twist:  "Can you
demonstrate that n & (n-1) clears the rightmost bit?".

And I will leave
this as an exercise to the reader for now.  Answer in a few days.

Published my first tutorial.

Blog

I made my first contribution to the Drupal project today. I wrote a tutorial (also published on my site) and published it just now. It took me two days to do. I was pretty nervous about publishing such a lengthy first article/page as my first contribution, but I received some good feedback, so I think it'll be fine.

Publishing the tutorial on my site meant I needed to figure out some details with my site. I had the system stripping HTML tags. Unfortuately, I was stripping too many tags: the formatting was lost. I've since added the tags, so it looks good here. I'm pretty excited.

There's a lot of configuration details on the site. I'm trying to keep the base code unmodified so that I can update at any point. We'll see how long I can keep from fixing things.

Pages