SQL JOIN WITH LIKE - 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 2000 Forums

Transact-SQL (2000)

SQL JOIN WITH LIKE

Author

Topic

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-12-21 : 11:22:20

I have two tables I am trying to join by Dept.

TABLE1
|ID|DEPT|EMPLOYEE|DATE

TABLE2
|ID|DEPT|ACCESS

TABLE1 has one dept number per DEPT
TABLE2 has multiple depts in DEPT

I am trying to join these two tables, so that if Table2 contains the department number that table1 has it is displayed. The Department numbers are separated by a comma.

SELECT T1.DEPT, T1.EMPLOYEE, T1.DATE, T2.DEPT, T2.ACCESS
FROM TABLE1 T1, TABLE2 T2
WHERE T1.DEPT LIKE T2.DEPT

Is this possible?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-21 : 11:29:49

quote:
Originally posted by 1sqlover

I have two tables I am trying to join by Dept.

TABLE1
|ID|DEPT|EMPLOYEE|DATE

TABLE2
|ID|DEPT|ACCESS

TABLE1 has one dept number per DEPT
TABLE2 has multiple depts in DEPT

I am trying to join these two tables, so that if Table2 contains the department number that table1 has it is displayed. The Department numbers are separated by a comma.

SELECT T1.DEPT, T1.EMPLOYEE, T1.DATE, T2.DEPT, T2.ACCESS
FROM TABLE1 T1, TABLE2 T2
WHERE T1.DEPT LIKE T2.DEPT

Is this possible?

The reason you have a problem is because table TABLE2 is not in 1st normal form.

Change it so that you have only a single value in each column, and the query will be much simpler.

CODO ERGO SUM

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-12-21 : 11:31:52

I agree, but wouldnt be equally complicated to have several Depts sharing the same record?

epoh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-21 : 11:32:57

May be this?

SELECT T1.DEPT, T1.EMPLOYEE, T1.DATE, T2.DEPT, T2.ACCESS
FROM TABLE1 T1, TABLE2 T2
WHERE T2.DEPT LIKE '%' + T1.DEPT + '%'

But as suggested, you need to normalize your table design for efficient solution.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-12-21 : 11:39:50

I see, any suggestions on how to normalize the table? Thanks, harsh athalye

epoh

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-21 : 11:40:23

quote:
Originally posted by 1sqlover

I agree, but wouldnt be equally complicated to have several Depts sharing the same record?

epoh

Just have one Dept per row.

CODO ERGO SUM

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-12-21 : 11:42:29

Ok, well I guess I should explain. T2.ACCESS is the key. That is what each dept needs. That is why I put multiple depts per record in ACCESS.

epoh

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-21 : 11:51:06

quote:
Originally posted by 1sqlover

Ok, well I guess I should explain. T2.ACCESS is the key. That is what each dept needs. That is why I put multiple depts per record in ACCESS.

epoh

In that case, you will need another table to represent the relationship between ACCESS and DEPT.

CODO ERGO SUM

1sqlover
Yak Posting Veteran

56 Posts

Posted - 2006-12-21 : 12:55:42

Yep, I just overlooked that. Thanks.

epoh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-22 : 03:02:12