SQL question

Posted on 2011-10-28
Last Modified: 2012-05-12
I have a file keyed by isbn13

lang code and isbn are attribute fields,

now I want to select those isbn13 who have the same isbn but different language code
Question by:Rozamunda
    LVL 37

    Accepted Solution

    select isbn13
    from your_table
    group by isbn13
    having count(distinct isbn) = 1
     and count(distinct language_code) > 1
    LVL 37

    Expert Comment

    or another approach (which might perform better)

    select t1.isbn13
    from your_table t1 join your_table t2
     on t1.isbn13 = t2.isbn13
      and t1.isbn = t2.isbn
     and t1.language_code > t2.language_code
    LVL 45

    Assisted Solution

    Hi Rozamunda,

    We probably need to know a bit more about your data, but it sounds like you want to compare multiple rows and find items with more than 1 language?

    A self join should do it.

    SELECT *
    FROM mytable t0
    INNER JOIN mytable t1
      ON t0.ISBN13 = t1.ISBN13
    WHERE t0.language < t1.language;

    If you have 3 or more languages for a particular item, they will show on multiple lines.  It'll take more complex SQL to show them on a single line.

    Good Luck,

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now