Gangashman Gangashman - 4 months ago 9
SQL Question

I need to add an option "where" to my sql request

I need to added option "where" to "party" selection.

This is my working code:

SELECT p.id, (
SELECT COUNT( * )
FROM accounts
WHERE party = p.id
) AS u_count, p.title, p.slogan, p.owner, u.nickname, p.candidat, c.nickname, p.register_date
FROM party p
LEFT OUTER JOIN accounts u ON p.owner = u.id
LEFT OUTER JOIN accounts c ON p.candidat = c.id


This case is not working: (with WHERE confirmed = 1)

SELECT p.id, (
SELECT COUNT( * )
FROM accounts
WHERE party = p.id
) AS u_count, p.title, p.slogan, p.owner, u.nickname, p.candidat, c.nickname, p.register_date
FROM party p WHERE confirmed = 1
LEFT OUTER JOIN accounts u ON p.owner = u.id
LEFT OUTER JOIN accounts c ON p.candidat = c.id

Answer

Syntax must be select ... from .. join ... on ... where ...

SELECT p.id, (
SELECT COUNT( * ) 
FROM accounts
WHERE party = p.id
) AS u_count, p.title, p.slogan, p.owner, u.nickname, p.candidat, c.nickname, p.register_date
FROM party p LEFT OUTER JOIN accounts u ON p.owner = u.id
LEFT OUTER JOIN accounts c ON p.candidat = c.id
WHERE confirmed = 1