Help with SELECT statement! - 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 2008 Forums

Transact-SQL (2008)

Help with SELECT statement!

Author

Topic

phalse
Starting Member

1 Post

Posted - 2013-08-02 : 10:32:08

Hello All,
I have two tables each with the following data:

Table1
Name Item
Bill Orange
John Lemon
Dan Apple

Table2
Name Item
John Orange-Lemon
Dan BananaOrange
Bill Orange,Grape,Cherry
Rob Kiwi

I need a select statement that does the following:
select table2.Name,table2.Item from table1,table2 where table1.name = table2.name and table2.item contains any word from table1.item that match names.

Sorry if this is confusing, let me know how I can clarify it. Thanks for the help!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-02 : 11:07:01

[code]

SELECT
DISTINCT t2.Name, t2.Item
FROM dbo.table2 t2
INNER JOIN dbo.table1 t1 ON
t1.name = t2.name AND
t2.Item LIKE '%' + t1.Item + '%'
ORDER BY
1

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-03 : 01:37:22

There's a small possibility of this returning cases where you have items not exactly the same but still have the pattern within them.
see this illustration



declare @Table1 table
(
Name varchar(30),
Item varchar(1000)
)
insert @Table1
values('Bill','Orange'),
('John', 'Lemon'),
('Dan', 'Apple')

declare @Table2  table
(
Name varchar(30),
Item varchar(2000)
)

insert @Table2
values('John','Orange-Lemon'),
('Dan', 'BananaOrange'),
('Bill', 'Orange,Grape,Cherry'),
('Rob', 'Kiwi'),
('Dan','Pineapple')

SELECT
    *
FROM @Table2 t2
INNER JOIN @table1 t1 ON
    t1.name = t2.name 
    
    AND
    t2.Item LIKE '%' + t1.Item + '%'
ORDER BY
    1


output
--------------------------------------------
Name	Item	Name	Item
Bill	Orange,Grape,Cherry	Bill	Orange
Dan	Pineapple	Dan	Apple
John	Orange-Lemon	John	Lemon

See the line in red above

I think you need some delimiter to separate between various items in Table2 for getting matches correctly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources