Devananda,
>Definitely not. The first LEFT JOIN, with the clauses
>"ON
pt.login_id=pab1.login_id ... WHERE pab1.login_id IS NULL",
> does not
make sense.
?!? It's a standard JOIN replacement for NOT IN (...).
>There is not a 1to1 relationship,
These joins neither find nor assume 1:1 relationships.
> and pab1.login_id will
rarely, if ever, be NULL in this query.
If you LEFT JOIN table a to table b using column c WHERE b.c IS NULL,
you get the rows in b which have no matching c values in a, which is
logically equivalent to c in a and NOT IN b.
This query ...
SELECT DISTINCT pt.offer_id
FROM paytable AS pt
INNER JOIN offers AS o USING (offer_id)
LEFT JOIN publisher_advertiser_blocks AS pab1
ON pt.login_id=pab1.login_id
LEFT JOIN publisher_advertiser_blocks AS pab2 ON
o.advertiser_id=pab2.advertiser_id
WHERE pt.login_id=1
AND pab1.login_id IS NULL OR pab2.advertiser_id IS NULL;
gives the same result as yours does, on the data you provided.
PB
-----
Devananda wrote:
Peter Brawley wrote:
Devananda,
OK, get the diescription in ordinary language right, and the query
ought to write itself. Your schema is like this? ...
and
you want the paytable rows ....
(i) for which there is an offers row matching paytable.offer_id, and
(ii) for which there is no pab row where
pab.login_id=paytable.login_id and pab.advertiser_id=offer_id?
Not quite. I want all the offer_id's which are stored in paytable ...
(i) for a specified login_id
(ii) that do not belong to advertiser_id's which that login_id is
blocked from.
Would that be ...
SELECT pt.offer_id, pt.login_id, o.advertiser_id
FROM paytable AS pt
INNER JOIN offers AS o USING (offer_id)
LEFT JOIN publisher_advertiser_blocks AS pab1 ON
pt.login_id=pab1.login_id
LEFT JOIN publisher_advertiser_blocks AS pab2 ON
o.advertiser_id=pab2.advertiser_id
WHERE pab1.login_id IS NULL OR pab2.advertiser_id IS NULL;
Definitely not. The first LEFT JOIN, with the clauses "ON
pt.login_id=pab1.login_id ... WHERE pab1.login_id IS NULL", does not
make sense. There is not a 1to1 relationship, and pab1.login_id will
rarely, if ever, be NULL in this query.
For example, let's say login_id (1) has a hundred rows in paytable,
each belonging to a different offer_id (1) ... (100), and of those, 10
belong to advertiser_id (1), 5 belong to advertiser_id (2), and the
rest to (3). If pab (login_id, advertiser_id) contains the rows
(1,1),(1,2) then the query should return to me 85 rows from paytable.
Here's example data:
INSERT INTO login_data (login_id) VALUES (1);
INSERT INTO advertiser_data (advertiser_id) VALUES (1), (2), (3);
INSERT INTO offers (offer_id, advertiser_id) VALUES (1, 1), (2, 1),
..., (11, 2), (12, 2), ..., (16, 3), ..., (100,3);
INSERT INTO paytable (offer_id, login_id) VALUES (1, 1), (2, 1), ...,
(100,1);
INSERT INTO pab (login_id, advertiser_id) VALUES (1,1), (1,2);
In this case, I would want all the rows in paytable:
(i) for login_id (1),
(ii) that do not belong to advertiser_id's (1) or (2).
In SQL, this could be ...
SELECT offer_id
FROM paytable
WHERE login_id=1 AND offer_id NOT IN
(
SELECT offer_id
FROM offers
LEFT JOIN publisher_advertiser_blocks AS pab USING (advertiser_id)
WHERE pab.login_id=1
);
... but I'd like to get away from the NOT IN (...) clause. Maybe I am
stuck using it?
I apologize if I've been unable to adequately explain the way these
tables relate to each other; honestly, I have had a bit of trouble
wrapping my brain around it all, myself.
Thanks again for all the suggestions,
Devananda
PB
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.25/247 - Release Date: 1/31/2006
--
Devananda vdv
http://devananda-vdv.blogspot.com/
http://mycat.sourceforge.net/
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.25/247 - Release Date: 1/31/2006
|
