Selecting from joined tables

[youradds]

Hi,

I'm trying to do a "join" kinda thing (relation) with a query. The table structures are:

CREATE TABLE IF NOT EXISTS `glinks_Advert_Relations` (
`ID` int(11) NOT NULL,
`CatName` char(255) NOT NULL,
KEY `CatName` (`CatName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `glinks_Adverts` (
`ID` int(11) NOT NULL auto_increment,
`Image` char(255) NOT NULL default '',
`AdType` char(255) NOT NULL,
`WhereToShow` char(255) NOT NULL,
`Username` char(255) NOT NULL,
`HitCredit` float default NULL,
`HitPrice` float default NULL,
`ImpressionsLeft` int(11) default NULL,
`ImpressionUsed` int(11) default NULL,
`ExpirationMonth` char(20) default NULL,
`isActive` char(5) NOT NULL,
`AreasToShow` char(255) NOT NULL,
`DestinationURL` char(255) default NULL,
`FullCatList` longtext,
`NEWS` int(11) default NULL,
`ARTICLES` int(11) default NULL,
`VIDEO` int(11) default NULL,
`PINUP` int(11) default NULL,
`CONTESTS` int(11) default NULL,
`ALL_LISTINGS` int(11) default NULL,
`isDefault` int(11) NOT NULL default '0',
`ALLAREAS` int(11) default '0',
PRIMARY KEY (`ID`),
KEY `isactive` (`isActive`),
KEY `id` (`ID`),
KEY `adtype` (`AdType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


Now, what I'm trying to do is -:

SELECT * FROM glinks_Adverts,glinks_Advert_Relations WHERE ALLAREAS = 1 OR Advert_Relations.CatName = "British Columbia/Vancouver/Concrete/Cement Finishing"

How exacly would I go by this?

Basically - a bit of history as to why I'm trying to do this.

The "Advert" table just holds the advert (but can also have stuff like NEWS, ALLAREAS,etc. Then the "relation" table holds a list of the "categoires" this listing can show in.

I need to randomly select a value, but just can't get it working :/

TIA!

Andy

 

[r937]

when you say

Advert_Relations.CatName = "British Columbia/Vancouver/Concrete/Cement Finishing"

does this mean that you want to see only those ads in this category?

i don't understand how this works together with

ALLAREAS = 1

wouldn't that show all ads anyway?

also, what is this about a random value? what value?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Hi,

Basically, we have a few types of Adverts:

1) Where ALLAREAS = true, then it will be part of the rotation for the WHOLE site
2) If we specify categories (in the glinks_Advert_Relations table), then it will show that advert in those categories

So basically, we need to not only randomly select an advert (wheree ither the ALLAREAS= true, OR it should be shown in that given category)

Hopefully that makes more sense

TIA

Andy

 

[r937]

doesn't help much, but i just noticed your thread on sitepoint forums, so i'll jump in there



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[youradds]

Hi,

NP - and thanks =) (its driving me nuts, as its holding back the whole project )

Cheers

Andy