473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Select from multiple tables

Hi guys,

I have four table with the same structure (id, var1, var2)

Table 1 in var1 have only one word (150000+ records)
Table 2 in var1 have two words (50000+ records)
Table 3 in var1 have three words (50000+ records)
Table 4 in var1 have four or more words (30000+ records)

What is the best way to search them?

I do search with UNION as following:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM (
  2.     (SELECT * FROM table1 WHERE var1 LIKE '".$searchWord."' ORDER BY var1)
  3.     UNION
  4.     (SELECT * FROM table2 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
  5.     UNION
  6.     (SELECT * FROM table3 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
  7.     UNION
  8.     (SELECT * FROM table4 WHERE var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."' ORDER BY var1)
  9.     ) AS WHOLEDATABASE ORDER BY var1
  10. ");
  11.  
I do this becouse first I want to show the results with one word, then two, then three, and others.

Is there any other way to do this becouse this way is very slow.
Apr 29 '12 #1
8 2372
Luuk
1,047 Expert 1GB
I tihnk this:
Expand|Select|Wrap|Line Numbers
  1. var1 LIKE '".$searchWord." %'
is a subset of this:
Expand|Select|Wrap|Line Numbers
  1. var1 LIKE '% ".$searchWord." %'
I do this becouse first I want to show the results with one word, then two, then three, and others.
This is not guaranteed to work this way.. ;)

If you really want the data from Table1 first you should do something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM (
  2. SELECT 1, t1.* FROM table1 t1 WHERE t1.var1 LIKE '".$searchWord."' 
  3. UNION
  4. SELECT 2, t2.* FROM table2 t2 WHERE t2.var1 LIKE '".$searchWord."' 
  5. .....
  6. )  ORDER BY 1, var1
  7.  
The ORDER BY in the SELECTs is also slowing down your query, the only place you need it (here) is at the end of your query..
Apr 29 '12 #2
I don't want to use
Expand|Select|Wrap|Line Numbers
  1. var1 LIKE '% ".$searchWord." %'
becouse I want to show only records that contains my search word at beginning of the record.
Apr 30 '12 #3
What is a difference between your code and mine?
Apr 30 '12 #4
Rabbit
12,516 Expert Mod 8TB
You have no other way of doing it. You should think about normalizing your data. Why are there different tables? It should just be one table with all the data. You should also put on indexes if you haven't already done so.
Apr 30 '12 #5
I've been thinking about that. I'm not sure how to do that. Can you please help me?
May 3 '12 #6
Luuk
1,047 Expert 1GB
I've been thinking about that. I'm not sure how to do that
Rabbit suggested two things, which of the two where you thinking about, or do not know how to do?

Also a question was asked ("Why are there different tables?"). I think this is a hind to the first item Rabbit suggested...

In my earlier reply i said that:
var1 LIKE '".$searchWord." %'
is a subset of:
var1 LIKE '% ".$searchWord." %'

Therefor it makes not much sence to do:
var1 LIKE '".$searchWord." %' OR var1 LIKE '% ".$searchWord." %' OR var1 LIKE '% ".$searchWord."'

Because this would give the same result as:
var1 LIKE '% ".$searchWord." %'
which you said you did not want to do.....
May 3 '12 #7
But I need results where my word is on beginnig of the string or in the middle with space before it.

In example if I search for test, I want next records:
test
test drive
dope test


I don't want to display: testing
May 8 '12 #8
Rabbit
12,516 Expert Mod 8TB
Did you normalize your data? Did you put indexes on the fields?

An additional optimization you can do is to convert the OR conditions into a UNION ALL query.
May 8 '12 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: jeff brubaker | last post by:
Hello, Currently we have a database, and it is our desire for it to be able to store millions of records. The data in the table can be divided up by client, and it stores nothing but about 7...
1
by: TeleTech1212 | last post by:
I am trying to select specific columns from multiple tables based on a common identifier found in each table. For example, the three tables: PUBACC_AC PUBACC_AM PUBACC_AN each have a...
1
by: Ahmet Karaca | last post by:
Hi. myds.Reset(); mycommand.SelectCommand.CommandText= "Select att1 from Ing as Ingredient, Pro as Product "+ "where Pro.ad='apple' and Pro.id=Ing.id"; mycommand.Fill(myds, "Product"); // Here...
8
by: Jason L James | last post by:
Hi all, does anyone know if I can create a dataview from multiple datatables. My dataset is constructed from four separate tables and then the relationships are added that link the tables...
5
by: Craig G | last post by:
i was told that its possible to load more than 1 datatable into a dataset using a stored procedure i need to fill 3 combo's on my form if i had a SQL Stored Proc that had 3 different select...
13
by: ricky.agrawal | last post by:
I'm really not sure how to go about this in Access. What I've created is a table for each location. Those tables are identical in format but different in information. The tables are named after...
2
by: chopin | last post by:
I am using Microsoft Access, and VBA. I was wondering if it was possible to select multiple tables using DAO. For example, here is the code I am thinking should work, but doesn't: sSQL =...
4
by: dreaken667 | last post by:
I have a MySQL database containing 16 tables of data. Each table has a different number of columns and there are few common field names accross tables. I do have one master table with which I connect...
2
by: danorourke99 | last post by:
Hi, In my Access database I have a table (dbo_000_DataCubeProcessing) which contains a list of tables that I need to export to Excel on a regular basis along with a checkbox for each. Once I...
3
by: DeanL | last post by:
Hi guys, Does anyone know of a way to create multiple tables using information stored in one table? I have a table with 4 columns (TableName, ColumnName, DataType, DataSize) and wanted to know...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.