Select entries by where from different tables

Question

I have two tables:

table "data"

 id        int(11) NOT NULL AUTO_INCREMENT,
 name      varchar(200),
 remark    varchar(200),

table "tags"

 id        int(11) NOT NULL AUTO_INCREMENT,
 macro     varchar(200),
 value     varchar(200),
 data_id   int(11)

One entry in "data" can have zero or more entries in "tags" which are connected by "data_id" (reference to "data.id").

Now I want to search for all entries in "data" with some WHERE condition from both tables (entries that also a match in tags key/value).

SELECT data.id,data.name 
FROM data,tags 
WHERE (data.name LIKE ?) 
   or (data.remark LIKE ?) 
   or (tags.value LIKE ?) ...

How to do this nested query?

THANKS

[How do I convert a “legacy” TO left outer join statement](https://stackoverflow.com/questions/4001045/how-do-i-convert-a-legacy-left-outer-join-statement-in-oracle) — Luuk, Apr 10 '21 at 19:42
You do not explain why the result are not the ones you expect.... — Luuk, Apr 10 '21 at 19:43
Please supply sample data and desired results. [How to ask a good question](https://stackoverflow.com/help/how-to-ask) — Stu, Apr 10 '21 at 19:46

Drakmord2 · Answer 1 · 2021-04-10T20:09:47.120

0

A SQL query with data from two tables need to make use of a JOIN clause

Your query will look like:

SELECT d.id, d.name 
FROM data AS d
    JOIN tags AS t ON t.data_id = d.id
WHERE (d.name LIKE ?) 
   or (d.remark LIKE ?) 
   or (t.value LIKE ?) ...

It's worth noting that this query will select only the data that has at least one tag. If you need the data even if there's no tag associated you could do:

SELECT d.id, d.name 
FROM data AS d
    LEFT OUTER JOIN tags AS t ON t.data_id = d.id
WHERE (d.name LIKE ?) 
   OR (d.remark LIKE ?)
   OR (t.value LIKE ?) ...

You can read more about it here and here.

edited Apr 10 '21 at 20:09

answered Apr 10 '21 at 19:48

Drakmord2

864
6
13

The OP is describing a `left outer join` scenario – Stu Apr 10 '21 at 19:50
@Stu Wouldn't `"WHERE condition from both tables"` imply a `INNER JOIN`? Regardless, I'll expand the answer. – Drakmord2 Apr 10 '21 at 19:59
1

The condition is `or` not `and` – Stu Apr 10 '21 at 20:04

Gordon Linoff · Answer 2 · 2021-04-17T16:50:18.353

0

I would suggest using exists:

SELECT d.id, d.name 
FROM data d
WHERE d.name LIKE ? OR
      d.remark LIKE ? OR
      EXISTS (SELECT 1 FROM tags t WHERE t.data_id = d.id AND t.value LIKE ?);

For performance, you want an index on tags(data_id, value):

CREATE INDEX idx_tag_data_id_value on tags(data_id, value);

Expressing this as a LEFT JOIN is rather tricky. Assuming that only one tag would match per data, it would be:

SELECT d.id, d.name 
FROM data d LEFT JOIN
     tags t
     ON t.data_id = d.id AND t.value LIKE ?
WHERE d.name LIKE ? OR
      d.remark LIKE ? OR
      t.data_id IS NOT NULL;

However, this can return duplicate data ids if multiple tags match the same data id. I recommend EXISTS.

edited Apr 17 '21 at 16:50

answered Apr 10 '21 at 23:33

Gordon Linoff

1,242,037
58
646
786

Hello everybody, THANKS for all the hints for my problem. The idea with the EXISTS seems to be the simplest one and also (with the extra SELECT) the most powerfull. THANKS to @gordon-linoff – devtry Apr 11 '21 at 09:02
I just tried this with a sample database (with random data): aprox 1000 `data` entries and 15000 `tags` entries. The `EXISTS` query seems to be very slow... :-( – devtry Apr 11 '21 at 17:01
@devtry . . . You want an index to support `EXISTS`. – Gordon Linoff Apr 11 '21 at 21:24
what do you mean with "you want an index"? – devtry Apr 12 '21 at 05:58
@devtry . . . I modified the answer already to point out the index you want. – Gordon Linoff Apr 12 '21 at 12:11
"For performance, you want an index on tags(data_id, value)" Sorry, I do not unterstand. What/how to add? – devtry Apr 17 '21 at 16:44

Select entries by where from different tables

2 Answers2