Solved

Conditional Inner Joib

Posted on 2004-09-04
3
400 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 68

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now