Subquery and multiple rows

1

Hi all,

I'm trying to get results from below query. It returns 0 rows but I know there are some records.

SELECT a.KEY1
,a.[KEY3]
,a.[NOTE1]
,a.[NOTE2]
,a.[NOTE3]
,a.[USER_ID]
,a.[USER_KEY]
,b.name
,[EVENT]
,[EVENT_TABLE]
,[EVENT_DATE]
FROM offense a with (nolock)
inner join empmast b on a.user_key = b.perno
where KEY1 in ('ECSO19OFF006971','ECSO19OFF005406','ECSO16OFF011326','ECSO16OFF011299','ECSO16OFF002158','ECSO19OFF006999','ECSO19OFF006971','ECSO18OFF010285',
'ECSO17OFF022301','ECSO13OFF033598','ECSO13OFF009674','ECSO12OFF000350','ECSO09OFF015200')
AND KEY1 IN (select UNIQUEKEY
from OFFENSE with (nolock))
order by event_date desc

Each of the long 'ECSOOFF' numbers has a UNIQUEKEY field in another table.

Thanks for your help so much. I appreciate it.

2

What does the result for this looks like?

select a.user_key , b.perno
FROM offense a with (nolock)
left join empmast b on a.user_key = b.perno
3

Post sample data. Either KEY1 does not exist in in the ECSO list of values or KEY1 does not exist in the UNIQUEKEY column in the offense table or none of the values in the ECSO list of values exist in the UNIQUEKEY column of the offense table or user_key does not exist in the perno column of the empmast table.

1 Like
4

Take for instance, the first number: ECSO19OFF006971

When I query the first table I get results...

select * from off_log where key1 = 'ECSO19OFF006971'

key1 key3 note3
ECSO19OFF006971

Likewise, when I search for the associated UNIQUEKEY field in the second table.

select offenseno, uniquekey from offense
where offenseno = 'ECSO19OFF006971'

offenseno uniquekey
ECSO19OFF006971 1(3842929*%#@

So I know the data is there. It is true for several of the OFF values I have listed. Thanks again.

5

Your query is looking for the value in UNIQUEKEY not offenseno
KEY1 IN (select UNIQUEKEY
from OFFENSE with (nolock)

6

Ah!!! Thanks for the catch. The thing is, though, the KEY1 field may be either the OFF number or the associated UNIQUEKEY value. I'm trying to return the results where it is only the UNIQUEKEY value for the listed OFF values.

7

Can you post sample data and expected results for your query?

8

Here is sample data from the off_log table. Notice the KEY1 field contains of two kinds: 1) ECSOXXXXX, and sometimes 2) the UNIQUEKEY value, which is found by querying another table, OFFENSE (select uniquekey from OFFENSE where offenseno = 'ECSOXXXXX')

Capture

I would like to get a listing of only the UNIQUEKEY rows in the off_log table for the specified 'ECSOXXXXX' values mentioned above.

Thanks for your patience and help.

9

Sorry, something is missing or I'm just not understanding. Your query above references 2 tables, OFFENSE and EMPMAST. There is no reference to an OFF_LOG table in the query. You will need to post sample data from ALL of the tables involved and what you expect for a result from your query.

10

Nope. My goof again. So sorry. It is supposed to be this.

11

Please post sample data from all tables involved and expected query results to remove any confusion. Thanks

12

please post your data the following way

create table #sample(KEY1 varchar(250), KEY2 varchar(250),, KEY3 INT) ---etc all columns

insert into #sample
select 'ESCO09OFF000497', '', 5656 union
select 'ESCO06OFF024141', '', null   --provide as much data as you can

etc

Help us help you.