Hi all,

I want to retrieve all posts which are not blocked for a particular
country. e.g.

the 3 tables involved are given below

Table post
id int auto_increment (primary)
title varchar(255)
detail text

table country
id int auto_increment (primary)
name varchar(255)

table block_list
id int auto_increment (primary)
post_id - references post primary key
country_id - references country primary key

so - after doing a bit of searching, i ended up with the following
query

select post.* from post left join block_list on (post.id =
block_list.post_id) where block_list.country_id = 1

now if i set block_list.id is null in where clause of the above query
- it will work, but it will simply block all the posts regardless of
any specific country ... which is not the required result.

is there a way to get the required result by making use of the left
join or any kind of join - i'm trying to avoid the use of "NOT IN" and
sub-queries

any help is appreciated.

thanks

On 18-11-10 01:50, mysqlNewbee wrote:
Why are you trying to avoid the use of NOT IN and sub-queries?

How do you get the country for a 'not-blocked' post?



--
Luuk

On 2010-11-18 01:50, mysqlNewbee wrote:
If you have a specific country you want to block, say 1 (what is wrong
with an iso-3166 country code btw?)

select post.*
from post
left join (
select post_id
from block_list
where block_list.country_id = 1
) as x
on post.post_id = x.post_id
where x.post_id is null

You could also try an exists predicate:

select post.*
from post
where not exists (
select 1 from block_list
where post.post_id = block_list.post_id
and block_list.country_id = 1
)

All untested

/Lennart

On Nov 18, 9:19*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Thanks Lennart, i'll try your first example, i guess left join with a
sub-query will still be faster then a NOT IN and sub-query.

On Nov 18, 1:14*pm, Luuk <L... (AT) invalid (DOT) lan> wrote:
Hi Luuk - NOT IN and sub-queries are generally slower then joins and
less efficient. The logic is that any post which is not supposed to be
shown in a particular country will be inserted in the block_list table
others wont - so thats why want to get it in a left join where if a
post record exists in block_list should be not be retrieved whereas
other posts which do not exist in the block_list table given a country
id should be returned..

Besides, this is a simple example i came up with to explain the
problem i am having - the actual data set i'm dealing with is big and
way more complex - in the actual query i have 3 sub-queries
essentially of the same kind and logic as the given example. The query
takes alot of time, i have optimized most of it, now am stuck on
converting these sub-queries into a left join to get a performance
boost ...

On 18-11-10 17:19, Lennart Jonsson wrote:
http://www.statoids.com/w3166use.html

at the bottom it says:
According to good design principles, the ISO code should not be used as
primary key in a table of countries. It would be better to use an
arbitrary, system-generated key. ISO codes change, but the
system-generated key can be kept to mean the same thing forever.


I look at that page just a week ago, and always tought that it was a
good idea to use iso-3166 codes for country info.... ;-)

I dont know the 'authority' of statoids, and if they have any?

--
Luuk

On 18-11-10 18:22, mysqlNewbee wrote:
ok, i see, you're just optimizing your query speed...

--
Luuk

On 2010-11-18 18:14, mysqlNewbee wrote:
You seem petrified by the evil subqueries ;-) My experience with MySql
is limited, but from what I can tell it doesn't do a very good job on
optimization and query rewriting, so I can understand your concern.
Another variant you might want to try is:

select post.*
from post
left join block_list
on post.post_id = x.post_id
and block_list.country_id = 1
where block_list.post_id is null

/Lennart

On 2010-11-18 19:12, Luuk wrote:
Interesting page, thanks for the link. Contrary to most other cookbook
recipes found on the net, they argue quite well for there standpoint.
However I disagree that we should *always* use a surrogate key for
country_code. Stability is one good criteria for a primary key, but it
is not the only one. Often you will end up in situations where different
criteria conflicts, and you will have to choose between them.

/Lennart

On 18-11-10 19:57, Lennart Jonsson wrote:
Indeed,
Because how big a chance is there a country code will change?
I think next change could be Belgium when they split that area between
the Netherlands and France in two parts....

;-)

--
Luuk