SELECT FROM a WHERE {end bit of field} not in B..?
-
Anybody any ideas how to achieve anything like this...
Table1
id | domain
-----------------------
1 | anything.com
2 | anyone.com
3 | pintabeer.com
4 | wodka.com
Table2
id | email
-------------------
3 | friend@home.com
Basiaclly, I need to do a select on Table2, where the email domain (or the last bit of the address) doesn't match anything in table1.
So my select should return ;
id email
--------------------------
3 | friend@home.com
Not sure if I'm missing something easy, but I'm certainly spinning in circles.
Thanks for any help.
Ollie Lord.
-
Maybe something like:
select * from Table2
where substring(email, charindex('@', email) + 1, len(email)) not in
(select domain from Table1)Assuming of course that email is not null and will always contain a valid email address.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Hi mia,
Thanks for the response. What you've said would work perfectly.
Unfortunately though, my example was a bit over simplistic.
It’s not possible for me to use a consistent character like the ‘@’ symbol.
Do you have any idea how could I go about solving the same problem, if my data were like this…;
Table1
id | domain
-----------------------
1 | anything.com
2 | anyone.com
3 | pintabeer.com
4 | wodka.com
Table2
id | email
-------------------
2 | somewhere@sandwich.anyone.com
6 | drunk@gimmie.pintabeer.com
I really just need to exclude all data where the end of the data in Table2 matches anything in Table1.
If this were just for 1 domain, obviously my select would simply be
SELECT email FROM Table2 WHERE email NOT LIKE ‘%excludeddomain’
Oh! And using any method that counts the dots is probably out too, because the domain in Table1 could have any number of dot (something.co.uk for example).
Any help would be greatly appreciated. Life is too short for problems a problem like this…!
Cheers,
Ollie.
-
Ollie,
Had a look again, and I think the following should give you what you need. I tested it briefly using the example data you provided & it produces the desired result. Don't know if there's another easier way to do it though!
select * from Table2
where id not in
(select t2.id
from Table2 t2
inner join Table1 t1 on t1.domain = right(t2.email, len(t1.domain)))Hope this one's more useful!
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Or more briefly:
select t2.id, t2.email
from Table2 t2
left outer join Table1 t1 on t1.domain = right(t2.email, len(t1.domain))
where t1.id is null
Multiple posts again - always think of a shorter way about 30 seconds after I post!!
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Mia
this change to your code will make it work:
right(rtrim(t2.email), len(rtrim(t1.domain)))
-
you can try,
select a.* from Table2 a left join Table1 b on a.email like '%' + b.domain
where b.id is null
rgds,
ZL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply