SQL Question

tophurler

Hi,

I dabbled in some SQL about 10 years ago when in University, and recently my job has thrown up a situation where it may be of use to me, I have two tables in Access and I want to compare the two tables to find the common rows in each,

I've tried the following

SELECT Field1 FROM t1, t2 WHERE t1.Field1 = t2.Field1;

But it's returning a blank table,
Would anyone be able to help? looking for a quick solution and it's driving me crazy!

jmcc

tophurler wrote: »

Hi,

I dabbled in some SQL about 10 years ago when in University, and recently my job has thrown up a situation where it may be of use to me, I have two tables in Access and I want to compare the two tables to find the common rows in each,

I've tried the following

SELECT Field1 FROM t1, t2 WHERE t1.Field1 = t2.Field1;

But it's returning a blank table,
Would anyone be able to help? looking for a quick solution and it's driving me crazy!

You may have an ambiguous Field1 in the statement as it may also exist in table 2. Try it with the Field1 value associated with table 1.

SELECT t1.Field1 FROM t1, t2 WHERE t1.Field1 = t2.Field1;

Though I haven't used Access for about seven years or so and I don't know what Microsoft have done in the meantime.

Regards...jmcc

FSL

Try
Select field1 from t1 where t1.field1 in(select field1 from t2)

ocallagh

SELECT t1.Field1 FROM t1 INNER JOIN t2 ON (t1.Field1 = t2.Field1)

Jagera

The obvious question - do any matches actually exist?

Check the data types of Field1 for both tables. They should be able to implicitly convert, or you need to do a CONVERT function.

tophurler

Thanks Guys have sorted it with your suggestions! Much appreciated for the speedy replies

fatlog

bw wrote: »

The obvious question - do any matches actually exist?

ha. i was reading through the comments and wondering when someone would say this!:D

sliotor

Hi, would anyone know what to do with this I am doing a like query to select info.
example:
SELECT * FROM customers
WHERE customer_name LIKE '26%'
which will return
26_xx
26_xx
265_xx
but i only want to display 26_xx

I have tried which was suggested from a site :
SELECT * FROM customers
WHERE customer_name LIKE 'H%!%' escape '!';
but that also returned
26_xx
26_xx
265_xx


Does anyone know a way around this, Apreciate the help thanks.

stevenmu

WHERE test LIKE '26[_]%'

Should work, the square brackets let you specify a character range, normally you'd use it for something like a-z, but it should force it to recognise _ as a character.

sliotor

stevenmu wrote: »

WHERE test LIKE '26[_]%'

Should work, the square brackets let you specify a character range, normally you'd use it for something like a-z, but it should force it to recognise _ as a character.

Thanks for your help, I was able to get it working in the end.
I had to use
select * from customers where customer_name like '26\_%' escape '\';

The \ precedes the escape character.