0

I'm using SQLite 3.27.2-3 on Debian Buster/stable. I could use some other DB, but for my use case (LuaTeX), something with low overhead seems to make sense.

My use case is that I have a letter (in the sense of correspondence) which refers to other letters in the text. I have a table that is automatically built from references in the text.

Let's say the table looks like this

RowID           From     To
=====           ====     ==
2019.06.05       PC      SW
2019.06.11       PC      SB
2019.06.12
2019.06.15       SB      PC
2019.06.22       DO      SW

and that it corresponds to some rows from a larger table, which I'll call attachments.

And let's suppose there is an abbreviations table which looks like

Abbreviation     Name
============     ====
B                Bashful
D                Dopey
DO               Doc
H                Happy
G                Grumpy
PC               Prince Charming
SB               Sleeping Beauty
S                Sleepy
SN               Sneezy

Then I want a table (abbreviations) that has a list of the abbreviations that occur in my short list of correspondence. This would look like

Abbreviation     Name
============     ====
DO               Doc
PC               Prince Charming
SB               Sleeping Beauty
SW               Snow White

Something like the query below works, approximately. But I also get blank output for when it doesn't match. I'm getting errors from SQLite (or LuaSQL, to be precise) so I'm wondering if it is because of the blank output.

Better (and simpler) approaches appreciated.

SELECT abbreviations.Abbreviation,
       abbreviations.Name
FROM attachments
LEFT OUTER JOIN abbreviations ON attachments.`From`=abbreviations.Abbreviation
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22')
UNION
SELECT abbreviations.Abbreviation,
       abbreviations.Name
FROM attachments
LEFT OUTER JOIN abbreviations ON attachments.`To`=abbreviations.Abbreviation
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22');
  • Please offer suggestions for a better title. I don't think mine is particularly good. – Faheem Mitha Jul 9 at 8:11
1
SELECT DISTINCT abbreviations.*
FROM attachments
JOIN abbreviations ON abbreviations.Abbreviation IN (attachments.`To`, attachments.`From`)
WHERE attachments.RowID IN ('2019.06.05',
                            '2019.06.11',
                            '2019.06.12',
                            '2019.06.15',
                            '2019.06.22');

?

  • Thank you for the answer. This appears to work - at least it gives the right answer for the example on hand. Can you elaborate a bit on the answer. I'm not familiar with the syntax used. And what kind of JOIN is this? – Faheem Mitha Jul 9 at 10:29
  • 1
    @FaheemMitha 1) You use left outer join. But you need only abbrevs which exists in both tables - so inner join needed. 2) You use union - 1st query joins by eq to from and 2nd by eq to to. I use one join by eq to from or to (a IN (b,c) eq a=b OR a=c). – Akina Jul 9 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.