Arch Linux

skualito

Member

Registered: 2008-11-19

Posts: 203

Help with sqlite queries

Hello,

I'm looking for a way to make a first query on a table and then use one of the results as another query to get more precise data.

Use case : I have a table containing data on chinese medical herbs. Each herb is on a row, and has columns "ID", "Reference", "Chinese", "Pinyin", "Nature", "Actions", ...

I have a very simple script `qsql`

#!/bin/sh

SEARCHTERM="$1"
QUERYHERB="SELECT ReferenceSection, Pinyin, Chinese FROM MedicalTable WHERE Pinyin LIKE '%${SEARCHTERM}%';"

sqlite3 /home/pascal/Media/apps/dbSansImage.db <<EOF  
.headers on
.mode column
${QUERYHERB}

EOF

The ouput is like this :

% qsql gui    
ReferenceSection  Pinyin                Chinese             
----------------  --------------------  --------------------
1.1.2             Gui Zhi               桂枝                  
7.1.2             Rou Gui               肉桂                  
8.1.13            Mei Gui Hua           玫瑰花                 
17.3.1            Dang Gui              当归                  
17.4.12           Gui Ban               龟板                  
17.4.12.A.        Gui Ban Jiao          龟板胶                 

What I would like to do now is that the script allows me to select one of the results and display its "Nature" and "Actions" data.

I don't know where to look to find out about using the results of a query to make another query.

Anyone ?

EDIT : found this topic

Last edited by skualito (2017-12-16 12:32:24)

cpdevlist

Member

Registered: 2016-09-24

Posts: 4

Re: Help with sqlite queries

Sounds like you're looking for a subquery:

http://www.sqlitetutorial.net/sqlite-subquery/

Trilby

Inspector Parrot

Registered: 2011-11-29

Posts: 29,520

Website

Re: Help with sqlite queries

The first line of the post makes it sound like a subquery would be needed.  But upon reading the rest, that's not it at all.  The OP just wants to know how to make the script interactive.  There are countless ways to do this: skualito, what have you tried?  How do you want the interaction to work?  How do you want to "select" the item to get more information about?

If this is about the sql itself, the second query would simply be "SELECT Nature, Actions FROM MedicalTable WHERE Pinyin = $choice"

If this is instead about how to write shell scripts, there are countless resources out there.  Start here:
http://mywiki.wooledge.org/BashGuide

---

"UNIX is simple and coherent..." - Dennis Ritchie, "GNU's Not UNIX" -  Richard Stallman

skualito

Member

Registered: 2008-11-19

Posts: 203

Re: Help with sqlite queries

Yes, thank you both.

@Trilby, you're right, I want to make the script interactive, so I will take a look and learn how to write (ba)sh scripts !

I would like the script to do the following :

- Prompt for a keyword to search for (ex. zhi)
- display the columns ID, ReferenceSection, Pinyin, Chinese
- Prompt for ID number
- Display content of Action column for the ID selected
- Exit

EDIT 2 : What I'm missing is the logic, I don't ask for a copy-paste solution

Last edited by skualito (2017-12-20 15:36:15)