Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.


4011 posts

Uber Geek
+1 received by user: 523

Trusted

Topic # 125713 17-Jul-2013 08:08
Send private message

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

NextPVR: 
Gigabyte AMD A8 Brix --> Samsung LA46A650D via HDMI, NextPVR,
OpenHAB: ODroid C2 eMMC DriveOpenHAB with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave and Bluetooth LE Sensors
Media:Chromecast v2, ATV4, Roku3, HDHomeRun Dual
Windows 2012 
Host (Plex Server/Crashplan): 2x2TB, 2x3TB, 1x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 1xW2k8, 2xUbuntu 16.04 LTS, Crashplan, NextPVR channel for Plex,NextPVR Metadata Agent and Scanner for Plex


Create new topic
2695 posts

Uber Geek
+1 received by user: 529


  Reply # 857903 17-Jul-2013 09:48
Send private message

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

2695 posts

Uber Geek
+1 received by user: 529


  Reply # 857905 17-Jul-2013 09:49
Send private message

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

1221 posts

Uber Geek
+1 received by user: 123


  Reply # 857918 17-Jul-2013 10:09
Send private message

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

 




4011 posts

Uber Geek
+1 received by user: 523

Trusted

  Reply # 857922 17-Jul-2013 10:17
Send private message

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....




Previously known as psycik

NextPVR: 
Gigabyte AMD A8 Brix --> Samsung LA46A650D via HDMI, NextPVR,
OpenHAB: ODroid C2 eMMC DriveOpenHAB with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave and Bluetooth LE Sensors
Media:Chromecast v2, ATV4, Roku3, HDHomeRun Dual
Windows 2012 
Host (Plex Server/Crashplan): 2x2TB, 2x3TB, 1x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 1xW2k8, 2xUbuntu 16.04 LTS, Crashplan, NextPVR channel for Plex,NextPVR Metadata Agent and Scanner for Plex


444 posts

Ultimate Geek
+1 received by user: 76


  Reply # 857953 17-Jul-2013 11:05
Send private message

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.

1608 posts

Uber Geek
+1 received by user: 266

Subscriber

  Reply # 858055 17-Jul-2013 14:00
Send private message

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



4011 posts

Uber Geek
+1 received by user: 523

Trusted

  Reply # 858060 17-Jul-2013 14:09
Send private message

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.






Previously known as psycik

NextPVR: 
Gigabyte AMD A8 Brix --> Samsung LA46A650D via HDMI, NextPVR,
OpenHAB: ODroid C2 eMMC DriveOpenHAB with Aeotech ZWave Controller, Raspberry PI, Wemos D1 Mini, Zwave and Bluetooth LE Sensors
Media:Chromecast v2, ATV4, Roku3, HDHomeRun Dual
Windows 2012 
Host (Plex Server/Crashplan): 2x2TB, 2x3TB, 1x4TB using DriveBender, Samsung 850 evo 512 GB SSD, Hyper-V Server with 1xW10, 1xW2k8, 2xUbuntu 16.04 LTS, Crashplan, NextPVR channel for Plex,NextPVR Metadata Agent and Scanner for Plex


Infrastructure Geek
4056 posts

Uber Geek
+1 received by user: 195

Trusted
Microsoft NZ
Subscriber

  Reply # 858194 17-Jul-2013 17:21
Send private message

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




Technical Evangelist
Microsoft NZ
about.me/nzregs
Twitter: @nzregs


1221 posts

Uber Geek
+1 received by user: 123


  Reply # 858234 17-Jul-2013 18:21
Send private message

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.




Software Engineer

 


Create new topic

Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.