Retrieving distinct values from two tables in vb.net

  • JohnPapa

    Mr or Mrs. 500

    Points: 533

    https://cdn.sstatic.net/Img/unified/sprites.svg?v=438582dc9e27), none; background-position-x: 0px; background-position-y: -170px; background-repeat: no-repeat; background-size: auto; border-bottom-color: rgb(0, 119, 204); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 119, 204); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 119, 204); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 119, 204); border-top-style: none; border-top-width: 0px; box-sizing: border-box; color: rgb(0, 119, 204); cursor: pointer; display: block; font-family: Arial,"Helvetica Neue",Helvetica,sans-serif; font-size: 1px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; height: 30px; line-height: 1.26px; margin-bottom: 2px; margin-left: 3px; margin-right: 3px; margin-top: 0px; overflow: hidden; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-decoration: none; text-indent: -9999px; vertical-align: baseline; width: 40px;"> 0 https://cdn.sstatic.net/Img/unified/sprites.svg?v=438582dc9e27), none; background-position-x: 0px; background-position-y: -220px; background-repeat: no-repeat; background-size: auto; border-bottom-color: rgb(0, 119, 204); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 119, 204); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 119, 204); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 119, 204); border-top-style: none; border-top-width: 0px; box-sizing: border-box; color: rgb(0, 119, 204); cursor: pointer; display: block; font-family: Arial,"Helvetica Neue",Helvetica,sans-serif; font-size: 1px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; height: 30px; line-height: 1.26px; margin-bottom: 10px; margin-left: 3px; margin-right: 3px; margin-top: 0px; overflow: hidden; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-decoration: none; text-indent: -9999px; vertical-align: baseline; width: 40px;">down vote https://cdn.sstatic.net/Img/unified/sprites.svg?v=438582dc9e27), none; background-position-x: 0px; background-position-y: -120px; background-repeat: no-repeat; background-size: auto; border-bottom-color: rgb(0, 119, 204); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(0, 119, 204); border-left-style: none; border-left-width: 0px; border-right-color: rgb(0, 119, 204); border-right-style: none; border-right-width: 0px; border-top-color: rgb(0, 119, 204); border-top-style: none; border-top-width: 0px; box-sizing: border-box; color: rgb(0, 119, 204); cursor: pointer; display: block; font-family: Arial,"Helvetica Neue",Helvetica,sans-serif; font-size: 1px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; height: 30px; line-height: 1.26px; margin-bottom: 2px; margin-left: 3px; margin-right: 3px; margin-top: 0px; overflow: hidden; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-decoration: none; text-indent: -9999px; vertical-align: baseline; width: 40px;" href="https://stackoverflow.com/questions/51465676/selecting-distinct-from-two-tables/51465946#">favorite

    I have table tblConsentQuestion with Questions

    intID   nvcText      bitActive17      Question1    True18      Question2    True19      Question3    False

    and table tblConsentData with the questions for every customer

    intID   intCustomerID   bitConsent      intIDQuestion 14      1               False           1915      1               True            18

    WHERE tblConsentQuestion.intID = tblConsentData.intIDQuestion

    I would like to retrieve in a VB.net dataset

    1. All tblConsentData for a specific Customer ie two records with intID = 14 and 15

    2. All active (bitActive = true) records in tblConsentQuestion WHERE tblConsentData.intIDQuestion <> tblConsentQuestion.intID, in this case only intID = 17 record (in addition to the two records)

    Output should be

    Question3   FalseQuestion2   TrueQuestion1   Null

    I tried something like

    str = "Select tblConsentQuestion.intID, bitConsent, nvcText" & fungGetLangId() & " AS nvcqText " _    & " From tblConsentData " _    & " Left OUTER JOIN tblConsentQuestion " _    & " On tblConsentData.intIDQuestion = tblConsentQuestion.intID " _    & " where tblConsentData.intCustomerID = " & intCustomerID & " " _    & " UNION ALL " _    & " Select tblConsentQuestion.intID, -1, nvcText" & fungGetLangId() & " " _    & " From tblConsentQuestion " _    & " Left OUTER JOIN tblConsentData " _    & " On tblConsentData.intIDQuestion = tblConsentQuestion.intID " _    & " WHERE(tblConsentQuestion.bitActive = 'True') "

    and I receive All active records in tblConsentQuestion, whereas I should not receive record with intID = 18, which exists in

  • Jeff Moden

    SSC Guru

    Points: 993788

    I guess that first and foremost, I need to advise you that building concatenated SQL like that leaves you wide open for SQL Injection.  Consider using parameterization instead.

    As for all the stuff above your actual question, what is all of that and how does it pertain to the question?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Thom A

    SSC Guru

    Points: 98136

    Jeff Moden - Sunday, July 22, 2018 1:18 PM

    As for all the stuff above your actual question, what is all of that and how does it pertain to the question?

    Pretty sure the OP has, for some reason, copied a bunch of the compiled webpage from SSC. No idea why. OP, It would be really nice if you edit it out, please. It'll probably also put people off answering your questions, as it makes it look like a "spam" post.

    In regards to your samples, it seems you forgotto provide the data togo with your column headings. Column names, on their own, don't give us very much to go on. There's a link in both my and Jeff's signatures on how to post sample data and DDL; have a look there and post back.

    Thanks.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • JohnPapa

    Mr or Mrs. 500

    Points: 533

    Thanks regarding your comments for injection.

    The column headings are field names.

    I include below the code which works
                str = "    SELECT AllData.* FROM " _
            & "( " _
            & " SELECT Q.intId, D.bitConsent As bitConsent, Q.nvcText" & fungGetLangId() & " " _
            & " FROM tblConsentQuestion AS Q " _
            & " LEFT JOIN tblConsentData AS D " _
            & " WITH (Nolock) " _
            & " ON Q.intId = D.intIDQuestion " _
            & " AND D.intCustomerID = 1 " _
            & " ) AS AllData " _
            & " JOIN tblConsentQuestion Q with (Nolock) " _
            & " ON AllData.nvcText" & fungGetLangId() & " = Q.nvcText" & fungGetLangId() & " " _
            & " WHERE( " _
            & " AllData.bitConsent Is Not NULL " _
            & " OR " _
            & " ( " _
            & " AllData.bitConsent Is NULL " _
            & " AND " _
            & " Q.bitActive = 'True' " _
            & " ) " _
            & " ) " _
            & " ORDER BY AllData.nvcText" & fungGetLangId() & " DESC "

  • Jeff Moden

    SSC Guru

    Points: 993788

    JohnPapa - Monday, July 23, 2018 12:03 AM

    Thanks regarding your comments for injection.

    The column headings are field names.

    I include below the code which works
                str = "    SELECT AllData.* FROM " _
            & "( " _
            & " SELECT Q.intId, D.bitConsent As bitConsent, Q.nvcText" & fungGetLangId() & " " _
            & " FROM tblConsentQuestion AS Q " _
            & " LEFT JOIN tblConsentData AS D " _
            & " WITH (Nolock) " _
            & " ON Q.intId = D.intIDQuestion " _
            & " AND D.intCustomerID = 1 " _
            & " ) AS AllData " _
            & " JOIN tblConsentQuestion Q with (Nolock) " _
            & " ON AllData.nvcText" & fungGetLangId() & " = Q.nvcText" & fungGetLangId() & " " _
            & " WHERE( " _
            & " AllData.bitConsent Is Not NULL " _
            & " OR " _
            & " ( " _
            & " AllData.bitConsent Is NULL " _
            & " AND " _
            & " Q.bitActive = 'True' " _
            & " ) " _
            & " ) " _
            & " ORDER BY AllData.nvcText" & fungGetLangId() & " DESC "

    Ok... so not able to "parameterize" such things (that I know of) but... What have you done in your code to guarantee that they will always be "field names" no matter what?  The "no matter what" may come in the form of a newbie that's desperate for a solution, finds your code, and uses it for human input.

    To be sure, the reason for my comments isn't to slam your code.  The reason is that one of the leading causes of compromised systems is still SQL Injection and I try to help folks keep from reading about their company and themselves in the news.

    You may actually do a check or derive the column names from sys.columns to ensure that the column names are valid but it's not apparent in the code on this post.  Do you do such a thing anywhere?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • JohnPapa

    Mr or Mrs. 500

    Points: 533

    Hi Jeff, many thanks for your comments.
    Can you please provide one example so that I can better understand what you are saying?

Viewing 6 posts - 1 through 6 (of 6 total)

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