SQL Join with Group By

Question

Ok, so i'm trying to write a complex query (at least complex to me) and need some pro help. This is my database setup:

Table: MakeList

| MakeListId | Make     |
| 1          | Acura    |
| 2          | Chevy    |
| 3          | Pontiac  |
| 4          | Scion    |
| 5          | Toyota   |

Table: CustomerMake

| CustomerMakeId | CustomerId | _Descriptor |
| 1              | 123        | Acura       |
| 2              | 124        | Chevy       |
| 3              | 125        | Pontiac     |
| 4              | 126        | Scion       |
| 5              | 127        | Toyota      |
| 6              | 128        | Acura       |
| 7              | 129        | Chevy       |
| 8              | 130        | Pontiac     |
| 9              | 131        | Scion       |
| 10             | 132        | Toyota      |

Table: Customer

| CustomerId | StatusId |
| 123        | 1        |
| 124        | 1        |
| 125        | 1        |
| 126        | 2        |
| 127        | 1        |
| 128        | 1        |
| 129        | 2        |
| 130        | 1        |
| 131        | 1        |
| 132        | 1        |

What i am trying to end up with is this...

Desired Result Set:

| Make    | CustomerId|
| Acura   | 123       |
| Chevy   | 124       |
| Pontiac | 125       |
| Scion   | 131       |
| Toyota  | 127       |

I am wanting a list of unique Makes with one active (StatusId = 1) CustomerId to go with it. I'm assuming i'll have to do some GROUP BYs and JOINS but i haven't been able to figure it out. Any help would be greatly appreciated. Let me know if i haven't given enough info for my question. Thanks!

UPDATE: The script doesn't have to be performant - it will be used one time for testing purposes.

Answer 1

You want to

(a) join the customer and customermake tables (b) filter on customer.statusid
(c) group by customermake._descriptor

Depending on your RDBMS, you may need to explicitly apply a group function to customer.customerid to include it in the select list. Since you don't care which particular customerid is displayed, you could use MIN or MAX to just pick an essentially arbitrary value.

Answer 2

Something like this:

select cm._Descriptor,
       min(cu.customerid)
from CustomerMake cm
  join Customer cu on cuo.CustomerId  = cm.CustomerId and cu.StatusId = 1
group by cm._Descriptor

I left out the MakeList table as it seems unnecessary because you are storing the full make name as _Descriptorin the CustomerMake table anyway (so the question is what is the MakeList table for? Why don't you store a FK to it in the CustomerMake table?)