CODEWITHSUNDEEP
mysqlsql
Greg Holmes
Greg Holmes

Reputation: 139

mysql subquery only returning one row

I have a query that returns many rows:

SELECT c.id AS courseID,
   c.fullname AS courseName,
   c.continuingeducationcredits AS cec,
   cc.name AS catName,
   IFNULL(ccp.name, '-No Parent Category-') AS catParent,
   ue.userid AS userID,
   CONCAT(u.firstname, ' ', u.lastname) AS name,
   ud.data AS employeeid,
   date_format(date_add('1970-01-01',interval round(c.startdate/60/60/24) DAY), '%m/%d/%Y') AS startdate,
   u.id AS uid,
   c.startdate AS cstartdate
FROM user_enrolments AS ue
INNER
    JOIN USER AS u ON ue.userid = u.id
INNER JOIN user_info_data AS ud ON (ud.fieldid = 1
                                      AND ud.userid = u.id)
INNER JOIN enrol AS e ON e.id = ue.enrolid
INNER JOIN course AS c ON c.id = e.courseid
INNER JOIN course_categories AS cc ON cc.id = c.category
LEFT
    OUTER JOIN course_categories AS ccp ON ccp.id = cc.parent
WHERE 1 = 1
  AND (cc.ID = 13
       OR ccp.ID = 13)
ORDER BY catParent,
         uid,
         cstartdate DESC

That works fine, and returns multiple rows. But if I try to use it as a subquery:

SELECT *
FROM
  (SELECT c.id AS courseID,
          c.fullname AS courseName,
          c.continuingeducationcredits AS cec,
          cc.name AS catName,
          IFNULL(ccp.name, '-NO Parent Category-') AS catParent, ue.userid AS userID, CONCAT(u.firstname, ' ', u.lastname) AS name,
          ud.data AS employeeid,
          date_format(date_add('1970-01-01',interval round(c.startdate/60/60/24) DAY), '%m/%d/%Y') AS startdate,
          u.id AS uid,
          c.startdate AS cstartdate
   FROM user_enrolments AS ue
   INNER JOIN USER AS u ON ue.userid = u.id
   INNER JOIN user_info_data AS ud ON (ud.fieldid = 1
                                       AND ud.userid = u.id)
   INNER JOIN enrol AS e ON e.id = ue.enrolid
   INNER JOIN course AS c ON c.id = e.courseid
   INNER
    JOIN course_categories AS cc ON cc.id = c.category
   LEFT OUTER JOIN course_categories AS ccp ON ccp.id = cc.parent
   WHERE 1 = 1
     AND (cc.ID = 13
          OR ccp.ID = 13)) AS Pseudotable
WHERE courseID = 8946
ORDER BY catParent,
         uid,
         cstartdate DESC

It will only ever return one row.

What am I doing wrong? :(

The first query is the inner query of the second query.

The first query gets all the users who attended a course within a category. The second is intended to filter that set, and get only the users who attended a single course.

Upvotes: 0

Views: 785

Answers (4)

Greg Holmes
Greg Holmes

Reputation: 139

I finally figured out the issue ... the application is Moodle, and it loads queries into an array, using a function called get_records_sql.

It requires the first row of the results to be a unique key. It uses that row as the index of the array.

So ... to make that happen:

Begin the SELECT like this-

SELECT @a:=@a+1 as serial_number, c.id as courseID,

And add this to the FROM clause-

LEFT OUTER JOIN mdl_course_categories AS ccp ON ccp.id = cc.parent, (SELECT @a:= 0) AS a WHERE

Upvotes: 0

d'alar'cop
d'alar'cop

Reputation: 2365

To be fair... my comment with this answer was first :)

WHERE courseID = 8946

Is in the second query. Remove it and it should work.

Upvotes: 1

Naftali
Naftali

Reputation: 146300

You have WHERE courseID = 8946 which you are reducing your query down to one ID

Upvotes: 0

TheEwook
TheEwook

Reputation: 11117

You have one row in your second query because of the WHERE condition which you haven't got in your first query.

WHERE courseID = 8946

Upvotes: 0

Related Questions