CODEWITHSUNDEEP
mysqlsearch
Linga
Linga

Reputation: 10553

MySQL search with all tables

I've three tables employee, job_informationandemergency_contact. Due to the Engine compatibility issues, I'm unable to perform FULL TEXT search. So, I'm trying FULL TEXT search by using the following query. But I'm getting repeated columns. Here is my Fiddle

The query is,

SELECT E.id, E.name, E.email, J.title, J.location
FROM employee E, job_information J, emergency_contact C
WHERE (E.id LIKE '%kavi%')
    OR (E.name LIKE '%kavi%')
    OR (E.email LIKE '%kavi%')
    OR (J.title LIKE '%kavi%')
    OR (J.location LIKE '%kavi%')
    OR (C.earea LIKE '%kavin%')
    OR (C.ephone LIKE '%kavi')

The expected result is,

ID  NAME    EMAIL   TITLE   LOCATION

3   kavi    abd2ab  lead    bangalore
4   kavi    abd2ab  lead    bangalore

Upvotes: 0

Views: 102

Answers (2)

GolezTrol
GolezTrol

Reputation: 116110

That because you just list all these tables comma separated, without any join condition:

FROM employee E, job_information J, emergency_contact C

This basically performs a cross join, returning a cartesian product of all data.

This should do it, that is, if each of the id's has the same meaning, namely the employee id. I'd suggest having a look at your naming, because 'id' is too generic a name to use like this. As your database grows, you'll have all kinds of ids with different meanings.

SELECT E.id, E.name, E.email, J.title, J.location 
FROM 
  employee E
  inner join job_information J on J.id = E.id
  inner join emergency_contact C on C.id = E.id 
WHERE  
  (E.id LIKE '%kavi%') OR 
  (E.name LIKE '%kavi%') OR 
  (E.email LIKE '%kavi%') OR 
  (J.title LIKE '%kavi%') OR 
  (J.location LIKE '%kavi%') OR 
  (C.earea LIKE '%kavi%') OR 
  (C.ephone LIKE '%kavi')

In case you have an employee that might not have a job_information or an emergency_contact (which might happen of course), you can use a left join instead of an inner join.

For instance, if you remove the job_information for employee 3, you will only see employee 4 returned by the query above, even though employee 3 still matches the filter 'kavi'. To solve this, use this query:

http://sqlfiddle.com/#!2/4166ae/3

SELECT E.id, E.name, E.email, J.title, J.location 
FROM 
  employee E
  LEFT JOIN job_information J on J.id = E.id
  LEFT JOIN emergency_contact C on C.id = E.id 
WHERE  
  E.id LIKE '%kavi%' OR 
  E.name LIKE '%kavi%' OR 
  E.email LIKE '%kavi%' OR 
  J.id IS NULL OR
  J.title LIKE '%kavi%' OR 
  J.location LIKE '%kavi%' OR
  C.id IS NULL OR
  C.earea LIKE '%kavi%' OR 
  C.ephone LIKE '%kavi'

It basically changes inner join to left join, and adds extra conditions to the where clause, allowing the ids of the detail tables to be null, indicating that there are no details for that employee.

Upvotes: 3

Jason Heo
Jason Heo

Reputation: 10236

could you try this? I have tested on SQLFiddle. you are missing "JOIN Condition"

SELECT E.id, E.name, E.email, J.title, J.location
FROM employee E JOIN job_information J ON E.id = J.id
    JOIN emergency_contact C ON  J.id = C.id
WHERE (E.id LIKE '%kavi%')
    OR (E.name LIKE '%kavi%')
    OR (E.email LIKE '%kavi%')
    OR (J.title LIKE '%kavi%')
    OR (J.location LIKE '%kavi%')
    OR (C.earea LIKE '%kavin%')
    OR (C.ephone LIKE '%kavi')

Above query works well, but I strongly recommend that your DB Design should be as follows. according to sqlFiddle, there is one to one relationship among tables.

CREATE TABLE employee(
    eid INT(5) NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    email VARCHAR(10),
    job_title VARCHAR(10),
    job_location VARCHAR(10),
    earea VARCHAR(6),
    ephone VARCHAR(10)
);

Upvotes: 0

Related Questions