Select top 1 inside a left join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select top 1 inside a left join

Author

Topic

johnnybutler7
Starting Member

16 Posts

Posted - 2009-06-16 : 18:20:29

Hi,

The following query below gives me 32 records when it should be only 30

SELECT count(company_lists.id) AS count_distinct_company_lists_id FROM [company_lists]
INNER JOIN companies ON company_lists.company_id = companies.id
LEFT JOIN financials financials_year_1 ON financials_year_1.company_id = companies.id
and DATEPART(year, financials_year_1.period_ending) = (select DATEPART(year, max(period_ending))
from financials f where f.company_id = companies.id)
LEFT JOIN assessment_score_company_lists ON assessment_score_company_lists.company_list_id = company_lists.id WHERE (1=1 AND list_id = 550)

I need to get the top 1 record in the left join financials so it only gives me 1 of these records even if there is 2,

Ive tried the below and i cant seem to get it to work

SELECT count(company_lists.id) AS count_distinct_company_lists_id FROM [company_lists]
INNER JOIN companies ON company_lists.company_id = companies.id
LEFT JOIN (SELECT top 1 * from financials
WHERE financials.company_id = companies.id
and DATEPART(year, financials.period_ending) = (select DATEPART(year, max(period_ending))
from financials f where f.company_id = companies.id)) financials_year_1
LEFT JOIN assessment_score_company_lists ON assessment_score_company_lists.company_list_id = company_lists.id
WHERE (1=1 AND list_id = 494)

Can anyone help?

JB

johnnybutler7
Starting Member

16 Posts

Posted - 2009-06-16 : 18:39:29

quote:
Originally posted by johnnybutler7

Hi,

The following query below gives me 32 records when it should be only 30

SELECT count(company_lists.id) AS count_distinct_company_lists_id FROM [company_lists]
INNER JOIN companies ON company_lists.company_id = companies.id
LEFT JOIN financials financials_year_1 ON financials_year_1.company_id = companies.id
and DATEPART(year, financials_year_1.period_ending) = (select DATEPART(year, max(period_ending))
from financials f where f.company_id = companies.id)
LEFT JOIN assessment_score_company_lists ON assessment_score_company_lists.company_list_id = company_lists.id WHERE (1=1 AND list_id = 550)

I need to get the top 1 record in the left join financials so it only gives me 1 of these records even if there is 2,

Ive tried the below and i cant seem to get it to work

SELECT count(company_lists.id) AS count_distinct_company_lists_id FROM [company_lists]
INNER JOIN companies ON company_lists.company_id = companies.id
LEFT JOIN (SELECT top 1 * from financials
WHERE financials.company_id = companies.id
and DATEPART(year, financials.period_ending) = (select DATEPART(year, max(period_ending))
from financials f where f.company_id = companies.id)) financials_year_1
LEFT JOIN assessment_score_company_lists ON assessment_score_company_lists.company_list_id = company_lists.id
WHERE (1=1 AND list_id = 494)

Can anyone help?

JB

Think i may have fixed this myself using the outer apply

SELECT count(company_lists.id) AS count_distinct_company_lists_id FROM [company_lists]
INNER JOIN companies ON company_lists.company_id = companies.id
OUTER APPLY (SELECT top 1 * from financials
WHERE financials.company_id = companies.id
and DATEPART(year, financials.period_ending) = (select DATEPART(year, max(period_ending))
from financials f where f.company_id = companies.id)) financials_year_1
LEFT JOIN assessment_score_company_lists ON assessment_score_company_lists.company_list_id = company_lists.id
WHERE (1=1 AND list_id = 550)

JB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-17 : 10:42:24

make sure you give an order inside subquery of outer apply based on your business rule. giving just a top 1 without any order by will just give you a random record which satisfies the given condition, as sql server doesnt have a concept of first or last record unless specified explicitly by means of an order by

djDash
Starting Member

3 Posts

Posted - 2009-06-27 : 07:12:01

Table Employee: (Primary Key EMP_ID)
EMP_ID EMP_NAME DEPT_ID
----------- --------------- ----------
1 JACK 10
2 SAM

Table Dept: (Primary Key DEPTNO)
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

I want output as:
Table Employee: (Primary Key EMP_ID)
EMP_NAME DNAME
--------------- ----------
JACK 10
SAM NO DEPT

I used Query:
e.emp_name, d.dname from employee e LEFT JOIN dept d on e.dept_ID = d.deptno;

Now how should I print 'NO DEPT' for null entry.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-27 : 07:21:17

isnull(d.dname,'NO DEPT') as DNAME

No, you're never too old to Yak'n'Roll if you're too young to die.

djDash
Starting Member

3 Posts

Posted - 2009-06-27 : 07:37:52

Thanks for your reply .isnull is a SQL server function. But I'm using Oracle which on use gives me an error:
ORA-00904: "ISNULL": invalid identifier

Can you help me with any oracle function?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-27 : 07:43:25

Please try
nvl(d.dname,'NO DEPT') as DNAME

No, you're never too old to Yak'n'Roll if you're too young to die.

djDash
Starting Member

3 Posts

Posted - 2009-06-27 : 07:51:05

Thanks a lot....It worked....
Can you suggest me any source ... so that I can master my SQL knowledge.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-27 : 07:58:24