Building query with one to many relation (1 Viewer)

[MrHans]

Hi all,

I need some help building a query that I need, but I can't get it to work.
I have a customers table and a customers contact details table.
Its a one to many relation.

CustomerID - FK
MediaTypeID - FK (its phone or email)
LabelID - FK (its a label, like invoice address or mobile address)
MediaAdress (the actual value)

I want to get a list with all CustomerID
Then only the email addresses
Then only the LabelID is 7
But if the customer doesnt have a labelid 7, then I just want the first one or a Null if he doesnt have any mail address.

Select CustomerID, MediaAddress
From tblContactDetails
Where MediaTypeID = 2
And LabelID = 7

But that only shows the ones with LabelID 7.
If there is no 7 I want the first one or a null.

Do you have any idea on how to achieve this?

 

[The_Doc_Man]

You have three criteria based on three fields. Do you want all of the possible answers in the same list? Or in three different lists?

 

[plog]

The best way to demonstrate data issues is with data. Please provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with. If that's multiple datasets then provide all you want.


Again, 2 sets of data--starting and expected results based on starting.

 

[MrHans]

You have three criteria based on three fields. Do you want all of the possible answers in the same list? Or in three different lists?

Thanks Doc, no I want just 1 record per customer.
If he has a specific invoice address, LabelID 7 then I want that one, otherwise just the first email address that the customer has.

I guess it's a group by clause that I need.

 

[MajP]

You are probably going to need a union query.

1. qryHasEmail

Select CustomerID, MediaAddress
From tblContactDetails
Where MediaTypeID = 2

2. qryNoEmailGroup7

Select CustomerID, MediaAddress
From tblContactDetails
Where MediaTypeID <> 2
And LabelID = 7

3. qryMinByGroup
Then I think you need a min of mediatype where not 2 or 7. You can replace min with first if that is really what you mean.

Select CustomerID, Min(MediaTypeID) as MinType where mediaType <> 2 and labelID <> 7
Group by CustomerID

4. qryMinByGroupAddress

Select CustomerID, MediaAddress
From tblContactDetails
InnerJoin qryMinByGroup
On TblContactDetails.CustomerID = qryMinByGroup.CustomerID and tblContactDetails.MediaType = qryMinByGroup.MinType

Now union 1,2,4