CODEWITHSUNDEEP
mysql
Kyle
Kyle

Reputation: 2379

MySQL Select Statement not working

I am trying to create a select statement to be used in a view. The goal is to create a view showing office description, party description, candidate name and incumbant indicator. I have the following table is my database:

database layout

SELECT `OFFICE DESCRIPTION`,
       `PARTY DESCRIPTION`,
       `CANDIDATE NAME`,
       `INCUMBANT INDICATOR`   
FROM   OFFICE,PARTY,CANDIDATE 
WHERE  CANDIDATE.`OFFICE CODE`= OFFICE.`OFFICE CODE` 
       AND CANDIDATE.`CANDIDATE ID`=`PARTY CANDIDATE`.`CANDIDATE ID` 
       AND `PARTY CANDIDATE`.`PARTY CODE`=PARTY.`PARTY CODE`;

Error:

#1054 - Unknown column 'PARTY CANDIDATE.CANDIDATE ID' in 'where clause'

I have verified all the columns do exist for this SQL statement to work but for some reason it keeps returning an error that PARTY CANDIDAT.CANDIDATE ID doesn't exist. Any help would be appreciated. Let me know if there is anything more you need me to uplaod including table definitions and layouts.

Upvotes: 1

Views: 1737

Answers (4)

Kyle
Kyle

Reputation: 2379

The solution was to use the following SQL code aided by @justin-kiang, and include the distinct function.

SELECT DISTINCT  `OFFICE DESCRIPTION` ,  `PARTY DESCRIPTION` ,  `CANDIDATE NAME` ,  `INCUMBANT INDICATOR` 
FROM OFFICE, PARTY, CANDIDATE,  `PARTY CANDIDATE` 
WHERE CANDIDATE.`OFFICE CODE` = OFFICE.`OFFICE CODE` 
AND CANDIDATE.`CANDIDATE ID` =  `PARTY CANDIDATE`.`CANDIDATE ID` 
AND  `PARTY CANDIDATE`.`PARTY CODE` = PARTY.`PARTY CODE` 
LIMIT 0 , 30;

Upvotes: 2

Ahsan Mahboob Shah
Ahsan Mahboob Shah

Reputation: 4029

i think the problem is in your FROM and WHERE clause.

FROM OFFICE, 'PARTY CANDIDATE', CANDIDATE

WHERE clause should be like

CANDIDATE.'CANDIDATE ID' = 'PARTY CANDIDATE'.'CANDIDATE ID'

Upvotes: 2

uvais
uvais

Reputation: 416

try this:

SELECT OFFICE.`OFFICE DESCRIPTION`,PARTY.`PARTY DESCRIPTION`,CANDIDATE.`CANDIDATE NAME`,`INCUMBANT INDICATOR` FROM OFFICE,PARTY,CANDIDATE WHERE CANDIDATE.`OFFICE   CODE`=OFFICE.`OFFICE CODE` AND CANDIDATE.`CANDIDATE ID`=`PARTY CANDIDATE`.`CANDIDATE ID` AND     `PARTY CANDIDATE`.`PARTY CODE`=PARTY.`PARTY CODE`;

Upvotes: 1

Justin Kiang
Justin Kiang

Reputation: 1290

  1. You need to enclose your field names with backticks

  2. You are missing Party Candidate from your FROM list

    SELECT `OFFICE DESCRIPTION`,`PARTY DESCRIPTION`,`CANDIDATE NAME`,`INCUMBANT INDICATOR` 
FROM OFFICE,PARTY,CANDIDATE,`PARTY CANDIDATE` 
WHERE CANDIDATE.`OFFICE CODE`=OFFICE.`OFFICE CODE` 
    AND CANDIDATE.`CANDIDATE ID`=`PARTY CANDIDATE`.`CANDIDATE ID` 
    AND `PARTY CANDIDATE`.`PARTY CODE`=PARTY.`PARTY CODE`;

Upvotes: 4

Related Questions