Inner Join and Count question

[kiblinger]

My database is named "pugetsound." What I want to do, conceptually:

1. Select all records with PLACE = XYZ.
2. Count how many of those selected records have XWALK = A or B or C.
3. If that is 2 or more, update the EPU_Flag field with a '1' for all those records where PLACE = XYZ.

This almost works but not quite; I get a "syntax error (missing operator)."


Update pugetsound
Set EPU_Flag = 1
From pugetsound
Inner Join (
Select PLACE
From pugetsound
Where PLACE = '01' AND XWALK In ('ESS', 'RSS', 'REM', 'EEM', 'INT')
Group By PLACE
Having Count(*) >= 2) As temp
On pugetsound.PLACE = temp.PLACE

 

[kiblinger]

oops -- #2 Should be, more properly:

2. Count how many of those selected records have
XWALK = ESS or RSS or REM or EEM or INT

 

[Remou]

Do you mean:
[tt]UPDATE pugetsound SET pugetsound.EPU_Flag = 1
WHERE pugetsound.Place In (Select PLACE From pugetsound Where PLACE = '01' AND XWALK In ('ESS', 'RSS', 'REM', 'EEM', 'INT') Group By PLACE
Having Count(*) >= 2)[/tt]

I guess you mean the table is called pugetsound, rather than the database.

 

[kiblinger]

Yep you're right - I meant table. Sorry about that. Awesome.

Now, what if I want to do that operation repeatedly for every set of records that have a distinct PLACE code?

That is, there are say 5 records that are PLACE = '01' -- do that operation to them. Next round up the 7 records that have PLACE = '02-117' and do it to them....

I am guessing SELECT DISTINCT comes into play but I can't see how to get it to run through all the distinct groups (there will be about 800 groups of records based on the PLACE field).

 

[Remou]

What is an indistinct place code?

If you leave out the reference to place in the where clause:

[tt]Select PLACE From pugetsound
Where PLACE = '01' AND
XWALK In ('ESS', 'RSS', 'REM', 'EEM', 'INT')
Group By PLACE
Having Count(*) >= 2[/tt]

You will update all rows with a place count greater than 2:

[tt]PLC XWLK EPU_Flag
01 ess 1
01 rss 1
01 xxx 1
02 xxx
02 xxx
03 ess 1
03 rss 1[/tt]

 

[kiblinger]

That does it. I never learned that "In" syntax correctly but now I have! Thanks!

 

[kiblinger]

OK, new twist:

I want to look at a group of records that share a COMPLEXID. I want to know if at least one record has an XWALK of ESS, RSS, or REM. Then if it has at least one XWALK of EEM, EEM_LOW, LAG-O, LAG-C, LAG/EEM-O, or LAG/EEM-C. Then if it has at least one XWALK = INT. Then, out of those 3 XWALK groups, did at least 2 of them come up "yes"?

So, as an example:

COMPLEXID: 1
EPU_Flag gets set to 0 because:
2 records had XWALK=ESS and 3 records had XWALK=RSS
0 records had XWALK=EEM or EEM_LOW or LAG-O or LAG-C or LAG/EEM-O or LAG/EEM-C
0 records had XWALK=INT


COMPLEXID: 2
EPU_Flag gets set to 0 because:
0 records had XWALK=ESS or RSS or REM
2 records had XWALK=EEM
0 records had XWALK=INT


COMPLEXID 3
EPU_Flag gets set to 1 because:
1 record had XWALK=RSS
1 record had XWALK=EEM_LOW
0 records had XWALK=INT


COMPLEXID 4
EPU_Flag gets set to 1 because:
1 record had XWALK=REM
0 records had XWALK=EEM or EEM_LOW or LAG-O or LAG-C or LAG/EEM-O or LAG/EEM-C
4 records had XWALK=INT


COMPLEXID 5
EPU_Flag gets set to 1 because:
1 record had XWALK=RSS
1 record had XWALK=LAG-C
1 record had XWALK=INT


I tried:

UPDATE pugetsound 
SET EPU_Flag = 1 
WHERE  COMPLEXID In 
(SELECT COMPLEXID FROM pugetsound WHERE
(XWALK In ('ESS', 'RSS', 'REM') GROUP BY COMPLEXID HAVING COUNT(*) >=1 
OR 
XWALK In ('EEM', 'EEM_LOW', 'LAG-O', 'LAG-C', 'LAG/EEM-O', 'LAG/EEM-C') GROUP BY COMPLEXID HAVING COUNT(*) >=1 
OR 
XWALK In ('INT') GROUP BY COMPLEXID HAVING COUNT(*) >=1)

GROUP BY COMPLEXID HAVING COUNT(*) >= 2)

Gives an error but I am close I think.....

 

[PHV]

Typed, untested:

UPDATE pugetsound 
SET EPU_Flag=1 
WHERE  COMPLEXID In (SELECT COMPLEXID FROM (
SELECT DISTINCT COMPLEXID FROM pugetsound WHERE XWALK In ('ESS','RSS','REM')
UNION ALL SELECT DISTINCT COMPLEXID FROM pugetsound WHERE XWALK In ('EEM','EEM_LOW','LAG-O','LAG-C','LAG/EEM-O','LAG/EEM-C')
UNION ALL SELECT DISTINCT COMPLEXID FROM pugetsound WHERE XWALK='INT'
) AS U GROUP BY COMPLEXID HAVING COunt(*)>=2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kiblinger]

That does it. Many thanks!

One question: where can I read more about that "AS U" syntax? I am unfamiliar with what that does.

 

[PHV]

U is an alias for the inline view.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886