Comparing two tables ?

MichaelHe

I have two semilar tables I which to compare.

A costumer table that likes something like this:

CostumerName
Filter1Chose1
Filter1Chose2
Filter1Chose3
Filter2Chose1
Filter2Chose2
Filter2Chose3

And a table with advertisers that looks the same way:

AdvertiserName
Filter1Chose1
Filter1Chose2
Filter1Chose3
Filter2Chose1
Filter2Chose2
Filter2Chose3

If a costumer has checkmark ex.
Filter1Chose1
Filter1Chose2
Filter2Chose2

I need it to compare the two tables and find the advertisers that has checkmark the same filter.
I need it to find if an advertiser has checkmarked ex. only Filter1Chose1 and Filter2Chose2.
Or Filter1Chose2 and Filter2Chose2.
Or all three.
But not if he has checkmarked Filter1Chose2 and NOT Filter2Chose2

In short terms...
If an advertiser has checkmark one or more of the same as the costumer in each Filter.

That sounded a little complicated... :o)
Please ask if you don't get what I mean :o)

Best
Michael

Roger_Ramjet

I understand you alright, even though you don't ask questions worth a damn.

Basic join where equal and true.

MichaelHe

I'm not that good at joining yet...
But as I see it in my head it will be a very long query when I have about 50 choses in 5-6 filters. ?

Or am I wrong.

Roger_Ramjet

Well, if you've got that many options, then yes.

So you need to rethink your database - seriously.

MichaelHe

So it would be something like:

SELECT * FROM Costumers LEFT JOIN Advertisers ON (
(Filter1Chose1.Costumers=Filter1Chose1.Advertisers OR
Filter1Chose2.Costumers=Filter1Chose2.Advertisers OR
Filter1Chose3.Costumers=Filter1Chose3.Advertisers)
AND
(Filter2Chose1.Costumers=Filter2Chose1.Advertisers OR
Filter2Chose2.Costumers=Filter2Chose2.Advertisers OR
Filter2Chose3.Costumers=Filter21Chose3.Advertisers)

and so on ???
And would take some time with 50 choses and 500 advertisers right ??

And it would also be wrong because it compares all the filters nomatter if there are checkmarked or not..
And it should only concentrade on the filters that are checkmarked.

Would there be an easier way if either Costumers or Advertises or both puts the filter in other tables:

CostumerLink:
CostumerID
Filter
Chose

AdvertiserLink:
AdvertiserID
Filter
Chose

And then compare these two instead ??

Roger_Ramjet

Yes, to all. Your specimen would need to include a 'AND Customers.Fieldchoose2 = TRUE' etc for all the join conditions.

A total monster which you should have seen coming when you designed your DB - with experience you will.

So yes, normalise your data out into linked tables that store the positive choices for the filters

filters table
filter_id primary key
filter

cust_filters
cust_id
filter_id

ad_filters
ad_id
filter_id

Then you just join cust_filters and ad_filters on filter_id to find out who have made the same choices.

Roger_Ramjet

PS YOU ONLY HAVE AN ENTRY IN EITHER TABLE IF THEY CHOSE YES. NEGATIVE CHOICES ARE EXCLUDED.

MichaelHe

Hi again

Thanks alot for your help.

I have not yet made the tables... It is a future project, that's why I want to get it right from the start...

So if I take an American ex.

A filter table contains all the American states:
filter_id 1
filter California

filter_id 2
filter Washington

and so on...

The advertisers has in an ad_filters table chosen a location ex. like this:
ad_id 1
filter_id 1

ad_id 2
filter_id 2

ad_id 3
filter_id 1

ad_id 3
filter_id 2

And two costumers has in a cust_filters table chosen this:
cust_id 1
filter_id 1

cust_id 2
filter_id 1

cust_id 2
filter_id 2

So now I'm a little new at this 🙂
How do I get a query to find the advertisers that matches costumers so that costumer 1 matches advertiser 1 and 3 and costumer 2 matches advertiser 1, 2 and 3 ?
Only one of the filters has to match.

Is this close to correct if I need to list which advertisers that matches costumer 2ex.:

SELECT * FROM ad_filters LEFT JOIN cust_filters
ON (ad_filters.filter_id = cust_filters.filter_id)
WHERE cust_filters.cust_id='2'
GROUP BY ad_filters.ad_id
ORDER BY ad_filters.ad_id

Roger_Ramjet

You don't need a left join, straight join where filter_id equal is all. Join every cust id with filter 1 to every ad_id with filter 1, etc.

Normally you would only be running this with additional conditions like specific customer or specific advertiser.

MichaelHe

Hi again

I have just edited my thread while you whrer ansering me :o)
I have edited it with my suggestion for finding ex. the matches for costumer 2..

MichaelHe

I have just tried testing it a little, and I think I can get it to work...

BUT

the million dollar question is now...

What if I want 2 filter ??
A Location filter AND a Type filter of some kind

So the costumers checkmarks one or more locations AND one or more Types ??

The advertiser needs to have checkmarked one or more of the same locations than the costumer as before AND one or more types the same as the costumer.

Now I'm really lost...

Do I need to put this second filter in a second table both in the filter table and the cust and ad tables..
Or ???

Roger_Ramjet

A Left Join is going to return the wrong results.

Get your logic right: you want the set of advertisers who's filters match those of customer 2. So you start from the customer

"SELECT * FROM cust_filters cf INNER JOIN ad_filters af USING(filter_id) 
INNER JOIN advertisers a USING(ad_id)
WHERE cf.cust_id = 2 ORDER BY ad_id";

That is the complete query, the order by is just fancy dressing and can be any column. If you want the filter details then inner join to that table as well.

MichaelHe

I can see that... But what about the second filter... ?
Can I right them all the the filter tables, and then somehow seperate them in more than one INNER JOIN so it matches the first filter AND the second...

Roger_Ramjet

Up to you. You can have them in the one table, but if you want it to match location seperately from type then seperate tables is easiest. Now, the query becomes pretty messy looking when you do this and will be a lot easier to write using sub-queries.

"SELECT * FROM cust_filters cf INNER JOIN ad_filters af USING(filter_id)
INNER JOIN cust_loc cl USING(cust_id)
INNER JOIN ad_loc al ON(cl.loc_id=al.loc_id) 
WHERE cf.cust_id = 2";

More or less like that.

MichaelHe

And by sub-queries you mean... one query inside another query ?