pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHi there, having problems with the following:
2+ messages / 2 participants
[nested] [flat]
* Hi there, having problems with the following:
@ 2001-03-23 13:10 Jens Philipsen <DNJP@Gyldendal.dk>
0 siblings, 1 reply; 2+ messages in thread
From: Jens Philipsen @ 2001-03-23 13:10 UTC (permalink / raw)
To: pgsql-sql
Hi there, having problems with the following:
On Postgresql (on Linux)
I have 3 tables:
1)employee
firstname
lastname
initials
2)has_title
initials
title
3)has_trade
initials
trade
Now, what I would like is to get a list of all employees with all their titles and trades - employees can have zero or several titles/trades.
For testing purposes I made a version in MS Access, in which this works:
(SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel, null AS fag
FROM medarbejder, har_titel, har_fag
where medarbejder.initialer=har_titel.initialer)
UNION
(SELECT medarbejder.initialer, fornavn, efternavn, telefon, null AS titel, fag
FROM medarbejder, har_fag, har_titel
WHERE medarbejder.initialer=har_fag.initialer)
But in Postgresql no such luck:
SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel as titel, null AS fag
FROM medarbejder, har_titel, har_fag
WHERE medarbejder.initialer=har_titel.initialer
UNION
SELECT medarbejder.initialer, fornavn, efternavn, telefon, null as titel, fag AS fag
FROM medarbejder, har_fag, har_titel
WHERE medarbejder.initialer=har_fag.initialer
Only produces an error:
"unable to transform varchar to unknown
Each UNION | EXCEPT | INTERSECT clause must have compatible target"
Any suggestions?
Thanx in advance
Jens
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Hi there, having problems with the following:
@ 2001-03-26 03:38 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Jens Philipsen <DNJP@Gyldendal.dk>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2001-03-26 03:38 UTC (permalink / raw)
To: Jens Philipsen <DNJP@Gyldendal.dk>; +Cc: pgsql-sql
You'll probably want something like null::varchar (or other appropriate
type) for the nulls. It's trying to grab the type from the first
select and can't figure the type out for the null. (Technically
speaking, it's probably supposed to be CAST(NULL AS VARCHAR) to
be technically sql complient, but hey)
On Fri, 23 Mar 2001, Jens Philipsen wrote:
> Hi there, having problems with the following:
> On Postgresql (on Linux)
> I have 3 tables:
>
> 1)employee
> firstname
> lastname
> initials
>
> 2)has_title
> initials
> title
>
> 3)has_trade
> initials
> trade
>
>
>
> Now, what I would like is to get a list of all employees with all
> their titles and trades - employees can have zero or several
> titles/trades.
>
> For testing purposes I made a version in MS Access, in which this works:
> (SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel, null AS fag
> FROM medarbejder, har_titel, har_fag
> where medarbejder.initialer=har_titel.initialer)
> UNION
> (SELECT medarbejder.initialer, fornavn, efternavn, telefon, null AS titel, fag
> FROM medarbejder, har_fag, har_titel
> WHERE medarbejder.initialer=har_fag.initialer)
>
> But in Postgresql no such luck:
>
> SELECT medarbejder.initialer, fornavn, efternavn, telefon, titel as titel, null AS fag
> FROM medarbejder, har_titel, har_fag
> WHERE medarbejder.initialer=har_titel.initialer
> UNION
> SELECT medarbejder.initialer, fornavn, efternavn, telefon, null as titel, fag AS fag
> FROM medarbejder, har_fag, har_titel
> WHERE medarbejder.initialer=har_fag.initialer
>
> Only produces an error:
> "unable to transform varchar to unknown
> Each UNION | EXCEPT | INTERSECT clause must have compatible target"
>
> Any suggestions?
> Thanx in advance
> Jens
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-03-26 03:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-23 13:10 Hi there, having problems with the following: Jens Philipsen <DNJP@Gyldendal.dk>
2001-03-26 03:38 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox