Can You See the Problem?

Can you see any problems with the query below? The parts in bold sometimes show NULL values when they are not supposed to. And the strange thing is, when I extract the parts that do work, the parts in bold get fixed!

SELECT

tcoupon.anch,
tcoupon.gotourl,
tcoupon.code,
DATE_FORMAT(tcoupon.expdate, '%d %M, %Y') AS formated_date,

tadv.adv,
tadv.advlogo,
tadv.advurl,

tcat.cat,
tcat.caturl,

texpun.expun,

tinstr.instr,
tinstr.blockanch

FROM tcoupon left join (tadv, tcat, texpun, tinstr) on

(tcoupon.advid = tadv.advid AND
tcoupon.catid = tcat.catid AND
tcoupon.expunid = texpun.expunid AND
tcoupon.instrid = tinstr.instrid);

 

This is because there will be no data in tables tadv,tcat and tinstr against texpun table. If you use INNER JOIN instead of LEFT JOIN, all these rows will be eliminated.

 

Hi, Mwasif,

I swapped LEFT JOIN for INNER JOIN and it didn't work. All of the results that I said were being returned as NULL were not returned at all. Any other ideas?

Note that tadv, texpun, tcat and tinstr are all parent tables. tcoupon is the only child table.

 

This is what I told you in my previous reply.

To know how LEFT JOIN works, visit http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
http://dev.mysql.com/doc/mysql/en/JOIN.html

 

Thanks, Mwasif!

However, I read through the material and tried the different joins. Still no joy! I can't seem to select all the data that I want. There are always NULL values where there shouldn't be. Here's the SELECT query again:

SELECT

tcoupon.anch,
tcoupon.gotourl,
tcoupon.code,
DATE_FORMAT(tcoupon.expdate, '%d %M, %Y') AS formated_date,

tadv.adv,
tadv.advlogo,
tadv.advurl,

tcat.cat,
tcat.caturl,

tinstr.instr,
tinstr.blockanch,

texpun.expun

FROM tcoupon left join (tadv, tcat, tinstr, texpun)

USING (advid, catid, instrid, expunid);

Code (markup):

The only thing that seems to work is if I remove one of my tables (texpun) from the query. Can anyone help, please? What could be the problem?

Here's my entire database:

--------------------

tcoupon

id (primary key)
type
date
anch
gotourl
code
expdate
count
expunid (foreign key)
catid (foreign key)
advid (foreign key)
instrid (foreign key)

-------------

tadv

advid (primary key)
adv
advdate
advlogo
advurl

-------------

tinstr

instrid (primary key)
instr
blockanch

-------------

tcat

catid (primary key)
cat
catdate
caturl

-------------

texpun

expunid (primary key)
expun

 

Thanks, guys, but I no longer need help regarding this matter.