FAQ
I am scrathing my head to write a SQL query to get this information:

I have two tables t1 and t2 with these values

Table t1

Col1 Col2
------ ------
1 One
2 Two
3 Three

Table t2

Col1 Colx
------ -------
1 Xone
2 Xtwo
2 Xtwotwo
3 XThree

I need to select Col2 from Table t1 and Colx from Table t2 with only
distinct values for Col1.

Here is what I am expecting

t1.Col2 t2. Colx
---------- -----------
One Xone
Two Xtwo
Three XThree

In t2.Colx value Xtwotwo is also acceptable as I need only one distinct
value from t1.Col2 and don't care for
whatever shows up under t2.Colx.

Any suggestions?

Thanks,
John

Search Discussions

  • Harel Safra at Feb 24, 2011 at 7:09 pm
    Something like this should work (haven't run it though):

    select t1.Col2, min(t2.Colx)
    from t1, t2
    where t1.col1 = t2.col1
    group by t1.Col2;

    Harel Safra
    On Thu, Feb 24, 2011 at 8:56 PM, john scott wrote:

    I am scrathing my head to write a SQL query to get this information:

    I have two tables t1 and t2 with these values

    Table t1
    ======
    Col1 Col2
    ------ ------
    1 One
    2 Two
    3 Three

    Table t2
    =======
    Col1 Colx
    ------ -------
    1 Xone
    2 Xtwo
    2 Xtwotwo
    3 XThree

    I need to select Col2 from Table t1 and Colx from Table t2 with only
    distinct values for Col1.

    Here is what I am expecting

    t1.Col2 t2. Colx
    ---------- -----------
    One Xone
    Two Xtwo
    Three XThree

    In t2.Colx value Xtwotwo is also acceptable as I need only one distinct
    value from t1.Col2 and don't care for
    whatever shows up under t2.Colx.

    Any suggestions?

    Thanks,
    John
    --
    http://www.freelists.org/webpage/oracle-l
  • John scott at Feb 24, 2011 at 7:18 pm
    Thanks guys. Now it looks so trivial.

    Forwarded message ----------
    From: Harel Safra
    Date: Thu, Feb 24, 2011 at 2:09 PM
    Subject: Re: Another SQL query help
    To: joscott2011.mail_at_gmail.com
    Cc: oracle-l@freelists.org

    Something like this should work (haven't run it though):

    select t1.Col2, min(t2.Colx)
    from t1, t2
    where t1.col1 = t2.col1
    group by t1.Col2;

    Harel Safra
    On Thu, Feb 24, 2011 at 8:56 PM, john scott wrote:

    I am scrathing my head to write a SQL query to get this information:

    I have two tables t1 and t2 with these values

    Table t1
    ======
    Col1 Col2
    ------ ------
    1 One
    2 Two
    3 Three

    Table t2
    =======
    Col1 Colx
    ------ -------
    1 Xone
    2 Xtwo
    2 Xtwotwo
    3 XThree

    I need to select Col2 from Table t1 and Colx from Table t2 with only
    distinct values for Col1.

    Here is what I am expecting

    t1.Col2 t2. Colx
    ---------- -----------
    One Xone
    Two Xtwo
    Three XThree

    In t2.Colx value Xtwotwo is also acceptable as I need only one distinct
    value from t1.Col2 and don't care for
    whatever shows up under t2.Colx.

    Any suggestions?

    Thanks,
    John
    --
    http://www.freelists.org/webpage/oracle-l
  • Guillermo Alan Bort at Feb 24, 2011 at 7:41 pm
    maybe something like this:
    select distinc(col1),colx from (
    select a.col1 col1, a.col2 col2, b.col1 bcol1, b.colx colx
    FROM t1 a join t2 b on a.col1 = b.col1)

    (haven't really tested it... just thinking out loud) and annoying column
    names, btw.
    Alan.-
    On Thu, Feb 24, 2011 at 3:56 PM, john scott wrote:

    I am scrathing my head to write a SQL query to get this information:

    I have two tables t1 and t2 with these values

    Table t1
    ======
    Col1 Col2
    ------ ------
    1 One
    2 Two
    3 Three

    Table t2
    =======
    Col1 Colx
    ------ -------
    1 Xone
    2 Xtwo
    2 Xtwotwo
    3 XThree

    I need to select Col2 from Table t1 and Colx from Table t2 with only
    distinct values for Col1.

    Here is what I am expecting

    t1.Col2 t2. Colx
    ---------- -----------
    One Xone
    Two Xtwo
    Three XThree

    In t2.Colx value Xtwotwo is also acceptable as I need only one distinct
    value from t1.Col2 and don't care for
    whatever shows up under t2.Colx.

    Any suggestions?

    Thanks,
    John
    --
    http://www.freelists.org/webpage/oracle-l
  • Asif Momen at Feb 27, 2011 at 7:57 am
    Hi John,

    Here are my two cents:

    query 1:
    select t1.col2, t2.colx
      from t1, t2
     where t1.col2 = Initcap(substr(t2.colx, 2));

    query 2:
    select t1.col2, b.colx
      from t1, (select col1, min(colx) colx from t2 group by col1) b
     where t1.col1 = b.col1;

    query 3:
    select t1.col2, b.colx
      from t1, (select col1, max(colx) colx from t2 group by col1) b
     where t1.col1 = b.col1;

    Regards
    Â
    Â
    Asif Momen
    Oracle ACEÂ
    Speaker at Oracle OpenWorld-2010
    Member, Editorial Board, Oracle Connect, All India Oracle User Group
    Oracle Certified Professional  10g, 9i
    Oracle Blogger at – http://momendba.blogspot.com

    ________________________________
    From: john scott
    To: oracle-l@freelists.org
    Sent: Thu, February 24, 2011 9:56:35 PM
    Subject: Another SQL query help

    I am scrathing my head to write a SQL query to get this information:

    I have two tables t1 and t2  with these values

    Table t1
    ======
    Col1        Col2
    ------         ------
    1             One
    2             Two
    3             Three

    Table t2
    =======
    Col1       Colx
    ------        -------        Â
    1            Xone   Â
    2            Xtwo    Â
    2            Xtwotwo       Â
    3            XThree

    I need to select Col2 from Table t1 and Colx from Table t2 with only distinct
    values for Col1.

    Here is what I am expecting

    t1.Col2           t2. Colx
    ----------           -----------
    One               Xone
    Two               Xtwo            Â
    Three            XThree

    In t2.Colx value Xtwotwo is also acceptable as I need only one distinct value
    from t1.Col2 and don't care for
    whatever shows up under t2.Colx.

    Any suggestions?

    Thanks,
    John

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 24, '11 at 6:56p
activeFeb 27, '11 at 7:57a
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase