CODEWITHSUNDEEP
mysqlview
user725913
user725913

Reputation:

Trouble creating MYSQL view

First off, the version of MYSQL I'm using is 5.1.66-community-log.

Now that we got that out of the way, I'm retrieving the following error when trying to create the below view:

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 'all select RRA.id, RRA.reply_id as replyID, RRA.user_id, RRA.vote_up, RRA.rank' at line 4

and the view:

$sql = "Create or replace view userRank as 
select PRA.id, PRA.post_id as postID, PRA.user_id, PRA.vote_up, PRA.rank_date 
from post_rank_activity PRA
union all 
select RRA.id, RRA.reply_id as replyID, RRA.user_id, RRA.vote_up, RRA.rank_date 
from reply_rank_activity RRA";

I've checked a few sites and this seems to be the correct syntax - or has something changed over time?

Upvotes: 1

Views: 195

Answers (4)

kangaswad
kangaswad

Reputation: 775

Parentheses may helps, try :

Create or replace view userRank as 
(select PRA.id, PRA.post_id as postID, PRA.user_id, PRA.vote_up, PRA.rank_date 
from post_rank_activity PRA)
union all 
(select RRA.id, RRA.reply_id as replyID, RRA.user_id, RRA.vote_up, RRA.rank_date 
from reply_rank_activity RRA)

Upvotes: 2

Vardan Gupta
Vardan Gupta

Reputation: 3585

It seems to be a bug even raised at MySQL Site

http://bugs.mysql.com/bug.php?id=22044

Upvotes: 0

Emmanuel N
Emmanuel N

Reputation: 7449

Union All column in all sections have to be similar in name and type

These two are different

  • PRA.post_id as postID

  • RRA.reply_id as replyID

Upvotes: 0

hd1
hd1

Reputation: 34657

According to this, your all is not needed, try:

Create or replace view userRank as select PRA.id, PRA.post_id as postID, PRA.user_id, PRA.vote_up, PRA.rank_date from post_rank_activity PRA union select RRA.id, RRA.reply_id as replyID, RRA.user_id, RRA.vote_up, RRA.rank_date from reply_rank_activity RRA;

and it should work -- untested.

Upvotes: 0

Related Questions