Here is 3 mysql tables format :
project_status
psid p_id cdid sid short_list res_sent
1 3 112 24 0 0
2 3 113 25 0 1
3 4 114 22 0 1
4 4 115 23 1 0
5 5 116 26 0 0
contact_details :
cdid family_name given_name department title cid
112 xx xx xx xx 1
113 xx xx xx xx 2
114 xx xx xx xx 3
115 xx xx xx xx 4
116 xx xx xx xx 5
company :
cid company_name
1 xx
2 xx
3 xx
4 xx
5 xx
Now, I need to search cdid from project_status table based on p_id and sid to show family_name, given_name, department, title
So that I am using following query and I get 3 results
SELECT
cd.family_name,
cd.given_name,
cd.department,
cd.title,
cd.cid,
ps.*, company.company_name
FROM
contact_details AS cd
LEFT JOIN project_status AS ps ON cd.cdid = ps.cdid
LEFT JOIN company ON company.cid = cd.cid
WHERE
1 = 1
AND ps.sid IN (25, 22)
AND ps.p_id = '3'
ORDER BY
company.company_name ASC
NOW again I want to search on last result data (which was 3 results) based on family_name or given_name or department search string
For that I am using following query :
SELECT
cd.family_name,
cd.given_name,
cd.department,
cd.title,
cd.cid,
ps.*, company.company_name
FROM
contact_details AS cd
LEFT JOIN project_status AS ps ON cd.cdid = ps.cdid
LEFT JOIN company ON company.cid = cd.cid
WHERE
1 = 1
AND ps.sid IN (25, 22)
AND company.company_name LIKE '%ab%'
OR cd.family_name LIKE '%ab%'
OR cd.given_name LIKE '%ab%'
AND ps.p_id = '3'
GROUP BY
cd.cdid
ORDER BY
company.company_name ASC
Now it's should not show me more that 3 result because I have 3 result shown on my first query. BUT unfortunately it's showing me more than 3 results from contact_details table
Try to modify last part of sql:
SELECT
cd.family_name,
cd.given_name,
cd.department,
cd.title,
cd.cid,
ps.*,
company.company_name
FROM contact_details AS cd
LEFT JOIN project_status AS ps ON cd.cdid = ps.cdid
LEFT JOIN company ON company.cid = cd.cid
WHERE ps.sid IN (25, 22)
AND (company.company_name LIKE '%ab%'
OR cd.family_name LIKE '%ab%'
OR cd.given_name LIKE '%ab%' )
AND ps.p_id = '3'
GROUP BY cd.cdid
ORDER BY company.company_name ASC
Your OR clauses are expaning your criteria well beyond what you think
I think this is probably what you ment to specify, its a bit of a guess though
SELECT
cd.family_name,
cd.given_name,
cd.department,
cd.title,
cd.cid,
ps.*, company.company_name
FROM
contact_details AS cd
LEFT JOIN project_status AS ps ON cd.cdid = ps.cdid
LEFT JOIN company ON company.cid = cd.cid
WHERE ps.sid IN (25, 22)
AND company.company_name LIKE '%ab%'
AND (cd.family_name LIKE '%ab%' OR cd.given_name LIKE '%ab%')
AND ps.p_id = '3'
GROUP BY
cd.cdid
ORDER BY
company.company_name ASC
The 1=1 is totally unnecessary also
SELECT cd.family_name, cd.given_name, cd.department, cd.title, cd.cid, ps.*, company.company_name
FROM contact_details AS cd
INNER JOIN project_status AS ps ON cd.cdid = ps.cdid
INNER JOIN company ON company.cid = cd.cid
WHERE ps.sid IN (25,22) AND ps.p_id = '3'
AND ( company.company_name LIKE '%ab%' OR cd.family_name LIKE '%ab%' OR cd.given_name LIKE '%ab%' )
ORDER BY company.company_name ASC