Join a joined table to itself

Question

I have a database table, dictionary, that has two strings and a language id.

The columns are:

id
product_id
key
translation
language_id

Another table, dictionary_versions, just has the dictionary_id and version of translation

id
dictionary_id
version_id

I have two different translations in the table for each key, but sometimes, only one translation for the key. I am looking for a way to get all translations of one language, and, if there are any, translation of another language id.

Im looking to get all translation of one language_id and organizer_id and one version. I have tried to create a temporary table with all values of what I am comparing to, then doing a left join of the temporary table on the dictionary table to get all translated values of the desired language, and if there are any translations of another language, include those.

The problem is that it gets slow when there are 10000 translations for two languages. Is there a better way to join a table onto itself using the dictionary_versions table as a where clause on both?

SELECT
    d.*
FROM
    dictionary d 
LEFT JOIN
    dictionary_versions dv ON dv.dictionary_id = d.id
LEFT JOIN
    dictionary d2
LEFT JOIN
    dictionary_versions dv2 ON d2.id = dv2.dictionary_id
ON
    d2.key = d.key 
WHERE
    d.product_id = 1 AND dv.version_id = 3
AND
    d.language_id = 1
LIMIT
    0,10

This was one of the queries I tried. However, if there are multiple versions, then it gets all version of the fd2 table, leading to data inaccuracies.

The other way I tried, was with the temporary table, this works, but it slow. it is done in two queries:

CREATE TEMPORARY TABLE IF NOT EXISTS dictionary_temp_1 
AS (
    SELECT d.* FROM `dictionary` AS `d`
    LEFT JOIN `dictionary_versions` AS `dv` ON dv.dictionary_id = d.id
    WHERE d.product_id = 1 AND dv.version_id = 3 AND d.language_id = 1 
    ORDER BY fd.key ASC LIMIT 0,10 )

Second:

SELECT
    d.key,
    d2.key AS toKey,
    d.translation AS `1`,
    d2.translation AS `2`
FROM
    `dictionary` AS `d`
LEFT JOIN
    `dictionary_versions` AS `dv` ON d.id = dv.dictionary_id
LEFT JOIN
    `dictionary_temp_1` AS `d2` ON d2.key = d.key
WHERE
     d.product_id = 1 AND dv.version_id = 3 AND d.language_id = 1
ORDER BY
     d.key ASC LIMIT 0,10

Note that key is a reserved word, so a poor choice for a table/column identifier — Strawberry, Oct 26 '18 at 22:23
@Strawberry why is it a poor choice aside from having to surround it by back ticks? — Ice76, Oct 26 '18 at 22:28
@Ice76 That's the reason why. I have a table with that name, it's a PITA having to remember to backtick it when I use it. Luckily I usually access it through a view, and I renamed the column there. — Barmar, Oct 26 '18 at 22:35
A tiny set of **sample rows**, plus the **expected result** would both illustrate your meaning and allow us to demonstrate a solution. Also, is actually helpful to use to see what code you have already tried. If you do these things instead of responding to questions your could be testing proposed solutions. .... It is poor practice to name any object using SQL reserved or key words, it will haunt you eventually. — Paul Maxwell, Oct 26 '18 at 22:46
that helps! but sample data? thing is, you already have that and we don't. just a few rows copy/pasted into question. you can form ascii table here if needed https://ozh.github.io/ascii-tables/ — Paul Maxwell, Oct 26 '18 at 23:06
NB. For performance related matters, one should always look at the `explain plan` and consider if the query is using indexes. Have you done this? What indexes exist on those tables? — Paul Maxwell, Oct 26 '18 at 23:11
Please read & act on [mcve]. PS Learn what LEFT JOIN returns: INNER JOIN rows plus unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of a LEFT JOIN. A WHERE or ON that requires a right table column to be not NULL after a LEFT JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN rows, ie "turns LEFT JOIN into INNER JOIN". You have that. In all your queries. — philipxy, Oct 27 '18 at 04:37
Possible duplicate of [Left Join not returning all rows](https://stackoverflow.com/questions/2700354/left-join-not-returning-all-rows) — philipxy, Oct 27 '18 at 04:41
Part of justifying "minimal" is finding a working example for maximal partial functionality. Show a program that calculates what you expect it to as it goes through (sub)expressions by saying what that is & showing that it actually does it via incremental output. Add code giving a problem. Ask about the (small) difference between the examples. (Basic debugging.) Then: This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. — philipxy, Oct 27 '18 at 04:51
@philipxy No where near the duplicate you mentioned. And I understand a LEFT JOIN, thank you. The working example is in the question, showing what I want to do. Where do you need more clarification? — Ice76, Oct 29 '18 at 17:29
Re the duplicate: Your first query result would be unchanged if the first LEFT were INNER so the link is at least one thing wrong with it. Re a MCVE: Think about what someone has to type to run your code or variations. Also your slowness is probably lack of indexes but you give no DDL. Probably with one query & indexes you will be OK but your wrong attempts have a certain error where instead of joining several separate join-and-aggrates people do all the joins first. That's the faq I mentioned. Re "minimal" If you examined the inputs to each operator you would see evidence of the problems. — philipxy, Oct 29 '18 at 21:09
[Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/q/45250646/3404097) [Understanding multiple table join with aggregation](https://dba.stackexchange.com/a/87479/439322) — philipxy, Oct 29 '18 at 21:09
I would agree that with the links I am not much addressing your speed problem in terms of the temp table approach since I am suggesting you use one query. Although the problem with the temp version is likely original lack of indexes & the overhead of the temp & its lack of indexes there & you haven't responded to multiple peoples requests re indexes. Why didn't you use a subquery for your temp table? Or if you have MySQL8--once again, version not given, per MCVE--you can try a CTE. Either might be able to use whatever indexes you have (including default ones). — philipxy, Oct 29 '18 at 21:25
@philipxy I have indexes on versions table, a key on dictionary_id, and the dictionary table has a index on four columns, product_id,language_id, key, and translation. The link about Understanding multiple table join with aggregation seems like a possible solution to my problem though, Thank you. — Ice76, Oct 29 '18 at 21:27
But you haven't given us your indexes (default & explict) so how do you expect us to help you? See my last comment which I was editing while you posted yours. [mcve] — philipxy, Oct 29 '18 at 21:28
Please excuse me, you have no aggregations so your problem with the first query (the faq I mentioned 2 days ago) is simpler than in the aggregate link that I gave today. A subquery instead of the temp might be enough but I suspect there is still a way to do the joins inline more simply & quickly. But there's no MCVE. PS What does "get all translation of one language_id and organizer_id and one version" mean & what does it add to your previous description of desired output? — philipxy, Oct 29 '18 at 22:03
@philipxy Apologize for you frustration, but your link did lead me to find a conclusion. Thank you for bearing it! — Ice76, Oct 29 '18 at 22:31
Thanks. I am not frustrated. Hoping you will next time create a MCVE before you consider asking & if that creation hasn't lead to your successful debugging then that you will give it in a question. — philipxy, Oct 29 '18 at 23:02

Ice76 · Accepted Answer · 2018-10-29T22:44:02.110

0

I managed to find a solution using this as a reference, thanks to @philipxy, pointing me to this answer for reference.

SELECT d.key, d2.key AS toKey, d.translation AS `1`, d2.translation AS `2`
FROM dictionary d 
INNER JOIN dictionary_versions dv ON dv.dictionary_id = d.id 
LEFT JOIN (
    SELECT d.* 
    FROM `dictionary` AS `d`
    INNER JOIN `dictionary_versions` AS `dv` ON d.id = dv.dictionary_id 
    WHERE d.product_id = 1 AND dv.version_id = 3 AND d.language_id = 2
) d2 ON d2.key = d.key 
WHERE d.product_id = 1 AND dv.version_id = 3 AND d.language_id = 1

This creates a subquery before joining it to the table itself. The where condition is applied to both tables, therefore getting the translations for one language_id, and matching any for a different language_id, if they exist.

edited Oct 29 '18 at 22:44

answered Oct 29 '18 at 22:30

Ice76

1,143
8
16

Happy for you. But the 1st & nested LEFTs should be INNER to not be misleading. If you think they must be LEFTs you are mistaken. As I already commented. – philipxy Oct 29 '18 at 22:41
@philipxy You are right, they should be INNER. The where condition covers what the LEFT would do – Ice76 Oct 29 '18 at 22:43
I guess you know what I'm talking about but your comment is phrased so unclearly it doesn't say anything, it just helps us maybe guess what you meant. If you force yourself to phrase things clearly using enough words, phrases & sentences then you will be able to see & reason & communicate & code more effectively. – philipxy Oct 29 '18 at 22:47

Join a joined table to itself

1 Answers1