Make an optional clause?

[Garethp]

I want to make a search where I have multiple WHERE clauses that are optional. I tried OR, but it doesn't give me what I want. Here's what I have

 

"SELECT members.ID, members.Username, COUNT(series.ID) as Stories FROM members, series WHERE members.Username LIKE '$Input%' AND (series.Author=members.ID OR series.CoAuthor LIKE concat('%;' + members.ID + ';%')) LIMIT $Start, $Limit"

 

The problem is that this excludes members who haven't Authored a series. I want to search ALL members, even if they haven't included a series, but also to fetch the series that they HAVE Authored, IF they have. I want to make it one SQL query so that I can ORDER BY COUNT(series.ID) or by another list of columns which I plan to add later

 

When I tried changing the AND to OR, it gave me the username of anyone who had written a story (I think), but what I want is to fetch series, if and only if they have Authored a series

 

How can I do this?

[Garethp]

Also, a second problem, I've found that adding COUNT(series.ID) only allows returns 1 result. Even though 4 people have Authored stories, only 1 row returns results

[Garethp]

I was thinking of doing something like this, would it work?

 

SELECT DISTINCT(members.ID) as ID, members.Username as Username
FROM members, series 
WHERE members.Username LIKE '$Input%' 
IF COUNT((series.Author=members.ID OR series.CoAuthor LIKE CONCAT ('%;', members.ID, ';%')) THEN AND (series.Author=members.ID OR series.CoAuthor LIKE CONCAT('%;',members.ID,';%')) 
LIMIT 0, 15

 

Would that work, and if so, how would I do it?

[fenway]

So many things.

 

First, you need a left join if you want to find non-matching rows.

Second, a count(*) without a group by will always return just one row.

Third, DISTINCT is not a function.