can this be done in a join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

can this be done in a join

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-28 : 15:23:16

I have 2 tables
customer
dialcodes

now I need to loop through the whole customer table and pull up all customers where the count of the following statement is more then 1

select count(geographic) as mycount from dialcodes where '" & phone1 & "' like code + '%'"
or if phone2 is not blank then the count of the following statment
select count(geographic) as mycount from dialcodes where '" & phone2 & "' like code + '%'"

I want to return a recordset when one of the 2 sql above returns more then 0. this in access so I can't use a stored procedure.

can i do this with a join?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-04-11 : 02:45:39

Assuming you have the info to join then yes, of course you can. However in this case it might be clearer to do it as a subquery almost identical to what you have.
Something like (I haven't tested it of course)

select * from customer
where
(select count(geographic) as mycount from dialcodes where '" & phone1 & "' like code + '%'" and customer.id = dialcodes.id) > 1
or (phone2 <>'' and -- might prefer null here
(select count(geographic) as mycount from dialcodes where '" & phone2 & "' like code + '%'" and customer.id = dialcodes.id)>1)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-11 : 02:57:08

May be this (not tested though):

Select c.* 
from Customer c
Join
(
	select id
	from dialcodes 
	where ( "'" & phone1 & "'" like code + "'%'"  or "'" & phone2 & "'" like code + "'%'")
	group by id
	having count(geographic) > 1
) as t
on c.id = t.id

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-11 : 09:01:57

Also, make sure that you use stored procedure with input parameters for better performance than using concatenated sql

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-11 : 09:11:32

Are there stored procedures in Access, Madhi?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-11 : 09:15:42

quote:
Originally posted by harsh_athalye

Are there stored procedures in Access, Madhi?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Well. I didnt see Forum name properly

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-17 : 01:36:37

this is not working -- i want the query to return all records where phone 1 doesn't match dialcodes and if phone2 is not null then all those don't match dialcodes -- this is all in access i'm not passing the phone1 and phone2 -- it should pull them from the customer table

is this possible?

ACushen
Starting Member

29 Posts

Posted - 2007-04-25 : 22:15:44

Actually, there are Stored Procedures in the recent versions of Access. Although I understand they are basically converted to Queries by the Jet engine as it runs them.

I don't know anyone who actually uses them, though...

-Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources