locked
SQL Query RRS feed

  • Question

  • I am working with a ticketing Database that has two tables.

     

    When a user puts in a request it writes data to one table. Then a e-mail message is generated that sends a link to an ASPX page that loads and show the data that was submitted. When this page loads it fires a stored procedure in the aspx page that creates an entry into the second database table.

     

    These two tables have a PK FK relationship.

     

    table1.requestid (PK)

    table2.ackid(PK)

    table2.requestid(FK)

     

    I am trying to create a SQL Query that will return all information in table one and table two that will return all records. I currently can only show all request that have been acknowledged or all request that have NOT been acknowledged. I want a single query that will return all data.

     

    How can I do this?

    Thursday, June 7, 2007 1:20 PM

Answers

  • Try something like:

     

    Code Snippet

    select table1.requestId,

           table2.ackId

      from table1

      left join table 2

        on table1.requestId = table2.requestId

     

    When you see a record in which "table2.ackId" is null you have a request that has not yet been acknowledged.

    Thursday, June 7, 2007 2:10 PM
  • If you truly want

    all information in table one and table two that will return all records
    , then you should use a FULL JOIN instead of a LEFT JOIN.

     

    Code Snippet


    SELECT 
       t1.RequestID,
       t2.AckID
    FROM Table1 t1

      FULL JOIN Table2 t2

         ON t1.RequestID = t2.RequestID

     

    Null values will indicate where you have missing data in one table.

    Thursday, June 7, 2007 5:38 PM

All replies

  • Try something like:

     

    Code Snippet

    select table1.requestId,

           table2.ackId

      from table1

      left join table 2

        on table1.requestId = table2.requestId

     

    When you see a record in which "table2.ackId" is null you have a request that has not yet been acknowledged.

    Thursday, June 7, 2007 2:10 PM
  • If you truly want

    all information in table one and table two that will return all records
    , then you should use a FULL JOIN instead of a LEFT JOIN.

     

    Code Snippet


    SELECT 
       t1.RequestID,
       t2.AckID
    FROM Table1 t1

      FULL JOIN Table2 t2

         ON t1.RequestID = t2.RequestID

     

    Null values will indicate where you have missing data in one table.

    Thursday, June 7, 2007 5:38 PM
  • Arnie,

     

    I think you are right.  I didn't interpret the OP right and incorrectly rejected the FULL JOIN option.  Thank for picking me up. 

     

    I guess I still have doubts about having an acknowledgement without a request; but I have seen only too many times in which stuff like "acknowledgements without requests" happen. 

     

    Thursday, June 7, 2007 7:00 PM