Full database search queries

Forums › IT Pro and developers › Full database search queries

davidcole

6037 posts

Uber Geek

Trusted

#125713 17-Jul-2013 08:08

If I had a bunch of tables in a database that all have different columns names eg

Books
ID, Title, Author
1, Green Man, Frank
2, Contact, Carl Sagan

Cars
ID, Make, Model, Colour
3,Ford,Laser,Green
4,Holden, Commodore, Blue

Companies
ID, Name, Address, EmployeeCount
5,Green Acres, 10 House St, 20
6,McDonalds, Featherston St, 50

How could I return the ID of rows where any of the columns is Green (assuming that 'Green' is used as a colour of a car, and 'Green' is part of a name of a company and a book

So I'd expect to returns IDs, 1, 3 and 5.

Ideally a SQL lite based sql query - but I'm assuming something like this would be cross DB and I'm not sure if you'd use the FTS extentions for this or not.

Previously known as psycik

Home Assistant: Gigabyte AMD A8 Brix, Home Assistant with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave, Shelly Humidity and Temperature sensors
Media:Chromecast v2, ATV4 4k, ATV4, HDHomeRun Dual
Server Host Plex Server 3x3TB, 4x4TB using MergerFS, Samsung 850 evo 512 GB SSD, Proxmox Server with 1xW10, 2xUbuntu 22.04 LTS, Backblaze Backups, usenetprime.com fastmail.com Sharesies Trakt.TV Sharesight

robjg63

4100 posts

Uber Geek

Subscriber

#857903 17-Jul-2013 09:48

Whew - Its been a while since I looked at databases...
But my guess is that you would need to select the tabname and ID into a temp table for each of the 3 tables you are searching - and you would have to say select ID from tabname where col1 = 'green' or col2 = 'green'...etc
and repeat for each table.

Hmm - then I found this - I think its sqlserver though - its a procedure to do roughly what I suggested above:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Nothing is impossible for the man who doesn't have to do it himself - A. H. Weiler

robjg63

4100 posts

Uber Geek

Subscriber

#857905 17-Jul-2013 09:49

This?
http://stackoverflow.com/questions/13514509/search-sqlite-database-all-tables-and-columns

Its using python to query the database though...

Nothing is impossible for the man who doesn't have to do it himself - A. H. Weiler

TwoSeven

1626 posts

Uber Geek

Subscriber

#857918 17-Jul-2013 10:09

Try something along the lines of the following (this is just a guess using T-SQL):

Select car.id, book.id, company.id FROM
(SELECT id FROM cars WHERE model like '%green%') as car,
(SELECT id FROM books WHERE colour like '%green%) as book,
(SELECT id FROM companies WHERE name like '%green%) as company

This will give you a result set but will contain duplicate values. In this example you will be able to determine which table the ID came from.

Personally I would probably just create a table function with three separate queries and output the result of a list of IDs.

One notes that there does not appear to be a logical relationship between any of the tables so one would question why one query is used.

Software Engineer
(the practice of real science, engineering and management)
A.I. (Automation rebranded)
Gender Neutral
(a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

...they/their/them...

davidcole

6037 posts

Uber Geek

Trusted

#857922 17-Jul-2013 10:17

TwoSeven: Try something along the lines of the following (this is just a guess using T-SQL):

Select car.id, book.id, company.id FROM
(SELECT id FROM cars WHERE model like '%green%') as car,
(SELECT id FROM books WHERE colour like '%green%) as book,
(SELECT id FROM companies WHERE name like '%green%) as company

This will give you a result set but will contain duplicate values. In this example you will be able to determine which table the ID came from.

Personally I would probably just create a table function with three separate queries and output the result of a list of IDs.

One notes that there does not appear to be a logical relationship between any of the tables so one would question why one query is used.

I guess the only problem with this solution is if you added a table or a column you'd have to update the code....

nzkc

1572 posts

Uber Geek

#857953 17-Jul-2013 11:05

This is a problem that SQL is bad at. Yes you can do it, but performance will suffer. What you're wanting is a full text query engine (e.g. Full Text Search in SQL Server, Lucerne, Solar etc).

That said, since you've said you're using SQLite I'm assuming its a small DB you can probably get away with sending lots of queries to each table.

itxtme

2102 posts

Uber Geek

#858055 17-Jul-2013 14:00

Why not use a UNION ?

It is a text search like stated above, you can union all tables that could ever have it in. But otherwise its normal practice to re-write querys when you change database structure

davidcole

6037 posts

Uber Geek

Trusted

#858060 17-Jul-2013 14:09

itxtme: Why not use a UNION ?

It is a text search like stated above, you can union all tables that could ever have it in. But otherwise its normal practice to re-write querys when you change database structure

You can dynamically union all the tables fairly easily enough, but how do you dyamically add all the where clauses for each field?

Also the example isnt great, but in this situation an new table can be added at any time to add new metadata about an object.

I'm tempted to probably do something like the python example that iterates the tables and columns.

Quic Broadband

Move to New Zealand's best fibre broadband service (affiliate link). Free setup code: R587125ERQ6VE. Note that to use Quic Broadband you must be comfortable with configuring your own router.

Regs

4066 posts

Uber Geek

Trusted

Snowflake

#858194 17-Jul-2013 17:21

What DBMS? SQL Server gives you queryable table and column definition system tables and a cursor could be used to cycle through all tables/columns in a database looking for a value.

I couldnt find the sql snippet i wrote a while back, so i googled one up for you:

http://stackoverflow.com/questions/709120/how-to-search-for-one-value-in-any-column-of-any-table-inside-one-ms-sql-databas

Sales Engineer
Snowflake
www.snowflake.com

about.me/nzregs
Twitter: @nzregs

TwoSeven

1626 posts

Uber Geek

Subscriber

#858234 17-Jul-2013 18:21

davidcole:
TwoSeven: Try something along the lines of the following (this is just a guess using T-SQL):

Select car.id, book.id, company.id FROM
(SELECT id FROM cars WHERE model like '%green%') as car,
(SELECT id FROM books WHERE colour like '%green%) as book,
(SELECT id FROM companies WHERE name like '%green%) as company

This will give you a result set but will contain duplicate values. In this example you will be able to determine which table the ID came from.

Personally I would probably just create a table function with three separate queries and output the result of a list of IDs.

One notes that there does not appear to be a logical relationship between any of the tables so one would question why one query is used.

I guess the only problem with this solution is if you added a table or a column you'd have to update the code....

In that case I wouldn't use a relational database, I'd probably use an OODB or (if a SQL is required) an ORM layer.

An example of the latter is using .Net LINQ and reflection, one can simply open a connection to the persistence layer (in this case database), bring back an entity set (collection of tables) and then make the query for each entity returned. In Microsoft terms this is called Generics.

edit: As mentioned in another post +1 for union I suspect.

...they/their/them...