Newbie - SQL Server Forums

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

Newbie

Author

Topic

marp
Starting Member

6 Posts

Posted - 2014-11-06 : 09:28:12

I need all machines with the software mindmanager, code 0409 or 0407 but not with the tag cst_MM_14_15. The tag also uses SOFTWARE.DISPLAY_NAME and I get “wrong” results. How can solve that? Tia!

select
MACHINE.NAME,
SOFTWARE.DISPLAY_NAME,
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE as 'OS Language'
from
MACHINE
left join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
left join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
left join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
where
(SOFTWARE.DISPLAY_NAME like '%Mindmanager%'
or '%Mindjet%')
and (MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0409'
or MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0407')
and (SOFTWARE.DISPLAY_NAME not like '%cst_MM_14_15%')
order by MACHINE.NAME

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-06 : 10:13:04

You get wrong results? I'm surprised you get any results at all, since your query has a syntax error:


where
(SOFTWARE.DISPLAY_NAME like '%Mindmanager%'
or '%Mindjet%')

is not valid SQL. perhaps you mean


(SOFTWARE.DISPLAY_NAME like '%Mindmanager%'
or SOFTWARE.DISPLAY_NAME like '%Mindjet%')

marp
Starting Member

6 Posts

Posted - 2014-11-06 : 10:26:50

you're right (thank you!) but i still have the problem with the SOFTWARE.DISPLAY_NAME not like '%cst_MM_14_15%'. i need all machines with mindmanager and mindjet but not the with the tag cst_MM_14_15 (they all have mindmanager or mindjet installed). andy idea?

select
MACHINE.NAME,
SOFTWARE.DISPLAY_NAME,
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE as 'OS Language'
from
MACHINE
left join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
left join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
left join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
where
(SOFTWARE.DISPLAY_NAME like '%Mindmanager%'
or SOFTWARE.DISPLAY_NAME like '%Mindjet%')
and (MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0409'
or MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0407')
and (SOFTWARE.DISPLAY_NAME not like '%cst_MM_14_15%')
order by MACHINE.NAME

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-06 : 10:36:16

Is there any reason why you need to use LEFT JOIN instead of INNER JOIN? With the possibility of NULL return values you would need use the ISNULL statement to properly handle them.

and (ISNULL(SOFTWARE.DISPLAY_NAME, '') not like '%cst_MM_14_15%')

Give that try or change to inner joins and see if it helps.

marp
Starting Member

6 Posts

Posted - 2014-11-07 : 00:32:43

quote:
Originally posted by mandm

Is there any reason why you need to use LEFT JOIN instead of INNER JOIN? With the possibility of NULL return values you would need use the ISNULL statement to properly handle them.

and (ISNULL(SOFTWARE.DISPLAY_NAME, '') not like '%cst_MM_14_15%')

Give that try or change to inner joins and see if it helps.

Still not getting the desired result. Is it not possible to do a scecond where on SOFTWARE.DISPLAY_NAME - all machines with mindjet or mindmanager and they should not have the tag cst_MM_14_15 ...i received machines with mindjet or mindmanager and some of them also have the tag cst_MM_14_15. Tia!

select
MACHINE.NAME,
SOFTWARE.DISPLAY_NAME,
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE as 'OS Language'
from
MACHINE
inner join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
inner join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
inner join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
where
(SOFTWARE.DISPLAY_NAME like '%Mindmanager%'
or SOFTWARE.DISPLAY_NAME like '%Mindjet%')
and (MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0409'
or MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0407')
and (SOFTWARE.DISPLAY_NAME not like '%cst_MM_14_15%')
order by MACHINE.NAME

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-07 : 08:15:42

Could you post some sample data? That would help everyone find what the issue is.

marp
Starting Member

6 Posts

Posted - 2014-11-10 : 01:07:41

quote:
Originally posted by mandm

Could you post some sample data? That would help everyone find what the issue is.

Example:

select
MACHINE.NAME,
SOFTWARE.DISPLAY_NAME,
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE as 'OS Language'
from
MACHINE
inner join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
inner join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
inner join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
where
(SOFTWARE.DISPLAY_NAME like '%Mindmanager%'
or SOFTWARE.DISPLAY_NAME like '%Mindjet%')
and (MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0409'
or MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0407')
and (SOFTWARE.DISPLAY_NAME not like '%cst_MM_14_15%')
order by MACHINE.NAME

NAME DISPLAY_NAME OS Language
BONN564XWL1 Mindjet MindManager Pro 6 409
BONNBCSWRP1 Mindjet MindManager Pro 6 409
BREN8Q26H4J Mindjet MindManager Pro 6 407
BURN2K46ZN1 MindManager X5 Pro 409
BURN66F82M1 MindManager X5 Pro 409
BURNBNK7XP1 MindManager X5 Pro 409
CLYN1V3NKQ1 Mindjet MindManager Pro 6 409
CLYNJB7C6S1 Mindjet MindManager Pro 6 409
CLYNJTDMKQ1 Mindjet MindManager Pro 6 409
DAIW4H5K23X Mindjet MindManager Viewer 6 409
daiwbc5w83x Mindjet MindManager Viewer 6 409
DALN1J6542X Mindjet MindManager Viewer 6 409
DALN2F7X2R1 Mindjet MindManager 9 409
DALN3SVY1Q1 Mindjet MindManager Viewer 6 409
DALN59C9YN1 Mindjet MindManager Viewer 6 409
.
.
.

...but when i do an

select
MACHINE.NAME,
SOFTWARE.DISPLAY_NAME,
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE as 'OS Language'
from
MACHINE
inner join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
inner join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
inner join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
where
(SOFTWARE.DISPLAY_NAME like '%cst_MM_14_15%') and
(MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0409'
or MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0407')
order by MACHINE.NAME

NAME DISPLAY_NAME OS Language
LEON3F5CXW1 cst_MM_14_15 407
LEON5CG4381DYV cst_MM_14_15 407
LEON5LGV3X1 cst_MM_14_15 407
LEONB3T93N1 cst_MM_14_15 407
LEONCGGF7W1 cst_MM_14_15 407
LEONCNU4119TXT cst_MM_14_15 407
LEONCNU421997Z cst_MM_14_15 407
LEOND3JD4S1 cst_MM_14_15 409
LEONG9ZXKQ1 cst_MM_14_15 407
VEIN4892PX1 cst_MM_14_15 407
VEINCNU424BM66 cst_MM_14_15 407
VEIWCD8J95J cst_MM_14_15 407
WBHN12ZLN4J cst_MM_14_15 407
WBHNCNU411CSSD cst_MM_14_15 407
WBHNCNU436B177 cst_MM_14_15 407
WIHN2TPJPP1 cst_MM_14_15 409
WIHN45SHLQ1 cst_MM_14_15 407
WIHN9R59LV1 cst_MM_14_15 407
WIHNCNU3519R58 cst_MM_14_15 407
WIHNCNU4249F1H cst_MM_14_15 407

The problem is that for example the machine LEON3F5CXW1 has MindManager 15 but also the flag cst_MM_14_15. i only want machines with Mindjet or Mindmanager, OS language 407 or 409 on the should not have the flag cst_MM_14_15.

NAME DISPLAY_NAME OS Language
LEON3F5CXW1 Mindjet MindManager 15 407

NAME DISPLAY_NAME OS Language
LEON3F5CXW1 cst_MM_14_15 407

Tia!

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-10 : 06:59:37

Can you show an example from your first query showing the incorrect results? It doesn't seem like you should even need the second filter for cst_MM_14_15 since you are already specifying that you only want Mindmanager or Mindjet.

marp
Starting Member

6 Posts

Posted - 2014-11-10 : 07:45:12

quote:
Originally posted by mandm

Can you show an example from your first query showing the incorrect results? It doesn't seem like you should even need the second filter for cst_MM_14_15 since you are already specifying that you only want Mindmanager or Mindjet.

Query

select
MACHINE.NAME,
SOFTWARE.DISPLAY_NAME,
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE as 'OS Language'
from
MACHINE
left join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
left join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
left join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
where
(MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0409'
or MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0407')
and (SOFTWARE.DISPLAY_NAME like '%cst_MM_14_15%')
and (MACHINE.NAME like 'LEON3F5CXW1')
order by MACHINE.NAME

Result (this is one machine as example) :

LEON3F5CXW1 cst_MM_14_15 0407

Query:

select
MACHINE.NAME,
SOFTWARE.DISPLAY_NAME,
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE as 'OS Language'
from
MACHINE
left join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
left join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
left join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
where
(SOFTWARE.DISPLAY_NAME like '%Mindmanager%'
or SOFTWARE.DISPLAY_NAME like '%Mindjet%')
and (MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0409'
or MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0407')
and (SOFTWARE.DISPLAY_NAME not like '%cst_MM_14_15%')
order by MACHINE.NAME

Result:

LEON3F5CXW1 Mindjet MindManager 15 0407

Still get the machine LEON3F5CXW1 but this machine has the flag cst_MM_14_15 ...i don't want machines with mindmanager or mindjet with the flag cst_MM_14_15 in my report. I also removed the part (SOFTWARE.DISPLAY_NAME not like '%cst_MM_14_15%') ...result contains LEON3F5CXW1. Tia!

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-10 : 08:36:18

Okay so let me restate the problem so I'm sure I understand.

When the LEON3F5CXW1 machine also has an entry that contains the cst_MM_14_15 flag you want all LEON3F5CXW1 machine rows excluded from the results even though there is an entry with Mindmanager or Mindjet right?

mandm
Posting Yak Master

120 Posts

Posted - 2014-11-10 : 08:47:58

This might be what you're looking for.

select
MACHINE.NAME,
SOFTWARE.DISPLAY_NAME,
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE as 'OS Language'
from
MACHINE
inner join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
inner join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
inner join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
where
(SOFTWARE.DISPLAY_NAME like '%Mindmanager%'
or SOFTWARE.DISPLAY_NAME like '%Mindjet%')
and (MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0409'
or MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE = '0407')
and MACHINE.NAME NOT IN (
select
MACHINE.NAME,
from
MACHINE
inner join
MACHINE_CUSTOM_INVENTORY ON MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID
inner join
MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
inner join
SOFTWARE ON SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID
WHERE SOFTWARE.DISPLAY_NAME LIKE '%cst_MM_14_15%'
)
order by MACHINE.NAME

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-11-10 : 10:12:22

Alternative to mandm's latest solution:

select m.NAME
      ,s.DISPLAY_NAME
      ,mci.STR_FIELD_VALUE as 'OS Language'
  from MACHINE as m
       inner join MACHINE_CUSTOM_INVENTORY as mci
               on mci.ID=m.ID
       inner join MACHINE_SOFTWARE_JT as msj
               on msj.MACHINE_ID=m.ID
       inner join SOFTWARE as s
               on s.ID=msj.SOFTWARE_ID
 where (s.DISPLAY_NAME like '%Mindmanager%'
    or  s.DISPLAY_NAME like '%Mindjet%'
       )
   and mci.STR_FIELD_VALUE in ('0407','0409')
   and not exists (select m2.ID
                     from MACHINE as m2
                          inner join MACHINE_SOFTWARE_JT as msj2
                                  on msj2.MACHINE_ID=m2.ID
                          inner join SOFTWARE as s2
                                  on s2.ID=msj2.SOFTWARE_ID
                    where m2.ID=m.ID
                      and s2.DISPLAY_NAME like '%cst_MM_14_15%'
                  )
 order by m.NAME

marp
Starting Member

6 Posts

Posted - 2014-11-11 : 07:23:09

Works perfect! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources