Re: [SQL] join question

[Manfred Koizar]

On Wed, 27 Nov 2002 09:37:07 -0800, Nathan Young <[EMAIL PROTECTED]>
wrote:
>OK, that works great, but I was told that I should avoid sub-selects when 
>possible for performance reasons.
>>
>> select member.memberId, member.name from member left outer join
>>  (select * from payment where yearPaid=2002) as a using (memberId) where
>>  yearPaid is null;

Nathan,
if you want a version without a subselect, try

SELECT m.memberId, m.name
FROM member AS m LEFT OUTER JOIN
 payment AS p ON p.yearPaid=2002 AND m.memberId=p.memberId
WHERE p.memberId IS NULL;

though I don't know whether it is faster.

Servus
 Manfred

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] retrieving specific info. from one column and locating it in another

[Christoph Haller]

>
> I'm trying to retrieve some info from one column and
> put it in another.  I have a column that has a bunch
> of information in it called 'Route'.  I don't need to
> show all of that information.  Instead I need divide
> that single column into two seperate columns called
> 'Sender' and 'Receiver'.  How do I divide this
> information up into these two columns.  I know of
> methods called charindex and patindex.  I need to do
> something like that but instead of returning the
> position of the info, to just return the selected
> info.
> Ex)  I have a column named Routewith info in it
> similar to 'UPS NS  Ground'
>How do I create a second column called
> 'Delivery' and pull only the 'NS' out of the Route
> column and put it into the 'Reciever' column?
>Similarly how would I pull just the UPS part
> out of Route and put it into 'Sender'?
>

Marc,

I've seen some tricky stuff to split column values yesterday on the
list.
It was sent by Peter Childs Subject  Re: [SQL] Question on SQL and
pg_-tables.

He found something like a split.
Adapted to your needs it would result in something similar to (supposed
blanks are your separators)

SELECT SUBSTRING(route,1,POSITION(' ' IN route)-1) AS ups,
SUBSTRING(route,POSITION(' ' IN route)+1,POSITION(' ' IN
SUBSTRING(route,POSITION(' ' in ROUTE)+1))) AS ns
FROM ...

Hope this helps.

Regards, Christoph




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] SQL query help!

[Arcadius A.]

Hello!

"Luis Sousa" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> This is a cryptographically signed message in MIME format.
>
> --ms080209060900030807050408
> Content-Type: text/plain; charset=us-ascii; format=flowed
> Content-Transfer-Encoding: 7bit
>
> Tell me what did you try with limit and group by.
> Where's IN, why don't you use EXISTS instead. It runs much master !
>



Thanks for the reply!
Alright, I'll use EXISTS instead of IN  I didn't know that EXISTS is
faster.

About my query, I have tried :

 "
SELECT * FROM entry where isapproved='y'  AND EXISTS (SELECT id
FROM subcategory WHERE catid='2') ORDER BY subcatid DESC LIMIT 5;
";
This will return only 5 rows

But when I add the GROUP BY, then I got error
"
SELECT * FROM entry where isapproved='y'  AND EXISTS (SELECT id
FROM subcategory WHERE catid='2') ORDER BY subcatid DESC LIMIT 5 GROUP BY
subcatid;
"

: ERROR:  parser: parse error at or near "GROUP"


Thanks.

Arcadius.




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Analyze + Index

[Rudi Starcevic]

Hi,

Say I have a table of 3000 user's,
Before I insert another user I need to check if that user is already in 
there, so I have a 3 column index on a table ( phone,lastname,firstname ).

First I check for the user using the index if they are not in there I 
then add an new user so I now have 3001.
For my next user I then check again using the same procedure.

Is the following correct ?

As I have not yet Analyzed the db my index is still 3000 rows and my 
user table is 3001.
If I add another user my index is still 3000 and my table 3002.

So I either analyze after each insert or use a seqential scan.

I wonder this as I have a csv file of users to bulk insert each day.
Typically I'll have a file of 1500 or so  users to add or append data to.

Thanks.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Analyze + Index

[Rudi Starcevic]

Hi,

I think I've found what I need to know.
From techdocs.postgresql.org

>> Every you run an INSERT, UPDATE, or DELETE on an indexed table, 
PostgreSQL must update all of the table's indexes to reflect the new 
data (unlike some
>> RDBMSs, PostgreSQL updates indexes immediately so they never get out 
of synch with the data). 

Thanks
Rudi.


---(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