LIKE compare two columns
Hello!
I have a issue comparing two columns.
I am using a select query, and here it is:
SELECT project_name AS project, project_manager AS manager, percent_complete AS completed, plcp_phase AS phase, (
SELECT COUNT( * ) FROM meetings WHERE project_name = project) AS meetings,
(SELECT COUNT( * ) FROM filesdocs WHERE product LIKE project) AS files,
(SELECT COUNT( * ) FROM issues WHERE project_name = project) AS issues
FROM projects GROUP BY project");
I have several tables, projects, whehere i obtain the project name, filesdocs, where i am looking for the amount of files for each project, and the same with issues and meetings.
In filesdocs, product name could be "xxx", or "yyy", or any other project name, OR "xxx yyy" (with a space), becuase that file is relavant for TWO projects. Which would mean if i LIKE seach for "%xxx%", then the files with the products named "xxx" AND "xxx yyy" will get returned. Works great if i supply the project name as variable, since i can quote the variable as "%variable%".
However, in the search above where i the seach finds all the different projects in one search, how can i do a LIKE seach with %'s on the column alias 'project'? Although the alias "project" LIKE product works, it does not return the count of files where products is in the form of "xxx yyy", as obviously if the current project in the sql search = "xxx", then "xxx" obviously does NOT equal "xxx yyy" and the count misses a file. But i cannot find a way of using the alias with % % marks encapsulating it.
Can this be done?
Or am i doing this search wrong?
Please ask if there are any questions.
PS, this is the (semi-graphical representation) kind of result I already get:
Project Name: | <...> | # Issues: | # Docs: | # Meetings:
--------------------------------------------------
xxxx ................................. 0............ 10 ................1
yyyy ................................. 1............ 13 ................5
zzzz ................................. 0............ 22 ................3
Files for project yyyy should be 14 and xxxx 11, because one of the products values in filesdocs is "xxxx yyyy", but the query above does not find it, only the products values named "xxxx" or "yyyy".
Laurence