[JonQCoder]

I have 2 select statements I would like to combine into one. By themselves the select statements run correctly, but when I try to combine them Mysql gives me an error, stating a column is unknown, when it exists. I'm trying to get certain information on users; first name, last name, email, course, role, and the data from a specific field. The way the database is setup, I can add additional fields for user information myself, and it stores the fields in one table, and the data for those fields in another. The table user_info_data stores the data and it keeps track of which field the data belongs to through a field id. The field I need the data from is fieldid 15.

Tables:
user(id,auth,confirmed,policyagreed,username,password,idnumber,firstname,lastname,email,phone etc..)
user_info_data(id,userid,fieldid,data)
role(id,name,shortname,description,sortorder)
role_assignments(id,roleid,contextid,userid...)
context(id,contextlevel,instanceid,path,depth)

First Select Statement: Pulls all the info I need EXCEPT the `data` field where fieldid='15'

SELECT user.firstname AS Firstname, 
	user.lastname AS Lastname, 
	user.email AS Email, 
	course.fullname AS Course, 
	role.name AS Role
FROM user, 
	course,
	role,
	role_assignments AS asg
INNER JOIN context AS context ON asg.contextid=context.id   
WHERE context.contextlevel = 50
AND role.id=asg.roleid
AND user.id=asg.userid
AND context.instanceid=course.id

Second Select Statement: Pulls the info I need but Course is unknown

SELECT user.firstname AS 'First Name',
	 user.lastname AS 'Last Name', 
	 user.email AS 'Email', 
	 user_info_data.data AS 'IBCLC Certified'
FROM user, user_info_data
WHERE user.id = user_info_data.userid
AND fieldid = '15'

Attempt at combining the 2:

SELECT user.firstname AS Firstname,
       user.lastname AS Lastname,
       user.email AS Email,
       course.fullname AS Course,
       role.name AS Role,
       user_info_data.data AS 'IBCLC Certified'
FROM user,
     course,
     role,
     role_assignments AS asg
     INNER JOIN context AS context ON asg.contextid=context.id  
     INNER JOIN user_info_data ON user.id = user_info_data.userid
                              AND fieldid = '15'
WHERE context.contextlevel = 50
AND role.id=asg.roleid
AND user.id=asg.userid
AND context.instanceid=course.id

PHPmyadmin gives me this error when I attempt to run the query. #1054 - Unknown column 'user.id' in 'on clause'. I don't know if the order matters or not? Because the column exists(See attached). Any help is greatly appreciated.


Edit:
If that doesn't work, is it possible to select from a select statement? Something like:

SELECT * FROM (SELECT user.id,user.firstname AS Firstname, 
	user.lastname AS Lastname, 
	user.email AS Email, 
	course.fullname AS Course, 
	role.name AS Role
FROM user, 
	course,
	role,
	role_assignments AS asg
INNER JOIN context AS context ON asg.contextid=context.id   
WHERE context.contextlevel = 50
AND role.id=asg.roleid
AND user.id=asg.userid
AND context.instanceid=course.id) AS userInfo
INNER JOIN user_info_data ON userInfo.id = user_info_data.userid
WHERE user_info_data.fieldid = '15';

Attached image(s)

• 

This post has been edited by JonQCoder: 18 July 2012 - 09:40 AM

[JonQCoder]

Okay, I tried what I asked in the edit, and it works! If anyone else stumbles across this in the future, this is the sql I used:

SELECT Firstname,Lastname,Email,user_info_data.data AS 'IBCLC Certified',Course FROM (SELECT user.id,user.firstname AS Firstname, 
	user.lastname AS Lastname, 
	user.email AS Email, 
	course.fullname AS Course, 
	role.name AS Role
FROM user, 
	course,
	role,
	role_assignments AS asg
INNER JOIN context AS context ON asg.contextid=context.id   
WHERE context.contextlevel = 50
AND role.id=asg.roleid
AND user.id=asg.userid
AND context.instanceid=course.id) AS userInfo
INNER JOIN user_info_data ON userInfo.id = user_info_data.userid
WHERE user_info_data.fieldid = '15';

[e_i_pi]

Whoa dude, that query might get the result but it's overkill, and it most likely renders your indexes useless, which will slow down your query times. Try this instead:

SELECT
	user.id,
	user.firstname AS Firstname, 
	user.lastname AS Lastname, 
	user.email AS Email, 
	course.fullname AS Course, 
	role.name AS Role,
	user_info_data.data AS 'IBCLC Certified'
FROM user
INNER JOIN role_assignments AS asg ON asg.userid = user.id
INNER JOIN role ON role.id = asg.roleid
INNER JOIN context AS context ON asg.contextid = context.id
INNER JOIN course ON course.id = context.instanceid
INNER JOIN user_info_data ON user.id = user_info_data.userid
WHERE context.contextlevel = 50
AND user_info_data.fieldid = '15';

Using multiple tables in the FROM clause is fraught with danger IMO, and I believe is just a convenient way of avoiding learning about JOIN syntax properly. JOINs are actually quite easy. You'll use LEFT OUTER JOIN and INNER JOIN in about 99% of cases. RIGHT OUTER JOIN is next to useless, FULL JOIN has limited use (I have used it once in a work scenario, and I write about 10-50 queries a day) and CROSS JOIN is handy but rarely used.

This post has been edited by e_i_pi: 19 July 2012 - 02:43 PM

[Duckington]

FYI: THere are some Moodle SQL queries in the blog in my signature if they're of any help to you.

[JonQCoder]

e_i_pi, on 19 July 2012 - 02:42 PM, said:

Whoa dude, that query might get the result but it's overkill, and it most likely renders your indexes useless, which will slow down your query times. Try this instead:

SELECT
	user.id,
	user.firstname AS Firstname, 
	user.lastname AS Lastname, 
	user.email AS Email, 
	course.fullname AS Course, 
	role.name AS Role,
	user_info_data.data AS 'IBCLC Certified'
FROM user
INNER JOIN role_assignments AS asg ON asg.userid = user.id
INNER JOIN role ON role.id = asg.roleid
INNER JOIN context AS context ON asg.contextid = context.id
INNER JOIN course ON course.id = context.instanceid
INNER JOIN user_info_data ON user.id = user_info_data.userid
WHERE context.contextlevel = 50
AND user_info_data.fieldid = '15';

Using multiple tables in the FROM clause is fraught with danger IMO, and I believe is just a convenient way of avoiding learning about JOIN syntax properly. JOINs are actually quite easy. You'll use LEFT OUTER JOIN and INNER JOIN in about 99% of cases. RIGHT OUTER JOIN is next to useless, FULL JOIN has limited use (I have used it once in a work scenario, and I write about 10-50 queries a day) and CROSS JOIN is handy but rarely used.

Lol, performance isn't really a concern, we only have to run these reports once a year. I will try out your sql though! Once I get the moodle install working again. Agh moodle and this odd error of mine!

Duckington, on 23 July 2012 - 04:59 AM, said:

FYI: THere are some Moodle SQL queries in the blog in my signature if they're of any help to you.

I will check them out! Everything we have is moodle 1.9 though(Behind, I know), I didn't set it all up so i'm working with what i'm given, however I do want to look into upgrading, just not sure if I can from 1.9 to 2.3+ XD

[Duckington]

Yeah we're on 1.9 as well for two of our Moodles, and 2.3 for the other one.

Upgrading is pretty hard if you have lots of customisations, they've changed some really core stuff like how the database queries are run.

[JonQCoder]

e_i_pi, on 19 July 2012 - 02:42 PM, said:

Whoa dude, that query might get the result but it's overkill, and it most likely renders your indexes useless, which will slow down your query times. Try this instead:

SELECT
	user.id,
	user.firstname AS Firstname, 
	user.lastname AS Lastname, 
	user.email AS Email, 
	course.fullname AS Course, 
	role.name AS Role,
	user_info_data.data AS 'IBCLC Certified'
FROM user
INNER JOIN role_assignments AS asg ON asg.userid = user.id
INNER JOIN role ON role.id = asg.roleid
INNER JOIN context AS context ON asg.contextid = context.id
INNER JOIN course ON course.id = context.instanceid
INNER JOIN user_info_data ON user.id = user_info_data.userid
WHERE context.contextlevel = 50
AND user_info_data.fieldid = '15';

Using multiple tables in the FROM clause is fraught with danger IMO, and I believe is just a convenient way of avoiding learning about JOIN syntax properly. JOINs are actually quite easy. You'll use LEFT OUTER JOIN and INNER JOIN in about 99% of cases. RIGHT OUTER JOIN is next to useless, FULL JOIN has limited use (I have used it once in a work scenario, and I write about 10-50 queries a day) and CROSS JOIN is handy but rarely used.

Your query worked wonderfully and it WAS a bit faster Thank you! I really need to get better with SQL. As i'm the only programmer here, I have to be more of a jack of all trades, doing basically all our IT tasks.

Duckington, on 25 July 2012 - 04:57 AM, said:

Yeah we're on 1.9 as well for two of our Moodles, and 2.3 for the other one.

Upgrading is pretty hard if you have lots of customisations, they've changed some really core stuff like how the database queries are run.

Luckily we only have the one site right now and it's about to launch to the public. And there are some small customizations that I'm sure I could put into 2.3, I plan to set up a development version to play with it and see. Also, your sql from your site DID help.