Select columns from Two Tables along with 'Like' statement

Question

I'm writing a Search Functionality for my website and I need to scan two tables and select rows which contain a specific string that I send (using LIKE). I wrote this but it doesn't send back anything.

SqlCommand cmd = new SqlCommand("Select * From Table1, Table2 where Name LIKE '" + searchText + "%' OR Table2 where Name LIKE '" + searchText + "%'", con);

Basically get all rows from BOTH tables having rows similar to the string I send. Scan both table's columns for a specific string I send using 'LIKE'

COLUMN NAMES are different :)

Is this the proper way to do this?

Seems not a valid SQL. You try to use multiple table in your `WHERE` clause. Which columns do you think you will get at the end? On which table? — Soner Gönül, May 13 '15 at 07:59
Your SQL is invalid. Write the SQL correctly, test in SSMS and then put it over here — Raj, May 13 '15 at 08:00
assuming both table structure are same, I think what you are looking for is this `"Select * From Table1 WHERE Name LIKE '" + searchText + "%' UNION ALL Select * From Table2 where Name LIKE '" + searchText + "%'"` — ughai, May 13 '15 at 08:02
Please add some sample data and expected output. what you are doing is a cross join. not sure if that is what you want — ughai, May 13 '15 at 08:03
If both tables have the same structure, whats the point of having 2 tables? — Marco, May 13 '15 at 08:03
Exactly, Serv. Also: Please try to avoid naming columns "Name" or the like, that's bad practice. If you do use column names like "Name", "Date" etc, enclose them in brackets in sql queries: "... WHERE [Table1].[Name] LIKE...". — LocEngineer, May 13 '15 at 08:07
@serv - In a few scenarios like History table, there can be tables with similar structure. I am not sure in this case though — ughai, May 13 '15 at 08:12
@ughai history tables usually have a few more columns then source tables such as the date the record went to history and so on... — Zohar Peled, May 13 '15 at 08:17
@LocEngineer I couldn't agree more. In fact, just a few days ago I've wrote [this answer](http://stackoverflow.com/questions/30131409/is-there-a-way-to-not-use-square-brackets-in-sql-server/30132058#30132058) that specifies the method I use to avoid using reserved words and function names in sql. I admit, this is a bit of self promoting, but the main reason I've linked it is because I truly believe this method is the best naming convention for database objecs. — Zohar Peled, May 13 '15 at 08:29
@LocEngineer guys I added that name on purpose so that it's clear for the good lads on Stack :) The column names were properly named — Jay, May 13 '15 at 08:33
@ZoharPeled guys I added that name on purpose so that it's clear for the good lads on Stack :) The column names were properly named — Jay, May 13 '15 at 08:33

score 2 · Answer 1 · answered May 13 '15 at 08:02

2

If you confirm that both tables have the same columns, you can use this query;

Select * From Table1
where Name LIKE '" + searchText + "%' 
UNION ALL
Select * From Table2 
where Name LIKE '" + searchText + "%'

answered May 13 '15 at 08:02

Nadeem_MK

7,533
7
50
61

Thats exactly, what I thought OP needs. Without expected output, it's hard to confirm if this is correct or not. – ughai May 13 '15 at 08:05
Downvote reasons: 1: What if the tables column doesn't match? you will get an exception. 2: still letting little booby tables jump in... – Zohar Peled May 13 '15 at 08:08
Sorry the column names are different – Jay May 13 '15 at 08:20
@ZoharPeled I did mention if the columns are identical, right? Have you even read the answer before? – Nadeem_MK May 13 '15 at 08:25
@Nadeem_MK: Sorry, I don't know how I've missed that. However, little bobby tables is still a valid reason for a downvote in my eyes. – Zohar Peled May 13 '15 at 08:30

score 2 · Accepted Answer · edited May 23 '17 at 12:10

2

You have several problems in your code.

The first in severity is the fact that you use string concatenation instead of parameters.
This makes your code very vulnerable to SQL injection attacks.

The second one is that your SQL is simply wrong.
You are using an implicit join without any join condition.
This makes a cross join, but I'm not sure that this is what you want.
Always use explicit joins. i.e from t1 inner join t2 on(t1.id = t2.id).
Implicit joins are out of style for more then 20 years now.
Read this and that for some more information about the differences between implicit and explicit joins

I won't give you an SQL suggestion since it's not very clear what is the desired outcome, but you have to take the points I made into consideration, if you want to write a good code.

update
Based on your comments, you can probably do something like this:

declare @Name varchar(10)='as'

SELECT Table1.*, Table2.*
FROM (
    SELECT t1_Id As Id, 1 As TableNumber
    FROM Table1 
    WHERE t1_Name LIKE @Name+'%'

    UNION ALL

    SELECT t2_Id as Id, 2 As TableNumber
    FROM Table2 
    WHERE t2_Name LIKE @Name+'%'
) SearchResults
LEFT JOIN Table1 ON(t1_Id = Id AND TableNumber = 1)
LEFT JOIN Table2 ON(t2_Id = Id AND TableNumber = 2)

see sql fiddle here

edited May 23 '17 at 12:10

Community

1
1

answered May 13 '15 at 08:13

Zohar Peled

79,642
10
69
121

Thank you for the suggestions mate :) I want to get ALL data from both the tables (Column names could be different) IF they contain similar data to the string I send. I assume that is done using LIKE. – Jay May 13 '15 at 08:29
So if I send "John", the query should return "John Skully" from table1 and also "John Doolittle" from table2 :) And every other John for that matter. I just want to scan two tables at once and avoid two queries – Jay May 13 '15 at 08:30
Do you have anything in common between these 2 tables that you can use for a join? – Zohar Peled May 13 '15 at 08:34
Do they have columns with unique values (like an Id column)? – Zohar Peled May 13 '15 at 08:43
Yes they do. Both of them has a 'id' column :) – Jay May 13 '15 at 08:45

score -1 · Answer 3 · answered May 13 '15 at 08:02

-1

Try this

SqlCommand cmd = new SqlCommand("Select * From Table1 tbl1, Table2 tbl2 where tbl1.Name LIKE '" + searchText + "%' OR tbl2.Name LIKE '" + searchText + "%'", con);

answered May 13 '15 at 08:02

Mairaj Ahmad

14,434
2
26
40

This will give a [cross join](http://en.wikipedia.org/wiki/Join_%28SQL%29#Cross_join), so if there are 5 results from `Table1` and 5 results from `Table2` then 25 rows will be returned. This is almost certainly not the correct result. – TobyLL May 13 '15 at 08:06

Select columns from Two Tables along with 'Like' statement

3 Answers3