yo im new

[Ancient_Doom]

Hello. :)I am new to SQL. Ive browsed t hrough some things and have found SQL is not that difficult to pick up. I have a little experience in python and VB. atm, I am working on a code for my clan-leader, and trying to build a query. I am trying to merge two SQL statements, which I know do work.

select _clan_hoa_user.username as Forumname, _clan_hoa_userfield.field11 as SummonerName, _clan_hoa_user.email as email, _clan_hoa_user.skype as skype,FROM_UNIXTIME(_clan_hoa_user.lastpost,'%Y %D %M') as LastPost  from _clan_hoa_user, _clan_hoa_userfield, _clan_hoa_usergroup	where _clan_hoa_user.userid = _clan_hoa_userfield.userid	and _clan_hoa_user.usergroupid = _clan_hoa_usergroup.usergroupid	AND _clan_hoa_userfield.field11 != ''	AND _clan_hoa_userfield.field20 != 'Yes'	AND _clan_hoa_usergroup.title = 'Member'	AND _clan_hoa_user.username NOT IN ('Bigifinda','Code47')	case WHEN _clan_hoa_user.lastpost = "0" THEN "No Posts"	Else FROM_UNIXTIME(_clan_hoa_user.lastpost) < DATE_SUB(NOW(), INTERVAL 7 DAY)	ORDER BY LastPost ASC

and

SELECT username, LastPost,CASE LastPostWHEN "0"THEN "No Posts"ELSE "Illegal Argument"END AS "Status"FROM _clan_hoa_user

together. They work seperately, but I am trying to merge them, and it produced this:

select _clan_hoa_user.username as Forumname, _clan_hoa_userfield.field11 as SummonerName, _clan_hoa_user.email as email, _clan_hoa_user.skype as skype,FROM_UNIXTIME(_clan_hoa_user.lastvisit,'%Y %D %M') as LastVisit  from _clan_hoa_user, _clan_hoa_userfield, _clan_hoa_usergroup	where _clan_hoa_user.userid = _clan_hoa_userfield.userid	and _clan_hoa_user.usergroupid = _clan_hoa_usergroup.usergroupid	AND _clan_hoa_userfield.field11 != ''	AND _clan_hoa_userfield.field20 != 'Yes'	AND _clan_hoa_usergroup.title = 'Member'	AND _clan_hoa_user.username NOT IN ('Bigifinda','Code47')	AND lastvisit(select *when "0" then "No Posts"When FROM_UNIXTIME(_clan_hoa_user.lastpost) < DATE_SUB(NOW(), INTERVAL 7 DAY)from _clan_hoa_user)	ORDER BY LastPost ASC

but I get an error from SQL Fiddle:Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * when "0" then "No Posts" When FROM_UNIXTIME(_clan_hoa_user.lastpost)' at line 9: I don't want ya to just feed me the answer to this little problem, but something pointed out so i can learn would be nice. I have looked up information on google and w3schools, but have come up dry so far. edit: and im just just here to get help, ill look around the forum a bit and see what i can learn

Edited October 23, 2012 by Ancient_Doom

[justsomeguy]

You need to understand what this does:

SELECT username, LastPost,CASE LastPostWHEN "0"THEN "No Posts"ELSE "Illegal Argument"END AS "Status"FROM _clan_hoa_user

That query selects 3 fields. 2 of them come from the table, one of them comes from a case statement. That selects username and LastPost from the table, and then it has a case statement for LastPost:

CASE LastPostWHEN "0"THEN "No Posts"ELSE "Illegal Argument"END AS "Status"

The case statement goes from CASE to END. After end, you can see that this new field is called Status. What that says is that if LastPost is 0, then status should be "No Posts", or else status will be "Illegal Argument". Those 5 lines together make up the case statement to set that Status field. Compare that with what you have:

AND lastvisit(select *when "0" then "No Posts"When FROM_UNIXTIME(_clan_hoa_user.lastpost) < DATE_SUB(NOW(), INTERVAL 7 DAY)from _clan_hoa_user)

First, you have parentheses right after lastvisit, so you're telling the server that lastvisit is a function that you want to run, and you're passing it the results of the select query. The select query is not valid, you have a "when" statement but it's not inside a "case" or any other type of statement that makes sense. Then you have another when statement right after it. Look up CASE statements in MySQL to get examples about how they work.