Match between column values returned from LIKE constraint in SQL

Question

Hi!

I am trying to match column values returned, but values are returned as a result of pattern matching:

select app.employees.id, app.employees.name, app.employees.current_bp, 
app.designation.designation from app.employees, app.designation 
where app.employees.id like 'khsn?' = app.designation.desig_id like 'khsn?';

As you can see, I am trying to retrieve a few column values, but java derby tool says:

ERROR 42884: No authorized routine named 'LIKE' of type 'FUNCTION' having compatible arguments was found.

How can I got about this?

Answer 1

You should use a JOIN, I think. I'm not sure I understand what result you're aiming for, but what you wrote there is like no SQL I've ever seen.

Answer 2

AS evilpenguin mentioned, you need to use a join

  select app.employees.id, app.employees.name, app.employees.current_bp, 
app.designation.designation from app.employees, app.designation where 
app.employees.id like 'khsn?' = app.designation.desig_id like 'khsn?';

could be (if I understand you correctly)

SELECT EMP.id, EMP.name, EMP.current_bp,
       DES.designation
FROM   app.employees AS EMP
INNER JOIN app.designation AS DES
       ON SUBSTR(EMP.id,0,4) = DES.desig_id
WHERE app.employees.id like 'khsn?'

Note, this is matching against the literal "khsn?" string.

If you want a wildcard, it should be WHERE app.employees.id like 'khsn%'

which would match anything starting with "khsn"

EDIT

Added SUBSTR based on OP's comments

Answer 3

DESIGNATION                        |DESI&
-----------------------------------------
Managing Director                  |khceA
General Manager                    |khceB
Company Secretary                  |khsnA
Accounts Officer                   |khsnB
Office Manager                     |khsnC
Personnel Officer                  |khsnD
Sr. Accounts Superintendent        |khsnE
Jr. Accounts Superintendent        |khsnF
Personal Secretary                 |khsnG
Accountant                         |khsnH
Sr. Office Assistant               |khsnI
Jr. Office Assistant               |khsnJ
Stenographer                       |khsnK
Jr. Typist                         |khsnL
Sr. Typist                         |khsnM
Sr. Surveyor Cum Draftsmen         |khsnN
Jr. Surveyor Cum Draftsmen         |khsnO
Sr. Driver                         |khsnP
Jr. Driver                         |khsnQ
Attender                           |khsnR
Night Watchmen                     |khsnS
Potstill Operator                  |khsnT
Boiler Operator                    |khsnU
Electrician                        |khsnV
Mechanic                           |khsnW
Jamedaar                           |khsnX

26 rows selected
ij> select * from employees;
ID      |NAME                               |CURRENT_BP  
---------------------------------------------------------
khceA001|R. M. N. Sahai                     |71690.0000  
khceB001|Anita S. Arekal                    |53380.0000  
khsnC001|R. Sundar                          |16800.0000  
khsnC002|A. Suresh Suvarna                  |16800.0000  
khsnE001|G. K. Sundar                       |18600.0000  
khsnF001|Olwin Peter D'Souza                |16800.0000  
khsnG001|Ida D'Souza                        |17250.0000  
khsnH001|Sankappa P.                        |12300.0000  
khsnI001|Poovamma                           |12000.0000  
khsnJ001|Damodar                            |7450.0000   
khsnM001|Champakamalini C.                  |6125.0000   
khsnN001|K. Krishna Rao                     |17250.0000  
khsnP001|Valerian Tauro                     |10500.0000  
khsnP002|Honappa Naik                       |10500.0000  
khsnQ001|Padmayya Gowda                     |9275.0000   
khsnR001|Tharanath K. P.                    |7100.0000   
khsnR002|Jayanthi                           |7100.0000   
khsnX001|J. Kempa                           |7625.0000

These are my tables really, so i want to display a concoction of these two, ie. all columns of employees table (the second table displayed here) corresponding to each record i want to show their designation too which am going to pull from designation table by checking if desig_id 's first 4 characters match the employee.id's first four characters.

is this possible?