Retrieving distinct values from two tables in vb.net

Question

Retrieving distinct values from two tables in vb.net

JohnPapa

Mr or Mrs. 500

Points: 583
More actions
July 22, 2018 at 11:03 am

#150817
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
All tblConsentData for a specific Customer ie two records with intID = 14 and 15
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

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

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

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99134 More actions · Answer 2

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.
Larnu.uk

JohnPapa Mr or Mrs. 500 Points: 583 More actions · Answer 3

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: 1004436 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JohnPapa Mr or Mrs. 500 Points: 583 More actions · Answer 5

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