SQL query and string search

Question

I have that database diagram (MySQL). Each Book has assigned a BookLanguage. Each BookLanguage has two different LanguageNames (the globalization purpose). And also, each Book can have an additional BookLanguage (but it's not required).

And here's the problem - I'm stuck here while building a SQL query for searching for that Books, whose have some string in the BookLanguageNames. It's simple to build the query with joining the BookLanguage and BookLanguageNames but how to cope with the AdditionalBookLanguages ?

enter image description here

What data do you want returned? We can't help if we don't know what you're looking for. — sybkar
– sybkar, Commented Nov 7, 2012 at 13:21
I want to get these Books, whose have some string in the Name column in the BookLanguageNames and also (if exist) in the AdditionalBookLanguages — Tony
– Tony, Commented Nov 7, 2012 at 13:23
What is the Code column in the BookLanguage table? Is it necessary? Why is not in the BookLanguageNames or AdditionalBookLanguages? — GregD
– GregD, Commented Nov 7, 2012 at 13:30
it's not normalize, additionalbook,book and booklanguage you have a loop, you should create only one relationship between them — jcho360
– jcho360, Commented Nov 7, 2012 at 13:50

GregD · Accepted Answer · 2012-11-07 14:03:05Z

1

How about simplifying the database design? Have three tables Book, BookLanguageName (both of them similar structure to yours) and, instead of AdditionalBookLanguage and BookLanguage have a "lookup table" - BookLanguage:

Book - here you keep all the information about the books.
BookLanguage - only three columns: BookID, BookLanguageID (or Code) and a boolean is_additional. BookID and BookLanguageID would be a composite primary key.
Language - here you keep all information related to a particular language.

In such case, writing query would be easier.

answered Nov 7, 2012 at 14:03

GregD

2,8773 gold badges31 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tony Over a year ago

sound very interesting, I'll consider that option. Thanks for the idea !

GregD Over a year ago

You're welcome, Tony! Please let us know later what was your final solution.

Collectives™ on Stack Overflow

SQL query and string search

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related