Forum Moderators: coopster
Here's the setup...
I have a db with, the following tables among others...
tDomain
tLink
I have the following query which is part of it
Select * From tLink Inner Join tDomainLink On tLink.LinkID = tDomainLink.LinkID
Where tLink.CategoryID = 11 And tDomainLink.DomainID = 1
And tDomainLink.Active = 'Y' Order By tLink.LinkID LIMIT 0,100
What I can't get is the second part. I need to further refine the query so that the above conditions must be met, BUT other conditions can't exist.
Basically, in pseudo code...
Look for all results in Category 11
Narrow the query to those that are associated with domain 1 and set to active (Y)
the above code produces results to this point)
Further narrow the query to those that are also associated with domain 3 but set to not active (N)
(can't figure out how to also include this in the query and get it to work properly. I've tried adding
And tDomainLink.DomainID = 3 And tDomainLink.Active = 'N'
to the above code with no success.
Any help is appreciated.
psuedo code
if condition1 and ((condition 2) or (condition3)) then blah...
make sense?
I need it to be , first meet condition 1, then conditions A+B and C+D must be met to create output.
Select *, COUNT(tDomainLink.DomainID) as MatchAmount From tLink Inner Join tDomainLink On tLink.LinkID = tDomainLink.LinkID
Where tLink.CategoryID = 11 And ((tDomainLink.DomainID = 1 And tDomainLink.Active = 'Y' ) OR (tDomainLink.DomainID = 3 And tDomainLink.Active = 'N')) Group By tLink.LinkID Having MatchAmount = 2 Order By tLink.LinkID LIMIT 0,100
Philosopher, you may also want to have a look at MySQL's control flow functions, particularly the IF() [mysql.com] function. Here is a recent thread that has a working example [webmasterworld.com] of that function.
The bit of code from scumm_bar2 worked. Thanks!
This has certainly been a crash course in sql over the past couple of days. Lots of reading and testing.
Unfortunately, pair has not upgrade to 4.1 or I would have been able to use subselects and it would be much easier.
I do have one additional problem I am faced with.
The idea behind the query I have been working on was to compare two different sites and find all the data that was being displayed on 1 and not the other by the field [tDomainlink.Active] set to either Y or N.
I now have the list of all the sites not being displayed and would like to Update the field associated with the site they are not displaying on so that they are also displaying there.
Unfortunately, without subselects, I haven't been able to figure how to do this.
Basically, I have Domain 1 and Domain 3 and I'm looking at Category X. If Domain 1 is set to display (tDomainLink.Active Y) and Domain is set not to display (tDomainLink.Active N) SET tDomainLink.Active = Y.
The results brought out by the current query are all associated with 1 and Y and not 3 and N or I would be able to read the Active field to a variable and possibly do it that way.
Again, any help is appreciated. it's been about 12 hours of trial and error (all error). ;)
Every single LinkID that the SELECT brings back has met all of your conditions, therefore you already know which field you need to update.
All you need to do is update to .Active = 'Y' (on the domain your SELECT was looking to find .Active = 'N', ie. Domain 3, in category 11) for each linkID result the SELECT gives back. You don't need to update only where category = 11 since you've already narrowed your result of linkIDs down to that category.
UPDATE tDomainLink SET Active = 'Y' Where LinkID = $linkID And DomainID = 3 And Active = 'N'
$linkID would be each LinkID returned back by the SELECT.
Or have I misunderstood?
