Re: Re[SQL] write multiple joins...
Well indexing (or lack thereof) could be the real problem but you could try "chaining" the tables select * from sale s, taxes t, property p, buyer b where s.id = t.id and t.id = p.id and p.id = b.id and see if that (or other combination) changes the query plan appreciably. (I would have to wonder if all those records are really unique per sale.id? e.g. multiple buyers per unit sold or school and property tax in separate records. And wondering further why, if you have sql access, do you need a single table. Maybe you want a view for convenience (e.g. select * from view_of_sales where lower(county) like 'a%' )? tripplowe wrote: Hey Folks, I have a query that seems like it will never end. I'm hoping you can help me rewrite it. I have 4 tables that contain information about house sales within the state (sale, taxes, property, buyer). Each table has an 'id' field that links each record. I am just trying to flatten these files so I will have all of the information in a single file. Below is my attempt to do so. Is there a more efficient way to rewrite the joins? select * from sale, taxes, property, buyer where sale.id = taxes.id and sale.id = property.id and sale.id = buyer.id I'm using postgresql 8.3 on a Windows 7 machine. Thank You, -Tripp -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using case to select 'which version to use'
Rodrigo E. De León Plicet wrote: > On Thu, Aug 20, 2009 at 10:17 AM, Gerardo Herzig wrote: >> Hi all. I have 2 functions , which returns the same columns, but with >> tottaly different logics. I want to wrap it with a third function, who >> will receive a boolean argument to decide wich sub-function have to use. >> >> I want to use case, so i dont have to use pl/pgsql. >> >> This is a non-working example: >> >> >> CREATE OR REPLACE FUNCTION get_oferta(varchar, varchar, varchar, bool, >> OUT cod varchar , OUT description varchar) >> returns setof record >> security definer >> as >> $$ >> select * from case $4 when true then >>(select * from get_oferta_from_a($1, $2, $3)) >> else >>(select * from get_oferta_from_b($1, $2, $3)) >> end; >> $$ language sql; >> >> Can i use case for a case like this? >> >> Thanks! >> Gerardo > > Try: > > SELECT * > FROM get_oferta_from_a ($1, $2, $3) > WHERE $4 > UNION ALL > SELECT * > FROM get_oferta_from_b ($1, $2, $3) > WHERE NOT $4; > > Well, looks like it will work. I think i found the right syntax now: select foo.* from (select case $4 when false then get_oferta_from_a($1, $2, $3) else get_oferta_from_b($1, $2, $3) end) as foo; Thanks! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] multiple substitution in a single replace call?
Hi all. There is a way to simulate the `pipe' in linux so y can use replace() for replacing 2 different things? Or i just have to call replace two times? Thanks! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
