your suggested query below returns 0 records, becuase the field will never
contain all three at the same time in the same record. the table looks
something like this:
(example only ;o)
UID Name Description Parent
Proj_ID
1 Status current status of project 0
0
2 Initializing Project is starting 1
100
3 Engineering Project in Eng phase 1
100
4 Type Who is doing Proj 0
0
5 Contr Contracted out 4
100
6 Govt Govt ran 4
100
7 Condition Proj open or close 0
0
8 Open Proj is open 7
100
9 closed Proj is closed 7
100
So if you ask for name to be LIKE Init and Contr and Open, 0 records
returned
I can get the three records by going for project ID, but have not had a
chance to run thru the suggections given by others.
Just have tried your suggections already so know what happens.
Thanks help
Rodney
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 11:32 AM
To: CF-Talk
Subject: RE: SQL Help
Ian Skinner said:
> My first suggestion would be an embed sub-query.
That would work if you add another level for the "Govt" condition.
> SELECT
> fields
>
> FROM
> Table.
> ( SELECT
> fields
>
> FROM
> TABLE
>
> WHERE
> Name LIKE "%Engineering%"
> ) AS FooBar
>
> WHERE
> FooBar.ID = Table.ID
> AND Name LIKE "%Open%"
>
> Of course this assumes you are using a DBMS that allows sub-queries.
> I understand that not all of them do.
It is pretty trivial to rewrite as a self-join. I adapted thee names
of the original problem:
SELECT
project_ID
FROM
Table T1 INNER JOIN
Table T2 USING (project_id) INNER JOIN
Table T3 USING (project_id)
WHERE
T1.Name LIKE '%Open%'
AND
T2.Name LIKE '%Engineering%'
AND
T3.Name LIKE '%Govt%'
Jochem
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
