Results 1 to 13 of 13

Thread: Intersection

  1. #1
    Join Date
    Jan 2007
    Posts
    3

    Unanswered: Intersection

    Hi All
    I am writing a search program using Oracle Database, I used intersect keyword, but unfortunatly, this keyword does not exist in MS Access. intersect keyword, returns the common records from two or multiple queries, I found a counterpart for this keyword in MS Access, which is inner join, but i didn't know how to use it. bellow is the query written in oracle and i need to convert it to MS Access, any help will be appricieated..

    select rd.rep_artittle as [title], rd.ar_ser as [code], rd.rep_code as [rdf]
    from report_definition rd,
    report_words rw,
    key_words kw
    where kw.id = rw.kw_id
    and rd.id = rw.rd_id
    and kw.aword = 'ONE'
    INTERSECT
    select rd.rep_artittle as [title], rd.ar_ser as [code], rd.rep_code as [rdf]
    from report_definition rd,
    report_words rw,
    key_words kw
    where kw.id = rw.kw_id
    and rd.id = rw.rd_id
    and kw.aword = 'TWO'


    Jotnarta

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It looks to me like you don't need to join those two together.
    Wouldn't this give you what you're looking for?

    select rd.rep_artittle as [title], rd.ar_ser as [code], rd.rep_code as [rdf]
    from report_definition rd,
    report_words rw,
    key_words kw
    where kw.id = rw.kw_id
    and rd.id = rw.rd_id
    and kw.aword IN ('ONE','TWO')
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If I remember intersection correctly, you will need to add the distinct keyword to remove duplicates to get an exact equivalence.

    Note that to get the affect of intersect in rdbms's that do not support the intersect keyword, you can inner join on ALL the columns to be viewed (in this case rd.rep_artittle, rd.ar_ser and rd.rep_code) or use a corrolated sub query to the same effect.

    As RedNeck mentioned though - you don't actually need intersect or even pseudo intersect here.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i find it absolutely fascinating that someone would know what INTERSECT is and not know what INNER JOIN is

    absolutely fascinating

    by the way, boys and girls, neither of you are close

    the solution is not IN ('ONE','TWO') , but instead it is those keys for which both 'ONE' and 'TWO' exist

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    the solution is not IN ('ONE','TWO') , but instead it is those keys for which both 'ONE' and 'TWO' exist

    Oops
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Posts
    3

    ('one', 'two')

    I think that IN ('ONE', 'TWO') will not work, cause it will return all records where keyword = 'ONE' and all records when keyword = 'TWO', and that will lead to a duplicate data.

    Intersect returns the common records from several queries

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - RedNeck & I confused made a mistake there.

    You need to join on the three columns as per my first post. Remember to use distinct too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Posts
    3

    Solution

    Thank you Very much, I think that i did find the solution, and that with the help of your notes and reply, following is the solution:

    SELECT rd.rep_artittle AS title, rd.ar_ser AS code, rd.rep_code AS rdf
    FROM report_definition AS rd,
    report_words AS rw,
    key_words AS a
    INNER JOIN key_words AS b ON a.aword=b.aword
    WHERE a.id=rw.kw_id And
    b.id=rw.kw_id And
    rd.id=rw.rd_id And
    a.aword='ONE' And b.aword='TWO';


    Note that to achieve intersection in MS Access, you have to inner join on the columns that will change query results. (i.e, it like creating two instances of table key_words).

    Thank you Very much
    Jotnarta

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    isnt the easier solution to do a pass through query, or am I missing something

    that way round you keep your Oracle syntax & functions, leave the main processing on the corporate server.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Nevermind. Had another question, but figured it out.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here is another way to do it --
    Code:
    select rd.rep_artittle as [title]
         , rd.ar_ser as [c0de]
         , rd.rep_code as [rdf]
      from report_definition rd
    inner
      join report_words rw
        on rw.rd_id = rd.id
    inner
      join key_words kw
        on kw.id = rw.kw_id
     where kw.aword IN ('ONE','TWO') 
    group
        by rd.rep_artittle 
         , rd.ar_ser 
         , rd.rep_code
    having count(*) = 2
    the problem with using the join approach is that for more terms, it becomes exceedingly if not impossibly complex

    whereas by simply counting the rows that exist and filtering the groups with a HAVING clause, you make one simple pass of the data and can handle much complexity

    what if the original requirement was to find those reports which had any 3 of six given keywords? try doing that with a join!

    answer: having count(*) >= 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    p.s. i had to change the 2nd column alias to [c0de] because spelled correctly it is interpreted as a vbulletin tag

    also, i appear to have forgotten once again that ms access needs parentheses if the FROM clause has more than two tables


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by r937
    select rd.rep_artittle as [title]
    , rd.ar_ser as [c0de]
    , rd.rep_code as [rdf]
    from report_definition rd
    inner
    join report_words rw
    on rw.rd_id = rd.id
    inner
    join key_words kw
    on kw.id = rw.kw_id
    where kw.aword IN ('ONE','TWO')
    group
    by rd.rep_artittle
    , rd.ar_ser
    , rd.rep_code
    having count(*) = 2
    Every time I think I'm getting pretty good at SQL, I see something like this that I can't even begin to comprehend
    Inspiration Through Fermentation

Posting Permissions

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