sql query select only one row from multiple rows

Question

I have this query:

SELECT p.[PostingID]
      ,[EmployerID]
      ,[JobTitle]                  
      ,pin.[IndustryID]         
FROM [Posting] p
INNER JOIN [City] c
  ON p.CityID = c.CityID
LEFT OUTER JOIN PostingIndustry pin
  ON p.PostingID = pin.PostingID
WHERE (c.CityID = @CityId OR @CityId IS NULL) 
  AND (p.StateProvinceID = @StateProvinceId OR @StateProvinceId IS NULL) 
  AND (pin.IndustryID = @IndustryId OR @IndustryId IS NULL) 
  AND 
  (
     (p.[Description] LIKE '%' + @Keyword + '%' OR @Keyword IS NULL) 
     OR (p.[JobTitle] LIKE '%' + @Keyword + '%'  OR @Keyword IS NULL)
  ) 
  AND p.StreetAddress IS NOT NULL 
  AND p.ShowOnMap = 1

Which returns results for all the pin.[IndustryID] if IndustryId is not selected or if all the industries are selected. If only one industry is selected I am getting one result which is good, but when one posting is included in multiple industries then I am getting multiple results as on the image shown below:

So for example when thats happening I want to get only one result for that posting id otherwise I am getting multiple results for one google map marker per the image below:

Is there a way how I can optimize the query above to do what I need? Thanks in advance, Laziale

Solution 1

What about selecting only the row with the smallest IndustryId:

SELECT [PostingID]
      ,[EmployerID]
      ,[JobTitle]                  
      ,MIN(pin.[IndustryID])         
FROM [Posting] p
INNER JOIN [City] c
  ON p.CityID = c.CityID
LEFT OUTER JOIN PostingIndustry pin
  ON p.PostingID = pin.PostingID
WHERE (c.CityID = @CityId OR @CityId IS NULL) 
  AND (p.StateProvinceID = @StateProvinceId OR @StateProvinceId IS NULL) 
  AND (pin.IndustryID = @IndustryId OR @IndustryId IS NULL) 
  AND 
  (
     (p.[Description] LIKE '%' + @Keyword + '%' OR @Keyword IS NULL) 
     OR (p.[JobTitle] LIKE '%' + @Keyword + '%'  OR @Keyword IS NULL)
  ) 
  AND p.StreetAddress IS NOT NULL 
  AND p.ShowOnMap = 1
GROUP BY [PostingID],[EmployerID],[JobTitle] 

Whenever you have only one, it returns that one, when you have more than one it returns only the one with the smallest IndustryId.

Solution 2

Don't select industry ID and use "SELECT DISTINCT..."

Solution 3

Try using a group by clause at the end

Use MAX(IndustryId).

Then also

GROUP BY PostingId,EmployerId,Jobtitle

Comments

[briskovich]

can you not select the industryID ? Why would the same row have so many same ID's ?

[Laziale]

@briskovich because one posting can have multiple industry id's, it means that one job post can be included in more industries. Thx

[Mad Dog Tannen]

Add MAX(IndustryCode) in the SELECT. Sorry im on my phone atm cant copy nice cide blocks

[Laziale]

top 1 will not work because there might be more results for that state or city but the industry will not be selected. If I do top 1 only one result will be displayed, although the IndustryId is null

[Filipe Silva]

I updated my answer to do a MIN on industryID and group by the remaining columns that are the same. I think it might give you what you are looking for.

[Mad Dog Tannen]

It should not as Max() will return only the maximum. I see you approved a answer with min and group by.

[Filipe Silva]

Should be the same yes.