Help pulling correct info

[TheCandyman]

I have a DB, which holds some phone numbers. Some are phone, fax, home, cell, etc.

So i type the phone number in, and then assign it properties from a drop down box(directory, office, office #2, home, cell) it creates a record with the item number(office)and assigns it to the userID. So a user who has a phone that is an office phone and it will show in a directory, it will have 2 records, one for with the phone ID and the other with directory ID.

I can pull just the office numbers, or just the home numbers just fine. But i can't pull the records that are office numbers AND showing up in direcory. It's simple sounding but i can't seem to figure it out.

In the query if i have it search for "directory" it finds all of them, but if i put ' "directory" AND "office" ' it comes back with nothing.

tblPhoneType has all listings of phones (home, office, directory)
tblMemPhone has all records of each phone

 

[lespaul]

So you have:


555-1212 directorycode
555-1212 officecode
555-1231 directorycode
555-1231 officecode


and now you want to find all the records that are directory numbers AND office numbers?

SELECT MemPhoneID, AzDAID, DPhone As PhoneNum, PhoneType
FROM tblMemPhone
INNER JOIN tblMemPhoneType on tblMemPhone.MemPhoneID = tblMemPhoneType.MemPHoneID
INNER JOIN tblPhoneType on tblMemPhoneType.PhoneTypeID = tblPhoneType.PhoneTypeID
WHERE PhoneType = 'directory' and tblMemPhone.MemPhoneID in (SELECT MemPhoneID FROM tblMemPhone 
INNER JOIN tblMemPhoneType on tblMemPhone.MemPhoneID = tblMemPhoneType.MemPHoneID
INNER JOIN tblPhoneType on tblMemPhoneType.PhoneTypeID = tblPhoneType.PhoneTypeID where PhoneTYpe = 'office')
[code]

there may be another way, but this should work.

HTH

leslie

 

[TheCandyman]

I can't seem to get it to work, do i need the Inner join on the WHERE ?

This is what i have:

SELECT tblMemPhone.MemPhoneID, tblMemPhone.AzDAID, tblMemPhone.PhoneNum AS DPhone, tblPhoneType.PhoneType
FROM tblPhoneType
INNER JOIN (tblMemPhone INNER JOIN tblMemPhoneType ON tblMemPhone.MemPhoneID = tblMemPhoneType.MemPhoneID) ON tblPhoneType.PhoneTypeID = tblMemPhoneType.PhoneTypeID
WHERE (((tblPhoneType.PhoneType)="directory"));

 

[swoodring27]

Have you tried "directory" OR "office"?

Susan

 

[TheCandyman]

Yes, it pulls everyone that has a directory phone, plus an office phone. Has to be 'and' just doens't work!

 

[lespaul]

what you need is a nested subquery, which is what I did:

write a query that returns all the ID's that are "office"

QUERY#1:
SELECT MemPhoneID FROM tblMemPhone 
INNER JOIN tblMemPhoneType on tblMemPhone.MemPhoneID = tblMemPhoneType.MemPHoneID
INNER JOIN tblPhoneType on tblMemPhoneType.PhoneTypeID = tblPhoneType.PhoneTypeID where PhoneTYpe = 'office'

then you use this query in your main query:

QUERY#2
SELECT MemPhoneID, AzDAID, DPhone As PhoneNum, PhoneType
FROM tblMemPhone
INNER JOIN tblMemPhoneType on tblMemPhone.MemPhoneID = tblMemPhoneType.MemPHoneID
INNER JOIN tblPhoneType on tblMemPhoneType.PhoneTypeID = tblPhoneType.PhoneTypeID
WHERE PhoneType = 'directory' and tblMemPhone.MemPhoneID in [COLOR=red](SELECT MemPhoneID FROM tblMemPhone 
INNER JOIN tblMemPhoneType on tblMemPhone.MemPhoneID = tblMemPhoneType.MemPHoneID
INNER JOIN tblPhoneType on tblMemPhoneType.PhoneTypeID = tblPhoneType.PhoneTypeID where PhoneTYpe = 'office')[/color]

So run Query#1 and make sure that it returns the MemPhoneID of all the entries that are type 'office'. If that works correctly, then you can use that information in Query#2 as part of the criteria.

You should be able to paste the SQL from Query#2 into your query and it should run (since you provided such a nice picture that has all the table names and field names!)

Leslie

 

[TheCandyman]

Sweet, i think that will work. I'll give it a try here in a bit.