[SQL] LEFT JOIN on one and/or another column
Hi. I have a table with two foreign keys (1 field each), like in id| serial ext_key_original | integer ext_key_exception | integer They mean different things, as one refers to a typical value, and the other one refers to an exception that applies for that tuple. Each key references a different table, one with typical data, and one with exception data. I'd like to join on the original, except when exception <> NULL, but I can't quite figure out how to do so. Can anyone help? Thanks you. :-) Octavio. -- Octavio Alvarez. E-mail: [EMAIL PROTECTED] Agradezco que sus correos sean enviados siempre a esta dirección. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] LEFT JOIN on one and/or another column (thanks)
Yes, Bruno. Thank you very much. That's what I was looking for, but since I hadn't used CASE nor COALESCE, I didn't know it was easier that way. The idea is that on a column I have info about a routine task, and in another one I have info about human-made changes to the time of that task, like assigning a different task, or moving it to another moment, and the query I'm trying to make should answer what tasks should be done now. Thank you. Bruno Wolff III said: > On Wed, Jan 28, 2004 at 20:27:00 -0800, > Octavio Alvarez <[EMAIL PROTECTED]> wrote: >> >> Hi. I have a table with two foreign keys (1 field each), like in >> >> id| serial >> ext_key_original | integer >> ext_key_exception | integer >> >> I'd like to join on the original, except when exception <> NULL, but I >> can't quite figure out how to do so. > > I am not completely sure from your description what exactly you are trying > to do, but it sounds like you can left join your table to the two foreign > key tables and then use coallesce to grab the value from the appropiate > joined table. -- Octavio Alvarez. E-mail: [EMAIL PROTECTED] Agradezco que sus correos sean enviados siempre a esta dirección. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [PERFORM] Seq scan on zero-parameters function
Tomasz Myrta said: > Dnia 2004-02-06 09:43, U¿ytkownik Octavio Alvarez napisa³: >> Thanks for the hint. >> >> In fact, my current_period_id() is based on time, but it should be >> constant along the query execution. I mean, I don't want some records >> filtered with some values and other with other values... I'll have an >> uncongruent recordset. > > Well - you didn't read the chapter I noticed you, did you? Huummm.. No... :-$ But now I did. Although the chapter makes it look as "how will the optimizer think the function behaves", not "how the function actually behaves". But thanks. It's a lot clearer now. I assume that if I want to make "timeofday" have a stable-behavior, I must enclose it in a sub-query. Am I right? -- Octavio Alvarez. E-mail: [EMAIL PROTECTED] Agradezco que sus correos sean enviados siempre a esta dirección. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL]
Sorry, I tried to make my subject as good as possible. I have a table where I store the dates in which I take out of my inventory (like "installation dates") table --- row_id SERIAL date DATE fk_item INTEGER and that's pretty much it. I want to have a query returning how long have been certain items lasting. Say I have: SELCT date FROM table WHERE fk_item = "INKJET_INK-BW" date - 2005-02-02 2005-03-05 2005-04-07 2005-05-02 I need something to calculate the intervals between those dates, like this: intervals (in days) 31 34 25 So I can get the stddev and stuff from the "duration" of the items. I've been having a hard time with it. I'm trying NOT to program new functions. Any help will be appreciated. --Octavio -- Ing. Octavio Alvarez Piza, Jefe de Informática, TBC Universidad. Tel.: +52 (664) 621-7111 ext. 133; E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
