• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

Conditional Inner Joib

Hi,

I have a table called CompanyLinks:

int LinkId
string url
string sitename
int companyId
date updateDate

And a table called Companies:

int companyId
string companyname

I would like to select all rows in which updatedate is more than two days ago, with a custom column LinkName that should be:
a. If CompanyId<>-1 : Companies.companyname
b. If CompanyId==-1: SiteName

(i.e., I would like to have an inner join on condition that companyId<>-1).

Thanks.

0
l_agmon
Asked:
l_agmon
1 Solution
 
Z03niECommented:
Maybe this query will work.

SELECT Companies.companyId,CompanyLinks.url,
LinkName = (CASE Companies.CompanyId  WHEN  -1 THEN CompanyLinks.SiteName
                             ELSE Companies.companyname END)
FROM Companies INNER JOIN CompanyLinks
ON Companies.companyId = CompanyLinks.companyId  AND Companies.companyId <> -1


But why would you like to make a condition companyId<>-1?
The LinkName field will be useless then. Certainly it will only show Companies.companyname since all companyId is not -1.
But anyway.... thats the query. make proper change as necessary. I'll be off till Monday. So if you have any other question that might have to wait until Monday, or other people in EE might got some answer for you.See ya.
0
 
l_agmonAuthor Commented:
I updated the select as follows:

SELECT dbo.CompanyLinks.LinkId, dbo.CompanyLinks.Url, dbo.CompanyLinks.ChangedDate,
               (CASE Companies.id WHEN - 1 THEN CompanyLinks.SiteName ELSE Companies.Name END) AS LinkName
FROM  dbo.CompanyLinks INNER JOIN
               dbo.Companies ON dbo.CompanyLinks.CompanyId = dbo.Companies.id AND dbo.Companies.id <> - 1
WHERE (dbo.CompanyLinks.CompanyId = @CompanyId)

The problem:
1. The Companies Table does not have any Company with Id=-1.

When I run the Select with @companyId=-1, I receive an empty dataset.

0
 
QlemoC++ DeveloperCommented:
I assume companyid <> -1 should be checked in CompanyLinks, not Conmpanies. So you will need an outer join:

SELECT dbo.CompanyLinks.LinkId, dbo.CompanyLinks.Url, dbo.CompanyLinks.ChangedDate,
      (CASE CompanyLinks.id WHEN - 1 THEN CompanyLinks.SiteName ELSE Companies.Name END) AS LinkName
FROM dbo.CompanyLinks
      LEFT JOIN dbo.Companies ON dbo.CompanyLinks.CompanyId = dbo.Companies.id
WHERE dbo.CompanyLinks.CompanyId = @CompanyId
  AND dbo.CompanyLinks.updateDate < convert(datetime, convert(varchar, getdate()-2,112));

Last part cuts date-and-time to date only.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now