Solved

Conditional Inner Joib

Posted on 2004-09-04
3
429 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
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 69

Accepted Solution

by:
Qlemo earned 50 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2016 Integration Service connecting to 2012 3 31
SQL R 21 25
Getting invalid Syntax SQL. 3 15
SQL - Simple Pivot query 8 12
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

809 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