Mysql get results from one table based on another with join

Question

I have 2 tables:

$sql = "CREATE TABLE $media_table (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `type` varchar(50) NOT NULL,
            `title` varchar(255) DEFAULT NULL,
            `description` varchar(2000) DEFAULT NULL,
            `playlist_id` int(11) NOT NULL,
            PRIMARY KEY (`id`),
            INDEX `playlist_id` (`playlist_id`),
        ) $charset_collate;";

$sql = "CREATE TABLE $taxonomy_table (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `type` varchar(10) NOT NULL,
                `title` varchar(500) NOT NULL,
                `media_id` int(11) NOT NULL,
                `playlist_id` int(11) NOT NULL,
                PRIMARY KEY (`id`),
                INDEX `media_id` (`media_id`),
                CONSTRAINT `mvp_taxonomy_ibfk_1` FOREIGN KEY (`media_id`) REFERENCES {$media_table} (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            ) $charset_collate;";

Let say I want to select all rows from media_table where playlist_id=5 and title from taxonomy_table="sport, football".

I could run 2 queries, first get all media_id from taxonomy_table where title="..." AND playlist_id="5", then second query select all rows from media_table WHERE id IN (ids).

Does this belongs to some kind of JOIN query maybe?

I tried this but I am not getting desired results:

$query = "SELECT * FROM {$media_table} 
        INNER JOIN {$taxonomy_table} 
        ON {$media_table}.id = {$taxonomy_table}.media_id 
        WHERE {$taxonomy_table}.type='tag' AND {$taxonomy_table}.title IN ($arg) AND {$taxonomy_table}.playlist_id=%d
        ORDER BY {$media_table}.order_id";

It seems like its mixing all columns from both tables into the results, but I only want to retrieve rows from media_table that have title(s) from taxonomy_table.

If it was me, I'd forget about the PHP for now, and instead see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Sep 23 '18 at 21:33

asattler · Accepted Answer · 2018-09-23T21:58:37.687

2

It seems like its mixing all columns from both tables into the results, but I only want to retrieve rows from media_table that have title(s) from taxonomy_table.

Reason is that the join enables you to select data from both tables. The trick in this case is to change the asterisk (*) to specific columns or to use a prefix for the asterisk.

examples for using a prefix:

$query = "SELECT {$media_table}.* FROM {$media_table} 
INNER JOIN {$taxonomy_table} 
ON {$media_table}.id = {$taxonomy_table}.media_id 
    `WHERE {$taxonomy_table}.type='tag' AND {$taxonomy_table}.title IN ($arg) AND` {$taxonomy_table}.playlist_id=%d
ORDER BY {$media_table}.order_id";

or

$query = "SELECT t1.* FROM {$media_table} t1
INNER JOIN {$taxonomy_table} t2
ON t1.id = t2.media_id 
WHERE t2.type='tag' AND t2.title IN ($arg) AND t2.playlist_id=%d
ORDER BY t1.order_id";

UPDATE: You've raised two "new" question in the comment: From your use-cases I don't see any reason for using a join here.

1: retrieve rows from media_table that have ALL required title(s) from taxonomy_table

From my point of view there is no simple solution with just using SQL (except maybe really hacky string-operations in SQL). Easiest solution might be something like this:

$countTitles = count(explode(",", $args))
$query = "SELECT media_id from {$media_table} WHERE $countTitles = (
SELECT count(media_id) from {$taxonomy_table} WHERE type='tag' AND title IN ($arg) AND playlist_id=%d
)"

2: retrieve rows from media_table that ANY required title(s) from taxonomy_table.

This is just a simple in-clause.

$query = "SELECT * FROM {$media_table}
WHERE media_id IN (
SELECT media_id FROM {$taxonomy_table} WHERE type='tag' AND title IN ($arg) AND playlist_id=%d
)
";

edited Sep 23 '18 at 21:58

answered Sep 23 '18 at 20:59

asattler

59
5

Still not quite right, this is returning one (same) row twice because it has both values in taxonomy_table. I dont want that. How would I 1: retrieve rows from media_table that have ALL required title(s) from taxonomy_table, 2: retrieve rows from media_table that ANY required title(s) from taxonomy_table. – Toniq Sep 23 '18 at 21:15
A join will always deliver you one row per "match" in the join-clause (except further whereclause or distinct is used). Usually joins are used when you want to have data from both tables. – asattler Sep 23 '18 at 21:26
So the solution for your 2nd question is e.g. select * from {$media_table} where media_id in (select media_id from {$taxonomy_table} where title in ($arg)) – asattler Sep 23 '18 at 21:30
To solve your 1st question you need more logic, SQL isn't really made to solve things like this in one query. One option might be to use your programming language to help you out, in PHP it might be something like this: select * from {$media_table} where " . count(explode(',', $args)) + 1 . " = (select count(media_id) from {$taxonomy_table} where title in ($arg) [+ other filter criteria you need] ) – asattler Sep 23 '18 at 21:35
If I just use 2 queries like I mentioned in my post, will this be significantly slower? Because it seems much easier/humanly readable. – Toniq Sep 23 '18 at 21:40
Depends on the amount of calls for this. If you're inside of a loop with e.g. 100 iterations, 2 queries would mean 200 queries instead of 100. This might impact the performance. Basically for your 2nd) usecase I would stick to a single query, the 1st) usecase I might accept 2 queries if this makes it more easy to maintain. – asattler Sep 23 '18 at 21:46
But isnt this type of query relatively widely used? select all rows from one table from some condition in another table – Toniq Sep 23 '18 at 21:50
sure it is. But your use case isn't just like this, it's having a filter-criteria which might be "match any" or "match all". Match any is very-common (simple in-clause) but "match all" is different. There was already another topic for this, see: https://stackoverflow.com/questions/11636061/matching-all-values-in-in-clause – asattler Sep 23 '18 at 21:53
Actually (unfortunately) I dont need match any, I just need match all. Do I need to change my table structure to make queries more efficient? Many tags belong to each media, so I am not sure how else I could structure these tables. – Toniq Sep 24 '18 at 02:12
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180641/discussion-between-toniq-and-asattler). – Toniq Sep 24 '18 at 02:17

urs_ng · Answer 2 · 2018-09-23T21:53:27.037

0

use a union instead of join with a nested / join query

    $query = "SELECT * FROM {$media_table} 
            INNER JOIN {$taxonomy_table} 
            ON {$media_table}.id = {$taxonomy_table}.media_id 
            WHERE {$taxonomy_table}.type='tag' AND {$taxonomy_table}.title IN ($arg) AND {$taxonomy_table}.playlist_id=%d
            ORDER BY {$media_table}.order_id 
union SELECT * FROM {$media_table}
WHERE media_id IN (
SELECT media_id FROM {$taxonomy_table} WHERE type='tag' AND title IN ($arg) AND playlist_id=%d
)
";

edited Sep 23 '18 at 21:53

answered Sep 23 '18 at 21:22

urs_ng

33
11

Can you provide an example? – Toniq Sep 23 '18 at 21:50
i have added an edited example. You can try this and group the results for unique resultset – urs_ng Sep 23 '18 at 21:54
This is one query? I get error: Incorrect usage of UNION and ORDER BY – Toniq Sep 24 '18 at 02:22
you can remove the order by clause from the first query and put it in the end for the overall output – urs_ng Sep 24 '18 at 05:32

Mysql get results from one table based on another with join

2 Answers2