SQL Help

[ricardino]

Hi all, im having a bit of trouble with a particular query that really seems to have me beat. Ive been through miles of stuff on the internet and cant seem to find the answer.

 

Basically i have two tables:

 

Issues and priorities

 

there are three priorities in the priorities table each with a priority code high, medium, low,  and each with an id.

 

In the issues table there is an issue_priority_id column that references these ids. there are other tables involved but as far as i can see, this is where the problem lies. The query i have at that minute:

 

SELECT issues.*, priorities.priority_code
                FROM (issues, priorities)
                INNER JOIN priorities AS pri1 ON issues.issue_priority_id = pri1.priority_id
                
                INNER JOIN assignments ON issues.issue_id = assignments.assignment_issue_id
                WHERE assignments.assignment_user_id = 1
                AND issues.issue_closed_by IS NULL 

 

What i want to achieve is to return each issues associated priority code when the query is run. However what is happening, is the query is returning three results, one for each of the entries in the priorities table.

 

my SQL:

 

priorities:

 

CREATE TABLE `priorities` (
  `priority_id` int(11) NOT NULL AUTO_INCREMENT,
  `priority_code` varchar(255) NOT NULL,
  PRIMARY KEY (`priority_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 

 

issues:

 

CREATE TABLE `issues` (
  `issue_id` int(11) NOT NULL AUTO_INCREMENT,
  `issue_raised` date NOT NULL,
  `issue_raised_by` int(11) NOT NULL,
  `issue_summary` text NOT NULL,
  `issue_details` text NOT NULL,
  `issue_status_id` int(11) NOT NULL,
  `issue_priority_id` int(11) NOT NULL,
  `issue_closed_by` int(11) DEFAULT NULL,
  `issue_closed_when` date DEFAULT NULL,
  `issue_is_answered` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`issue_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; 

 

and assignments (although i dont think theres a problem here):

 

CREATE TABLE `assignments` (
  `assignment_id` int(11) NOT NULL AUTO_INCREMENT,
  `assignment_issue_id` int(11) NOT NULL,
  `assignment_user_id` int(11) NOT NULL,
  `assignment_date` date DEFAULT NULL,
  PRIMARY KEY (`assignment_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; 

 

Any help would be hugely appreciated, i really dont seem to be able to get my head around this.

 

Thanks very much.

[JustLikeIcarus]

Try this

 

SELECT issues.*, priorities.priority_code
FROM issues, priorities, assignments
WHERE issues.issue_priority_id = priorities.priority_id
AND issues.issue_id = assignments.assignment_issue_id
AND assignments.assignment_user_id = 1
AND issues.issue_closed_by IS NULL