Simple Query With Conditional

[PCHomepage]

The query below is a simple and functional one that is bringing results but it is not bringing results when lc2.BaseCategory is empty as it sometimes is. When it has a value, it must match lc.ID but when it's empty, I still need the other data. I used to be a wiz at this but I'm afraid I've gotten quite rusty so can someone please assist?

SELECT lc2.ID, lc.CategoryName AS BaseCategory, lc2.CategoryName AS SubCategory,
sp.PathName, lc2.SubFolder, lc2.FileName 
FROM lookup_categories lc, lookup_categories lc2, site_paths sp 
WHERE lc.SitePath = sp.ID 
[COLOR=red]AND lc2.BaseCategory = lc.ID[/color] 
GROUP BY lc.CategoryName, SubCategory
ORDER BY lc.CategoryName

 

[vacunita]

How about:

SELECT lc2.ID, lc.CategoryName AS BaseCategory, lc2.CategoryName AS SubCategory,
sp.PathName, lc2.SubFolder, lc2.FileName 
FROM lookup_categories lc, lookup_categories lc2, site_paths sp 
WHERE lc.SitePath = sp.ID 
AND [COLOR=#dd4400][b](lc2.BaseCategory = lc.ID OR lc2.BaseCategory = '')[/b][/color]
GROUP BY lc.CategoryName, SubCategory
ORDER BY lc.CategoryName

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[PCHomepage]

Thank you for the suggestion. I had actually already tried several variations of that but they either bring up too many results (more times than the content of the two tables combined!) or the same result as my posted code. In other words, yours gives the same 21 rows as mine so I'll probably have to rethink the query itself. You may have noticed that it is trying to join to itself which is probably most of the problem.

For example, lookup_categories contains fields:

[bold]ID | CategoryName | BaseCategory | SitePath | SubFolder | FileName[/bold]

with values like:

[bold]5 | Contacts | NULL | 11 | NULL | NULL[/bold]

while a mating row might have:

[bold]24 | Forum | 5 | 3 | forum | NULL[/bold]

In the mating row, the 5 refers to the ID from the Contacts value so the expected result of the query is:

[bold]24 | Contacts | Forum | /path/subfolder/ | forum | NULL[/bold]

and the query is indeed giving it as it should but missing are entries like:

[bold]5 | Contacts | NULL | /path/ | NULL | NULL[/bold]