SQL query question

[Joost Kraaijeveld]

Hi all,

I have 2 tables, with a 1-n relation:

parent( oid, parent_name)
child(oid, child_name, iod_parent)

How do I get the parent_names of all parents without a child?

TIA

Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [email]J.Kraaijeveld@Askesis.nl[/email]
web: [url]www.askesis.nl[/url]

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tomasz Myrta]

> Hi all,

>
> I have 2 tables, with a 1-n relation:
>
> parent( oid, parent_name)
> child(oid, child_name, iod_parent)
>
> How do I get the parent_names of all parents without a child?

select parent_name from parent
left join child on (parent.oid=child.iod_parent)
where child.oid is null;

or

select parent_name from parent
where not exists (select * from child where
child.iod_parent=parent.oid);

Regards,
Tomasz Myrta

[Uwe C. Schroeder]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Maybe it's to late for me to think correctly (actually I'm sure of that). I'm
going to ask anyways.
I have a table like

id int4
user_id int4
photo varchar
image_type char(1)

where image_type is either G or X
What I want to do is have ONE query that gives me the count of images of each
type per user_id.
So if user 3 has 5 photos of type G and 3 photos of type X
I basically want to have a result 5,3
It got to be possible to get a query like that, but somehow it eludes me
tonight.

Any pointers are greatly appreciated.

UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFCAdOMjqGXBvRToM4RApgvAJsEUsdl6hrVGqRwJ+NI7J rqQqQ5GgCgkTQN
pavTkx47QUb9nr7XO/r/v5k=
=B3DH
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[Jonel Rienton]

Hi Uwe,

I did a solution for you using PLPgSQL,

create or replace function countem() returns varchar as $$
declare
gcount integer;
xcount integer;
result varchar;
begin
select count(*) into gcount
from pix where image_type = 'G';

select count(*) into xcount
from pix where image_type = 'X';

select gcount || ', ' || xcount
into result;

return result;

end;
$$ LANGUAGE plpgsql;

hope this helps, it's simple and always, there's another (better)
solution
it's my first stab at plpgsql so please bear with me.

-----
Jonel Rienton
[url]http://blogs.road14.com[/url]
Software Developer, *nix Advocate

On Feb 3, 2005, at 1:32 AM, Uwe C. Schroeder wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Maybe it's to late for me to think correctly (actually I'm sure of
> that). I'm
> going to ask anyways.
> I have a table like
>
> id int4
> user_id int4
> photo varchar
> image_type char(1)
>
> where image_type is either G or X
> What I want to do is have ONE query that gives me the count of images
> of each
> type per user_id.
> So if user 3 has 5 photos of type G and 3 photos of type X
> I basically want to have a result 5,3
> It got to be possible to get a query like that, but somehow it eludes
> me
> tonight.
>
> Any pointers are greatly appreciated.
>
> UC
>
> - --
> Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> Phone: +1 650 872 2425 San Bruno, CA 94066
> Cell: +1 650 302 2405 United States
> Fax: +1 650 872 2417
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.3 (GNU/Linux)
>
> iD8DBQFCAdOMjqGXBvRToM4RApgvAJsEUsdl6hrVGqRwJ+NI7J rqQqQ5GgCgkTQN
> pavTkx47QUb9nr7XO/r/v5k=
> =B3DH
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> [email]majordomo@postgresql.org[/email])
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [email]majordomo@postgresql.org[/email])

[Jonel Rienton]

you're right it's late, i better to get to bed myself, i forgot to
throw in the parameter for the user_id in there, i'm sure you can
figure that one out.

regards,

-----
Jonel Rienton
[url]http://blogs.road14.com[/url]
Software Developer, *nix Advocate
On Feb 3, 2005, at 1:32 AM, Uwe C. Schroeder wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Maybe it's to late for me to think correctly (actually I'm sure of
> that). I'm
> going to ask anyways.
> I have a table like
>
> id int4
> user_id int4
> photo varchar
> image_type char(1)
>
> where image_type is either G or X
> What I want to do is have ONE query that gives me the count of images
> of each
> type per user_id.
> So if user 3 has 5 photos of type G and 3 photos of type X
> I basically want to have a result 5,3
> It got to be possible to get a query like that, but somehow it eludes
> me
> tonight.
>
> Any pointers are greatly appreciated.
>
> UC
>
> - --
> Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> Phone: +1 650 872 2425 San Bruno, CA 94066
> Cell: +1 650 302 2405 United States
> Fax: +1 650 872 2417
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.3 (GNU/Linux)
>
> iD8DBQFCAdOMjqGXBvRToM4RApgvAJsEUsdl6hrVGqRwJ+NI7J rqQqQ5GgCgkTQN
> pavTkx47QUb9nr7XO/r/v5k=
> =B3DH
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> [email]majordomo@postgresql.org[/email])
>

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Roman Neuhauser]

# [email]uwe@oss4u.com[/email] / 2005-02-02 23:32:28 -0800:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Maybe it's to late for me to think correctly (actually I'm sure of
> that). I'm going to ask anyways. I have a table like
>
> id int4
> user_id int4
> photo varchar
> image_type char(1)
>
> where image_type is either G or X
> What I want to do is have ONE query that gives me the count of images
> of each type per user_id.
> So if user 3 has 5 photos of type G and 3 photos of type X
> I basically want to have a result 5,3

SELECT COUNT(*) FROM t GROUP BY t.user_id, t.image_type

--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see [url]http://www.eyrie.org./~eagle/faqs/questions.html[/url]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

[url]http://archives.postgresql.org[/url]