? A select top1 within a select ?

Question

? A select top1 within a select ?

jbalbo

SSCertifiable

Points: 7583
More actions
April 10, 2015 at 2:42 pm

#306739

Hi
I have a query where I want to select only the top record which meets the criteria from one of the tables (if any)
but want all the other records
In the example I should have 200+ plus records but only get 100 when I add table EVT where I and picking up "AND (dbo.EVT.DATEDACT IS NULL) AND (dbo.EVT.EVTCODE = 'pcn')"
What I really want is the 200+ records without the "AND (dbo.EVT.DATEDACT IS NULL) AND (dbo.EVT.EVTCODE = 'pcn')"
and if a record exists that has the above then display dbo.EVT.EVTCODE else leave blank
Hopefully I explained this OK...
Thanks
SELECT dbo.CLI.A_DATE, dbo.CLI.ADDNO, dbo.CLI.CLITYPE, dbo.SVAC.PROGNO, dbo.PGDM.PROGDES, dbo.CLI.DATEADM, dbo.CLI.CLINO, dbo.CLI.SEX, dbo.CLI.LNAME,
dbo.CLI.FNAME, dbo.ADDR.STATE, dbo.ADDR.STREETNO + ' ' + dbo.ADDR.STREET AS Address, dbo.ADDR.ZIP, dbo.ADDR.CITY, dbo.CLI.MNAME, dbo.CLI.MINIT,
dbo.CLI.PK_CLI, dbo.SVAC.PK_SVAC, dbo.CLI.DOB, dbo.CLI.TEL1, dbo.CLI.TEL2, dbo.EVT.EVTCODE
FROM dbo.CLI INNER JOIN
dbo.SVAC ON dbo.CLI.PK_CLI = dbo.SVAC.PK_SVAC INNER JOIN
dbo.PGDM ON dbo.SVAC.PROGNO = dbo.PGDM.PROGNO INNER JOIN
dbo.ADDR ON dbo.CLI.ADDNO = dbo.ADDR.ADDNO INNER JOIN
dbo.EVT ON dbo.CLI.CLINO = dbo.EVT.CLINO
WHERE (dbo.CLI.CLITYPE LIKE '%A%') AND (dbo.SVAC.PROGNO LIKE '%286%' OR
dbo.SVAC.PROGNO LIKE '%288%' OR
dbo.SVAC.PROGNO LIKE '%289%')
AND (dbo.EVT.DATEDACT IS NULL) AND (dbo.EVT.EVTCODE = 'pcn')

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Could you perhaps include a little DDL, sample data and an example of expected output?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

jbalbo (4/10/2015)
Hi
I have a query where I want to select only the top record which meets the criteria from one of the tables (if any)
but want all the other records
In the example I should have 200+ plus records but only get 100 when I add table EVT where I and picking up "AND (dbo.EVT.DATEDACT IS NULL) AND (dbo.EVT.EVTCODE = 'pcn')"
What I really want is the 200+ records without the "AND (dbo.EVT.DATEDACT IS NULL) AND (dbo.EVT.EVTCODE = 'pcn')"
and if a record exists that has the above then display dbo.EVT.EVTCODE else leave blank
Hopefully I explained this OK...
Thanks

Maybe I'm misinterpreting, but do you just want an outer join? Something like: -

SELECT dbo.CLI.A_DATE,

dbo.CLI.ADDNO,

dbo.CLI.CLITYPE,

dbo.SVAC.PROGNO,

dbo.PGDM.PROGDES,

dbo.CLI.DATEADM,

dbo.CLI.CLINO,

dbo.CLI.SEX,

dbo.CLI.LNAME,

dbo.CLI.FNAME,

dbo.ADDR.STATE,

dbo.ADDR.STREETNO + ' ' + dbo.ADDR.STREET AS ADDRESS,

dbo.ADDR.ZIP,

dbo.ADDR.CITY,

dbo.CLI.MNAME,

dbo.CLI.MINIT,

dbo.CLI.PK_CLI,

dbo.SVAC.PK_SVAC,

dbo.CLI.DOB,

dbo.CLI.TEL1,

dbo.CLI.TEL2,

dbo.EVT.EVTCODE

FROM dbo.CLI

INNER JOIN dbo.SVAC ON dbo.CLI.PK_CLI = dbo.SVAC.PK_SVAC

INNER JOIN dbo.PGDM ON dbo.SVAC.PROGNO = dbo.PGDM.PROGNO

INNER JOIN dbo.ADDR ON dbo.CLI.ADDNO = dbo.ADDR.ADDNO

LEFT OUTER JOIN dbo.EVT ON dbo.CLI.CLINO = dbo.EVT.CLINO AND dbo.EVT.DATEDACT IS NULL AND dbo.EVT.EVTCODE = 'pcn'

WHERE ( dbo.CLI.CLITYPE LIKE '%A%' )

AND (

dbo.SVAC.PROGNO LIKE '%286%'

OR dbo.SVAC.PROGNO LIKE '%288%'

OR dbo.SVAC.PROGNO LIKE '%289%'

);

If not, then I think that like Alan suggested, we're going to need consumable sample data and expected result based on the sample data. Take a look at the following link --> http://www.sqlservercentral.com/articles/Best+Practices/61537/ <--[/url] for how best to set up sample data in a way to help the unpaid volunteers of this site to help you.

Thanks.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Since no one else pointed it out, I sure will:

WHERE (dbo.CLI.CLITYPE LIKE '%A%') AND (dbo.SVAC.PROGNO LIKE '%286%' OR

dbo.SVAC.PROGNO LIKE '%288%' OR

dbo.SVAC.PROGNO LIKE '%289%')

That is going to lead to some very serious table scans. I sure hope your tables are not that big because this query, with that WHERE clause, is going to read every row, every time. It's having the wild card at the start of the LIKE comparison that's going to kill you. If those are real numbers, they should be treated as such and use an equality operator. If those numbers are stored as part of some bigger column of a compound value or something, I strongly advise you to break that out into another column where you can use equality operations instead of this type of LIKE comparison.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Grant Fritchey (4/11/2015)
Since no one else pointed it out, I sure will:
WHERE (dbo.CLI.CLITYPE LIKE '%A%') AND (dbo.SVAC.PROGNO LIKE '%286%' OR
dbo.SVAC.PROGNO LIKE '%288%' OR
dbo.SVAC.PROGNO LIKE '%289%')
That is going to lead to some very serious table scans. I sure hope your tables are not that big because this query, with that WHERE clause, is going to read every row, every time. It's having the wild card at the start of the LIKE comparison that's going to kill you. If those are real numbers, they should be treated as such and use an equality operator. If those numbers are stored as part of some bigger column of a compound value or something, I strongly advise you to break that out into another column where you can use equality operations instead of this type of LIKE comparison.

Just wonder how this works

😎

dbo.CLI.CLITYPE LIKE '%A%'

with this

CLI.CLITYPE

-----------

ACCEPTED

ABORTED

FAILED

IN ADMINISTRATION

IN ARREARS

ARCHIVED

ADORABLE

PROFITABLE

ANONYMOUS

BTW: this looks like a gui/wizard generated query, much better to use aliases than a full reference to an object, suggest you should improve the style;-)