dazed

[riluve]

Sorry, I know this is going to sound fundamental, but I just can't seem to figure it out from the manual or from an online tutorial. I have a field (x) who's value should be a flag to indicate if the value of another field occurs in a selection of a 3rd field (Z). Such that:

X indicates that Y occurs in the set Z.

keep in mind that these fields are nested in a much larger query and Y relate to Z through an INNER JOIN. The basic structure of the entire query is as follows:

Select Count(T1.A), Count(DISTINCT T1.B), ROUND(LOG(T1.C)), X = true if T1.Y occurs in T2.Z
FROM T1
INNER JOIN T2 ON T1.ID = T2.ID
WHERE T1.B BETWEEN D AND E
AND . . . .
AND . . .

So, I am not sure how to assemble this field or even if this is the best 'structure' for what I am trying to do. However, a push in the right direction would be much appriciated.

 

[r937]

which table contains the field X?

and what does "occurs in" mean? can you give examples?

r937.com | rudy.ca

 

[riluve]

X is a field that is purely a derivative of Y and Z.

If Table1 is a list of some action, say Logging in, then Table2 could be a list of people able to Log in.

Y could be the date the action occured. Z could be the birthday of the person that logged in. X would simply indicate if in the selected set of logging in events, did any of then occur on that persons birthday.

In this case, the logic of the original example should be adjusted slightly as follows:

X indicates that Z occurs in the set Y.

X = true if T2.Z occurs in T1.Y

 

[r937]

i asked which table X is in, and you said "X is a field that is purely a derivative of Y and Z"

but Y and Z are different tables, right?

so, does any table actually contain a field called X?

logging in? people able to log in? birthdays?

could you give a more specific example query please?

this is too confusing --

X = true if T1.Y occurs in T2.Z
FROM T1
INNER JOIN T2 ON T1.ID = T2.ID
WHERE T1.B BETWEEN D AND E


r937.com | rudy.ca

 

[riluve]

X = true if entry in Table2.Z occurs in merged set from Table1.Y

Table1
LOGID-PERSON-DATE(Y)
0001--1111---11MAR
0002--1154---11MAR
0003--1102---11MAR
0004--1210---11MAR
0005--1050---12MAR
0006--1111---12MAR
0007--1154---12MAR
0008--1102---12MAR

Table2
PERSON-BDAY(Z)
1111--11MAR
1170--11MAR
1154--20APR
1102--30JAN
1210--12MAR
1050--22JUN

resulting query:
Person TimesLoggedIn LoggedOnBDAY(X)
1111---2---------------Yes
1170---0---------------No
1154---2---------------No
1102---2---------------No
1210---1---------------No
1050---1---------------No

 

[dnoeth]

Untested:

select
t2.person,
count(t1.person) as TimesLoggedIn,
max(case
when t1.date = t2.dbay then 'Yes'
else 'No'
end) as LoggedOnBDay
from table2 as t2
left outer join table1 as t1
on t1.person = t2.person
group by t2.person

Dieter

 

[riluve]

Perfect - sweet, great thnx!