How will i correct this SQL/MYSQL FULL Outer Join Problem?

Question

`SELECT leaderstbl.lfname AS leaders, IF(COUNT(vfname)>1,COUNT(vfname),0) AS followers, IF(COUNT(household)>1,COUNT(household),0) AS household FROM votersinfotbl WHERE vleaders IN (SELECT vleaders FROM votersinfotbl WHERE vaddress = 'ABACA') AND vleaders NOT IN ('NONE', 'none', 'None', '') AND vaddress = 'ABACA' AND household IN (SELECT vleaders FROM votersinfotbl WHERE vaddress = 'ABACA')

FULL OUTER JOIN leaderstbl ON votersinfotbl.lfname=leaderstbl.lfname
GROUP BY leaderstbl.lfname ORDER BY vleaders ASC`

what seems to be the problem of my query sir?

it displays this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FULL OUTER JOIN leaderstbl ON votersinfotbl.lfname=leaderstbl.lfname GROUP BY l' at line 3

I am having trouble can someone help me?

PS: this is for my capstone thanks everyone for your help!

https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql — ScaisEdge, Jan 31 '19 at 08:56
Have you read the error message properly? Why do you use a `JOIN` after the `WHERE` clause? — Nico Haase, Jan 31 '19 at 09:02
Possible duplicate of [How can I fix MySQL error #1064?](https://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) — Nico Haase, Jan 31 '19 at 09:02
Your code has multiple errors -- basically, it just makes no sense. You should provide sample data and desired results. — Gordon Linoff, Jan 31 '19 at 12:31

score 1 · Answer 1 · answered Jan 31 '19 at 08:56

mysql does not have full outer join, you can do left join two times or left and right join and union which actually work as like full outer join

select col1..coln from table1 t1 left join table2 t2 on t1.col=t2.col
union
select col1..coln from table2 t2 left join table1 t1 on t1.col=t2.col

or

select col1..coln from table1 t1 left join table2 t2 on t1.col=t2.col
union
select col1..coln from table1 t1 right join table2 t2 on t1.col=t2.col

score 1 · Answer 2 · answered Jan 31 '19 at 08:58

You are using WHERE clause before FULL OUTER JOIN. Try this:

SELECT leaderstbl.lfname                             AS leaders,
       IF(COUNT(vfname) > 1, COUNT(vfname), 0)       AS followers,
       IF(COUNT(household) > 1, COUNT(household), 0) AS household
FROM votersinfotbl
FULL OUTER JOIN leaderstbl ON votersinfotbl.lfname=leaderstbl.lfname
WHERE vleaders IN (SELECT vleaders FROM votersinfotbl WHERE vaddress = 'ABACA')
  AND vleaders NOT IN ('NONE', 'none', 'None', '')
  AND vaddress = 'ABACA'
  AND household IN (SELECT vleaders FROM votersinfotbl WHERE vaddress = 'ABACA')
GROUP BY leaderstbl.lfname
ORDER BY vleaders ASC

How will i correct this SQL/MYSQL FULL Outer Join Problem?

2 Answers2