Sql Join Question

ChicoMendez

hi

a few threads ago a few of ye were talking about maxmind ... so i decided to give it a go.
basically i have created an app that shows dvd's but im setting it up so we can blacklist some countries for certain dvds.

i have a table which stores the blacklisted countries for each dvd _blacklist and of course the _dvd table

i store the users country in a cookie (to cut down on db calls)

on accessing a dvd that has been blacklisted i can detect if teh user can see it or not - no probs there .

anyhow my problem is when im creating the category listing page
here i would only like to be able to show only the dvds that the user can see (ie not blacklisted dvds)

heres my sql query as it stands -

$sql="SELECT f.* FROM #__science as f LEFT JOIN #__blacklist as b ON (b.sid=f.id) WHERE f.cid=".$id." AND b.code <>'".$_COOKIE.
"'";

it dont work though .. it obviously something to do with the join but i cant figure it out
anyone know ?

tnx alot

misterq

try echoing $sql out to page, copy and paste the query into phpmyadmin and run it there.

You will get some descriptive error messages back.

Best way to debug something like that

seamus

misterq wrote:

try echoing $sql out to page, copy and paste the query into phpmyadmin and run it there.

You will get some descriptive error messages back.

Best way to debug something like that

Agreed.

die($sql); has solved many problems for me in the past

ChicoMendez

cheers lads but its more the how do a formulate my query rather than debugging it im looking for

i want to select all DVDs from #__science where the #__blacklist does not have a blacklisted country for the current user ... its actual structuring of teh query that is proving hardship!

seamus - just wanted to say thanks of previous responses to previous posts - u have sorted some of my issues on more than one occasion

ill get back to pulling my hair out!!

tnx

seamus

ChicoMendez wrote:

seamus - just wanted to say thanks of previous responses to previous posts - u have sorted some of my issues on more than one occasion

Nae bother.

OK, you're halfway there.

Left join is a powerful little helper, but it can be tough to get your head around it.

When you LEFT JOIN a table on, this doesn't reduce the number of rows returned in the original table. In your example above, the query will get *all* rows in f that match the WHERE clause, then it will locate all rows in b that match WHERE clauses. Then it matches rows in b against rows in f, based on the condition in ON clause.
The kicker here is that the LEFT JOIN means that all rows in f are returned in the query, even if there is no match in b. If you do a right join, you get the opposite. A normal join should suffice for you here. There are two ways of doing it:

First Way:

$sql="
	SELECT 
		f.* 
	FROM 
		#__science f 
	INNER JOIN 
		#__blacklist b 
		ON 
			(b.sid=f.id) 
	WHERE 
		f.cid=".$id." 
		AND b.code <>'".$_COOKIE['location']."'";

Second Way (Note, no joins):

$sql="
	SELECT 
		f.* 
	FROM 
		#__science f,  
		#__blacklist b 			 
	WHERE 
		f.cid=".$id." 
		AND (b.sid=f.id)
		AND b.code <>'".$_COOKIE['location']."'";

phil

print is probably one of the best debugging tools you can ever use

Also, I realise you're probably talking about the syntax but the second statement has an implicit JOIN. The second query is just a shorthand for the first.

i.e. in any SQL engine I've ever seen, the difference between those two queries is semantic only

ChicoMendez

yes indeed print is a wonderful function but again im not walking about debugging ive done that till im blue in teh fact

seamus
ive tryed that query alright.. thing is i was trying it initially also but it dont bring back the result it should...

ok here it is :


CREATE TABLE `jos_blacklist` (
`id` tinyint(5) unsigned NOT NULL auto_increment,
`code` varchar(50) collate latin1_general_ci NOT NULL default '',
`sid` varchar(250) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=239 ;

INSERT INTO `jos_blacklist` VALUES (234, 'AI', '4');
INSERT INTO `jos_blacklist` VALUES (238, 'MX', '4');


CREATE TABLE `jos_science` (
`id` int(11) NOT NULL auto_increment,
`cid` int(11) NOT NULL default '0',
`tid` int(11) NOT NULL default '0',
`name` varchar(50) collate latin1_general_ci NOT NULL default '',
`year` varchar(4) collate latin1_general_ci NOT NULL,
`flv` varchar(250) collate latin1_general_ci NOT NULL,
`producer` varchar(250) collate latin1_general_ci NOT NULL,
`broadcaster` varchar(250) collate latin1_general_ci NOT NULL,
`runtime` varchar(10) collate latin1_general_ci NOT NULL,
`purl` varchar(250) collate latin1_general_ci NOT NULL,
`psize` varchar(250) collate latin1_general_ci NOT NULL,
`pprice` varchar(10) collate latin1_general_ci NOT NULL,
`pcode` varchar(50) collate latin1_general_ci NOT NULL,
`tvcode` varchar(50) collate latin1_general_ci NOT NULL,
`hdcode` varchar(50) collate latin1_general_ci NOT NULL,
`tvurl` varchar(250) collate latin1_general_ci NOT NULL,
`tvsize` varchar(250) collate latin1_general_ci NOT NULL,
`tvprice` varchar(10) collate latin1_general_ci NOT NULL,
`hdurl` varchar(250) collate latin1_general_ci NOT NULL,
`hdprice` varchar(10) collate latin1_general_ci NOT NULL,
`hdsize` varchar(250) collate latin1_general_ci NOT NULL,
`awards` text collate latin1_general_ci NOT NULL,
`cast` text collate latin1_general_ci NOT NULL,
`description` text collate latin1_general_ci NOT NULL,
`sdescription` text collate latin1_general_ci NOT NULL,
`metakeys` varchar(250) collate latin1_general_ci NOT NULL,
`metadesc` varchar(250) collate latin1_general_ci NOT NULL,
`metatitle` varchar(250) collate latin1_general_ci NOT NULL,
`dvdurl` varchar(250) collate latin1_general_ci NOT NULL,
`dvdname` varchar(250) collate latin1_general_ci NOT NULL,
`dvdimage` varchar(250) collate latin1_general_ci NOT NULL,
`dvdprice` varchar(250) collate latin1_general_ci NOT NULL,
`hits` tinyint(11) NOT NULL,
`rate` varchar(20) collate latin1_general_ci NOT NULL,
`image` varchar(255) collate latin1_general_ci NOT NULL,
`checked_out` tinyint(11) NOT NULL,
`checked_out_time` datetime NOT NULL,
`published` tinyint(1) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=8 ;


INSERT INTO `jos_science` VALUES (4, 5, 3, 'Casino Royale', '2006', '20pre.flv', 'john mcbride', 'Metro-Goldwyn-Mayer (MGM) ', '144', 'wmv', '50', '12.30', '1125', '1126', '', '500.wmv', '345', '20.50', '', '', '', ' Won BAFTA Film Award. Another 4 wins & 16 nominations', '<table border="0"><tbody><tr><td><img src="http://i.imdb.com/Photos/Events/5825/th-DanielCrai_Mazur_12885842_400.jpg&quot; alt=" " width="57" height="90" /> <br /></td><td>as dsalk djaslkj dlksajldsajlsaj sdlaj lsdajkl sda </td></tr><tr><td> <img src="http://ia.ec.imdb.com/media/imdb/01/I/96/65/38m.jpg&quot; alt=" " /></td><td> asdklas ldkja lkjdlkasj lkdasjlkdjsalk jsaljsalkdjlksdajl<br /></td></tr></tbody></table>', '<p class="plotpar"> Casino Royale introduces James Bond before he holds his license to kill. But Bond is no less dangerous, and with two professional assassinations in quick succession, he is elevated to '00' status. Bond's first 007 mission takes him to Uganda where he is to spy on a terrorist, Mollaka. Not everything goes to plan and Bond decides to investigate, independently of MI6, in order to track down the rest of the terrorist cell. Following a lead to the Bahamas, he encounters Dimitrios and his girlfriend, Solange. He learns that Dimitrios is involved with Le Chiffre, banker to the world's terrorist organizations. Secret Service intelligence reveals that Le Chiffre is planning to raise money in a high-stakes poker game in Montenegro at Le Casino Royale. MI6 assigns 007 to play against him, knowing that if Le Chiffre loses, it will destroy his organization. 'M' places Bond under the watchful eye of the beguiling Vesper Lynd. At first skeptical of what value Vesper can provide, Bond's interest in her deepens as they brave danger together and even torture at the hands of Le Chiffre. In Montenegro, Bond allies himself with Mathis MI6's local field agent, and Felix Leiter who is representing the interests of the CIA. The marathon game proceeds with dirty tricks and violence, raising the stakes beyond blood money and reaching a terrifying climax. <em> Written by <a href="http://www.imdb.com/SearchPlotWriters?Krafty">Krafty</a&gt; </em> </p> <p class="plotpar">James Bond is a seductive British secret agent for M:I-6 who has achieved "007" status. After trying to capture a criminal in an embassy, by illegally entering the country, 007's boss 'M' is displeased with his efforts and is unsure if he is the right man for the job. As he continues to follow the trail, the trail leads Bond to the Bahmas to corrupt banker Le Chiffre who is funding international terrorists. After seducing Solange, the beautiful wife of criminal Alex Dimitrios. 007 takes part in a card game in Montengro called 'Casino Royale' where Le Chiffre plans on raising the prize money for terrorist activities. With help from the beautiful and mysterious British government agent Vesper Lynd who is supplying his betting money, field agent Rene Mathis and Undercover CIA agent Felix Leiter, 007 sets out to thwart the corrupt bankers efforts to raise the money, where if Le Chiffre looses, his organization will collapse. Where 007 must uses his instincts and wits as he also deals with attempts on his life, double agents and finds himself falling in love with Vesper. <em> Written by <a href="http://www.imdb.com/SearchPlotWriters?Daniel Williamson&quot;&gt;Daniel Williamson</a> </em> </p> <p class="plotpar">The British Establishment has an ally, in the guise of newly appointed double-00 James Bond. Daniel Craig joins a prestigious line up of iconic cinematic history; James Bond is back with a vengeance. His introduction is a sparing partner of both intelligence and brutality, his mission is to take the ultimate gamble and place his life on the line in the arenas of terrorism and the ivory towers of power. Taking Bond around the world once more but it is in Casino Royale where this battle of wit and cunning will take place, taking no prisoners and leaving nothing but death in his wake. This gripping updated Ian Fleming story is dealt with the expectancy of urgency and drama that when the chips are down this action packed high-octane movie will deliver what England expects. <em> Written by <a href="http://www.imdb.com/SearchPlotWriters?Cinema_Fan">Cinema_Fan</a&gt; </em> </p>', 'In his first mission, James Bond must stop Le Chiffre, a banker to the world''s terrorist organizations, from winning a high-stakes poker tournament at Casino Royale in Montenegro.', 'bond, james, ', 'desc desc desc', 'casion toyale page title', '', '', 'dvd.gif', '20.30', 63, '', 'casino.jpg', 63, '2007-03-27 00:44:47', 1, 0);


NOW WHEN I RUN THE QUERY :

SELECT f. *
FROM jos_science AS f
INNER JOIN jos_blacklist AS b ON ( b.sid = f.id )
WHERE f.cid =5
AND b.code != 'MX'


I GET RETURNED 'CASINO ROYAL' ... now this cant be right as casino royal is blacklisted for Mexico 'MX' ... but the query is still returning it....
does that clarify things any bit ??

tnx alot

seamus

Ah, I see now.

The problem is that there may be multiple rows in the blacklist that correspond to a single row in the article.

You just need a little reversal of logic.

try
Edit - Scratch that it returns a million rows (not literally).

Try this

SELECT f.*
FROM jos_science f 
WHERE f.id NOT IN (
SELECT b.sid
FROM jos_blacklist b
WHERE b.code = 'AB')

Basically what I'm doing first, is getting a list of SIDs that *are* blacklisted for this particular Code (as opposed to trying to find the articles that aren't banned), and then using that to get myself a list of IDs that don't match anything of those SIDs.

I'm not mad about using subqueries. Deep down I always feel that if I'm using subqueries, I just haven't figured out a good enough "normal" query. But that may just be me.

ChicoMendez

excellent thanks!

can you PM me your paypal address - id like to buy you a pint!

seamus

The sentiment is appreciated, but no need. Just HTH

ChicoMendez

ok problem sorted --- UNTIL i play with the app a bit and decide that if i have a film that is shown only in Ireland for example and therefore blacklisted in all the other countries then it means i have to add all the other countries of teh world to the blacklist - what a pain in the arse!

therefore i have created another table

CREATE TABLE `jos_blacknotlist` (
`id` tinyint(5) unsigned NOT NULL auto_increment,
`code` varchar(50) collate latin1_general_ci NOT NULL default '',
`sid` varchar(250) collate latin1_general_ci NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=239 ;

therefore how do i edit the working query

[PHP]SELECT f.*
FROM jos_science f
WHERE f.id NOT IN (
SELECT b.sid
FROM jos_blacklist b
WHERE b.code = 'AB')[/PHP]

to work for my new scenario .... im try im trying but i dont see the light!

Tnx

seamus

Easy enough. Assuming the jos_blacknotlist contains a list of countries for which the film *isn't* blacklisted, then just reverse the logic.
[PHP]SELECT f.*
FROM jos_science f
WHERE f.id IN (
SELECT b.sid
FROM jos_blacknotlist b
WHERE b.code = 'IE')[/PHP]
This will give you a list of all films *not* blacklisted for Ireland.

ChicoMendez

Thats great but how do i merge the two above sql statements like this :


bascially the notblacklisted should take precedence over the blacklisted.

if there are entries in the notblacklisted then it should only be shown for them (we could forget the blacklisted table altogether)

if there are no entries in the notblacklisted then it should check which countries are blacklisted - like our first statement

tnx

ChicoMendez

any ideas on that ?

tnx

seamus

You may need to give me a little more info on what exactly is it that you want to do.

Assuming that what you want to do, ultimately is get a list of movies for a given country, that are blacklisted. If they are in the whitelist, then they are specifically not included in the blacklist (even if they appear in the blacklist). Yes?

That again, should be simple enough, though needs a bit of nesting.
Break it down into its component queries.

First statement. Get a list of IDs that are blacklisted for a particular country:

SELECT 
    b.sid
FROM 
    jos_blacklist b
WHERE 
    b.code = 'IE'

Easy.
Now alter this to get a list of IDs that are blacklisted, except for those that appear in the whitelist:

SELECT 
    b.sid
FROM 
    jos_blacklist b
WHERE 
    b.code = 'IE'
    AND b.sid NOT IN ( SELECT 
                             bn.sid 
                       FROM 
                             jos_blacknotlist bn
                       WHERE
                             bn.code = 'IE'
                      )

The finally, use that last statement to find a list of all movies not blacklisted (just like in your post #11).

SELECT 
    f.*
FROM 
    jos_science f 
WHERE 
    f.id NOT IN (
                 SELECT 
                     b.sid
                 FROM 
                     jos_blacklist b
                 WHERE 
                     b.code = 'IE'
                     AND b.sid NOT IN ( SELECT 
                                              bn.sid 
                                        FROM 
                                              jos_blacknotlist bn
                                        WHERE
                                              bn.code = 'IE'
                                       )
                )

Yes, it's messy

ChicoMendez

hi

as it turns out the server im using uses mysql 4.0 ... so no sub queries arhhhhhh

therefore the issue is this : http://boards.ie/vbulletin/showthread.php?t=2055088286

tnx