none
SQL querry need help RRS feed

  • Question

  • Hi,

    i am very new in using SQL and i am still learning.

    I need help with a SQL querry.

    Here is my problem.

    Table1

    ResourceID, Computername

    101, Computer1

    201, Computer2

    301, Computer3

    ....

    Table 2

    ResourceID, Software, Version

    101, Java, 8

    101, Adobe

    101, Word

    201, Java, 7

    201, Adobe

    301, Word

    Output should look like that:

    101, Computer1, Java, 8

    201, Computer2, Java, 7

    301, Computer3, Java, not installed

    I am getting:

    101, Computer1, Java, 8

    101, Computer1, Java, not installed

    201, Computer2, Java, 7

    201, Computer2, Java, notinstalled

    301, Computer3, Java, not installed

    Querry I am using:

    SELECT DISTINCT Table1.Computername, Table1.ResourceID, Table2.Software,
    
    CASE	WHEN Table2.Software LIKE '%JDK%' THEN 'Java'
    		WHEN Table2.Software NOT LIKE '%JAVA%' THEN 'notinstalled'
    END AS [Java]
    
    
    
    FROM   v_R_System LEFT JOIN
                             Table2 ON Table1.ResourceID = Table2.ResourceID




    • Edited by Strahle_fz Monday, August 5, 2019 9:33 AM
    Monday, August 5, 2019 9:21 AM

Answers

  • Thank you for your answer. To be honest, I did not quite understand her. But I found a workaround using a temp table.

    SELECT v_GS_INSTALLED_SOFTWARE.ResourceID, v_GS_INSTALLED_SOFTWARE.ProductName0, v_GS_INSTALLED_SOFTWARE.ProductVersion0
    Into #TMP_Software1
    FROM v_GS_INSTALLED_SOFTWARE
    WHERE v_GS_INSTALLED_SOFTWARE.ProductName0 LIKE '%JDK%'
    
    SELECT DISTINCT v_R_System.Name0, v_GS_COMPUTER_SYSTEM.PrimaryOwnerName0, #TMP_Software1.ProductName0 AS JAVA_Product, #TMP_Software1.ProductVersion0 AS JAVA_Version 
    
    
    FROM   v_R_System LEFT JOIN
                             v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID LEFT JOIN
                             #TMP_Software1 ON v_R_System.ResourceID = #TMP_Software1.ResourceID
    

    • Marked as answer by Strahle_fz Tuesday, August 6, 2019 6:20 AM
    Tuesday, August 6, 2019 6:20 AM

All replies

  • create table #t1 (resid int, compname char(2))

    insert into #t1 values (101,'C1')
    insert into #t1 values (201,'C2')
    insert into #t1 values (301,'C3')

    create table #t2 (resid int, SOWT varchar(20),ver int)

    insert into #t2 values (101,'Java',8)
    insert into #t2 values (101,'adobe',null)
    insert into #t2 values (101,'word',null)
    insert into #t2 values (201,'Java',7)
    insert into #t2 values (201,'adobe',null)
    insert into #t2 values (301,'word',null)

    select #t1.resid,coalesce(SOWT,'Java') soft,coalesce(cast(ver as varchar(10)),'not instralled') ver
     from #t1 left join #t2
    on #t1.resid=#t2.resid
    and SOWT='Java'

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, August 5, 2019 9:48 AM
    Moderator
  • Thank you for your answer. To be honest, I did not quite understand her. But I found a workaround using a temp table.

    SELECT v_GS_INSTALLED_SOFTWARE.ResourceID, v_GS_INSTALLED_SOFTWARE.ProductName0, v_GS_INSTALLED_SOFTWARE.ProductVersion0
    Into #TMP_Software1
    FROM v_GS_INSTALLED_SOFTWARE
    WHERE v_GS_INSTALLED_SOFTWARE.ProductName0 LIKE '%JDK%'
    
    SELECT DISTINCT v_R_System.Name0, v_GS_COMPUTER_SYSTEM.PrimaryOwnerName0, #TMP_Software1.ProductName0 AS JAVA_Product, #TMP_Software1.ProductVersion0 AS JAVA_Version 
    
    
    FROM   v_R_System LEFT JOIN
                             v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID LEFT JOIN
                             #TMP_Software1 ON v_R_System.ResourceID = #TMP_Software1.ResourceID
    

    • Marked as answer by Strahle_fz Tuesday, August 6, 2019 6:20 AM
    Tuesday, August 6, 2019 6:20 AM