[SOLVED] problem when simulating FULL JOIN and grouping

[vintox]

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

[Ken2k7]

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.

[vintox]

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

[Mchl]

Well... what is the error you are getting?

[vintox]

#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

[Mchl]

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

[vintox]

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

[Mchl]

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 ...