WHERE command on UNION tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

WHERE command on UNION tables

Author

Topic

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-02-19 : 11:21:42

Hi there, I have some identical tables that I want to query for a search Is there anyway I can execute the unions first then a where command on all the tables at once I have tried using go but it doesn't seem to work, so I put the where statemtents at the end of each union for now. Here's my code:

strSQL = "SELECT * FROM england WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
		 "UNION ALL SELECT * FROM ni WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
		 "UNION ALL SELECT * FROM wales WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
		 "UNION ALL SELECT * FROM scotland WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%'"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 11:23:21

Try like this:

Select * from
(
select * from table1
union all
select * from table2
union all
...
) t
where <some-condition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-02-19 : 11:36:05

Jackpot! that works perfectly, many thanks Harsh!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-19 : 12:04:44

If the tables are large, it is probably better to keep the WHERE on each element of the UNION ALL.

CODO ERGO SUM

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-19 : 18:58:04

Personally I would turn this into one table and add a country column (ni, wales, scotland).
If you really had to, you could then create views for using the country column for ni, wales, scotland.

Bearing in mind what it looks like you are trying to do it might also be worth considering full text indexing.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 09:30:44

I hope someone doesn't enter
';GO;DROP TABLE England--

in the iKeyword textbox.

E 12°55'05.25"
N 56°04'39.16"

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 11:25:43

ok you win so how would I change

WHERE company LIKE '%" & iKeyword & "%'

what would be the correct syntax I have tried

[{@iKeyword}]

{@iKeyword}

but they don't work

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-11 : 11:38:10

WHERE company LIKE '%' + @Keyword + '%'

And that is SQL code, not VB or some other front end. You should really read this:

http://www.developer.com/db/article.php/3438221

and this:

http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 11:54:39

Thanks, I have had a quick skim over your articles but I will look at them in more depth later. I just tried:

WHERE company LIKE '%' + @Keyword + '%'

but I get:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/search/Default.asp, line 82

I'm using VB and ASP classic any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 12:05:04

Do you have parameters cleary defined in your VB/ASP?

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 12:11:46

No I haven't how would you go about that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 12:16:10

Have a look at this:-

http://www.macronimous.com/resources/stored_procedures_for_ASP_and_VB_Programmers.asp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-11 : 12:39:04

quote:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Wait ... Are you using MS Access, not SQL Server ??

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-11 : 12:41:21

quote:
Originally posted by visakh16

Have a look at this:-

http://www.macronimous.com/resources/stored_procedures_for_ASP_and_VB_Programmers.asp

That's only an "OK" article .. i appreciate what he is saying, but his example #3 is really, really bad -- he should be using ADO parameters, not concatenation.

- Jeff
http://weblogs.sqlteam.com/JeffS

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 13:07:13

Yep, Access

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-11 : 13:46:56

You should be posting question in the Access forum here, or on an Access website, this is a Microsoft SQL Server website. Access and SQL Server are completely different products, they have different SQL dialects and handle things like parameters and stored procedures and all that completely differently.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources