Re: [SQL] more information on JOINs
On Sat, Sep 22, 2001 at 08:11:03AM -0400, postgresql wrote: > Where can I get more information and examples on using JOINs. I > have been reading over the Chapter 2 on from the interactive user > docs but I need a little more in the way of examples. I am having a > problem understanding when I would need a LEFT or RIGHT JOIN. > > Please, a 'point' in the right (no pun intended) direction would be > appreciated. > > Ted > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > end of the original message Take a look at the reference manual, part I "SQL Commands": http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-select.html Francesco Casadei ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] more information on JOINs
Thanks for this reference, I have been here. I guess my problem is that I don't understand a need for a 'join' that is returning NULLs. If I understandJOINs correctly, they are returning columns that contain NULLs. An example please where I would want this result. I have created a job tracking system that includes invoicing, collections reporting, aging. When I first learned to do the INNER JOIN ON, I replaced the processing that I was doing in my client app and let Postgres do it. So now I am examining and studying the other joins. I just can not figure out why I would EVER want one. Which leads me to think that I just don't understand them. Please, if someone has a good example.. Ted [EMAIL PROTECTED] -Original Message- From: Francesco Casadei <[EMAIL PROTECTED]> To: postgresql <[EMAIL PROTECTED]> Date: Sat, 22 Sep 2001 17:40:57 +0200 Subject: Re: [SQL] more information on JOINs > On Sat, Sep 22, 2001 at 08:11:03AM -0400, postgresql wrote: > > Where can I get more information and examples on using JOINs. I > > have been reading over the Chapter 2 on from the interactive user > > docs but I need a little more in the way of examples. I am having a > > problem understanding when I would need a LEFT or RIGHT JOIN. > > > > Please, a 'point' in the right (no pun intended) direction would be > > appreciated. > > > > Ted > > > > > > > > ---(end of > broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > end of the original message > > Take a look at the reference manual, part I "SQL Commands": > > http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-s elect > .html > > Francesco Casadei ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] more information on JOINs
On Sat, 22 Sep 2001, postgresql wrote: > > Thanks for this reference, I have been here. I guess my problem is > that I don't understand a need for a 'join' that is returning NULLs. If I > understandJOINs correctly, they are returning > columns that contain NULLs. > > An example please where I would want this result. I have created a > job tracking system that includes invoicing, collections reporting, > aging. When I first learned to do the INNER JOIN ON, I replaced the > processing that I was doing in my client app and let Postgres do it. > So now I am examining and studying the other joins. I just can not > figure out why I would EVER want one. Which leads me to think that I > just don't understand them. Okay, the reason for left/right joins are for cases like where you have a details table that does not include a row for every parent and you want to get a list of all parents and get detail information for those that have it. Say you have a customer table, and a table with comments about customers. Not every customer has a comment. Now you want to get every customer and their comment if they have one. If you did a query like: select customer.name, comment.text from customer inner join comment using (id); you'd only get those customers that had comments because there is no comment row for the rest of the customers. If you do: select customer.name, comment.text from customer left outer join comment using (id); you'd get all customers, with either their comment or a NULL for the comment text. ---(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 through to the mailing list cleanly
[SQL] confounding, incorrect constraint error
I've hit some really evil nastiness that is either a Postgres 7.1.3 bug,
or signs of early-onset senility for me. I was having trouble with my
database dying while inserting some values, and running some PL/pgSQL.
The schema is as listed below, and I'm getting
psql:fuck.sql:175: ERROR: ExecReplace: rejected due to CHECK constraint
users_logged_in
while inserting values into the uservote table. If I had a few columns to
the users table, postgres crashes instead of giving this (nonsensical)
error.
I'd greatly appreciate any insight, even if it involves a 2x4.
Below is a significantly simplified version of my schema, which exhibits
the above problem.
DROP RULE uservote_update_item_mod;
DROP RULE uservote_delete_item_dec;
DROP RULE uservote_insert_item_inc;
DROP RULE itemvote_update_item_mod;
DROP RULE itemvote_delete_item_dec;
DROP RULE itemvote_insert_item_inc;
DROP FUNCTION mod_node_vote_count(INT4, INT2, INT2);
DROP TABLE uservote;
DROP TABLE itemvote;
DROP TABLE item;
DROP TABLE users;
DROP TABLE node;
DROP SEQUENCE node_id_seq;
CREATE SEQUENCE node_id_seq;
CREATE TABLE node (
node_id INT4 UNIQUE NOT NULL DEFAULT nextval('node_id_seq'),
nameTEXT NOT NULL,
nays INT4 NOT NULL DEFAULT 0
CHECK ( nays >= 0 ),
yays INT4 NOT NULL DEFAULT 0,
CHECK ( yays >= 0 ),
rating INT2 NOT NULL DEFAULT 50
CHECK ( rating >= 0 AND rating <= 100 ),
PRIMARY KEY (node_id)
);
CREATE TABLE users (
node_id INT4 UNIQUE NOT NULL,
email TEXT NOT NULL,
realnameTEXT NOT NULL,
pass_hash VARCHAR(32) NOT NULL,
logged_in INT2 NOT NULL DEFAULT 0
CHECK (logged_in = 0 OR logged_in = 1)
) INHERITS (node);
CREATE TABLE item (
node_id INT4 UNIQUE NOT NULL,
creator_id INT4 NOT NULL
REFERENCES users (node_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
reason TEXT NOT NULL
) INHERITS (node);
CREATE TABLE itemvote (
vote_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
target_id INT4 NOT NULL
REFERENCES item (node_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
user_id INT4 NOT NULL
REFERENCES users (node_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
naysINT2 NOT NULL
CHECK (nays = 0 OR nays = 1),
PRIMARY KEY (user_id, target_id)
);
CREATE TABLE uservote (
vote_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
target_id INT4 NOT NULL
REFERENCES users (node_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
user_id INT4 NOT NULL
REFERENCES users (node_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
naysINT2 NOT NULL
CHECK (nays = 0 OR nays = 1),
PRIMARY KEY (user_id, target_id)
);
-- modifies an items nays/yays count totals as appropriate
-- first arg: item number
-- second arg: 1 or 0, nays or yays.
-- third arg: 1 or 0, add a vote, or remove a vote
CREATE FUNCTION mod_node_vote_count (INT4, INT2, INT2) RETURNS INT2 AS '
DECLARE
node_num ALIAS for $1;
nay_status ALIAS for $2;
add ALIAS for $3;
nay_tot INT4 NOT NULL DEFAULT 0;
yay_tot INT4 NOT NULL DEFAULT 0;
BEGIN
IF add = 1
THEN
IF nay_status = 1
THEN
UPDATE node SET nays = nays + 1 WHERE node_id = node_num;
ELSE
UPDATE node SET yays = yays + 1 WHERE node_id = node_num;
END IF;
ELSE
IF nay_status = 1
THEN
UPDATE node SET nays = nays - 1 WHERE node_id = node_num;
ELSE
UPDATE node SET yays = yays - 1 WHERE node_id = node_num;
END IF;
END IF;
SELECT nays INTO nay_tot FROM node WHERE node_id = node_num;
SELECT yays INTO yay_tot FROM node WHERE node_id = node_num;
IF nay_tot + yay_tot != 0
THEN
UPDATE node SET rating = CEIL( (yay_tot * 100)/(yay_tot + nay_tot) ) WHERE
node_id = node_num;
ELSE
UPDATE node SET rating = 50 WHERE node_id = node_num;
END IF;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
-- vote totalling rules
-- vote insertion
CREATE RULE itemvote_insert_item_inc
[SQL] more information on JOINs
Where can I get more information and examples on using JOINs. I have been reading over the Chapter 2 on from the interactive user docs but I need a little more in the way of examples. I am having a problem understanding when I would need a LEFT or RIGHT JOIN. Please, a 'point' in the right (no pun intended) direction would be appreciated. Ted ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
