Post reply

Joining two queries

  • July 17, 2012 at 11:28 am

    #392762

    I am attempting to join the two queries together. The two queries function properly separately. I've gotten the CASE statement from query 2 to work with query 1.

    Some background, this query is being run against a business intelligence program that creates graphs/charts off autorun inserts. Right now when no new data is inserted into the table for a specific IM.NAME the program looks back to the last record, which skews the chart results. I need a '1' to print out when one of the 5 IM.NAME fields does not return based on the master query below. I've included a SQLfiddle showing the 2nd query in action: SQL Fiddle

    SELECT DISTINCT

    SA.DELETED,

    PAT.VERSION,

    PAT.PATTERNDATE,

    SA.AGENT_VERSION,

    SCL.COMPUTER_NAME AS Computer_Name,

    SCO.OPERATION_SYSTEM AS Operation_System,

    dateadd(s,convert(bigint,SA.CREATION_TIME)/1000,

    '01-01-1970 00:00:00') CREATION_DTTM,

    dateadd(s,convert(bigint,SA.LAST_UPDATE_TIME)/1000,

    '01-01-1970 00:00:00') Lastupdatetime,

    DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000,

    '01-01-1970 00:00:00')LAST_SCAN_TIME,

    PAT.PATTERNDATE AS Pattern_Date,

    SCL.USER_NAME AS User_Name,

    VSC.IP_ADDR1_TEXT AS IP_Add,

    CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-BCK%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-PPP%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-MES%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-MMT%' THEN IM.NAME

    ELSE '1' END AS Group_Name,

    IM.NAME as test

    FROM ((((SEM_AGENT SA

    INNER JOIN SEM_CLIENT SCL

    ON ((SA.COMPUTER_ID=SCL.COMPUTER_ID)

    AND (SA.DOMAIN_ID=SCL.DOMAIN_ID))

    AND (SA.GROUP_ID=SCL.GROUP_ID))

    INNER JOIN SEM_COMPUTER SCO

    ON ((SA.COMPUTER_ID=SCO.COMPUTER_ID)

    AND (SA.DOMAIN_ID=SCO.DOMAIN_ID))

    AND (SA.DELETED=SCO.DELETED))

    INNER JOIN PATTERN PAT

    ON SA.PATTERN_IDX=PAT.PATTERN_IDX)

    INNER JOIN IDENTITY_MAP IM

    ON SCL.GROUP_ID=IM.ID)

    INNER JOIN V_SEM_COMPUTER VSC

    ON SCO.COMPUTER_ID=VSC.COMPUTER_ID

    AND SA.DELETED=0

    WHERE PAT.Patterndate < (SELECT MAX(Patterndate) -1 FROM Pattern)

    AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'

    ORDER BY Computer_Name

    2nd query

    Select (CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-BCK%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-PPP%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-MES%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-MMT%' THEN IM.NAME

    ELSE '1' END) AS Group_Name,

    tagTypes.tagType,

    IM.NAME

    From

    (

    Select '%-FRN%' AS tagType

    UNION

    Select '%-BCK%' AS tagType

    UNION

    Select '%-PPP%' AS tagType

    UNION

    Select '%-MES%' AS tagType

    UNION

    Select '%-MMT%' AS tagType

    ) AS tagTypes

    left join

    IDENTITY_MAP IM

    on IM.NAME like tagTypes.tagType

    And here is what I've gotten so far but I get the error "The multi-part identifier "tagTypes.tagType" could not be bound. The multi-part identifier "tagTypes.tagType" could not be bound"

    SELECT DISTINCT

    SA.DELETED,

    PAT.VERSION,

    PAT.PATTERNDATE,

    SA.AGENT_VERSION,

    SCL.COMPUTER_NAME AS Computer_Name,

    SCO.OPERATION_SYSTEM AS Operation_System,

    dateadd(s,convert(bigint,SA.CREATION_TIME)/1000,

    '01-01-1970 00:00:00') CREATION_DTTM,

    dateadd(s,convert(bigint,SA.LAST_UPDATE_TIME)/1000,

    '01-01-1970 00:00:00') Lastupdatetime,

    DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000,

    '01-01-1970 00:00:00')LAST_SCAN_TIME,

    PAT.PATTERNDATE AS Pattern_Date,

    SCL.USER_NAME AS User_Name,

    VSC.IP_ADDR1_TEXT AS IP_Add,

    CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-BCK%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-PPP%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-MES%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-MMT%' THEN IM.NAME

    ELSE '1' END AS Group_Name,

    IM.NAME AS test,

    tagTypes.tagType,

    (Select '%-FRT%' AS tagType

    UNION

    Select '%-BCK%' AS tagType

    UNION

    Select '%-PPP%' AS tagType

    UNION

    Select '%-MES%' AS tagType

    UNION

    Select '%-MMT%' AS tagType)

    AS tagTypes

    FROM ((((SEM_AGENT SA

    INNER JOIN SEM_CLIENT SCL

    ON ((SA.COMPUTER_ID=SCL.COMPUTER_ID)

    AND (SA.DOMAIN_ID=SCL.DOMAIN_ID))

    AND (SA.GROUP_ID=SCL.GROUP_ID))

    INNER JOIN SEM_COMPUTER SCO

    ON ((SA.COMPUTER_ID=SCO.COMPUTER_ID)

    AND (SA.DOMAIN_ID=SCO.DOMAIN_ID))

    AND (SA.DELETED=SCO.DELETED))

    INNER JOIN PATTERN PAT

    ON SA.PATTERN_IDX=PAT.PATTERN_IDX)

    INNER JOIN IDENTITY_MAP IM

    ON SCL.GROUP_ID=IM.ID)

    INNER JOIN V_SEM_COMPUTER VSC

    ON SCO.COMPUTER_ID=VSC.COMPUTER_ID

    LEFT JOIN IDENTITY_MAP

    ON IM.NAME = tagTypes.tagType

    AND SA.DELETED=0

    WHERE PAT.Patterndate < (SELECT MAX(Patterndate) -1 FROM Pattern)

    AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'

    ORDER BY Computer_Name

  • July 17, 2012 at 11:33 am

    #1513756

    That error is saying that there is no column named tagType in the tagTypes table. In the second query, you have:

    From

    (

    Select '%-FRN%' AS tagType

    UNION

    Select '%-BCK%' AS tagType

    UNION

    Select '%-PPP%' AS tagType

    UNION

    Select '%-MES%' AS tagType

    UNION

    Select '%-MMT%' AS tagType

    ) AS tagTypes

    So you have a virtual table called tagTypes with a column called tagType.

    In the query giving you an error, that is not the case. The subquery is in your SELECT clause, not the FROM.

    Jared
    CE - Microsoft

  • July 17, 2012 at 11:40 am

    #1513761

    Try this:

    SELECT DISTINCT

    SA.DELETED,

    PAT.VERSION,

    PAT.PATTERNDATE,

    SA.AGENT_VERSION,

    SCL.COMPUTER_NAME AS Computer_Name,

    SCO.OPERATION_SYSTEM AS Operation_System,

    dateadd(s,convert(bigint,SA.CREATION_TIME)/1000,

    '01-01-1970 00:00:00') CREATION_DTTM,

    dateadd(s,convert(bigint,SA.LAST_UPDATE_TIME)/1000,

    '01-01-1970 00:00:00') Lastupdatetime,

    DATEADD(s, convert(bigint,LAST_SCAN_TIME)/1000,

    '01-01-1970 00:00:00')LAST_SCAN_TIME,

    PAT.PATTERNDATE AS Pattern_Date,

    SCL.USER_NAME AS User_Name,

    VSC.IP_ADDR1_TEXT AS IP_Add,

    CASE WHEN IM.NAME LIKE '%-FRN%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-BCK%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-PPP%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-MES%' THEN IM.NAME

    WHEN IM.NAME LIKE '%-MMT%' THEN IM.NAME

    ELSE '1' END AS Group_Name,

    IM.NAME AS test,

    tagTypes.tagType

    FROM SEM_AGENT SA

    INNER JOIN SEM_CLIENT SCL

    ON ((SA.COMPUTER_ID=SCL.COMPUTER_ID)

    AND (SA.DOMAIN_ID=SCL.DOMAIN_ID))

    AND (SA.GROUP_ID=SCL.GROUP_ID))

    INNER JOIN SEM_COMPUTER SCO

    ON ((SA.COMPUTER_ID=SCO.COMPUTER_ID)

    AND (SA.DOMAIN_ID=SCO.DOMAIN_ID))

    AND (SA.DELETED=SCO.DELETED))

    INNER JOIN PATTERN PAT

    ON SA.PATTERN_IDX=PAT.PATTERN_IDX)

    INNER JOIN IDENTITY_MAP IM

    ON SCL.GROUP_ID=IM.ID)

    INNER JOIN V_SEM_COMPUTER VSC

    ON SCO.COMPUTER_ID=VSC.COMPUTER_ID

    LEFT JOIN IDENTITY_MAP

    ON IM.NAME = tagTypes.tagType

    AND SA.DELETED=0

    LEFT JOIN (Select '%-FRT%' AS tagType

    UNION

    Select '%-BCK%' AS tagType

    UNION

    Select '%-PPP%' AS tagType

    UNION

    Select '%-MES%' AS tagType

    UNION

    Select '%-MMT%' AS tagType) tagTypes

    ON IM.name = tagTypes.tagType

    WHERE PAT.Patterndate < (SELECT MAX(Patterndate) -1 FROM Pattern)

    AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'

    ORDER BY Computer_Name

    Jared
    CE - Microsoft

  • July 17, 2012 at 11:43 am

    #1513763

    Ugh... Hold on, this is messed up... Give me a minute to fix.

    Jared
    CE - Microsoft

  • July 17, 2012 at 11:45 am

    #1513766

    Thanks for the response.

    I tried the new query and received this error "Incorrect syntax near ')'. Incorrect syntax near 'tagTypes'. Incorrect syntax near the keyword 'AND'."

    I know these are small errors but I've been staring at this all morning and I appreciate the help.

  • July 17, 2012 at 11:45 am

    #1513767

    Ok, now I am lost... What is the point of your 2nd query? It does nothing for you.

    Jared
    CE - Microsoft

  • July 17, 2012 at 11:45 am

    #1513768

    Ok, no problem. Thanks

  • July 17, 2012 at 11:49 am

    #1513771

    The second query prints a row with '1's if no IM.NAME values exists that are like a value in the virtual table tagTypes

  • July 17, 2012 at 11:59 am

    #1513777

    Ok, so just insert another CASE after our groupName:

    SELECT DISTINCT SA.DELETED

    ,PAT.VERSION

    ,PAT.PATTERNDATE

    ,SA.AGENT_VERSION

    ,SCL.COMPUTER_NAME AS Computer_Name

    ,SCO.OPERATION_SYSTEM AS Operation_System

    ,dateadd(s, convert(BIGINT, SA.CREATION_TIME) / 1000, '01-01-1970 00:00:00') CREATION_DTTM

    ,dateadd(s, convert(BIGINT, SA.LAST_UPDATE_TIME) / 1000, '01-01-1970 00:00:00') Lastupdatetime

    ,DATEADD(s, convert(BIGINT, LAST_SCAN_TIME) / 1000, '01-01-1970 00:00:00') LAST_SCAN_TIME

    ,PAT.PATTERNDATE AS Pattern_Date

    ,SCL.USER_NAME AS User_Name

    ,VSC.IP_ADDR1_TEXT AS IP_Add

    ,CASE

    WHEN IM.NAME LIKE '%-FRN%'

    THEN IM.NAME

    WHEN IM.NAME LIKE '%-BCK%'

    THEN IM.NAME

    WHEN IM.NAME LIKE '%-PPP%'

    THEN IM.NAME

    WHEN IM.NAME LIKE '%-MES%'

    THEN IM.NAME

    WHEN IM.NAME LIKE '%-MMT%'

    THEN IM.NAME

    ELSE '1'

    END AS Group_Name

    ,IM.NAME AS test

    ,CASE

    WHEN IM.NAME LIKE '%-FRN%'

    THEN '%-FRN%'

    WHEN IM.NAME LIKE '%-BCK%'

    THEN '%-BCK%'

    WHEN IM.NAME LIKE '%-PPP%'

    THEN '%-PPP%'

    WHEN IM.NAME LIKE '%-MES%'

    THEN '%-MES%'

    WHEN IM.NAME LIKE '%-MMT%'

    THEN '%-MMT%'

    ELSE '1'

    END AS tagType

    FROM SEM_AGENT SA

    INNER JOIN SEM_CLIENT SCL

    ON SA.COMPUTER_ID = SCL.COMPUTER_ID

    AND SA.DOMAIN_ID = SCL.DOMAIN_ID

    AND SA.GROUP_ID = SCL.GROUP_ID

    INNER JOIN SEM_COMPUTER SCO

    ON SA.COMPUTER_ID = SCO.COMPUTER_ID

    AND SA.DOMAIN_ID = SCO.DOMAIN_ID

    AND SA.DELETED = SCO.DELETED

    INNER JOIN PATTERN PAT

    ON SA.PATTERN_IDX = PAT.PATTERN_IDX

    INNER JOIN IDENTITY_MAP IM

    ON SCL.GROUP_ID = IM.ID

    INNER JOIN V_SEM_COMPUTER VSC

    ON SCO.COMPUTER_ID = VSC.COMPUTER_ID

    AND SA.DELETED = 0

    WHERE PAT.Patterndate < (

    SELECT MAX(Patterndate) - 1

    FROM Pattern

    )

    AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'

    ORDER BY Computer_Name

    Also, all of your parentheses were making this extremely hard to read. Try not to use parentheses unless they are required to make it easier to read.

    Jared
    CE - Microsoft

  • July 17, 2012 at 12:11 pm

    #1513787

    Thanks, this query runs but its not giving me the result I was looking for.

    For example, this query returned one row with MMT. However, I would like to receive the row with MMT as well as blank rows for the other 4 tagTypes with just a '1' in the row.

  • July 17, 2012 at 12:24 pm

    #1513794

    neb2886 (7/17/2012)

    Thanks, this query runs but its not giving me the result I was looking for.

    For example, this query returned one row with MMT. However, I would like to receive the row with MMT as well as blank rows for the other 4 tagTypes with just a '1' in the row.

    I really don't know what that means being that you have not given any sample data. "The row with MMT" means nothing to me. For all I know, your table only has 1 row in it 🙂 Take a look at the link in my signature to an article by Jeff Moden on how to post. While you are getting that together, I will attempt to see if I can figure out what you are doing by attempting to simulate your tables and some data.

    Jared
    CE - Microsoft

  • July 17, 2012 at 12:26 pm

    #1513796

    Try changing your INNER JOIN IDENTITY_MAP IM to a LEFT JOIN IDENTITY_MAP IM

    Jared
    CE - Microsoft

  • July 17, 2012 at 12:40 pm

    #1513806

    This is an example of how the virtual table was working:

    http://sqlfiddle.com/#!3/f2bb4/1

    The whole larger query is checking an anti-virus database for hosts within 5 different groups that have out of date pattern dates.

    Right now, today, I have one host within the 'MMT' group that has a definition out of date. Which means that all other hosts in the 'MMT' group as well as all of the hosts in the other 4 groups (FRT, BCK, PPP and MES) are up to date and therefore do not display.

    I am using a tool that charts this data and it uses this query to insert data on the 1st and 15th of every month into a secondary database (which it then draws from to create its charts). Right now if the insert statement runs and there are no new updates for a particular group (i.e. on the 1st the FRT group had 2 hosts out of date but on the 15th has 0) the query will not show a blank row for the FRT group which would then be inserted into the secondary database and timestamped for the purposes of the chart.

    The result right now is that I am only getting old data for some of the groups and the charts are displaying some as being out of date when they actually are not.

    Does this help clarify? I can be more specific if need be.

  • July 17, 2012 at 12:44 pm

    #1513809

    neb2886 (7/17/2012)

    This is an example of how the virtual table was working:

    http://sqlfiddle.com/#!3/f2bb4/1

    The whole larger query is checking an anti-virus database for hosts within 5 different groups that have out of date pattern dates.

    Right now, today, I have one host within the 'MMT' group that has a definition out of date. Which means that all other hosts in the 'MMT' group as well as all of the hosts in the other 4 groups (FRT, BCK, PPP and MES) are up to date and therefore do not display.

    I am using a tool that charts this data and it uses this query to insert data on the 1st and 15th of every month into a secondary database (which it then draws from to create its charts). Right now if the insert statement runs and there are no new updates for a particular group (i.e. on the 1st the FRT group had 2 hosts out of date but on the 15th has 0) the query will not show a blank row for the FRT group which would then be inserted into the secondary database and timestamped for the purposes of the chart.

    The result right now is that I am only getting old data for some of the groups and the charts are displaying some as being out of date when they actually are not.

    Does this help clarify? I can be more specific if need be.

    I'm sorry... This means nothing to me. I need table definitions and sample data with expected results. Your virtual table is worthless because the data is already being returned, as far as I can see.

    Jared
    CE - Microsoft

  • July 17, 2012 at 12:53 pm

    #1513813

    OH! You want all of those types no matter what. That is why you are doing that first... Ok. Do this:

    ;WITH tagTypes (tagType VARCHAR(10))

    AS

    (Select '%-FRN%' AS tagType

    UNION

    Select '%-BCK%' AS tagType

    UNION

    Select '%-PPP%' AS tagType

    UNION

    Select '%-MES%' AS tagType

    UNION

    Select '%-MMT%' AS tagType)

    SELECT DISTINCT SA.DELETED

    ,PAT.VERSION

    ,PAT.PATTERNDATE

    ,SA.AGENT_VERSION

    ,SCL.COMPUTER_NAME AS Computer_Name

    ,SCO.OPERATION_SYSTEM AS Operation_System

    ,dateadd(s, convert(BIGINT, SA.CREATION_TIME) / 1000, '01-01-1970 00:00:00') CREATION_DTTM

    ,dateadd(s, convert(BIGINT, SA.LAST_UPDATE_TIME) / 1000, '01-01-1970 00:00:00') Lastupdatetime

    ,DATEADD(s, convert(BIGINT, LAST_SCAN_TIME) / 1000, '01-01-1970 00:00:00') LAST_SCAN_TIME

    ,PAT.PATTERNDATE AS Pattern_Date

    ,SCL.USER_NAME AS User_Name

    ,VSC.IP_ADDR1_TEXT AS IP_Add

    ,CASE

    WHEN IM.NAME LIKE '%-FRN%'

    THEN IM.NAME

    WHEN IM.NAME LIKE '%-BCK%'

    THEN IM.NAME

    WHEN IM.NAME LIKE '%-PPP%'

    THEN IM.NAME

    WHEN IM.NAME LIKE '%-MES%'

    THEN IM.NAME

    WHEN IM.NAME LIKE '%-MMT%'

    THEN IM.NAME

    ELSE '1'

    END AS Group_Name

    ,IM.NAME AS test

    ,tagTypes.tagType

    FROM tagTypes

    LEFT JOIN IDENTITY_MAP IM

    ON IM.NAME like tagTypes.tagType

    INNER JOIN SEM_CLIENT SCL

    ON SCL.GROUP_ID = IM.ID

    INNER JOIN SEM_AGENT SA

    ON SA.COMPUTER_ID = SCL.COMPUTER_ID

    AND SA.DOMAIN_ID = SCL.DOMAIN_ID

    AND SA.GROUP_ID = SCL.GROUP_ID

    INNER JOIN SEM_COMPUTER SCO

    ON SA.COMPUTER_ID = SCO.COMPUTER_ID

    AND SA.DOMAIN_ID = SCO.DOMAIN_ID

    AND SA.DELETED = SCO.DELETED

    INNER JOIN PATTERN PAT

    ON SA.PATTERN_IDX = PAT.PATTERN_IDX

    INNER JOIN V_SEM_COMPUTER VSC

    ON SCO.COMPUTER_ID = VSC.COMPUTER_ID

    AND SA.DELETED = 0

    WHERE PAT.Patterndate < (

    SELECT MAX(Patterndate) - 1

    FROM Pattern

    )

    AND SCO.OPERATION_SYSTEM NOT LIKE '%2000%'

    ORDER BY Computer_Name

    If this does not solve your problem, I am not going to be able to help any more until I get the requsted tables and sample data. It is just a waste of my time and everyone else reading this if I am just shooting in the dark.

    Jared
    CE - Microsoft

Viewing 15 posts - 1 through 15 (of 22 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy