To select a column from a table using values from another table

Question

I have tables with columns as below:

Employee:

Id number(10),  
Name varchar2(10)

Department:

Id number(10),
Name varchar2(10),
Emp_ids varchar2(10)

The values present in the tables respectively are:

Employee:

1,abc,111
2,def,222
3,xyz,333

Department:

111,development,'1,2'
222,testing,'2,3'

My problem is that I need to select Employee Names using the emp_ids column from the Department table.

Example:

SELECT names FROM employee 
 WHERE id in (SELECT emp_ids FROM department WHERE name = 'development');

Since emp_ids is varchar2 datatype I am unable to execute the above command.

NOTE: As I am using Activerecord Base Connection to connect my Oracle DB from Ruby, I am including ruby in the tags too.

when you were running the code `SELECT names FROM employee WHERE id in (SELECT emp_ids FROM department WHERE name = 'development');`, what did you get? — Arup Rakshit, Apr 18 '13 at 10:00
I am facing datatype mismatch error: **ORA-01722: invalid number** — kattashri, Apr 18 '13 at 10:03
possible duplicate of [how to convert csv to table in oracle](http://stackoverflow.com/questions/3142665/how-to-convert-csv-to-table-in-oracle) — APC, Apr 18 '13 at 10:04
That's because '1,2' is a string and isn't a valid number. Basically you have a CSV which you need to split into separate tokens. There's *loads* of examples of how to this on SO. Check out the suggested duplicate for workarounds. — APC, Apr 18 '13 at 10:06
As an aside, storing values like this is *a really bad idea*. Even worse than a nested table column. Use a relational approach such as an intersection table, or use the standard solution: DEPT_ID as a foreign key on EMPLOYEE. — APC, Apr 18 '13 at 10:08
As you apperently have an N:N relationship the interasection table is the only viable approach (although haveing employees work for two departments simultaneously will create some interesting clashes when salary appraisal time comes around). — APC, Apr 18 '13 at 10:16
This is clearly a case for an intersection table: what a rubyist would call a "has_many through", or maybe a has_and_belongs_to_many. — David Aldridge, Apr 18 '13 at 12:04

Vincent Malgrat · Answer 1 · 2013-04-18T16:31:54.277

2

Your data model is not properly designed. You could build a query that would work but it would be:

more complex than needed,
eventually inefficient as your data grows in size,
open to inconsistency because you can't define referential constraints properly,
most certainly vendor specific.

I suggest you use a reliable data model. You have a N-N relationship, you need three tables:

Employee (Id number(10) primary key, Name varchar2(10))
Department (Id number(10) primary key,Name varchar2(10))

Employee_department (
   emp_id number(10) references employee (id),
   dep_id number(10) references department(id),
   constraint pk_emp_dept primary key (emp_id, dep_id)
)

Then your query will work flawlessly, efficiently and you won't lose sleep at night regarding data inconsistency.

SELECT names 
  FROM employee 
 WHERE id in (SELECT ed.emp_id 
                FROM department d
                JOIN employee_department ed ON d.id = ed.dep_id
               WHERE d.name = 'development');

As a database developer your two main goals are:

To make sure data is properly saved to the database. Nothing else is more important than the data. If you can't trust your data, what good is a database? Eliminating inconsistencies is a top priority.
To make a bug-free application. If your model is appropriate, your queries will be simpler, you will make less bugs, your applications will be faster and in general more reliable.

edited Apr 18 '13 at 16:31

answered Apr 18 '13 at 10:09

Vincent Malgrat

66,725
9
119
171

Yes Vincent you are right but my company has designed to implement like that. And our customer has asked to bring a solution with a possible query – kattashri Apr 18 '13 at 10:15
@kattashri there are many ways to convert a "csv" type list of values to a view, for example using [a technique from another SO](http://stackoverflow.com/questions/1381256/reverse-in-oracle-this-path-z-y-x-to-x-y-z). However I must stress that it is a workaround due to a poor choice in data model. You should always strive to defend the most appropriate data model because it will make data management easier and applications in general more reliable :) – Vincent Malgrat Apr 18 '13 at 10:24
@kattashri With all due respect - then your company should re-consider its choice of the data model. What are the reasons your company wants to use a csv list instead of the standard intersection table? – Frank Schmitt Apr 18 '13 at 12:01
Time to start looking for a competent company to work for, if this is how they build their systems. There's a world of hurt coming up for this. – David Aldridge Apr 18 '13 at 12:02
Just a note to say that as this is a ruby & activerecord system, the columns for the join table would conventionally be id as the primary key, and employee_id and department_id as the foreign keys (with a composite unique key on them, preferably, and not nulls) – David Aldridge Apr 18 '13 at 12:07

score 0 · Answer 2 · answered Apr 18 '13 at 10:11

0

i suggest you change your table structure.

Employee:

   id 
   name
   dept_id

Department:

   id 
   name

answered Apr 18 '13 at 10:11

Rey Lancer

1

This doesn't reflect the business rules represented by the posted data – APC Apr 18 '13 at 10:17
`declare @ids nvarchar(50) declare @strsql nvarchar(500) select @ids = Emp_IDS from tblDepartment where depName='dev' set @ids='''' + REPLACE(@ids,',',''',''') + '''' set @strsql = 'SELECT name FROM tblemployee WHERE convert(nvarchar,id) in ( ' + @ids + ' )' exec sp_executesql @strsql` – Rey Lancer Apr 18 '13 at 10:40

To select a column from a table using values from another table

2 Answers2