6 Replies - 1775 Views - Last Post: 25 July 2012 - 06:18 AM

#1 JonQCoder  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 44
  • Joined: 23-May 11

Joining 2 tables, mysql not seeing a column which exists?

Posted 18 July 2012 - 09:30 AM

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)

  • Attached Image

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


Is This A Good Question/Topic? 0
  • +

Replies To: Joining 2 tables, mysql not seeing a column which exists?

#2 JonQCoder  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 44
  • Joined: 23-May 11

Re: Joining 2 tables, mysql not seeing a column which exists?

Posted 18 July 2012 - 09:55 AM

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';

Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,663
  • Joined: 30-January 09

Re: Joining 2 tables, mysql not seeing a column which exists?

Posted 19 July 2012 - 02:42 PM

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

Was This Post Helpful? 2
  • +
  • -

#4 Duckington  Icon User is offline

  • D.I.C Addict

Reputation: 164
  • View blog
  • Posts: 599
  • Joined: 12-October 09

Re: Joining 2 tables, mysql not seeing a column which exists?

Posted 23 July 2012 - 04:59 AM

FYI: THere are some Moodle SQL queries in the blog in my signature if they're of any help to you.
Was This Post Helpful? 0
  • +
  • -

#5 JonQCoder  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 44
  • Joined: 23-May 11

Re: Joining 2 tables, mysql not seeing a column which exists?

Posted 24 July 2012 - 12:09 PM

View Poste_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!

View PostDuckington, 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
Was This Post Helpful? 0
  • +
  • -

#6 Duckington  Icon User is offline

  • D.I.C Addict

Reputation: 164
  • View blog
  • Posts: 599
  • Joined: 12-October 09

Re: Joining 2 tables, mysql not seeing a column which exists?

Posted 25 July 2012 - 04:57 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 JonQCoder  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 44
  • Joined: 23-May 11

Re: Joining 2 tables, mysql not seeing a column which exists?

Posted 25 July 2012 - 06:18 AM

View Poste_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 :D 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.

View PostDuckington, 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. :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1