Professional Web Applications Themes

SQL query question - PostgreSQL / PGSQL

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.KraaijeveldAskesis.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...

  1. #1

    Default SQL query question

    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.KraaijeveldAskesis.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

    Joost Kraaijeveld Guest

  2. #2

    Default Re: SQL query question

    > 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
    Tomasz Myrta Guest

  3. #3

    Default SQL query question

    -----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]majordomopostgresql.org[/email])

    Uwe C. Schroeder Guest

  4. #4

    Default Re: SQL query question

    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]majordomopostgresql.org[/email])
    >

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

    Jonel Rienton Guest

  5. #5

    Default Re: SQL query question

    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]majordomopostgresql.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

    Jonel Rienton Guest

  6. #6

    Default Re: SQL query question

    Am Donnerstag, 3. Februar 2005 08:32 schrieb Uwe C. Schroeder: 

    select user_id,image_type, count(id)
    from <table>
    group by user_id,image_type

    should do it.

    --
    Markus Schulz

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to org

    Markus Guest

  7. #7

    Default Re: SQL query question

    # [email]uweoss4u.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]

    Roman Neuhauser Guest

Similar Threads

  1. Query Question Please HELP
    By flooker in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 26th, 04:45 PM
  2. Query Question
    By Jason MacKenzie in forum Coldfusion - Advanced Techniques
    Replies: 51
    Last Post: November 18th, 04:58 AM
  3. SOS! Query Question
    By design in progress in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 11th, 05:39 AM
  4. 6.1 Query of Query Question. Ref to own Col
    By smcgovern in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: April 24th, 10:17 PM
  5. ASP SQL query question
    By Brandon in forum ASP Database
    Replies: 6
    Last Post: May 3rd, 04:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139