MySQL Forums
Forum List  »  Newbie

LIKE compare two columns
Posted by: Laurence Leach
Date: February 03, 2006 07:13AM

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

Options: ReplyQuote


Subject
Written By
Posted
LIKE compare two columns
February 03, 2006 07:13AM
February 03, 2006 08:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.