DB Browser (SQLite) - view data out of different db's

(1) By Ninon (996699) on 2022-08-08 13:16:51 [link] [source]

Dear reader,

I am not sure if I am right here - but I try ;-)
I am using the "DB Browser (SQLite)" and I am struggling a bit...

I want to compare, rather display 2 columns form two different database sqlite files but I don't get it to work.
Do you have any hints for me?
I have opened db1 and attached db2, but I receive an error when I write an SQL statement regarding db2...

BR,
Ninon

(2) By Larry Brasfield (larrybr) on 2022-08-08 13:32:51 in reply to 1 [link] [source]

Show your SQL.

(3) By Chris Locke (chrisjlocke1) on 2022-08-08 15:08:47 in reply to 1 [link] [source]

This is the support forum for SQLite itself.

The support area for DB Browser for SQLite is here: https://github.com/sqlitebrowser/sqlitebrowser/issues

DB Browser for SQLite is not affiliated with SQLite at all - it just uses the SQLite library. For the best support, head to the above link.

In saying that, DB4S allows attaching multiple databases and the nightly version allows viewing multiple tables, so what you want to achieve can be done in the GUI - no SQL needed.

(4) By Ninon (996699) on 2022-08-09 09:36:16 in reply to 2 [link] [source]

Hi Larry,

I have two columns that I want to display next to each other in order to plot the containing data (either in a new table or in a new view).

data 1: column named "4p0" in Database "Mx" table "DATA" - containing 10 values

data 2: column named "4p0" in Database "Mz" table "DATA" - containing 10 values

when I execute this command:

SELECT "4p0" FROM DATA UNION SELECT "4p0" FROM Mz.DATA

I get one large column containing all 20 values now. Which command do I need to excute in order to get two columns in one view/table?

I can save and open an sql-file (via the buttons in "Execute SQL") - is there also a way to see the history which commands I have already entered in the command window?

BR, Ninon

(5) By Gunter Hick (gunter_hick) on 2022-08-09 10:33:34 in reply to 4 [link] [source]

The UNION operator concatenates two result sets, while eliminating duplicates. If you know that there are no duplicates (or don't care about them), use UNION ALL, which may be much faster.

What you are looking for is a JOIN. The naive

SELECT d1."4p0",d2."4p0" FROM Mx.DATA d1 JOIN Mz.DATA d2;

will show you the cartesian product (in your case all 100 rows of it) of each row from the LHS with every row from RHS. This is probably not what you want. You need to add a constraint to determine which LHS row should be displayed next to which RHS row.

Unfortunately you have not shared your schema, so it is not possible to determine how to do this.

ASSUMING you have a key field "k" common to both tables, you would use

SELECT d1."4p0",d2."4p0" FROM Mx.DATA d1 JOIN Mz.DATA d2 ON d1."k" = d2."k";

but this does not produce all rows unless the set of "k" values is identical in both tables. If the "k" value sets do not match, then you would need to add

UNION ALL
SELECT d1."4p0",NULL FROM Mx.DATA d1 WHERE NOT EXISTS (SELECT 1 FROM Mz.DATA WHERE k = d1.k
UNION ALL
SELECT NULL,d2."4p0" FROM Mz.DATA d2 WHERE NOT EXISTS (SELECT 1 FROM Mx.DATA WHERE k = d2.k

You are obfuscating your genius level schema for the purpose of asking an absoute beginner level question, right?

(6) By Ninon (996699) on 2022-08-09 11:03:28 in reply to 3 [link] [source]

Hi Chris, thank you very much for your answer! BR, Ninon

(7) By David Raymond (dvdraymond) on 2022-08-09 11:28:25 in reply to 5 [source]

Perhaps something like the below? Gives row numbers to every field in the 2 tables, and the (recently added) full outer join allows them to have different numbers of records and you'll still see all the values of the other table. Just add in some extra there if you actually care about ordering of the values.

select
Mx4p0,
Mz4p0
from
(
    select row_number() over () as compare_field,
    "4p0" as Mx4p0
    from Mx.DATA
) as a
full outer join
(
    select row_number() over () as compare_field,
    "4p0" as Mz4p0
    from Mz.DATA
) as b
using (compare_field)
;

(8) By Chris Locke (chrisjlocke1) on 2022-08-09 11:59:43 in reply to 4 [link] [source]

is there also a way to see the history which commands I have already entered

Click 'View' and 'SQL Log' (if its not already checked).

If it is checked, then you already have a 'SQL Log' window - click on that.

(9.2) By Chris Locke (chrisjlocke1) on 2022-08-09 12:08:44 edited from 9.1 in reply to 1 [link] [source]

So DB Browser for SQLite allows you to open two tables in two different panes, and following your other post, you can see the SQL log on the right.

imgur.com/Q1PAC0o

Depending on your main goal - eg, find duplicates, missing records, etc, you'd have to construct an SQL statement, but just the browsing records side of things can be done without any SQL.