Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Distinct / join troubles

    Hi

    I have 3 tables of data

    First table is "Logs" :
    usr src dst date1
    xxx 1.2.3.4 1.5.6.7 2010-08-03 20:27
    xxx 1.2.3.4 1.5.6.7 2010-08-03 20:30
    xxx 1.2.3.4 1.5.6.7 2010-08-03 20:34
    yyy 2.3.4.5 2.6.4.8 2010-09-03 20:45
    zzz 7.8.5.1 2.1.3.4 2010-09-03 20:47
    yyy 2.3.4.5 2.6.4.8 2010-09-03 20:51
    etc...


    second table is "Ip"
    src value
    1.2.3.4 56
    2.3.4.5 79
    7.8.5.1 14
    etc...

    last table is "Country"
    ipstart ipend Cty Cntry Country
    10 25 en eng england
    26 75 us usa united state
    76 81 ch chi china
    etc...


    I need to produce different views :

    First one : the view "user / source / date"
    To do that i do Select usr, src, date1 FROM Logs
    ORDER BY date1 DESC;
    My issue is to get unic results. My solution was to use a DISTINCT but distinct don't work in this case : every entries are different because of the date. So i need to do a distinct on the usr field only with a display of the src and the date.

    Expected output :
    xxx 1.2.3.4 2010-08-03 20:27
    yyy 2.3.4.5 2010-08-03 20:45
    zzz 7.8.5.1 2010-08-03 20:47



    Second one : the view : "user/src/cty/cntry/country"
    The aim is to link the src fields of logs table with country table.
    How it work : each src has a value and we need to link this src to a country : to do that i have the table country who got the fields ipstart et ipend : if the value is between those fields then i need to display the corresponding cty, cntry and country values.
    Here i am really lost.
    A between wont work but i think that with an if condition like if value INF ipend AND IF value SUP ipstart.
    But i have no idea on how to get there.
    And i can create a table from that view to work with it later.

    Expected output
    xxx 1.2.3.4 us usa united state
    yyy 2.3.4.5 ch chi china
    zzz 7.8.5.1 en eng england


    Last aim : a view by country.
    Using the previous table, i need to display a view for the differents countries
    The best would be a statement where i can select the country.
    But it implies completing the previous table.

    Expected output
    China yyy, iii, ooo,
    England zzz, jjj, uuu
    or
    China yyy
    iii
    ooo
    England zzz
    jjj
    uuu

    I'm sorry i'm starting in database but i hope you can help me.
    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what you posted does not make it easy for folks to provide you working SQL solutions.
    Post DDL (CREATE TABLE) for your tables.
    Post DML (INSERT) for sample test data.
    Then folks might be willing to spend time making SQL that works for you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Jurasicbit View Post
    First one : the view "user / source / date"
    To do that i do Select usr, src, date1 FROM Logs
    ORDER BY date1 DESC;
    My issue is to get unic results. My solution was to use a DISTINCT but distinct don't work in this case : every entries are different because of the date. So i need to do a distinct on the usr field only with a display of the src and the date.

    Expected output :
    xxx 1.2.3.4 2010-08-03 20:27
    yyy 2.3.4.5 2010-08-03 20:45
    zzz 7.8.5.1 2010-08-03 20:47
    Looks like a simple aggregation, but your code (probably greatest date), text description (no clue) and expected result (lowest date) differ. Anyway, the expected output could be achieved by this:
    Code:
    SELECT usr, src, MIN(date1)
    FROM Logs
    GROUP BY usr, src;
    Change the aggregation function (MIN), if the requirement is different.
    Quote Originally Posted by Jurasicbit View Post
    Second one : the view : "user/src/cty/cntry/country"
    The aim is to link the src fields of logs table with country table.
    How it work : each src has a value and we need to link this src to a country : to do that i have the table country who got the fields ipstart et ipend : if the value is between those fields then i need to display the corresponding cty, cntry and country values.
    Here i am really lost.
    A between wont work but i think that with an if condition like if value INF ipend AND IF value SUP ipstart.
    But i have no idea on how to get there.
    And i can create a table from that view to work with it later.
    "wont work" is not valid Oracle error message. If data types of are IP.VALUE, COUNTRY.IPSTART and COUNTRY.IPEND are numbers, join should be proceeded correctly with this join condition:
    Code:
    ip.value BETWEEN country.ipstart AND country.ipend
    Quote Originally Posted by Jurasicbit View Post
    Last aim : a view by country.
    Using the previous table, i need to display a view for the differents countries
    The best would be a statement where i can select the country.
    But it implies completing the previous table.

    Expected output
    China yyy, iii, ooo,
    England zzz, jjj, uuu
    or
    China yyy
    iii
    ooo
    England zzz
    jjj
    uuu
    Just curious, where III, OOO, JJJ, UUU came into the result set from as they are not in your sample data.
    Anyway, this looks like another case of using string aggregation technique (at least the first output). It is described e.g. here: http://www.oracle-base.com/articles/...Techniques.php

  4. #4
    Join Date
    Oct 2010
    Posts
    2

    received postraj

    @anacedent : Here are my create table : CREATE TABLE Logs (
    user TEXT,
    src TEXT,
    dst TEXT,
    date1 DATETIME
    );

    CREATE TABLE Ip (
    src TEXT,
    value INTEGER
    );

    CREATE TABLE country (
    ipstart INTEGER,
    ipend INTEGER,
    ctry TEXT,
    cntry TEXT,
    country TEXT
    );

    I know they're isn't no primary key but since i didn't understood totally the concept i didn't try to use it and since it seems to work as it is ...
    But if you think i should use a primary key ( i was thinking on user but not sure ) pleqse tell me.

    @flyboy
    Code:
    Code:
    SELECT usr, src, MIN(date1)
    FROM Logs
    GROUP BY usr, src;
    It works perfect but i have one last issue with it : the result are not ordered i mean i expected it to be ordered by date ( asc or desc ) i tried to add " ORDER BY date1; but it didn't work.

    "
    wont work" is not valid Oracle error message. If data types of are IP.VALUE, COUNTRY.IPSTART and COUNTRY.IPEND are numbers, join should be proceeded correctly with this join condition:
    Code:
    ip.value BETWEEN country.ipstart AND country.ipend
    => my values are numbers so it should work.
    But i still don't know to get my expected output. I tried something like :
    Code:
    select usr, src, cty, cntry, country from logs, country where ip.value BETWEEN country.ipstart AND country.ipend
    but it didn't work and don't feel right.
    What i don't know how to do is to link the src to his value equivalent in the ip database any help ? and how to create a table from this to keep working on for the next question ? use a virtual table ?

    Just curious, where III, OOO, JJJ, UUU came into the result set from as they are not in your sample data.
    Sorry my bad i just add it to show that there could have more results it should have been in sample date.
    After looking to your link i got the problem that i don't know how to use the answer to the previous question : I think i need a table built from the previous question.
    But if i'm not wrong the answer would be :
    Code:
    Column userp
    Selec Country, LISTAGG(user, ',')
    Within group ( ORDER BY user ) AS userp
    FROM *tablefromthepreviousquestion?*
    Group by Country;
    The userp command would be the new name. But i think i still need a table from the previous question

    Still thanks for the answer it makes my brain try to work ^^

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Jurasicbit View Post
    Code:
    Code:
    SELECT usr, src, MIN(date1)
    FROM Logs
    GROUP BY usr, src;
    It works perfect but i have one last issue with it : the result are not ordered i mean i expected it to be ordered by date ( asc or desc ) i tried to add " ORDER BY date1; but it didn't work.
    Once again, "didn't work" is not valid Oracle error message. What about describing the issue or (better) re-posting Oracle response?
    Fortunately, Oracle SQL is documented in SQL Reference book, available e.g. online on http://tahiti.oracle.com/ If you looked on ORDER BY clause of the SELECT statement, you would see it is possible to state column position instead of its expression (and no, DATE1 is not present in SELECT list).
    Quote Originally Posted by Jurasicbit View Post
    "
    => my values are numbers so it should work.
    But i still don't know to get my expected output. I tried something like :
    Code:
    select usr, src, cty, cntry, country from logs, country where ip.value BETWEEN country.ipstart AND country.ipend
    but it didn't work and don't feel right.
    What i don't know how to do is to link the src to his value equivalent in the ip database any help ? and how to create a table from this to keep working on for the next question ? use a virtual table ?
    And again, "didn't work" is not valid Oracle error message. What about describing the issue or (better) re-posting Oracle response?
    I have no idea, what you mean by "virtual table", anyway, the obvious mistake of this SQL is, that it references IP table (IP.VALUE), although it is not present in FROM clause. Maybe you should add it there with proper join condition(s) to LOGS table.
    Quote Originally Posted by Jurasicbit View Post
    Sorry my bad i just add it to show that there could have more results it should have been in sample date.
    After looking to your link i got the problem that i don't know how to use the answer to the previous question : I think i need a table built from the previous question.
    But if i'm not wrong the answer would be :
    Code:
    Column userp
    Selec Country, LISTAGG(user, ',')
    Within group ( ORDER BY user ) AS userp
    FROM *tablefromthepreviousquestion?*
    Group by Country;
    The userp command would be the new name. But i think i still need a table from the previous question
    Yes, if you use Oracle 11gR2 (although you did not post this information, I suppose it is true) as stated in that article.

Tags for this Thread

Posting Permissions

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