I am not SQL expert but maybe you could create a view that would be a
cross-tab of your table so that the view contains only on record for each
project based on the project id.
So that the view would contain:
Project ID, Status, Type, Condition, .......................
and then you could just:
SELECT * from view
WHERE status= 'Engineering'
AND type = 'Contractor'
AND condition = 'Open'
That's about the best that I could think of right now.
Mario
-----Original Message-----
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 12:12 PM
To: CF-Talk
Subject: RE: SQL Help
How about:
WHERE (name LIKE '%Open%' or name LIKE '%Engineering%') AND name NOT LIKE
('%closed%')
Personally, this is probably not the best way to handle status. I always
have a status field - either an int or a small character field and use a
constraint
('open','closed','pending') and always be assured of a proper status.
-Mark
gets me projects that are closed as well as open
-----Original Message-----
From: Bruce, Rodney S HQISEC/Veridian IT Services
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 10:41 AM
To: CF-Talk
Subject: SQL Help
Hi all
I need some help with a query that supplies a report.
The issue for me is one of the tables: (simplified)
Table
UID Name Description Parent Project_id
This table contains different information in the same field (name),like the
projects status (Initializing, Engineering, etc) , the type of project
(Contractor, Govt), and condition (Open, Closed).
So projects can have 1, 2 or 3 records in this table.
The problem is if the user wants a report of all Open-Engineering-Govt
projects.
Name is a Text field.
So I can get all the open with:
WHERE name LIKE '%Open%'
(needs to be LIKE because the information is not consistant, ie: Open, is
Open, Opened, etc).
But
WHERE name LIKE '%Open%' and name LIKE '%Engineering%'
gets me nothing becuase the field cant be both at same time
and
WHERE name LIKE '%Open%' or name LIKE '%Engineering%'
gets me projects that are closed as well as open
I can get the information I need by running mulitple queries:
<CFQUERY name="first">
WHERE name LIKE '%Open%'
</CFQUERY>
<CFOUTPUT query="first">
<CFQUERY name="second">
WHERE name LIKE '%Engineering%' and project_ID =
#first.project_id#
</CFQUERY>
<CFIF second.recordcount GT 0>
is both opened and Engineering.
</CFIF>
</CFOUTPUT>
But this is cumbersome.
anyone know a better way?
We are using: CF5 and SQL 2000.
Thanks
Rodney
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
