Sqlite select much complicated in bash

Question

1.00/5 (1 vote)

See more:

Hello friends, I wanted to ask a question about a SELECT statement in SQLite3. I have 4 tables structured as shown in the diagram,

|-------------------------|
|   TabAnaOra1            |
|-------------------------|
| Var1 | Var2 | Name      |
|-------------------------|
| 0.23 | 25.00| francesco |  
| 1.25 | 11.02| jhon      |
| 10   | 0.002| julia     |
|  ......

|-------------------------|
|   TabAnaOra2            |
|-------------------------|
| Var1 | Var2 | Name      |
|-------------------------|
| 0.77 | 35.00| francesco |  
| 0.25 | 41.02| jhon      |
| 70   | 8.002| julia     |
|  ......  

|   TabAnaOra3            |
|-------------------------|
| Var1 | Var2 | Name      |
|-------------------------|
| 0.03 | 28.00| rudolf    |  
| 9.25 | 15.02| jhon      |
| 100  | 0.102| julia     |
|  ......

|-------------------------|
|   TabAnaOra4            |
|-------------------------|
| Var1 | Var2 | Name      |
|-------------------------|
| 0.13 | 2.00 | francesco |  
| 125  |  1.02| jhon      |
| 10.22| 1.002| maria     |
|  ......

and I would like to populate a fifth table with the lowest and highest values as described in the diagram.

|----------------------------------------------------------|
|                  DataMix                                 |
|----------------------------------------------------------|
| Name      | Var1Min | Var1Max |    Tab1     |    Tab2    |
|----------------------------------------------------------|
| francesco | 0.13    | 0.77    | TabAnaOra3  | TabAnaOra2 |
| jhon      | 0.25    | 125     | TabAnaOra2  | TabAnaOra4 |
| julia     | 10      | 100     | TabAnaOra1  | TabAnaOra3 |
|  ......

I've tried to create a script , but it only works for the first two tables and only for the first 12 records.anyone can look if my select call is correct?

What I have tried:

Bash

#!/bin/bash

DB_PATH="ConfigDB.db"
TABLE1="TabAnaOra1"
TABLE2="TabAnaOra2"
TABLE3="TabAnaOra3"
TABLE4="TabAnaOra4"
MIX_TABLE="DataMix"

INSERT_MIX_TABLE="INSERT INTO $MIX_TABLE
    SELECT
        NULL,
        t1.Name,
        MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) AS  Var11,
        MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) AS  Var12,
        CASE
            WHEN t1. Var1 = MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE1'
            WHEN t2. Var1 = MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE2'
            WHEN t3. Var1 = MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE3'
            WHEN t4. Var1 = MIN(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE4'
        END AS Tab1,
        CASE
            WHEN t1. Var1 = MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE1'
            WHEN t2. Var1 = MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE2'
            WHEN t3. Var1 = MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE3'
            WHEN t4. Var1 = MAX(t1. Var1, t2. Var1, t3. Var1, t4. Var1) THEN '$TABLE4'
        END AS Tab2
    FROM
        $TABLE1 AS t1
        JOIN $TABLE2 AS t2 ON t1.Name = t2.Name
        JOIN $TABLE3 AS t3 ON t1.Name = t3.Name
        JOIN $TABLE4 AS t4 ON t1.Name = t4.Name
    GROUP BY
        t1.Name;"

# Esecuzione della query SQL
sqlite3 "$DB_PATH" "$INSERT_MIX_TABLE"

Posted 12-Feb-24 10:46am

GiulioRig

Add a Solution

Comments

Richard MacCutchan 13-Feb-24 3:36am

Ypou have not explained what the problem is.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2024-02-14T02:50:00

@_Asif_ 's solution will work with the data you provided although I would have used UNION instead of UNION ALL (the net effect is the same because there cannot be duplicates due to the tablename column).

But it's worth pointing out that if there are records in more than 1 of the tables that match the minimum value then you will get multiple rows for that name and value e.g. with

SQL

declare @TabAnaOra2 table (Var1 decimal(6,3), Var2 decimal(6,3), [Name] varchar(30));
insert into @TabAnaOra2 (Var1, Var2, [Name]) values
(0.77,35.00,'francesco')
,(0.25,41.02,'jhon')
,(70  ,8.002,'julia');

declare @TabAnaOra3 table (Var1 decimal(6,3), Var2 decimal(6,3), [Name] varchar(30));
insert into @TabAnaOra3 (Var1, Var2, [Name]) values
(0.03, 28.00,'rudolf')
,(0.25, 15.02,'jhon')
,(100 , 0.102,'julia');

you would get

Name		MinVar1	MaxVar1	TableName	TableName
francesco	0.130	0.770	TabAnaOra4	TabAnaOra2
jhon		0.250	125.000	TabAnaOra2	TabAnaOra4
jhon		0.250	125.000	TabAnaOra3	TabAnaOra4
julia		10.000	100.000	TabAnaOra1	TabAnaOra3
maria		10.220	10.220	TabAnaOra4	TabAnaOra4
rudolf		0.030	0.030	TabAnaOra3	TabAnaOra3

So you may want to consider a 2nd temporary table to resolve that issue e.g.

SQL

DECLARE @TmpTable2 TABLE
(
	Var1   decimal(6, 3),
	Var2   decimal(6, 3),
	Name	varchar(20),
	Tab1 varchar(50),
	Tab2 varchar(50)
);
INSERT INTO @TmpTable2
SELECT T.MinVar1, T.MaxVar1, T.[Name], T1.TableName as Tab1, T2.TableName as Tab2
FROM
(
	SELECT Name, min(var1) as MinVar1, max(var1) as MaxVar1
	FROM @TmpTable T
	GROUP BY Name
) T LEFT OUTER JOIN @TmpTable T1 ON T.Name = T1.Name AND T.MinVar1 = T1.Var1
	LEFT OUTER JOIN @TmpTable T2 ON T.Name = T2.Name AND T.MaxVar1 = T2.Var1


SELECT [Name], Min(Var1) as MinVar1, Max(Var1) as MaxVar1, Max(Tab1) as Tab1, Max(Tab2) as Tab2
from @TmpTable2
GROUP BY [Name];

The real question is why use such an awful database design - four tables, all with the same schema and all with similar data looks prime for a single table with an extra column to indicate source (for example). Far more extensible design and your queries will be a lot simpler too

_Asif_ · Answer 2 · 2024-02-13T22:35:00

You can use the below approach to achieve your desired result.

SQL

DECLARE @TabAnaOra1 TABLE
(
	Var1   decimal(6, 3),
	Var2   decimal(6, 3),
	Name	varchar(20)
);

DECLARE @TabAnaOra2 TABLE
(
	Var1   decimal(6, 3),
	Var2   decimal(6, 3),
	Name	varchar(20)
);

DECLARE @TabAnaOra3 TABLE
(
	Var1   decimal(6, 3),
	Var2   decimal(6, 3),
	Name	varchar(20)
);

DECLARE @TabAnaOra4 TABLE
(
	Var1   decimal(6, 3),
	Var2   decimal(6, 3),
	Name	varchar(20)
);

DECLARE @TmpTable TABLE
(
	Var1   decimal(6, 3),
	Var2   decimal(6, 3),
	Name	varchar(20),
	TableName varchar(50)
);

INSERT INTO @TabAnaOra1
SELECT 0.23, 25.00, 'francesco'
UNION ALL
SELECT 1.23, 11.02, 'jhon'
UNION ALL
SELECT 10, 0.002, 'julia';

INSERT INTO @TabAnaOra2
SELECT 0.770, 35.00, 'francesco'
UNION ALL
SELECT 0.25, 41.02, 'jhon'
UNION ALL
SELECT 70, 8.002, 'julia';

INSERT INTO @TabAnaOra3
SELECT 0.03, 28.00, 'rudolf'
UNION ALL
SELECT 9.25, 15.02, 'jhon'
UNION ALL
SELECT 100, 0.102, 'julia';

INSERT INTO @TabAnaOra4
SELECT 0.13, 2.00, 'francesco'
UNION ALL
SELECT 125, 1.02, 'jhon'
UNION ALL
SELECT 10.22, 1.002, 'maria';

INSERT INTO @TmpTable (Var1, Var2, Name, TableName)
select Var1, Var2, Name, 'TabAnaOra1' As TableName
FROM @TabAnaOra1 T1
UNION ALL
select Var1, Var2, Name, 'TabAnaOra2' As TableName
FROM @TabAnaOra2 T2
UNION ALL
select Var1, Var2, Name, 'TabAnaOra3' As TableName
FROM @TabAnaOra3 T3
UNION ALL
select Var1, Var2, Name, 'TabAnaOra4' As TableName
FROM @TabAnaOra4 T4

SELECT T.*, T1.TableName, T2.TableName
FROM
(
	SELECT Name, min(var1) as MinVar1, max(var1) as MaxVar1
	FROM @TmpTable T
	GROUP BY Name
) T LEFT OUTER JOIN @TmpTable T1 ON T.Name = T1.Name AND T.MinVar1 = T1.Var1
	LEFT OUTER JOIN @TmpTable T2 ON T.Name = T2.Name AND T.MaxVar1 = T2.Var1

Although it's a bit tricky and requires modifications in your bash script, but I am confident you can do it.

Sqlite select much complicated in bash

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0