If you want to exclude records where x = y, you would need to add something like AND x <> y (x does not equal y) to your WHERE. Keep in mind that you would need to parenthesize the WHERE components based on your intention when combining ANDs and ORs.
Without seeing the data in your tables I'm not sure why you currently are not getting results. My recommendation would be to start taking pieces out until you get the result you expect, and then adding them back testing your expectations until something doesn't match.
A few other notes:
- The
or die(..) here I don't think does anything, since the left side is simply string concatenation which I don't think could fail. You are not actually doing any MySQL related work when assigning to $search_sql.
- I would recommend looking into parameterized queries rather than using string concatenation and variable interpolation to generate your query. It will be a bit more work initially, but tends to be easier to maintain and reason about and can easily be more secure.
- I think
INNER JOIN and JOIN have no functional difference, so I would pick one or the other and be consistent.
- Using
SELECT * when grouping by something has the potential to give unexpected/inconsistent results. Any column that is not in either the GROUP BY clause or contained in an aggregate will return the value on the first row of the group, but first is not guaranteed to be consistent (unless you ORDER). Other DBMS's (Oracle and SQL Server, at least, I think) actually forbid this behavior.
Update
SELECT Description
FROM User u
JOIN UserSkills us ON u.UserId = us.UserId
JOIN Skills s ON us.SkillId = s.SkillId
WHERE
(
Description LIKE '%".$value."%'
OR FName LIKE '%".$value."%'
OR LName LIKE '%".$value."%'
OR JobRole LIKE '%".$value."%'
)
AND UserId NOT IN (
SELECT UserID
FROM User u
JOIN UserTasks ut ON u.UserId = ut.UserId
WHERE TaskID = $Task
)
GROUP BY Description
Update with schema and data
CREATE TABLE users (
id INT NOT NULL,
first VARCHAR(16),
last VARCHAR(16),
PRIMARY KEY (id)
);
CREATE TABLE skills (
id INT NOT NULL,
description VARCHAR(64) NOT NULL,
job_role VARCHAR(64) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE user_skills (
user_id INT NOT NULL,
skill_id INT NOT NULL,
FOREIGN KEY user_skills_user (user_id) REFERENCES users (id),
FOREIGN KEY user_skills_skill (skill_id) REFERENCES skills (id)
);
CREATE TABLE user_tasks (
user_id INT NOT NULL,
task_id INT NOT NULL,
PRIMARY KEY (user_id,task_id),
FOREIGN KEY user_tasks_user (user_id) REFERENCES users (id)
);
INSERT INTO users VALUES (0,'FA','LA');
INSERT INTO users VALUES (1,'FB','LB');
INSERT INTO users VALUES (2,'FC','LC');
INSERT INTO skills VALUES (0,'Skill Description A','Job Role A');
INSERT INTO skills VALUES (1,'Skill Description B','Job Role B');
INSERT INTO skills VALUES (2,'Skill Description C','Job Role C');
INSERT INTO user_skills VALUES (0,0);
INSERT INTO user_skills VALUES (0,1);
INSERT INTO user_skills VALUES (0,2);
INSERT INTO user_skills VALUES (1,0);
INSERT INTO user_skills VALUES (1,1);
INSERT INTO user_skills VALUES (2,1);
INSERT INTO user_tasks VALUES (0,1);
INSERT INTO user_tasks VALUES (1,2);
INSERT INTO user_tasks VALUES (2,1);
SELECT *
FROM users u
JOIN user_skills us ON u.id = us.user_id
JOIN skills s ON us.skill_id = s.id
WHERE
(
s.description LIKE ''
OR u.first LIKE '%F%'
OR u.last LIKE ''
OR s.job_role LIKE ''
)
AND u.id NOT IN (
SELECT user_id
FROM user_tasks
WHERE task_id = 2
)
;
This should return 4 records, 3 for FA LA by 3 skills, and one for FC LC by 1 skill. FB LB is not returned because it is assigned task 2. Changing to task_id = 1 should return 2 records, FB LB by 2 skills.
