over my head - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

over my head

Author

Topic

riluve
Starting Member

3 Posts

Posted - 2006-02-11 : 02:34:34

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 (y) 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(Table1.A),
Count(DISTINCT Table1.B),
ROUND(LOG(Table1.C)),
X = true if Table1.Y occurs in Table2.Z
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table1.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.

.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-11 : 08:25:06

Sorry.. don't quite understand what you want. Can you post your table's DDL, some sample data and expected result ?

----------------------------------
'KH'
everything that has a beginning has an end

riluve
Starting Member

3 Posts

Posted - 2006-02-11 : 11:21:18

Certainly, well this may clear it up:

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 logged in. 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 them 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

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

.

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-11 : 14:37:07

SELECT Person, COUNT([day]) AS COUNT_Day
FROM Table1
GROUP BY Person

SELECT Person, CASE WHEN BDAY IS NOT NULL THEN 'Yes' ELSE 'No' END AS BDAY_Login
FROM Table1 l LEFT JOIN Table2 r ON l.[day] = r.bday

Just join the two together

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

riluve
Starting Member

3 Posts

Posted - 2006-02-11 : 19:17:46

Perfect - sweet, great thnx!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources