Mysql getting all joined records both tables

Question

I'm using a great php framework for working with my database, medoo.php. Link to medoo docs for reference: http://medoo.in/doc

What I Want To Happen:

• Return all records from joined tables, where it matches one or more conditions from a right table. (main table is 'items')
• Includes matching/joined records from the right table. (all 'tags' related to 'items' returned in this case).

What Is Currently Happening:

• Returns records from left table (items) joined with right table (tags). However, it's only returning the records explicitly containing tags from the WHERE condition. I want all tags joined to the items, on items that have tags matching the WHERE condition.

Let's get some code to better illustrate the issue:

//example 'items' table
    id    -   123
    name  -   'test'
    desc  -   'testing'

//example 'tags' table
    id    -   100
    name  -   'tag1'

//example 'taglist' table (relational)
    id      -   10
    itemid  -   123
    tagid   -   100

That illustrates the basic setup, minimized a bit for clarity.

Then, using medoo, I'm selecting from 'items' and joining tags/taglist with left joins (currently). The WHERE clause: tag.id matches the posted tagid to search. Also tried taglist.tagid in the WHERE.

The results returned are of all 'items' that contain tags matching the WHERE clause, as you'd expect. However, I want all of the tags that go with those items, not just the tags I searched (which is what I get).

I'm thinking it must be something to do with the JOINs or the WHERE clause, but I must say I'm lost after hours of attempts, research and more attempts. Hoping someone can give me a quick hand, spotting where I've likely made an error in my logic.

For further reference, here is what the full query setup, using medoo.

//setup query
$table = 'items';
$join = [
    "[>]taglist(tl)" => ["id" => "itemid"],
    "[>]filelist(fl)" => ["id" => "itemid"],
    "[>]tags(t)" => ["tl.tagid" => "id"],
    "[>]files(f)" => ["fl.fileid" => "id"]
];
$cols = [
    "items.id(id)",
    "items.itemid(itemid)",
    "items.name(name)",
    "items.def(def)",
    "items.class(class)",
    "items.timeline(timeline)",
    "t.id(tagid)",
    "t.name(tagname)",
    "f.id(fileid)",
    "f.name(filename)",
    "f.path(filepath)",
    "f.type(filetype)",
    "tl.tagid"
];
$where = [
    "AND" => ["tl.tagid" => $tags],
    "ORDER" => ["items.id DESC","t.name ASC"],
    "LIMIT" => [$start,$limit]
];

// Do query
$results = $db->select($table, $join, $cols, $where);

If you're not familiar with medoo, this should still illustrate how the query is setup fairly simply. (Query works just fine, I'm just not getting all the results I want).

Thank you for any advice. I'm learning a heck of a lot this week!

Answer 1

If you do leftside LEFT JOIN rightside ON something and then do WHERE rightside.column = something you convert the LEFT JOIN into a straight JOIN.

Straight JOINs suppress rows from the leftside that don't match the ON in the join.

So try getting rid of your WHERE condition.

Edit I don't know the framework you mention.

But, to get every item that has a particular tagid, you do this.

      SELECT DISTINCT itemid FROM taglist WHERE tagid = $tags

Then you can do your main query with

    WHERE items.id IN (SELECT DISTINCT itemid FROM taglist WHERE tagid = $tags)

to filter out just the items with matching tags. Your main query will fetch all the tags, including the one with tagid = $tags and all the others, but only for the items having the matching tag. It's all set theory at its heart.