Re: Help with Select Statement
От | Nabil Sayegh |
---|---|
Тема | Re: Help with Select Statement |
Дата | |
Msg-id | 40747CFF.2050100@e-trolley.de обсуждение исходный текст |
Ответ на | Help with Select Statement (Kevin Lohka <klohka@aboutfacedata.ab.ca>) |
Ответы |
Re: Help with Select Statement
|
Список | pgsql-novice |
Kevin Lohka wrote: > 1) If the address.people_id field matches the person, use the city and > province values, > > 2) If there is no address record with a matching people_id then use the > default 0 address record values. > > 3) If there is no address record with a matching people_id or the > default 0 then fill address.city, address.province with null values > > I'd only like to have one record returned for each person. Without looking in detail I think you might find the following usefull: Consider the following example tables: CREATE TEMP TABLE foo (foo_id int PRIMARY KEY, foo text); INSERT INTO foo VALUES (1, 'one'); INSERT INTO foo VALUES (2, 'two'); INSERT INTO foo VALUES (3, 'three'); CREATE TEMP TABLE bar (foo_id int REFERENCES foo, bar text); INSERT INTO bar VALUES (1, 'eins'); INSERT INTO bar VALUES (2, 'zwei'); ----------------------------------------------------------- "LEFT OUTER JOIN" SELECT * FROM foo LEFT OUTER JOIN bar USING (foo_id); Row 3 will be filled with NULLs as it doesnt occur in bar. ------------------------------------------------------------ "COALESCE" SELECT coalesce(bar, 'This comes instead of NULL') FROM foo LEFT OUTER JOIN bar USING (foo_id); Whenever bar.bar is NULL it will be replaced by the given value. ------------------------------------------------------------ HTH -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
В списке pgsql-novice по дате отправления: