Hide rows in table1 if same value found in table2 ?

(1) By anonymous on 2021-11-08 02:39:43 [link] [source]

Hello.
I am using DB4S.

In table1 one I have rows like this:

ABC123 12345
ABC123 67890
ABC123 98765
ABC123 99999
DEF123 12345
DEF123 55555
XYZ123 01234

etc

I want to retain the data in the table rather than deleting it, but after processing it I want to hide it from view based on the value in col1.  So, I want to add ABC123 to table2 and by doing this it hides all ABC123 rows in table1 'browse data' view.  I can't use either column as primary key as both columns have instances of the same value.

Can anyone help me with the code for table1 "if value from table1 col1 found in table2 col1, don't show those rows in table1" (basically!) ?

Thank you.
Rgds.
Rob

(2.1) By Gunter Hick (gunter_hick) on 2021-11-08 07:56:16 edited from 2.0 in reply to 1 [link] [source]

If you can change the schema, add a column to store the "deleted" attribute, so you can SELECT ... WHERE NOT deleted.

Otherwise

SELECT ... WHERE NOT EXISTS (SELECT 1 FROM table2 where table2.field == table1.field);

Or

SELECT ... WHERE table1.field NOT IN (SELECT DISTINCT field FROM table2);

Edit: added DISTINCT to above SELECT

(3) By anonymous on 2021-11-08 09:18:06 in reply to 2.1 [link] [source]

Hi Gunter, many thanks.

Just to be clear as I'm a complete noob at this stuff, do you mean the schema of table2 or table1?

Table1 can't be changed as I import new data dailt in csv format as needs to match the table format exactly otherwise DB4S will allow the import.

And do I replace the word 'field' with column name?

To put it very simply, if I add a single column in table2 and put a row with ABC123 in the cell, I want all rows in table1 with ABC123 to disappear from view (the column names will match). Sorry for any confusion!

Thanks & rgds. Rob

(4) By Gunter Hick (gunter_hick) on 2021-11-08 10:04:39 in reply to 3 [link] [source]

Yes, you use your own table and column names. I suggest using something a little more descriptive than "table1" and "table2".

Currently you are limiting yourself to the schema of your data source (the csv file). This is keeping you from adopting the simple solution. I would suggest you do the following:

x) Keep a table (maybe call it import_data) that exactly matches the csv file.

x) Define a second table with a descriptive name that has the additional fields you may need, including the "deleted" field.

x) Transfer the imported data using your own dedicated "schema conversion" statement. This could probably be an INSERT INTO .. DELETE FROM import_data RETURNING <field list>;

x) Do all your programming against your own schema. Any changes in the csv file schema or your own schema are handled at exactly one point, the above schema conversion statement.

(5) By anonymous on 2021-11-08 13:11:11 in reply to 4 [source]

Thanks again Gunter. I'm sorry but all that has gone right over my head. Maybe I can try explaining it with pictures what I want to do ?

Please see https://imgur.com/a/d3nORtD.

The top pic is an example of my table1.

The middle pic is table2 and an example of what I want to do. By entering ABC123 and FGH000 I want those 5 rows (3x ABC123 and 2x FGH000) to be hidden in table1 view. The schema for both is TEXT for hex and callsign, date for date. No PK set. I can add additional stuff to table2 layout/schema if needed, but not to table1.

The 3rd pic is asking for what code I need in the box to make it happen.

I hope that is clearer?

Many thanks. Rob

(6) By Gunter Hick (gunter_hick) on 2021-11-08 15:38:18 in reply to 5 [link] [source]

I am not sure that you are on the right forum. AFAIK DB4S is maintained by somebody completely different than the SQLite library, which is maintained here. Questions about the operation of the browser should be directed to the DB4S maintainers.

(7) By Ryan Smith (cuz) on 2021-11-08 20:33:58 in reply to 5 [link] [source]

Gunter is right, this doesn't seem to be an SQLite problem.

However, DB4S is simply a DB admin tool, like a myriad others, and a very good one at that. It's function is to display the contents of a DB, edit it and to let you write queries. It isn't really a display tool to let you customize views (that I know of).

Having said that, if you create a VIEW in SQL with what you want to display, and then select that view (much like any other table), it will show what you require.

Off the top of my head, this should create the sort of view you would like:

CREATE VIEW table1_view AS
SELECT *
  FROM table1
 WHERE table1.HEX NOT IN (SELECT table2.HEX FROM table2)
;

Once you run that SQL successfully, a new object should appear where your list of tables are with that name (table1_view), selecting it will show only the lines that do not have corresponding entries in table2.

I did not test that since I don't have your tables, but it should work in principle.

PS: If ever you want to remove that new view object from your DB again, you can run this SQL:

DROP VIEW table1_view;

(8) By anonymous on 2021-11-09 04:21:48 in reply to 7 [link] [source]

Dear Ryan,

Thank you so much for this! That is exactly what I wanted. You've just made my job about 10x easier & faster. Virtual beers to you!

Krgds. Rob