Обсуждение: Joins~
This may be just a re-post of my previous mail, but unfortunately, I haven't got any reply whatsoever to this problem, Is the question too easy to answer or what, I couldn't get what it meant. I am posting the problem again, hopefully somebody will be able to give me a solution. ---------------------------------------------------------------------- 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@indvalley.com Life is not fair - get used to it. Bill Gates
On Wed, 2001-11-21 at 09:37, Gurudutt wrote: > 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 maybe it works like this: select * from (<join tickettab and ticketmultab however you want to) as ticketjoined order by ticketjoined.arrivaldate, ticketjoined.arrivaltime let me know if it works. Markus Bertheau
Hello Markus, Doesn't seem to be working, markus , I tried this query -- QUERY --- select tickettab.tokenid, ticketmultab.techcode,ticketmultab.techcode, tickettab.problemstmt,ticketmultab.problemstmt from (tickettab LEFT JOIN ticketmultab ON tickettab.tokenid=ticketmultab.tokenid) as ticketjoinedtab order by ticketjoinedtab.arrivaldate desc, ticketjoinedtab.arrivaltime desc --- END QUERY -- Error I got is ---- ERROR ---- ERROR: Column reference "arrivaldate" is ambiguous ------ END ERROR -- Are there any modifications that I need to do, to the query. Thanks for the reply -- Best regards,Gurudutt mailto:guru@indvalley.com Life is not fair - get used to it. Bill Gates Wednesday, November 21, 2001, 5:27:59 PM, you wrote: MB> On Wed, 2001-11-21 at 09:37, Gurudutt wrote: >> 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 MB> maybe it works like this: MB> select * from (<join tickettab and ticketmultab however you want to) as MB> ticketjoined order by ticketjoined.arrivaldate, ticketjoined.arrivaltime MB> let me know if it works. MB> Markus Bertheau MB> ---------------------------(end of broadcast)--------------------------- MB> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> select tickettab.tokenid, ticketmultab.techcode,ticketmultab.techcode,
> tickettab.problemstmt,ticketmultab.problemstmt
> from (tickettab LEFT JOIN ticketmultab ON tickettab.tokenid=ticketmultab.tokenid)
> as ticketjoinedtab order by
> ticketjoinedtab.arrivaldate desc,
> ticketjoinedtab.arrivaltime desc
Oh, I see, joining is of course false, my fault. must be like this, i
think:
select <fields> from tablea union select <the same fields> from tableb
order by <field>
I tried it with two sample tables on 7.1.3 and it worked:
bert=> select version(); version
-------------------------------------------------------------PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC
2.96
(1 row)
bert=> \d ta Table "ta"Attribute | Type | Modifier
-----------+---------+-----------------------------------------------id | integer | not null default
nextval('"ta_id_seq"'::text)data | text | i | integer |
Index: ta_id_key
bert=> \d tb Table "tb"Attribute | Type | Modifier
-----------+---------+------------------------------------id | integer | default nextval('ta_id_seq'::text)data
| text | i | integer |
bert=> select * from ta;id | data | i
----+------+--- 1 | qwe | 1 2 | ert | 3 3 | tzu | 5
(3 rows)
bert=> select * from tb;id | data | i
----+------+--- 4 | wer | 2 5 | rtz | 4 6 | zui | 6
(3 rows)
bert=> select * from ta union select * from tb order by i;id | data | i
----+------+--- 1 | qwe | 1 4 | wer | 2 2 | ert | 3 5 | rtz | 4 3 | tzu | 5 6 | zui | 6
(6 rows)
Markus Bertheau
Hello Markus,
It worked like anything, Thanks a lot, actually it saved so much of my
code in php, infact a function had been writted to do the same.
Thanks again
--
Best regards,Gurudutt mailto:guru@indvalley.com
Life is not fair - get used to it.
Bill Gates
Friday, November 23, 2001, 1:25:22 PM, you wrote:
>> select tickettab.tokenid, ticketmultab.techcode,ticketmultab.techcode,
>> tickettab.problemstmt,ticketmultab.problemstmt
>> from (tickettab LEFT JOIN ticketmultab ON tickettab.tokenid=ticketmultab.tokenid)
>> as ticketjoinedtab order by
>> ticketjoinedtab.arrivaldate desc,
>> ticketjoinedtab.arrivaltime desc
MB> Oh, I see, joining is of course false, my fault. must be like this, i
MB> think:
MB> select <fields> from tablea union select <the same fields> from tableb
MB> order by <field>
MB> I tried it with two sample tables on 7.1.3 and it worked:
bert=>> select version();
MB> version
MB> -------------------------------------------------------------
MB> PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
MB> (1 row)
bert=>> \d ta
MB> Table "ta"
MB> Attribute | Type | Modifier
MB> -----------+---------+-----------------------------------------------
MB> id | integer | not null default nextval('"ta_id_seq"'::text)
MB> data | text |
MB> i | integer |
MB> Index: ta_id_key
bert=>> \d tb
MB> Table "tb"
MB> Attribute | Type | Modifier
MB> -----------+---------+------------------------------------
MB> id | integer | default nextval('ta_id_seq'::text)
MB> data | text |
MB> i | integer |
bert=>> select * from ta;
MB> id | data | i
MB> ----+------+---
MB> 1 | qwe | 1
MB> 2 | ert | 3
MB> 3 | tzu | 5
MB> (3 rows)
bert=>> select * from tb;
MB> id | data | i
MB> ----+------+---
MB> 4 | wer | 2
MB> 5 | rtz | 4
MB> 6 | zui | 6
MB> (3 rows)
bert=>> select * from ta union select * from tb order by i;
MB> id | data | i
MB> ----+------+---
MB> 1 | qwe | 1
MB> 4 | wer | 2
MB> 2 | ert | 3
MB> 5 | rtz | 4
MB> 3 | tzu | 5
MB> 6 | zui | 6
MB> (6 rows)
MB> Markus Bertheau