[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Conditional Inner Joib

Posted on 2004-09-04
3
Medium Priority
?
467 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 71

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

650 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