CODEWITHSUNDEEP
mysqlsql
user860511
user860511

Reputation:

Select From Where Not Exists query

Memberships table:

CREATE TABLE `Consultant_Memberships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `membership_url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

Memberships_List table:

CREATE TABLE `Consultant_Memberships_List` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `consultant_id` int(11) DEFAULT NULL,
  `membership_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Within the Memberships table, there is a list of 'Societies' which the member can become a part of. On selection, this is then added to the 'Memberships_List' in the form of:

  1. id - Auto increment
  2. consultant_id - The unique ID of the user who's added the societies
  3. membership_id - Refers to the 'id' from the memberships table.

I want to be able to show in a drop down list only the memberships which the user hasn't chosen yet. So far I've got:

$query = $db->query("SELECT `Consultant_Memberships.`id`, `Consultant_Memberships`.`title` `FROM `Consultant_Memberships 
WHERE NOT EXISTS (SELECT `Consultant_Memberships`.`id`, `Consultant_Memberships`.`title` 
WHERE `Consultant_Memberships`.`id` = $user_id)");

I'm currently getting this error, and also unsure if this is the correct query:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `Consultant_Memberships_List`.`id` = )' at line 1' in /Users/Sites/pages/medical.php:72
Stack trace:
#0 /Users/Sites/pages/medical.php(72): PDO->query('SELECT `Consult...')
#1 /Users/Sites/index.php(18): include('/Users/Site...')
#2 {main}
  thrown in /Users/Sites/pages/medical.php on line 72

Upvotes: 1

Views: 274

Answers (4)

Bulat
Bulat

Reputation: 6979

FROM is missing in the NOT EXISTS subquery.

Upvotes: 1

SSC
SSC

Reputation: 3008

Kindly try this one:

SELECT a.id, a.title 
FROM Consultant_Memberships a, Consultant_Memberships_List b
WHERE a.id <> b.consultant_id

The reason you are getting syntax error because if you see your subquery you will find out that you are not specifying any table in it.

WHERE NOT EXISTS (SELECT `Consultant_Memberships`.`id`, `Consultant_Memberships`.`title` 
WHERE `Consultant_Memberships`.`id` = $user_id)

If you need more help, please let us know...

Regards... Mr.777

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157991

Always watch to the left from the highlighted query part. WHERE in your case

SELECT `Consultant_Memberships.`id`, `Consultant_Memberships`.`title`
`FROM <--- extra backtick
`Consultant_Memberships <--- unclosed backtick

by the way, do not overuse backticks. most of your fields require them not

...and you have your query totally screwed as it seems
As far as I understood your question, it have to be

SELECT cm.id, title 
FROM Consultant_Memberships cm LEFT JOIN Consultant_Memberships_List
ON cm.id=membership_id WHERE membership_id IS NULL

Please note that your question has nothing to do with PDO.
it's clear SQL query question.

Upvotes: 0

rohitarora
rohitarora

Reputation: 1365

SELECT `Consultant_Memberships.`id`, `Consultant_Memberships`.`title` `FROM `Consultant_Memberships 
WHERE NOT EXISTS (SELECT `Consultant_Memberships`.`id`, `Consultant_Memberships`.`title` 
WHERE `Consultant_Memberships`.`id` = $user_id)

you have a wrong syntax try something like this i am not writing exect query but checked in sql fiddle and thats wrond

SELECT Consultant_Memberships.id, Consultant_Memberships.title FROM Consultant_Memberships 
WHERE NOT EXISTS (SELECT Consultant_Memberships.id, Consultant_Memberships.title from Consultant_Memberships
WHERE Consultant_Memberships.id = 1)

Upvotes: 0

Related Questions