SELECT from two tables using LIKE

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

General SQL Server Forums

New to SQL Server Programming

SELECT from two tables using LIKE

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-16 : 14:53:44

I'm using MS SQL 2005 server with two tables:

table 1
-------

code code_description sub_category notes
SH Shirt (Made in USA) x 20
HA Hat (Factory) x 36
SH Shirt (Made in China) x 24

table 2
-------

code code_description retail
---- ---------------- ------
TR Trousers 15.00
SH Shirt 10.95

I wish to be able to SELECT from both tables where code is like %S% and give the result:

SH Shirt
SH Shirt (Made in USA) x 20
SH Shirt (Made in China) x 24

How can I do this please?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-16 : 15:43:23

You can use union.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-16 : 23:55:51

select code, code_description from table1 where code like 'S%'
union all
select code, code_description from table2 where code like 'S%'

don't use '%S%' unless you really need it. 'S%' matches anything that starts with S. '%S%' matches anything with an S anywhere in the string, so it can't use any indexes you may have.

elsasoft.org

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-17 : 05:20:01

quote:
Originally posted by jezemine

select code, code_description from table1 where code like 'S%'
union all
select code, code_description from table2 where code like 'S%'

don't use '%S%' unless you really need it. 'S%' matches anything that starts with S. '%S%' matches anything with an S anywhere in the string, so it can't use any indexes you may have.

elsasoft.org

Thanks for the example. However, SQL Server complained:
'All queries combined using a UNION, INTERSECT or EXCEPT must have an equal number of expressions in their target lists'

In my case code and code_description ar ethe same in both tables, but table 1 also has the sub_category column.

How can I restructure my query to get the result I want please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 05:29:14

Can you post the query you used?

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-17 : 06:12:44

Sure, I used:

SELECT code, code_description
FROM table 2
WHERE (code LIKE 's%')
UNION ALL
SELECT code, code_description, sub_category
FROM table 1
WHERE (code LIKE 's%')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 06:40:32

Are you using SQL Server 2000 or SQL Server 2005?

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

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-17 : 07:08:24

SQL Server 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 07:19:28

[code]DECLARE @Table1 TABLE (Code VARCHAR(2), CodeDescription VARCHAR(5), SubCategory VARCHAR(200), Notes VARCHAR(10))

INSERT @Table1
SELECT 'SH', 'Shirt', '(Made in USA)', 'x 20' UNION ALL
SELECT 'HA', 'Hat', '(Factory)', 'x 36' UNION ALL
SELECT 'SH', 'Shirt', '(Made in China)', 'x 24'

DECLARE @Table2 TABLE (Code VARCHAR(2), CodeDescription VARCHAR(15), Retail MONEY)

INSERT @Table2
SELECT 'TR', 'Trousers', 15.00 UNION ALL
SELECT 'SH', 'Shirt', 10.95

SELECT Code, CodeDescription, SubCategory
FROM @Table1
WHERE Code LIKE '%s%'

UNION ALL

SELECT Code, CodeDescription, NULL
FROM @Table2
WHERE Code LIKE '%s%'[/code]

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

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-12-17 : 07:29:00

Hi Peso,

So I can use NULL where there are no matching columns or more columns , is that correct. Thank you

Necessity is the mother of all inventions!

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-17 : 07:33:51

Aha! Use NULL to balance out the missing column(s) - genius! I never thought of that. MANY thanks Peso.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 07:37:56

quote:
Originally posted by Vijaykumar_Patil

Hi Peso,

So I can use NULL where there are no matching columns or more columns , is that correct. Thank you

Necessity is the mother of all inventions!

Yes

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-17 : 07:42:27

Hmmmm ....

using the query

SELECT code, code_description, NULL
FROM table 2
WHERE (code LIKE 's%')
UNION ALL
SELECT code, code_description, sub_category
FROM table 1
WHERE (code LIKE 's%')

gave me the error:

"Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 07:46:03

Did you see my response?

NEVER NULL FIRST!
That's why I posted a suggestion with REAL columns first to that the query engine knows what datatypes to deal with.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 07:47:11

If you persist/insist of having NULL first, YOU MUST CAST to proper datatype.

SELECT code, code_description, CAST(NULL AS blablabla) -- Use same datatype as sub_category in table1
FROM table2
WHERE code LIKE 's%'
UNION ALL
SELECT code, code_description, sub_category
FROM table1
WHERE code LIKE 's%'

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

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-12-17 : 07:49:41

Thank you folks ,..I learnt something new..wish to keeping learning more on this site.

Necessity is the mother of all inventions!

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-17 : 08:23:32

I see .. but using

SELECT code, code_description, sub_category
FROM table 1
WHERE (code LIKE 's%')
UNION ALL
SELECT code, code_description, NULL
FROM table 2
WHERE (code LIKE 's%')

now gives me

"Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict."

Casting the NULL gives me the same error. What have I done wrong please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 08:26:42

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68547

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 08:27:20

Ok, so now you have different collations on the two table.

SELECT code COLLATE Finnish_Swedish_CI_AS, code_description COLLATE Finnish_Swedish_CI_AS, sub_category COLLATE Finnish_Swedish_CI_AS
FROM table1
WHERE (code LIKE 's%')
UNION ALL
SELECT code COLLATE Finnish_Swedish_CI_AS, code_description COLLATE Finnish_Swedish_CI_AS, NULL
FROM table 2
WHERE (code LIKE 's%')

Change the collation schema to an appropriate for you.

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

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-17 : 09:44:29

The data is now returned as expected - thanks!

However, there are no column names. Any idea why this is please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 09:47:48

By design.

SELECT	code COLLATE Finnish_Swedish_CI_AS AS Code,
	code_description COLLATE Finnish_Swedish_CI_AS AS code_description,
	sub_category COLLATE Finnish_Swedish_CI_AS AS sub_category
FROM	table1
WHERE	code LIKE 's%'

UNION ALL

SELECT	code COLLATE Finnish_Swedish_CI_AS,
	code_description COLLATE Finnish_Swedish_CI_AS,
	NULL
FROM	table2
WHERE	code LIKE 's%'

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources