Select records from one table that appear in another table 1

[lck092580]

Hi I'm trying to select records out of Table A's column that has matching values to Table B's column. For example:

Table A

URL
abc.com
gmail.com
hotmail.com

Table B

Domains
gmail
hotmail

So what would happen is once I run this query on Table A, I will only get abc.com.

Here's what I have but it doesn't work

SELECT *
FROM URL
WHERE URL.URL Like "(SELECT tableB FROM DOMAINS)*";

 

[PHV]

SELECT U.*
FROM [Table A] AS U INNER JOIN [Table B] AS D ON U.URL Like D.Domains & '.*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lck092580]

Now I'm trying to use this to delete records:

DELETE *
FROM URL_SPAM_PROCESSED_LIST
WHERE (

SELECT U.*
FROM URL_SPAM_PROCESSED_LIST AS U INNER JOIN DND_DOMAINS AS D ON U.URL Like D.Domain & '.*');

I think I'm not incorporating it properly.

 

[lck092580]

DELETE *
FROM URL_SPAM_PROCESSED_LIST
WHERE URL = (

SELECT U.*
FROM URL_SPAM_PROCESSED_LIST AS U INNER JOIN DND_DOMAINS AS D ON U.URL Like D.Domain & '.*');

Forgot URL = added it in but still doesn't work.

 

[PHV]

DELETE FROM URL_SPAM_PROCESSED_LIST
WHERE URL IN (
SELECT U.URL
FROM URL_SPAM_PROCESSED_LIST AS U INNER JOIN DND_DOMAINS AS D ON U.URL Like D.Domain & '.*');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lck092580]

Dear god!

Ok, now for a simple one, what's the SQL statement to define what a text field, field size is if I were to incorprate it into something like this:

SELECT DISTINCT * INTO URL_SPAM_PROCESSED_LIST
FROM URL_SPAM_PROCESS_LIST
WHERE URL<>"";

Basically I want the URL field to not default to 25 but to 255.

Thanks. You've saved me so much time.

 

[JerryKlmns]

The field size of Text data type default value is defined on menu Tools --> Options Tables/Queries tab. Look at top left