Joins on columns

Database
Enthusiast

Joins on columns

HI,

I have a table emp as showm below

emp_no emp_name
------ -------
1 aaaa
2 bbbb
3 cccc
4 dddd
5 aabb
6 ccgg

Another table Condi(Column name : condi_Details has only one row):

condi_Details
--------------
a%,bbbb

Now I want to ouput all the rows from emp table where emp_name like a%, bbbb.

Output:

emp_no emp_name
------ -------
1 aaaa
2 bbbb
5 aabb

If I store a% and bbbb in 2 different rows, I will apply a join condition. But here, both a% and bbbb are stored in 1 row.
What would be my join condition?

Any suggestion?

Thanks in advance.
4 REPLIES
Enthusiast

Re: Joins on columns

Hi Vijay,

In my opinion, first you must normalize the data:
a) Create a table lookup condi_Details ( condi_Id, conde_Desc )
b) Insert all the distinct conditions.
c) Create a table relation (many to many) condi_emp ( emp_nro, condi_Id )
d) Insert into condi_emp all the relations
e) Make the query as you need (Inner Join if you need only when match the condition)

Regards.
Enthusiast

Re: Joins on columns

Thanks Dixxie. This thing, I have achieved. My requirement is, I cannot create distinct conditions in a table.

Any other suggestions?

Thanks
Senior Apprentice

Re: Joins on columns

Where do you get that data from?
Your data source should provide usefull data, not that kind of garbage.

The only way to achieve your desired result using SQL is to split that string into rows and then join using LIKE.
The easiest way to split would be a Table UDF.

Dieter
Enthusiast

Re: Joins on columns

Thanks Dnoeth.