I’m screwing up this LEFT JOIN

Question

I'm doing a left join on several tables. What I want to happen is that it list all the businesses. Then, it looks through the photos, videos, specials and categories. IF there are photos, then the tables shows yes, if there are videos, it shows yes in the table.

It does all that without any problems. Except for one thing. For every photo, it shows the business that many times. For example, if there are 5 photos in the DB for a business, it shows the business five times.

Obviously, this is not what I want to happen. Can you help?

function frontPageList() {
    $this->db->select('b.id, b.busname, b.busowner, b.webaddress, p.thumb, v.title, c.catname');
    $this->db->from ('business AS b');
    $this->db->where('b.featured', '1');
    $this->db->join('photos AS p', 'p.busid = b.id', 'left');
    $this->db->join('video AS v', 'v.busid = b.id', 'left');
    $this->db->join('specials AS s', 's.busid = b.id', 'left');
    $this->db->join('category As c', 'b.category = c.id', 'left');
    return $this->db->get();

Answer 1

A join will multiply the number of rows if there is more than one match. If this is not what you want then you should either start with getting a count and then query each table individually for the actual rows, or you should do grouping in code based on when the business values change.

Answer 2

if you don't care about the photos themselves, or which one it grabs, you can try adding a on GROUP BY b.id