I am getting ready to do the third edition of SQL FOR SMARTIES this
month. If anyone has an SQL programming technique, trick or tip that
they would like to see in the book, drop me an email.
You get a virtual beer and your name in the book. Even better, if you
have an improvement on something in
>> I'd like to figure out a way to add simple re-occurrances of
events. I can think of two ways,
[a] Using application logic, create a finite number of future
occurrences --for example, for 10 occurrences, 10 entries into
ftr_cal_events will be created. This seems like an ugly hack. <<
No, not r
>> are you joe celko, guy who wrote those sql books? <<
Yes.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get th
>> The table at hand is more a kind of a collection of graphs where I
want to find all possible paths between a given starting point and a
given end point. <<
For the reachabiity index of a general graph, you need Warshal's
algorithm.
Let V = number of nodes in the graph
Let A[i,j] be the adjacen
>> I've got a table called 'link_t' containing a collection of seller
-
buyer relations between two parties. <<
That is not a real linked list, but let's ignore bad terminology. One
way to do this is with cursors, but they will take time and trend to
be proprietary.
Anohter way is to build a tre
The usual example of a tree structure in SQL books is called an
adjacency list model and it looks like this:
CREATE TABLE Personnel
(emp CHAR(10) NOT NULL PRIMARY KEY,
boss CHAR(10) DEFAULT NULL REFERENCES Personnel(emp),
salary DECIMAL(6,2) NOT NULL DEFAULT 100.00);
Personnel
emp
Please write DDL and not narrative. here is my guess at what you are
trying to do. What you posted was not a table because you had no key.
TEXT is not the datatype to use for names -- unless they are thousand
of characters long!!
Recording age as an integer is useless -- give us the birthday an
Would this give you what you want?
SELECT j.id, j.created, COUNT(mj.mid),
SUM(CASE WHEN ml.state <> 11 THEN 1 ELSE 0 END) AS tally_1,
SUM (CASE WHEN ml.state IN(2,5) THEN 1 ELSE 0 END)AS tally_2
FROM j, mj, ml
WHERE j.fkey = 1
AND mj.jid = j.id
AND ml.jid = j.id;
mining the size of the subset of key
columns within a boundary established by the current row in each table
via those correlated subquery expressions.
The rest is MOD arithmetic; add one to get rid of the 0 in the modulus
cycle, use < and <= in the comparisons to adjust the
