Re: Joins!!

Hello,

I have few set of questions for the PGSQL list users

Q 1. Consider the following query, suppose I want the sum of the
tickettabcount and ticketmultabcount, how do I modify my query to get the
same. If I use operator "+" in between these, it's been ignored by the
PgSQL and throws a error!!

QUERY
-----
select count(tickettab.tokenid) as tickettabcount,
(select count(tokenid) from ticketmultab
where agentid='danny' and arrivaldate='2001-11-12') as
ticketmultabcount
from tickettab
where tickettab.agentid='danny' and
tickettab.arrivaldate='2001-11-12'

RESULT
-------

tickettabcount || ticketmultabcount
------------- ------------------
9 2

Q 2. I join two tables, I get a result set which has values from
both the tables, now if I want to sort the combination of the result
set how do I do that!!, not the individual table result set.

Hoping for positive reply, thanks!!

--
Best regards,
Gurudutt mailto:guru(at)indvalley(dot)com

Life is not fair - get used to it.
Bill Gates

>
> Q 1. Consider the following query, suppose I want the sum of the
> tickettabcount and ticketmultabcount, how do I modify my query to get the
> same. If I use operator "+" in between these, it's been ignored by the
> PgSQL and throws a error!!
>
> QUERY
> -----
> select count(tickettab.tokenid) as tickettabcount,
> (select count(tokenid) from ticketmultab
> where agentid='danny' and arrivaldate='2001-11-12') as
> ticketmultabcount
> from tickettab
> where tickettab.agentid='danny' and
> tickettab.arrivaldate='2001-11-12'
>
> RESULT
> -------
>
>
> tickettabcount || ticketmultabcount
> ------------- ------------------
> 9 2
>
Don't use the column name aliasing and it should work:
select sum(tickettab.tokenid) +
(select sum(tokenid) from ticketmultab
where agentid='danny' and arrivaldate='2001-11-12')
from tickettab
where tickettab.agentid='danny' and
tickettab.arrivaldate='2001-11-12' ;
>
>
> Q 2. I join two tables, I get a result set which has values from
> both the tables, now if I want to sort the combination of the result
> set how do I do that!!, not the individual table result set.
>
What is so mysterious about your join?
To answer your question, it would be very helpful to see the query
instead of reading prose.
Reagrds, Christoph

Hello Haller,

Thanks for the reply, although I found a peculiar bug in PhpPgAdmin
(Ver: 2.2.1),the web client for postgres. It used to escape the "+" operator
everytime I used it, but then tried the same query in psql, it
worked!!

I have two tables

1. tickettab
2. ticketmultab

Both the tables have arrivaldate and arrivaltime as fields

I want to sort the combination of the result set, like
suppose an entry exists in tickettab as 2001-11-12 12:30
and the next entry in tickettab is 2001-11-12 16:40

if there exists a entry in ticketmultab as 2001-11-12 13.30
then I should get the results as follows

ticketid arrival date arrival time

1 2001-11-12 12:30:00 -- tickettab entry
2 2001-11-12 13:30:00 --ticketmultab entry
3 2001-11-12 16:40:00 -- tickettab entry

so depending on the arrival date and arrival time I need the result
set to be sorted.

I used bubble sort in php by moving the result set into an array and
then sorting it. Is there any other way to do this thru SQL.

--
Best regards,
Gurudutt mailto:guru(at)indvalley(dot)com

Life is not fair - get used to it.
Bill Gates

Wednesday, November 14, 2001, 4:05:24 AM, you wrote:

>>
>> Q 1. Consider the following query, suppose I want the sum of the
>> tickettabcount and ticketmultabcount, how do I modify my query to get the
>> same. If I use operator "+" in between these, it's been ignored by the
>> PgSQL and throws a error!!
>>
>> QUERY
>> -----
>> select count(tickettab.tokenid) as tickettabcount,
>> (select count(tokenid) from ticketmultab
>> where agentid='danny' and arrivaldate='2001-11-12') as
>> ticketmultabcount
>> from tickettab
>> where tickettab.agentid='danny' and
>> tickettab.arrivaldate='2001-11-12'
>>
>> RESULT
>> -------
>>
>>
>> tickettabcount || ticketmultabcount
>> ------------- ------------------
>> 9 2
>>
HC> Don't use the column name aliasing and it should work:
HC> select sum(tickettab.tokenid) +
HC> (select sum(tokenid) from ticketmultab
HC> where agentid='danny' and arrivaldate='2001-11-12')
HC> from tickettab
HC> where tickettab.agentid='danny' and
HC> tickettab.arrivaldate='2001-11-12' ;
>>
>>
>> Q 2. I join two tables, I get a result set which has values from
>> both the tables, now if I want to sort the combination of the result
>> set how do I do that!!, not the individual table result set.
>>
HC> What is so mysterious about your join?
HC> To answer your question, it would be very helpful to see the query
HC> instead of reading prose.
HC> Reagrds, Christoph

HC> ---------------------------(end of broadcast)---------------------------
HC> TIP 3: if posting/reading through Usenet, please send an appropriate
HC> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
HC> message can get through to the mailing list cleanly