can i get some help on JOINS please....

k, so normally i know its bad to come out and ask for straight code...but i am (sort of)...

i've got a old school query:

select 
    mc.id, 
    mc.created, 
    title, 
    body, 
    mu.user_name, 
    mcat.cat_name
from
    mini_content as mc, 
    mini_post as p, 
    mini_user as mu, 
    mini_category_relationship as mcr, 
    mini_category as mcat
where 
    mc.id = p.content_id
and 
    mu.id = mc.user_id
and 
    mcr.post_id = mc.id
and 
    mcr.cat_id = mcat.cat_id

Code (markup):

as you can see there's alot of relationships going on, now before you panic, the above code prints this out:

id    created       title      body                  user_name      cat_name 
--+-------------+-----+-------------------+--------------+----------+
3   2009-01-13   bbl    be back later         Amanda Barry  art
4   2009-01-13   ttyn   talk to you never   Jane Smith      art 
2 rows in set (0.00 sec)

Code (markup):

so my question is...how do i re-write my query using JOINS? (ok...i know this is a very basic 'blog' style table and of course i could of just used one table and been done with it, but i've got another thread going bout that

this is kinda my exercise on how to better use/understand joins

 

select 
    mc.id, 
    mc.created, 
    title, 
    body, 
    mu.user_name, 
    mcat.cat_name
from
   mini_content as mc 
   INNER JOIN mini_post as p ON mc.id = p.content_id 
   INNER JOIN mini_user as mu ON mu.id = mc.user_id
   INNER JOIN mini_category_relationship as mcr ON mcr.post_id = mc.id
   INNER JOIN mini_category as mcat ON mcr.cat_id = mcat.cat_id

Code (markup):

 

thanks for the reply, i was getting lost cause sometimes i see them with '( )' and other times they don't have them, whats the difference?

 

See what with '()' ?

 

"old school query".... hm ... it is not "old scholl" it has another meaning and different results.

 

i picked that up from another, post, thats what someone else referred to it as

 

you basically first need to determine which of the tables used in the joins is your main table from which you want to fetch data.And then use suitable outer joins on other tables(Informix DB).

The above querry can be better and more efficiently written as:

select 
    mc.id, 
    mc.created, 
    title, 
    body, 
    mu.user_name, 
    mcat.cat_name
from
   mini_content mc 
   outer mini_post p  
   outer mini_user mu  
   outer mini_category_relationship mcr  
   outer mini_category mcat 
where mc.id = p.content_id 
    and mu.id = mc.user_id
    and mcr.post_id = mc.id
    and mcr.cat_id = mcat.cat_id

Code (markup):