MySQL search with all tables

Question

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

Your tables are not linked together, that is to say that there are no foreign keys. If you are going to use the PK in all 3 tables to refer to the employee, there is NO point separating them into different tables. — Achrome, Nov 30 '13 at 07:19
in the tables provided in fiddle ,you are using, there is no relation between the three tables.you need to assign some kind of relation between them to join the tables and get results freom all the three tables — Bhadra, Nov 30 '13 at 07:20
http://stackoverflow.com/questions/1381186/fulltext-search-with-innodb — Sebas, Nov 30 '13 at 07:22
That would defeat the purpose of indexing, because you do need to have a PK in every table. Although, in this case, the problem seems to be with the way columns are named. — Achrome, Nov 30 '13 at 07:32

GolezTrol · Answer 1 · 2013-11-30T07:36:11.703

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.

See http://dev.mysql.com/doc/refman/5.0/en/join.html In MySQL they are semantically equal. But in most databases there is a difference between `inner`, `left`/`right` and `cross join`. With `join`/`cross join` you don't specify a condition (making it basically equivalent to the query you had), and with `inner join`, you specify a join condition. I like to follow these same rules in MySQL, even if you don't need to. I keeps my queries more portable and better readable. — GolezTrol, Nov 30 '13 at 07:26
Note that this query will fail if there are no records in a details table like job_information. When you `join` or `inner join`, you require a matching record in both tables. Using `left join`, you can solve this, but the condition in the where clause will be more complicated as well. I'll add an example in a minute. — GolezTrol, Nov 30 '13 at 07:30
I've a matching id in my original table, but in fiddle I didn't created, but in my code I've primary and foreign keys — Linga, Nov 30 '13 at 07:31
I'm not talking about relations. FK relations are good for keeping data integrity, but they won't affect the result of this query. I meant that maybe there are no details for some employees, in which case you need a slightly altered query if you still want to be able to find those employees. I've added an example and explanation to the answer. — GolezTrol, Nov 30 '13 at 07:37

Jason Heo · Accepted Answer · 2013-11-30T07:27:09.793

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)
);

I just created a sample, thats y didn't make any relations in fiddle — Linga, Nov 30 '13 at 07:29
@ling.s Aha, if there is one to zero relationship (e.g, employee has no emergency) `LEFT JOIN` should be used. — Jason Heo, Nov 30 '13 at 07:31

MySQL search with all tables

2 Answers2