Sorry my bad, no caffene when I look at your poste and answered, was tunnel
visioned on the WHERE and totaly missed the USING part of th query.
Will experment and see what I get.
Thanks
Rodney
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 10, 2004 9:03 AM
To: CF-Talk
Subject: RE: SQL Help
Bruce, Rodney S HQISEC/Veridian IT Services wrote:
>
> your suggested query below returns 0 records, becuase the field will
> never contain all three at the same time in the same record.
That is not the test being done.
> 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.
WFM even with a subset of the data:
jochemd=> begin;
BEGIN
jochemd=> create table example (uid int, name text, description text,
parent int, proj_id int);
CREATE TABLE
jochemd=>
jochemd=> insert into example values (3, 'Engineering', 'Project in
Eng phase', 1, 100);
INSERT 6060153 1
jochemd=> insert into example values (6, 'Govt', 'Govt ran', 4, 100);
INSERT 6060154 1
jochemd=> insert into example values (8, 'Open', 'Proj is open', 7, 100);
INSERT 6060155 1
jochemd=>
jochemd=> SELECT
jochemd-> proj_ID
jochemd-> FROM
jochemd-> Example T1 INNER JOIN
jochemd-> Example T2 USING (proj_id) INNER JOIN
jochemd-> Example T3 USING (proj_id)
jochemd-> WHERE
jochemd-> T1.Name LIKE '%Open%'
jochemd-> AND
jochemd-> T2.Name LIKE '%Engineering%'
jochemd-> AND
jochemd-> T3.Name LIKE '%Govt%';
proj_id
---------
100
(1 row)
jochemd=> rollback;
ROLLBACK
jochemd=>
Are you sure your database understands USING correctly?
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
