SQL querying two tables lots of columns

Question

I have two tables which have all the same columns but different data. They represent two different days. The tables are D1Table and D2Table. The columns are author1, author2, author3, author4, author 5, position1 - position5, title and biography- biography5. In the query I am trying to get the columns that match the text typed into a text box by a user (so they can do a search on both days).

What I have so far works fine on the first table but I really want to search both the first and second table, I just don't know how to, I have tried using join and union but I haven't had much luck I just get errors obviously I am doing something wrong. Also the query I am using is really long as you can see, I am sure there must be a better way to do this:

txt = "SELECT * from D1Table WHERE Synopsis LIKE '%" + txtBText + 
      "%' OR  Author1 LIKE '%" + txtBText + "%' OR Author2 LIKE '%" + txtBText + 
      "%' OR Author3 LIKE '%" + txtBText + "%' OR Author4 LIKE '%" + txtBText + 
      "%'   OR Author5 LIKE '%" + txtBText + "%' OR Biography1 LIKE '%" + txtBText + 
      "%' OR Biography2 LIKE '%" + txtBText + "%' OR Biography3 LIKE '%" + txtBText + 
      "%' OR Biography4 LIKE '%" + txtBText + "%' OR Biography5 LIKE '%" + txtBText + 
      "%' OR Title LIKE '%" + txtBText + "%'  OR Position1 LIKE '%" + txtBText + 
      "%'OR Position2 LIKE '%" + txtBText + "%' OR Position3 LIKE '%" + txtBText + 
      "%' OR Position4 LIKE '%" + txtBText + "%' OR Position5 LIKE '%" + txtBText + "%' "; 

Now I know this is pretty awful and as you can probably tell I am rubbish with SQL queries (I am just starting to learn them). I have been looking all over the internet for some time trying to learn the syntax but this is as far as I have got, so I thought it was probably about time I asked for help. If anyone can give me some guidance it would be much appreciated.

Answer 1

You can use UNION ALL and run two queries to get the result... As shown by RBarryYoung

SELECT COLUMN1, COLUMN2 FROM TABLE1 WHERE COLUMN1 LIKE '% Variable %' OR COLUMN2 LIKE '% Variable %'
UNION ALL
SELECT COLUMN1, COLUMN2 FROM TABLE2 WHERE COLUMN1 LIKE '% Variable %' OR COLUMN2 LIKE '% Variable %'

Answer 2

If you're able to, a redesign of the tables would really help. Depending on how many properties there are, and reasons for partitioning the days into different tables, you could have a single table called DayData which has columns Date (maybe stored as an int or using SQL 2008 R2's DATE data type) Field Value

If position matters (eg author1 is somehow treated differently from author2) then you could also have an index field (type integer). It depends on whether you always have exactly 5 values of each type, or if you may have 0, 1 or N, and whether or not the types can change over time.

Anyway, if you're stuck with what you've got, you could try full text search, or if that's a bridge too far, try

"SELECT * from (select * from D1Table UNION D2Table) D1D2
WHERE Synopsis LIKE '%" + txtBText + "%' OR Author1 LIKE '%" + txtBText + "%'
OR Author2 LIKE '%" + txtBText + "%' OR Author3 LIKE '%" + txtBText + "%'
OR Author4 LIKE '%" + txtBText + "%' OR Author5 LIKE '%" + txtBText + "%'
OR Biography1 LIKE '%" + txtBText + "%' OR Biography2 LIKE '%" + txtBText + "%'
OR Biography3 LIKE '%" + txtBText + "%' OR Biography4 LIKE '%" + txtBText + "%' 
OR Biography5 LIKE '%" + txtBText + "%' OR Title LIKE '%" + txtBText + "%' 
OR Position1 LIKE '%" + txtBText + "%'OR Position2 LIKE '%" + txtBText + "%' 
OR Position3 LIKE '%" + txtBText + "%' OR Position4 LIKE '%" + txtBText + "%' 
OR Position5 LIKE '%" + txtBText + "%' "; 

Note the derived table called D1D2: (select * from D1Table UNION D2Table)

However, you have another problem - building the statement like you are is vulnerable to a SQL injection attack (search this site or Google). Someone could put

'--delete * from D1Table; 'select * from D1Table where ''1=

and delete the data from your D1Table. Major websites get hacked using similar techniques - rather than deleting data the queries are manipulated to generate error messages or deliberate timing delays, leaking information about the database design and usernames, passwords, etc.

Hope that helps :)

Answer 3

While I basically agree with the other answers that I have seen here, I am going to take a different tack with my answer:

If you are trying to learn SQL, then it is imperative that you discipline yourself to use better formmating. This may seem petty, but in truth you will find it easy to read, understand and especially edit if you use more understandable formatting.

And while I really miss c/c++ type block-quotes in c#, decades of VB programming have acclimated me to the somewhat ugly requirements of this without them. Compare your example:

txt = "SELECT * from D1Table WHERE Synopsis LIKE '%" + txtBText + "%' OR Author1 LIKE '%" + txtBText + "%' OR Author2 LIKE '%" + txtBText + "%' OR Author3 LIKE '%" + txtBText + "%' OR Author4 LIKE '%" + txtBText + "%' OR Author5 LIKE '%" + txtBText + "%' OR Biography1 LIKE '%" + txtBText + "%' OR Biography2 LIKE '%" + txtBText + "%' OR Biography3 LIKE '%" + txtBText + "%' OR Biography4 LIKE '%" + txtBText + "%' OR Biography5 LIKE '%" + txtBText + "%' OR Title LIKE '%" + txtBText + "%' OR Position1 LIKE '%" + txtBText + "%'OR Position2 LIKE '%" + txtBText + "%' OR Position3 LIKE '%" + txtBText + "%' OR Position4 LIKE '%" + txtBText + "%' OR Position5 LIKE '%" + txtBText + "%' ";

to its equivalent:

txt = ""
+ "  SELECT  * "
+ "  from    D1Table "
+ "  WHERE   Synopsis    LIKE '%" + txtBText + "%' "
+ "      OR  Author1     LIKE '%" + txtBText + "%' "
+ "      OR  Author2     LIKE '%" + txtBText + "%' "
+ "      OR  Author3     LIKE '%" + txtBText + "%' "
+ "      OR  Author4     LIKE '%" + txtBText + "%' "
+ "      OR  Author5     LIKE '%" + txtBText + "%' "
+ "      OR  Biography1  LIKE '%" + txtBText + "%' "
+ "      OR  Biography2  LIKE '%" + txtBText + "%' "
+ "      OR  Biography3  LIKE '%" + txtBText + "%' "
+ "      OR  Biography4  LIKE '%" + txtBText + "%' "
+ "      OR  Biography5  LIKE '%" + txtBText + "%' "
+ "      OR  Title       LIKE '%" + txtBText + "%' "
+ "      OR  Position1   LIKE '%" + txtBText + "%' "
+ "      OR  Position2   LIKE '%" + txtBText + "%' "
+ "      OR  Position3   LIKE '%" + txtBText + "%' "
+ "      OR  Position4   LIKE '%" + txtBText + "%' "
+ "      OR  Position5   LIKE '%" + txtBText + "%' "
;

Clearly easier to read and easier to understand. What's not immediately apparent is that its also easier to edit. Consider that you need to check two tables by adding a UNION:

txt = ""
// <CUT from here
+ "  SELECT  * "
+ "  from    D1Table "
+ "  WHERE   Synopsis    LIKE '%" + txtBText + "%' "
+ "      OR  Author1     LIKE '%" + txtBText + "%' "
+ "      OR  Author2     LIKE '%" + txtBText + "%' "
+ "      OR  Author3     LIKE '%" + txtBText + "%' "
+ "      OR  Author4     LIKE '%" + txtBText + "%' "
+ "      OR  Author5     LIKE '%" + txtBText + "%' "
+ "      OR  Biography1  LIKE '%" + txtBText + "%' "
+ "      OR  Biography2  LIKE '%" + txtBText + "%' "
+ "      OR  Biography3  LIKE '%" + txtBText + "%' "
+ "      OR  Biography4  LIKE '%" + txtBText + "%' "
+ "      OR  Biography5  LIKE '%" + txtBText + "%' "
+ "      OR  Title       LIKE '%" + txtBText + "%' "
+ "      OR  Position1   LIKE '%" + txtBText + "%' "
+ "      OR  Position2   LIKE '%" + txtBText + "%' "
+ "      OR  Position3   LIKE '%" + txtBText + "%' "
+ "      OR  Position4   LIKE '%" + txtBText + "%' "
+ "      OR  Position5   LIKE '%" + txtBText + "%' "
// CUT to here>
// VV add a UNION
+ " UNION ALL "
// <PASTE here
+ "  SELECT  + "
+ "  from    D2Table "  // <-- change the table name here
+ "  WHERE   Synopsis    LIKE '%" + txtBText + "%' "
+ "      OR  Author1     LIKE '%" + txtBText + "%' "
+ "      OR  Author2     LIKE '%" + txtBText + "%' "
+ "      OR  Author3     LIKE '%" + txtBText + "%' "
+ "      OR  Author4     LIKE '%" + txtBText + "%' "
+ "      OR  Author5     LIKE '%" + txtBText + "%' "
+ "      OR  Biography1  LIKE '%" + txtBText + "%' "
+ "      OR  Biography2  LIKE '%" + txtBText + "%' "
+ "      OR  Biography3  LIKE '%" + txtBText + "%' "
+ "      OR  Biography4  LIKE '%" + txtBText + "%' "
+ "      OR  Biography5  LIKE '%" + txtBText + "%' "
+ "      OR  Title       LIKE '%" + txtBText + "%' "
+ "      OR  Position1   LIKE '%" + txtBText + "%' "
+ "      OR  Position2   LIKE '%" + txtBText + "%' "
+ "      OR  Position3   LIKE '%" + txtBText + "%' "
+ "      OR  Position4   LIKE '%" + txtBText + "%' "
+ "      OR  Position5   LIKE '%" + txtBText + "%' "
// ^^ change these column names if needed
;

What's left out of here is that for a UNION, the SELECT's must return the same number of columns and the data-types of the columns must be compatible. So if D1Table and D2Table have different columns, then you'll have to remove the "*" and make compatible column-lists for each SELECT.

Answer 4

My first reaction would be why you need such an approach, may be you should explain your needs better. But in any case you better look into full text search, just google it.

Here are some useful links:

http://en.wikipedia.org/wiki/Full_text_search

In text retrieval, full text search refers to techniques for searching a single computer-stored document or a collection in a full text database. Full text search is distinguished from searches based on metadata or on parts of the original texts represented in databases (such as titles, abstracts, selected sections or bibliographical references).

In a full text search, the search engine examines all of the words in every stored document as it tries to match search criteria (e.g., words supplied by a user). Full text searching techniques became common in online bibliographic databases in the 1990s[verification needed]. Many web sites and application programs (such as word processing software) provide full-text search capabilities. Some web search engines such as AltaVista employ full text search techniques while others index only a portion of the web pages examined by its indexing system.[1]

http://msdn.microsoft.com/en-us/library/ms142571.aspx Some quotes from the links above: Comparing LIKE to Full-Text Search

In contrast to full-text search, the LIKE Transact-SQL predicate works on character patterns only. Also, you cannot use the LIKE predicate to query formatted binary data. Furthermore, a LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.