8 Replies - 406 Views - Last Post: 25 April 2014 - 08:57 AM Rate Topic: -----

#1 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 445
  • View blog
  • Posts: 3,088
  • Joined: 12-January 10

how would i check if a record exists in a join

Posted 25 April 2014 - 07:07 AM

problem: I have to check whether or not a record exists in a join and if not ignore that user but still load all the other information for that user

  var db = Simple.Data.Database.OpenNamedConnection("sqlConn");

                var expr1 = db.PON_APP_USERS.STATUS == 1;

                var sUsers = db.PON_APP_USERS.FindAll(expr1)
                    .Select(
                    db.PON_APP_USERS.USERKEY,
                    db.PON_APP_USERS.LAST_NAME,
                    db.PON_APP_USERS.FIRST_NAME,
                    db.PON_APP_USERS.AGENCY,
                    db.PON_APP_USERS.USERUSERS.CBI_NO,
                    db.PON_APP_USERS.USERUSERS.EXPRNC_STAT_IN,
                    db.PON_APP_USERS.USERUSERS.TEST_STAT_IN,
                    db.PON_APP_USERS.USERUSERS.TWO_WKS_TRNG_DT)
                    .LeftJoin(db.USERUSERS, USERKEY: db.PON_APP_USERS.USERKEY);


Is This A Good Question/Topic? 0
  • +

Replies To: how would i check if a record exists in a join

#2 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: how would i check if a record exists in a join

Posted 25 April 2014 - 07:33 AM

Use an Outer Join

Or in LINQ, left outer join

This post has been edited by Momerath: 25 April 2014 - 07:35 AM

Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 445
  • View blog
  • Posts: 3,088
  • Joined: 12-January 10

Re: how would i check if a record exists in a join

Posted 25 April 2014 - 07:46 AM

im new to simple data such is in 2 days new so if you don't mind showing me step by step it would be greatly appreciated

this is what they are requesting of that join

/// If no record is join with the above statement, page will crash. Please make adjustments to allow the page to load if the user does not have a record in USERUSERS table.
Was This Post Helpful? 0
  • +
  • -

#4 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: how would i check if a record exists in a join

Posted 25 April 2014 - 08:08 AM

It appears you are using a custom database class (Simple.Data.Database). What does the method LeftJoin look like?
Was This Post Helpful? 0
  • +
  • -

#5 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 445
  • View blog
  • Posts: 3,088
  • Joined: 12-January 10

Re: how would i check if a record exists in a join

Posted 25 April 2014 - 08:13 AM

simple data is from here
http://simplefx.org/...oadingJoins.htm

that is a built in method to simple data not a custom one
Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4462
  • View blog
  • Posts: 7,776
  • Joined: 08-June 10

Re: how would i check if a record exists in a join

Posted 25 April 2014 - 08:13 AM

I'm guessing this is the Simple.Data project:

https://github.com/m...dle/Simple.Data

I believe that it uses DynamicObject to make query building "simple".
Was This Post Helpful? 0
  • +
  • -

#7 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: how would i check if a record exists in a join

Posted 25 April 2014 - 08:30 AM

And there is an example of exactly what you need to do on the page you linked, DarenR. Click on the "Explicit Join" and scroll down to the 2nd example.
Was This Post Helpful? 2
  • +
  • -

#8 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 445
  • View blog
  • Posts: 3,088
  • Joined: 12-January 10

Re: how would i check if a record exists in a join

Posted 25 April 2014 - 08:45 AM

I didn't even notice those headings were links-- thanks

  dynamic userUsersAlias;

                var expr1 = db.PON_APP_USERS.STATUS == 1;

                var sUsers = db.PON_APP_USERS.FindAll(expr1)
                                        .OuterJoin(db.USERUSERS.As("user"), out userUsersAlias).On(userUsersAlias.USERKEY == db.PON_APP_USERS.USERKEY)
                  .Select(
                    db.PON_APP_USERS.USERKEY,
                    db.PON_APP_USERS.LAST_NAME,
                    db.PON_APP_USERS.FIRST_NAME,
                    db.PON_APP_USERS.AGENCY,
                    userUsersAlias.CBI_NO,
                    userUsersAlias.EXPRNC_STAT_IN,
                    userUsersAlias.TEST_STAT_IN,
                    userUsersAlias.TWO_WKS_TRNG_DT)
                     .LeftJoin(db.USERUSERS, USERKEY: db.PON_APP_USERS.USERKEY);


for future reference
Was This Post Helpful? 0
  • +
  • -

#9 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4462
  • View blog
  • Posts: 7,776
  • Joined: 08-June 10

Re: how would i check if a record exists in a join

Posted 25 April 2014 - 08:57 AM

Yeah, I'd say that's a neat but poorly designed documentation page. I wouldn't have seen it if Momerath hadn't pointed it out.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1