CODEWITHSUNDEEP
mysql
Tim
Tim

Reputation: 662

MySql Query with join conditions

Let's assume that I want a list of all my books with the author and his country name. But I only want famous authors or if they are not famous, they must be from country id 123.

SELECT book.id, author.name, country.name
FROM book
LEFT JOIN author ON author.id = book.authorid
LEFT JOIN country ON country.id = author.countryid
WHERE author.famous = 1 OR (author.famous = 0 AND country.id = 123)

This query gives me a list of all the books that have a famous author or from country 123. But I also want the books without an author, so I add "author.id is null or ..."

SELECT book.id, author.name, country.name
FROM book
LEFT JOIN author ON author.id = book.authorid
LEFT JOIN country ON country.id = author.countryid
WHERE (author.id is null or (author.famous = 1 OR (author.famous = 0 AND country.id = 123)))

But here we have a problem, now we have all the books which have a famous author or an author from country 123 AND the books without an author.

But the books that have an author which is not famous and not from country 123 are not in the list. How can I manage this in 1 query? Or is this impossible and do I need a subquery?

I should have join conditions over multiple tables, but that's impossible.

Thanks!

Edit: Just to make sure everyone understands the problem. At the end I want a list of all my books, and next to the books I want the information of the author but only if the author is famous or if he's from countryid 123.

My queries had a mistake so here is a better query

SELECT book.id, author.name, country.name
FROM book
LEFT JOIN author ON author.id = book.authorid
LEFT JOIN country ON country.id = author.countryid
WHERE author.id is null OR author.famous = 1 OR country.id = 123

But with this query I still don't get the books with an non-famous author from outside countryid 123. I want these books in the list without author information next to it.

So I don't want to join the book with the author when the author is not famous and not from countryid 123!

Upvotes: 0

Views: 178

Answers (4)

Felquir
Felquir

Reputation: 441

Try this query, you get any book with :

SELECT book.id, if(author.famous <> 0 or country.id = 123,GROUP_CONCAT(author.*),null), country.name
FROM book
LEFT JOIN author ON author.id = book.authorid
LEFT JOIN country ON country.id = author.countryid

Thanks

Upvotes: 0

Richard Raby
Richard Raby

Reputation: 106

Assuming an author is famous when author.famous = 1 and not famous when it is 0, your first set of where conditions looks wrong, it looks to currently filter to only famous authors regardless of country, it should be:

WHERE author.famous = 1 OR country.id = 123

If the final data set you are looking for is the combined:

  • All books from famous authors (regardless of country)
  • All books from country id is 123 (regardless of fame)
  • All books without authors (regardless of country)

Your condition would be:

WHERE author.famous = 1 OR country.id = 123 OR author.id is null

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

Please have a try with this query:

SELECT book.id, author.name, country.name
FROM book
LEFT JOIN author ON author.id = book.authorid
LEFT JOIN country ON country.id = author.countryid
WHERE author.id is null or author.famous = 1 or (author.famous = 0 AND country.id = 123)

Also I changed 

or (author.famous = 1 AND country.id = 123)

to

or (author.famous = 0 AND country.id = 123)

because of

or if they are NOT famous, they must be from country id 123

Actually it's not much different from your query, I just omitted obsolete parantheses, but I don't see, why it shouldn't work. Are you sure, there are actually books without an author in your database?

Upvotes: 1

Itay
Itay

Reputation: 16777

First of all, you have a mistake on your first query. Here author.famous = 1 OR (author.famous = 1 AND country.id = 123) you're getting only books that their author is famous AND from 123. Maybe you meant author.famous = 1 OR (author.famous = 0 AND country.id = 123), but there's no use for this also. Two conditions are enough.

You can just write:

SELECT book.id, author.name, country.name
FROM book
LEFT JOIN author ON author.id = book.authorid
LEFT JOIN country ON country.id = author.countryid
WHERE (author.famous = 1 OR country.id = 123)

For the second query, try this:

SELECT book.id, author.name, country.name
FROM book
LEFT JOIN author ON author.id = book.authorid
LEFT JOIN country ON country.id = author.countryid
WHERE (author.id is null OR author.famous = 1 OR country.id = 123)

Upvotes: 1

Related Questions