Loading ...
Sorry, an error occurred while loading the content.

SQL JOIN Q: 3 tables on one common clientID across each table...

Expand Messages
  • daniel_kramer@rocketmail.com
    Here is a query that joins two tables to get a client s information: It works regardless if there is data or no data in the actives table because of the
    Message 1 of 5 , Jun 10, 2009
    • 0 Attachment
      Here is a query that joins two tables to get a client's information:

      It works regardless if there is data or no data in the 'actives' table
      because of the RIGHT OUTER JOIN....

      SELECT c.firstName, c.lastName, c.addressStreetNumb er, c.addressStreet,
      c.addressLine2, c.city, c.state, c.zip1, c.zip2, c.clientID, c.email1,
      c.email2, c.company, c.phoneCell, c.phoneHome, c.phoneOffice, c.phoneOther,
      c..ip1, c.ip2, c.ip3, c.ip4, c.UPI, c.VCI, c.dslPhoneNumber, a.cardCVV,
      a.cardExpMonth, a.cardExpYear, a.cardNameOn, a.cardNumber, a.cardRefNum
      FROM actives a RIGHT OUTER JOIN
      clients c ON c.clientID = a.clientID
      WHERE (a.clientID = 'BF2B1E91-3048' )

      HOWEVER...

      I have a third table called "billingCycle" which also contains information for the client's record (can be join on "clientID" too), but I cannot, so far, get that table to successfully join the query

      For instance, it has a planType column and any way I've joined the table so far not only is planType not in the result set, but nothing is returned for ANY table.

      All three tables can join on clientID and I want the 'clients' record
      whether on not there is data in the 'actives' or the 'billingCycle' tables.

      Follow?

      ANY HELP?

      THANKS IN ADVANCE!
    • Paul Livengood
      try this; SELECT c.Fields, a.Fields, bc.Fields FROM clients c LEFT JOIN actives a ON c.clientID = a.clientID LEFT JOIN billingCycle bc ON a.clientID =
      Message 2 of 5 , Jun 10, 2009
      • 0 Attachment
        try this;

        SELECT c.Fields,
        a.Fields,
        bc.Fields
        FROM clients c
        LEFT JOIN actives a
        ON c.clientID = a.clientID
        LEFT JOIN billingCycle bc
        ON a.clientID = bc.clientID
        WHERE a.clientID = 'BF2B1E91-3048'

        Of course you will need to replace the "Fields" names.

        HTH
        Paul


        --- In SQLQueriesNoCode@yahoogroups.com, "daniel_kramer@..." <daniel_kramer@...> wrote:
        >
        > Here is a query that joins two tables to get a client's information:
        >
        > It works regardless if there is data or no data in the 'actives' table
        > because of the RIGHT OUTER JOIN....
        >
        > SELECT c.firstName, c.lastName, c.addressStreetNumb er, c.addressStreet,
        > c.addressLine2, c.city, c.state, c.zip1, c.zip2, c.clientID, c.email1,
        > c.email2, c.company, c.phoneCell, c.phoneHome, c.phoneOffice, c.phoneOther,
        > c..ip1, c.ip2, c.ip3, c.ip4, c.UPI, c.VCI, c.dslPhoneNumber, a.cardCVV,
        > a.cardExpMonth, a.cardExpYear, a.cardNameOn, a.cardNumber, a.cardRefNum
        > FROM actives a RIGHT OUTER JOIN
        > clients c ON c.clientID = a.clientID
        > WHERE (a.clientID = 'BF2B1E91-3048' )
        >
        > HOWEVER...
        >
        > I have a third table called "billingCycle" which also contains information for the client's record (can be join on "clientID" too), but I cannot, so far, get that table to successfully join the query
        >
        > For instance, it has a planType column and any way I've joined the table so far not only is planType not in the result set, but nothing is returned for ANY table.
        >
        > All three tables can join on clientID and I want the 'clients' record
        > whether on not there is data in the 'actives' or the 'billingCycle' tables.
        >
        > Follow?
        >
        > ANY HELP?
        >
        > THANKS IN ADVANCE!
        >
      • daniel_kramer@rocketmail.com
        Paul, Thanks, but now no clients are returned where there is no actives data. Otherwise it returns everything from each table, but if actives info is
        Message 3 of 5 , Jun 10, 2009
        • 0 Attachment
          Paul,

          Thanks, but now no "clients" are returned where there is no "actives" data. Otherwise it returns everything from each table, but if actives info is missing, then no records are returned at all.


          --- In SQLQueriesNoCode@yahoogroups.com, "Paul Livengood" <p_livengood@...> wrote:
          >
          > try this;
          >
          > SELECT c.Fields,
          > a.Fields,
          > bc.Fields
          > FROM clients c
          > LEFT JOIN actives a
          > ON c.clientID = a.clientID
          > LEFT JOIN billingCycle bc
          > ON a.clientID = bc.clientID
          > WHERE a.clientID = 'BF2B1E91-3048'
          >
          > Of course you will need to replace the "Fields" names.
          >
          > HTH
          > Paul
          >
          >
          > --- In SQLQueriesNoCode@yahoogroups.com, "daniel_kramer@" <daniel_kramer@> wrote:
          > >
          > > Here is a query that joins two tables to get a client's information:
          > >
          > > It works regardless if there is data or no data in the 'actives' table
          > > because of the RIGHT OUTER JOIN....
          > >
          > > SELECT c.firstName, c.lastName, c.addressStreetNumb er, c.addressStreet,
          > > c.addressLine2, c.city, c.state, c.zip1, c.zip2, c.clientID, c.email1,
          > > c.email2, c.company, c.phoneCell, c.phoneHome, c.phoneOffice, c.phoneOther,
          > > c..ip1, c.ip2, c.ip3, c.ip4, c.UPI, c.VCI, c.dslPhoneNumber, a.cardCVV,
          > > a.cardExpMonth, a.cardExpYear, a.cardNameOn, a.cardNumber, a.cardRefNum
          > > FROM actives a RIGHT OUTER JOIN
          > > clients c ON c.clientID = a.clientID
          > > WHERE (a.clientID = 'BF2B1E91-3048' )
          > >
          > > HOWEVER...
          > >
          > > I have a third table called "billingCycle" which also contains information for the client's record (can be join on "clientID" too), but I cannot, so far, get that table to successfully join the query
          > >
          > > For instance, it has a planType column and any way I've joined the table so far not only is planType not in the result set, but nothing is returned for ANY table.
          > >
          > > All three tables can join on clientID and I want the 'clients' record
          > > whether on not there is data in the 'actives' or the 'billingCycle' tables.
          > >
          > > Follow?
          > >
          > > ANY HELP?
          > >
          > > THANKS IN ADVANCE!
          > >
          >
        • Paul Livengood
          sorry typeo. In the WHERE clause it should be c.ClientID, not a.ClientID. HTH Paul
          Message 4 of 5 , Jun 10, 2009
          • 0 Attachment
            sorry typeo.
            In the WHERE clause it should be c.ClientID, not a.ClientID.

            HTH
            Paul


            --- In SQLQueriesNoCode@yahoogroups.com, "daniel_kramer@..." <daniel_kramer@...> wrote:
            >
            > Paul,
            >
            > Thanks, but now no "clients" are returned where there is no "actives" data. Otherwise it returns everything from each table, but if actives info is missing, then no records are returned at all.
            >
            >
            > --- In SQLQueriesNoCode@yahoogroups.com, "Paul Livengood" <p_livengood@> wrote:
            > >
            > > try this;
            > >
            > > SELECT c.Fields,
            > > a.Fields,
            > > bc.Fields
            > > FROM clients c
            > > LEFT JOIN actives a
            > > ON c.clientID = a.clientID
            > > LEFT JOIN billingCycle bc
            > > ON a.clientID = bc.clientID
            > > WHERE a.clientID = 'BF2B1E91-3048'
            > >
            > > Of course you will need to replace the "Fields" names.
            > >
            > > HTH
            > > Paul
            > >
            > >
            > > --- In SQLQueriesNoCode@yahoogroups.com, "daniel_kramer@" <daniel_kramer@> wrote:
            > > >
            > > > Here is a query that joins two tables to get a client's information:
            > > >
            > > > It works regardless if there is data or no data in the 'actives' table
            > > > because of the RIGHT OUTER JOIN....
            > > >
            > > > SELECT c.firstName, c.lastName, c.addressStreetNumb er, c.addressStreet,
            > > > c.addressLine2, c.city, c.state, c.zip1, c.zip2, c.clientID, c.email1,
            > > > c.email2, c.company, c.phoneCell, c.phoneHome, c.phoneOffice, c.phoneOther,
            > > > c..ip1, c.ip2, c.ip3, c.ip4, c.UPI, c.VCI, c.dslPhoneNumber, a.cardCVV,
            > > > a.cardExpMonth, a.cardExpYear, a.cardNameOn, a.cardNumber, a.cardRefNum
            > > > FROM actives a RIGHT OUTER JOIN
            > > > clients c ON c.clientID = a.clientID
            > > > WHERE (a.clientID = 'BF2B1E91-3048' )
            > > >
            > > > HOWEVER...
            > > >
            > > > I have a third table called "billingCycle" which also contains information for the client's record (can be join on "clientID" too), but I cannot, so far, get that table to successfully join the query
            > > >
            > > > For instance, it has a planType column and any way I've joined the table so far not only is planType not in the result set, but nothing is returned for ANY table.
            > > >
            > > > All three tables can join on clientID and I want the 'clients' record
            > > > whether on not there is data in the 'actives' or the 'billingCycle' tables.
            > > >
            > > > Follow?
            > > >
            > > > ANY HELP?
            > > >
            > > > THANKS IN ADVANCE!
            > > >
            > >
            >
          • peternilsson42
            ... Your where condition negates the outer join. You re saying that a.clientID must be a specific value, i.e. it cannot be null. Left joins are much more
            Message 5 of 5 , Jun 11, 2009
            • 0 Attachment
              --- In SQLQueriesNoCode@yahoogroups.com, "daniel_kramer@..." <daniel_kramer@...> wrote:
              >
              > Here is a query that joins two tables to get a client's
              > information:
              >
              > It works regardless if there is data or no data in the
              > 'actives' table because of the RIGHT OUTER JOIN....
              >
              > SELECT c.firstName, c.lastName, c.addressStreetNumb er, c.addressStreet,
              > c.addressLine2, c.city, c.state, c.zip1, c.zip2, c.clientID, c.email1,
              > c.email2, c.company, c.phoneCell, c.phoneHome, c.phoneOffice, c.phoneOther,
              > c..ip1, c.ip2, c.ip3, c.ip4, c.UPI, c.VCI, c.dslPhoneNumber, a.cardCVV,
              > a.cardExpMonth, a.cardExpYear, a.cardNameOn, a.cardNumber, a.cardRefNum
              > FROM actives a RIGHT OUTER JOIN
              > clients c ON c.clientID = a.clientID
              > WHERE (a.clientID = 'BF2B1E91-3048' )

              Your where condition negates the outer join. You're saying
              that a.clientID must be a specific value, i.e. it cannot
              be null.

              Left joins are much more intuative...

              select c.*, a.*
              from clients c
              left join actives a on a.clientID = c.clientID
              where c.clientID = 'BF2B1E91-3048'

              > HOWEVER...
              >
              > I have a third table called "billingCycle" which also
              > contains information for the client's record (can be
              > join on "clientID" too), but I cannot, so far, get that
              > table to successfully join the query

              select c.*, b.*, a.*
              from clients c
              left join actives a on a.clientID = c.clientID
              left join billing b on b.clientID = c.clientID
              where c.clientID = 'BF2B1E91-3048'

              If you want billing to only appear in the presence of
              actives rows, use b.clientID = a.clientID in the join.

              Note that if you want conditions on the rows for actives
              or billing, then you need to specify that as part of the
              join condition, not the where clause.

              --
              Peter
            Your message has been successfully submitted and would be delivered to recipients shortly.