Select Statement Help request 1

[JuanitaC]

Sorry for the long description, but here goes:
I have four tables:

Opportunity
---------------------
OppID ProjectID
---------------------
1 44
2 45


Project
---------------------
ProjID Title
---------------------
44 Project1
45 Project2


ProjectDealers
---------------------
ProjectID DealerID
---------------------
44 100
44 101
45 101


Dealers
--------------------
DealerID DealerName
--------------------
100 Walmart
101 K-mart



My current select statment is:
SELECT b.OppID, a.Title, d.DealerName
FROM Project a
LEFT JOIN Opportunity b ON a.ProjectID = b.ProjectID
LEFT JOIN ProjectDealers c ON a.ProjectID = c.ProjectID
LEFT JOIN Dealers d ON c.DealerID = d.DealerID


My current result set is:
OppID Title DealerName
-------------------------------------------
1 Project1 Walmart
1 Project1 K-mart
2 Project2 K-mart


What I need the select statement to do is restrict the results to one dealer per project, so my results would be:
OppID Title DealerName
-------------------------------------------
1 Project1 Walmart
2 Project2 K-mart


I am unsure how to accomplish this, as I am fairly new to SQL. Any ideas would be most appreciated. Thanks.

 

[SQLBill]

Since you appear to want each "project" to be retrieved once only, have you tried using 'distinct'?

Would this work?

SELECT b.OppID, distinct(a.Title), d.DealerName
FROM Project a
LEFT JOIN Opportunity b ON a.ProjectID = b.ProjectID
LEFT JOIN ProjectDealers c ON a.ProjectID = c.ProjectID
LEFT JOIN Dealers d ON c.DealerID = d.DealerID

-Bill

 

[jhall156]

You want to use a GROUP BY clause
any fields that you group by will return only 1 row for each value in that field. Any field that you don't group by you need to aggregate in some way i.e. SUM(thefield),COUNT(thefield),MAX(thefield),MIN(thefield) etc.

What isn't clear to me from your description is how will you determine which dealer you want. If it doesn't matter then choose MIN or MAX. JHall

 

[JuanitaC]

Distinct selects a distinct row... so I have added
SELECT DISTINCT OppID, Title, DealerName
but that gives the same result, because each row is distinct. When I tried your suggestion of
DISTINCT(a.Title)
I got an error: Incorrect syntax near the keyword 'distinct'.

 

[JuanitaC]

Thanks everyone... I figured it out myself:

SELECT b.OppID, a.Title,
(select top 1 DealerName from dealers e
inner join projectdealers f ON f.dealerid = e.dealerid
inner join projects g ON f.projid = g.projid
where g.projid = a.projid) as DealerName
FROM Project a
LEFT JOIN Opportunity b ON a.ProjectID = b.ProjectID
LEFT JOIN ProjectDealers c ON a.ProjectID = c.ProjectID
LEFT JOIN Dealers d ON c.DealerID = d.DealerID