Inverse of a Query
Inverse of a Query
What would be the inverse of the following query:
SELECT * FROM `test_result`
INNER JOIN `perinfo` ON `test_result`.`mobileno` = `perinfo`.`mobileno`
WHERE `perinfo`.`insti_id` = '2'
AND `test_id` = (SELECT `test_id`
FROM `test`
WHERE `test_name` = 'One')
i want to select rows from database table 'perinfo' where insti_id is 2 and also they are present in data table 'test_result'
3 Answers
3
Try this:
SELECT * FROM `test_result` `t`
INNER JOIN `perinfo` ON `test_result`.`mobileno` = `perinfo`.`mobileno`
WHERE `perinfo`.`insti_id` = '2'
AND NOT EXISTS(SELECT 1 FROM `test`
WHERE `test_name` = 'One'
AND `test_id` = `t`.`test_id`)
you can change your filter uisng != (not equal) condition instead of = (equal)
!=
=
SELECT * FROM `test_result`
INNER JOIN `perinfo` ON `test_result`.`mobileno` = `perinfo`.`mobileno`
WHERE `perinfo`.`insti_id` != '2'
AND `test_id` != (SELECT `test_id`
FROM `test`
WHERE `test_name` = 'One')
but if you have more then a result from subquery you should is IN or NOT IN
SELECT * FROM `test_result`
INNER JOIN `perinfo` ON `test_result`.`mobileno` = `perinfo`.`mobileno`
WHERE `perinfo`.`insti_id` != '2'
AND `test_id` NOT IN (SELECT `test_id`
FROM `test`
WHERE `test_name` = 'One')
I don't understand what you mean by "the inverse of a query", but to obtain the results you describe in the question's text you'd want to use
SELECT *
FROM `perinfo`
INNER JOIN `test_result`
ON `test_result`.`mobileno` = `perinfo`.`mobileno`
WHERE `perinfo`.`insti_id` = '2'
In other words, just reverse the positions of perinfo and test_result in the query, and remove the sub-select on test because it's not specified as part of the desired results.
perinfo
test_result
test
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
