fetch data from database using @searchValue

Question

I have given two table, and I want to solve following tasks:

Write the SQL DDL for creating a new table called Job that relates a person to a company in a Many-to-Many relationship.
Provided an SQL parameter called @searchValue, create an SQL query that will return all the people who worked for a company where the name or description contains the search term.

I have studied https://social.technet.microsoft.com/wiki/contents/articles/19854.tutorial-many-to-many-dimension-example-for-sql-server-analysis-services.aspx and a similar question like How to make SQL many-to-many same-type relationship table but still can not figure it out.

I have tried solving question (1) as below but wondering that is it recommend to add job-id as primary key in job table?

create table Job
(
  person_id int,
  company_id int,
  CONSTRAINT person_cat_pk PRIMARY KEY (person_id, company_id),
  CONSTRAINT FK_ person
      FOREIGN KEY (person_id) REFERENCES person (person_id),
  CONSTRAINT FK_company
      FOREIGN KEY (company_id) REFERENCES category (company_id)
);

I also tried solving question (2):

SELECT * FROM Company WHERE (name LIKE '%' + @searchValue + '%') OR (description  LIKE '%' + @searchValue + '%')

Then, what is the role of Job table in question 2? Is this query correct?

You might want to add a (..)OR (@searchValue IS NULL) to allow for the all option.. As for an IDENTITY key, I say go ahead....Space is not limited as it once was and that key would make future CRUDS easier. — Ross Bush, Sep 05 '17 at 02:01

score 0 · Answer 1 · answered Sep 05 '17 at 02:07

The question is to "return all people who worked for a company" and "where the name or description contains the search term". Now, when the question says "where the name or description contains the search term", it means name or description of the company some person worked for.

So, most likely, you are supposed to return a person entity for the person which is stored in person table as firstName, lastName, dateOfBirth who worked for a company as stored in Company table whose name or description matches search value. Your job table is the one which contains the details about any person working for a company. This table generally should have 2 more fields, JobStartDate, JobEndDate, may be role as well, but may be that is not part of the question. So, your query should be like

    SELECT a.firstName, a.lastName, a.dateOfBirth 
FROM Person a INNER JOIN Job b ON a.id = b.person_id 
INNER JOIN Company c ON b.company_id = c.id 
WHERE ((c.name LIKE '%' + @searchValue + '%') 
OR (c.description  LIKE '%' + @searchValue + '%'))

fetch data from database using @searchValue

1 Answers1