?
Solved

Conditional Inner Joib

Posted on 2004-09-04
3
Medium Priority
?
457 Views
Last Modified: 2009-07-29
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
Comment
Question by:l_agmon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Expert Comment

by:Z03niE
ID: 11979814
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
 

Author Comment

by:l_agmon
ID: 11979856
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
 
LVL 70

Accepted Solution

by:
Qlemo earned 200 total points
ID: 11980504
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question