title: Selecting first in each (SQL / PHP)
body: <p>Ok, I tried holding off and looking for answers, but I need your guys' help! I have three tables- category, project, and images. I have it set up so that images are linked to a specific project, and each project has a category. I use two inner joins to do this (is this correct?) What I'm looking to do eventually is have PHP echo out the first image from each project, and then I'll use ajax to load in the rest of the images per project. I've looked at LIMIT and I'm sure there's a way to do it easily with PHP and a for loop, but I'm just stumped! How can I echo out just the first "filename" per project? Thanks in advance!</p>
  
  <p>Tables:</p>
  
  <pre><code>CREATE TABLE `category` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name` (`name`)
  ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
  
  CREATE TABLE `project` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `caption` text NOT NULL,
    `category` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
  
  CREATE TABLE `images` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `filename` varchar(255) NOT NULL,
    `project` int(50) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>SELECT i.filename as filename,
            i.project as project_id,
            p.name as project_name,
            c.name as category
  
  FROM images i
  
  INNER JOIN project p
  ON i.project = p.id
  
  INNER JOIN category c
  ON p.category = c.id
  </code></pre>