vintox Posted May 18, 2009 Share Posted May 18, 2009 I am trying to make a full join query i found some simulating methods for simulating full join but when i am using simulating and the selection is a grouping value like count() i am getting a syntax error the query is going like this: SELECT p.*,COUNT(reply) as rcount FROM reply r LEFT OUTER JOIN posts p ON p.id = r.post GROUP BY r.post UNION SELECT p.*,COUNT(reply) as rcount FROM reply r RIGHT OUTER JOIN posts p ON p.id = r.post GROUP BY r.post WHERE COUNT(reply) IS NULL Thanks in advance vintox Quote Link to comment https://forums.phpfreaks.com/topic/158551-solved-problem-when-simulating-full-join-and-grouping/ Share on other sites More sharing options...
Ken2k7 Posted May 18, 2009 Share Posted May 18, 2009 I don't get your SQL. 1. You're counting a table name? I'm referring to COUNT(reply). Isn't reply the table name? 2. Why are you using UNION on the same SQL? 3. WHERE COUNT(reply) IS NULL? What? ??? I don't think COUNT(reply) will ever be NULL. I can be 0. And change COUNT(reply) to rcount. Quote Link to comment https://forums.phpfreaks.com/topic/158551-solved-problem-when-simulating-full-join-and-grouping/#findComment-836220 Share on other sites More sharing options...
vintox Posted May 18, 2009 Author Share Posted May 18, 2009 1.In reply table the is a reply field 2.I am using UNION on the same SQL because Mysql doesn't have an option of using FULL OUTER JOIN 3.even if i use WHERE rcount=0 or trying COUNT(reply)=0 i get syntax error Quote Link to comment https://forums.phpfreaks.com/topic/158551-solved-problem-when-simulating-full-join-and-grouping/#findComment-836221 Share on other sites More sharing options...
Mchl Posted May 18, 2009 Share Posted May 18, 2009 Well... what is the error you are getting? Quote Link to comment https://forums.phpfreaks.com/topic/158551-solved-problem-when-simulating-full-join-and-grouping/#findComment-836230 Share on other sites More sharing options...
vintox Posted May 18, 2009 Author Share Posted May 18, 2009 #1064 - 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 'WHERE rcount=0 LIMIT 0, 30' at line 10 Quote Link to comment https://forums.phpfreaks.com/topic/158551-solved-problem-when-simulating-full-join-and-grouping/#findComment-836235 Share on other sites More sharing options...
Mchl Posted May 18, 2009 Share Posted May 18, 2009 And did you do what it recommends? check the manual that corresponds to your MySQL server version for the right syntax to use WHERE has to be before GROUP BY Quote Link to comment https://forums.phpfreaks.com/topic/158551-solved-problem-when-simulating-full-join-and-grouping/#findComment-836245 Share on other sites More sharing options...
vintox Posted May 18, 2009 Author Share Posted May 18, 2009 the group By is for the right and left joins the WHERE is for the union i looked at this article and tried to build a query for my needs http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join Quote Link to comment https://forums.phpfreaks.com/topic/158551-solved-problem-when-simulating-full-join-and-grouping/#findComment-836279 Share on other sites More sharing options...
Mchl Posted May 18, 2009 Share Posted May 18, 2009 Each query in UNION is in fact a separate query. If you want to use WHERE with UNION results, you have to use subquery SELECT * FROM ( query1 UNION query2 ) AS subQuery WHERE ... Quote Link to comment https://forums.phpfreaks.com/topic/158551-solved-problem-when-simulating-full-join-and-grouping/#findComment-836303 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.