CODEWITHSUNDEEP
mysql
superkytoz
superkytoz

Reputation: 1279

Trying to make a subquery with three tables

Hi I'm using the following query to let users search for an order:

SELECT orders.*, tasks_x.task_all
        FROM orders LEFT JOIN (SELECT GROUP_CONCAT(tasks.task_name SEPARATOR ",")
            AS task_all, ordertasks.id_order 
                FROM tasks JOIN ordertasks 
                on ordertasks.id_task = tasks.id GROUP BY ordertasks.id_order) as tasks_x
                ON tasks_x.id_order = orders.id WHERE orders.order_name 
                LIKE "%keyword%" OR tasks_x.task_all LIKE "%keyword%"

They can search an order through a order name or a task name that is linked to a order that they were searching for. But now I want also that they can search an order through a company name.

Here is my clients table: https://i.sstatic.net/qtDS3.png

And here are my other tables if you need them:

orders:

https://i.sstatic.net/RjTjD.png

ordertasks:

https://i.sstatic.net/NRBve.png

tasks:

https://i.sstatic.net/NNNLq.png

I have also put the query and my tables on sqlfiddle here is the link:

http://sqlfiddle.com/#!2/7d942/2

To be actually honest I had received help from my internship supervisor to create the sub query that you find above. So I don't know how to achieve this. I think I know how to do this with joins, but not with subqueries. But I gladly want to know it how this can be achieved with a subquery.

Upvotes: 0

Views: 55

Answers (1)

Jaylen
Jaylen

Reputation: 40361

If the relation between orders to (task or ordertasks) is 1:1 then this query should do the trick with no subqueries

SELECT o.*, t.task_name
FROM orders AS o
LEFT JOIN clients AS c ON c.id = o.id_client
LEFT JOIN ordertasks AS x on x.id_order = o.id
LEFT JOIN tasks AS t ON t.id = x.id_order
WHERE o.order_name LIKE "%keyword%" 
OR t.task_name LIKE "%keyword%" 
OR c.companyName LIKE "%keyword%";

If the relation between orders to (task or ordertasks) is 1:n where n can be greater than 1 then this query should do the trick

SELECT c.companyName, x.task_all, o.*
FROM orders AS o 
LEFT JOIN (
    SELECT GROUP_CONCAT(t.task_name) AS task_all, x.id_order 
    FROM tasks AS t
    INNER JOIN ordertasks AS x on x.id_task = t.id
    WHERE t.task_name LIKE "%wau%"
    GROUP BY x.id_order
) AS x ON x.id_order = o.id
LEFT JOIN clients AS c ON c.id = o.id_client
WHERE o.order_name LIKE "%wau%" 
OR x.id_order IS NOT NULL
OR c.companyName LIKE "%wau%"

Note: I do not agree with using LIKE operation to find records because LIKE "%keyword%"will use no indexes "this mean MySQL will do a whole table scan every time "per table". Trying to search using LIKE will be very slow if you have lots of records in the tables

to improve the speed you can use Full-Text Index on the columns and then use MATCH() AGAINST() logic to search for the records instead of using LIKE

If you want to go that route then you would have do the the following

  1. add FULLTEXT INDEX on orders.order_name
  2. add FULLTEXT INDEX on task.task_name
  3. add FULLTEXT INDEX on clients.companyName

Here are the queries that you would have to run

ALTER TABLE orders ADD FULLTEXT INDEX order_name (order_name);
ALTER TABLE tasks ADD FULLTEXT INDEX task_name (task_name);
ALTER TABLE tasks ADD FULLTEXT INDEX company_name (companyName);

Then the first query will be something like this

SELECT o.*, t.task_name
FROM orders AS o
LEFT JOIN clients AS c ON c.id = o.id_client
LEFT JOIN ordertasks AS x on x.id_order = o.id
LEFT JOIN tasks AS t ON t.id = x.id_order
WHERE MATCH('keyword') AGAINST(o.order_name)
OR MATCH('keyword') AGAINST(t.task_name)
OR MATCH('keyword') AGAINST(c.companyName);

The second option will be something like this

SELECT c.companyName, x.task_all, o.*
FROM orders AS o 
LEFT JOIN (
    SELECT GROUP_CONCAT(t.task_name) AS task_all, x.id_order 
    FROM tasks AS t
    INNER JOIN ordertasks AS x on x.id_task = t.id
    WHERE MATCH('wau') AGAINST(t.task_name)
    GROUP BY x.id_order
) AS x ON x.id_order = o.id
LEFT JOIN clients AS c ON c.id = o.id_client
WHERE o.order_name LIKE "%wau%" 
OR x.id_order IS NOT NULL
OR c.companyName LIKE "%wau%"

Upvotes: 1

Related Questions