Re: [SQL] JOIN question with multiple records
Scott, Casey wrote: I have 2 tables. One containing information about servers, and the other containing information about IP addresses. E.G. Server table: namemac mac2 - SERVER1 00:0d:56:ba:ad:92 SERVER2 00:0d:56:ba:ad:93 00:0d:56:ba:ad:96 SERVER3 00:0d:56:ba:ad:94 SERVER4 00:0d:56:ba:ad:95 00:0d:56:ba:ad:97 I think you've got the design of this table wrong. It looks like you're leaving mac2 NULL where the server has only one network-card. This is wrong - mac2 is not "unknown" it is "card not present" or similar (and the type of the column should then be not mac-address but mac-address-and-not-present). I'm also not sure how you will handle the case when a server has 3 network-cards. Also, if you want to know which server has a specific mac-addr then you'll need to check two columns with your current design. If possible I'd suggest reworking the table to something like: (name, card-id, mac-addr) and you'd then have: SERVER2 0 00:0d:56:ba:ad:93 SERVER2 1 00:0d:56:ba:ad:96 ... Then a crosstab function / case statement can reformat your query output as required. SELECT servers.name,addresses.ipaddr,servers.application_mgr FROM servers LEFT JOIN addresses ON addresses.mac = servers.mac OR addresses.mac = servers.mac2 Well, if you can't change the structure of your tables you could do something like: SELECT ... FROM servers s LEFT JOIN addresses a1 ON s.mac = a1.mac LEFT JOIN addresses a2 ON s.mac = a2.mac The crucial bit is aliasing the "addresses" table twice. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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
Re: [SQL] FOREIGN KEYs ... I think ...
On Thursday 05 January 2006 04:58, [EMAIL PROTECTED] wrote: >That's not what foreign keys do. The only thing a foreign key > provides is a guarantee that if any records in B (the referencing > table) still reference a record in table A (the referenced table) > then you cannot delete that referenced record. Just a little nitpick: A foreign key will also guarantee that you can't insert or update records with an fk in the referencing table that doesn't match one already entered into the referenced table. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] FOREIGN KEYs ... I think ...
Thanks to everyone for the responses ... ended up doing a trigger on the comments table that updates another table to maintain a "pointer" to the active record ... sped up the query that was hampering us from ~26 000ms to 47ms ... the killer part of the query was that each time it was havin gto figure out the 'active remark record' doing a 'max(create_time)' ... On Wed, 4 Jan 2006, Marc G. Fournier wrote: I'm not sure if I'm looking at (for) the right thing or not, but if I am, then I'm not finding any useful examples :( I have two tables, simplified as: CREATE TABLE incident_summary ( id serial, subject text, status boolean ); CREATE TABLE incident_comments ( id serial, incident_id int4, body text, comment_date timestamp, status boolean ); Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed to the same state ... It *looks* like it should be simple enough, I want incident_comments.status to change to incident_summary.status whenever incident_summary.status changes ... since I'm finding nothing searching on FOREIGN KEYS, I'm guessing that I'm looking at the wrong thing ... So, what should I be searching on / reading for this one? Pointers preferred, especially one with some good examples :) Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PostgreSQL and uuid/guid
On Mon, 02 Jan 2006 14:26:14 +0100 Mario Splivalo <[EMAIL PROTECTED]> threw this fish to the penguins: > While browsing the web I've seen that many people 'need' the ability to > create uuid/guid values from within the PostgreSQL. Most of them are > switching from MSSQL, and they're missing the newid() function, which in > MSSQL created the uuid/guid value. > > Now I'm in need of such function too. I'm finaly putting together > MSSQL-like-merge-replication for PostgreSQL, and the guid/uuid values > would come more than handy here. ... > Now, it should be possible to create postgresql function (in python, > forn instance) wich would call the uuidgen program, but that approach > seems rather slow. Since it looks like you are familiar with python, you could try a uuid generator in pure python -- here's one I played with a while ago: (don't know if it's still maintained, but there are others if you search comp.lang.python). http://www.alcyone.com/pyos/uid/ > I'd like to have postgresql function written in C that would call > uuid_generate > (http://www.die.net/doc/linux/man/man3/uuid_generate.3.html). > Considering what is said for the uuidgen manpage (concerning random and > pseudorandom generated numbers) I feel that uuids generated this way are > what I need. > > What is the general feeling concerning the uuid issue I explained above? > I've never written a C postgreSQL function, and any help (or > documentation pointout) would be greatly appreciated. > If I posted this to the wrong mailing list, please point me out to the > correct one. You question is quite welcome here! -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
