[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL question

Posted on 2011-10-28
Medium Priority
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
  • 2
LVL 37

Accepted Solution

momi_sabag earned 1600 total points
ID: 37046996
select isbn13
from your_table
group by isbn13
having count(distinct isbn) = 1
 and count(distinct language_code) > 1
LVL 37

Expert Comment

ID: 37046999
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 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 400 total points
ID: 37047018
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.

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

872 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