Click here to Skip to main content
15,902,189 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
table employee(SSN,Fname)
table dependants(ESSN,dependant name)

how i can get the names of employees that is similar to any dependent name

using sub query only and like operator only

What I have tried:

SELECT [Fname]
FROM [dbo].[Employee]
WHERE '%'+[Fname]+'%' LIKE (SELECT [Dependent_name] FROM [dbo].[Dependent])

i tried this but i know its wrong
because the return is more than one value!
Posted
Updated 1-Nov-19 6:42am
Comments
Richard MacCutchan 1-Nov-19 8:55am    
You should take the Dependent_name from user input, remove the sub SELECT clause, and just match on the string.

Found it !
SELECT [Dependent_name] FROM [Dependent],[Employee]
WHERE [Dependent].[Dependent_name] LIKE '%' + [Employee].[Fname] + '%'
 
Share this answer
 
Comments
Member 14641515 1-Nov-19 11:03am    
Simple solution but clever to
check the Like operator like that way
We always using = to mapping between tables at joins

Thank you dear 👏😊
The way I understand it is that the dependency should be a relation of the employee table to itself (a many-to-many relation).
TABLE employee
   SSN,   -- the id of the employee
   Fname -- the name of the employee

TABLE dependant
   ResponsibleSSN, -- the id of the employee being depended on
   DependantSSN    -- the id of the employee being dependant
In such a context the dependant table should not hold any name, just a foreign key to the proper employee in the employee table.

A request for your requirement could then be
SQL
SELECT DISTINCT
  e.FName
FROM
 employee e
 INNER JOIN dependant d on d.DependantSSN = e.SSN
Using LIKE in this context would mean that you know in advance the value that you are searching for, and does not make really sense.
Or maybe, I misunderstood your goal entirely?
 
Share this answer
 
Comments
Member 14641515 1-Nov-19 9:07am    
I don't want to know the name of the employee with the name of his dependens no

I want to show the names of the employees in case of
The employee name is like dependens name whatever this dependens is for this employee or not

E1 mina
E2 Kero
E3 Kevin

Dept for emp 1 is Raul Kevin
Dept for emp 2 is Leo
Dept for emp 3 is samir mina

So I want the result to be
Mina
Kevin
Richard MacCutchan 1-Nov-19 10:04am    
You should put this information in your question so it is clear what you are trying to do.
This works, but will only return one name sadly:
SELECT [Dependent_name]
FROM [dbo].[Dependent]
WHERE [Dependent_name] LIKE 
  '%' + (SELECT TOP 1 [Fname] FROM [dbo].[Employee]) + '%'
 
Share this answer
 
v2
Comments
Member 14641515 1-Nov-19 10:12am    
It can't solve the problem because
I want to get the names of employees that is similar to ((((any)))) dependant name

The task is to solve using like and sub query only
RickZeeland 1-Nov-19 10:24am    
The only thing that I can think of is using a cursor, see: http://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/
Another option would be to use Exists:
SQL
SELECT [Fname]
FROM [dbo].[Employee]
WHERE Exists
(
    SELECT 1
    FROM [dbo].[Dependent]
    WHERE [Dependent].[Dependent_name] LIKE '%' + [Employee].[Fname] + '%'
)
EXISTS (Transact-SQL) - SQL Server | Microsoft Docs[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900