Returning Data from two different tables.

  • Folks,

    Thanks in advance for any help you can provide. I am new to SQL and I have a small issue.

    Hope I can articulate this well.

    I have joined two tables and the data I am requesting is returning if all conditions are met. However, if I ask for data from the joined tables and the FK(GID) do not exist, then no data is returned.

    Hope that makes sense.

  • Try using an left/right outer join to specify that data from the table on either the left or right side of the join will be returned even if there is no match in the joined table. It sounds like you are using an inner join by default.

     

    SQL BOL 

    Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join

  • rstack,

    I really appreicated your response, however I tried that and still did not get the results I expected. Perhap this will help. If I use the if exist...statement I get the results I want. See the example below:

    IF EXISTS

    (SELECT dbo.AWorkInfo.reqid, dbo.WorkInfo.reqid, dbo.WorkInfo.reqname, dbo.WorkInfo.reqphone, dbo.AWorkInfo.aComments

    FROM dbo.AWorkInfo

    INNER JOIN dbo.WorkInfo ON dbo.AWorkInfo.reqid = dbo.WorkInfo.reqid AND dbo.AWorkInfo.reqid = dbo.WorkInfo.reqid

    WHERE     (dbo.AWorkInfo.reqid = '277'))

    BEGIN

        SELECT dbo.AWorkInfo.reqid, dbo.WorkInfo.reqid, dbo.WorkInfo.reqname, dbo.WorkInfo.reqphone, dbo.AWorkInfo.aComments

         FROM dbo.AWorkInfo

    INNER JOIN dbo.WorkInfo ON dbo.AWorkInfo.reqid = dbo.WorkInfo.reqid AND dbo.AWorkInfo.reqid = dbo.WorkInfo.reqid

         WHERE     (dbo.AWorkInfo.reqid = '277')

    END

    ELSE

       SELECT     dbo.WorkInfo.reqid, dbo.WorkInfo.reqname, dbo.WorkInfo.reqphone

                                     FROM dbo.WorkInfo

                                     WHERE     (dbo.WorkInfo.reqid = '4486')

    My problem is my REQID is not static. Need to make it dynamic. i.e. (WHERE (dbo.Workinfo.reqid = @reqid)

    I need to data to return even if there is no reqid in the second table. However, if there is a reqid in the second table return all data.

    Can you help me?

    Thanks,

     

Viewing 3 posts - 1 through 2 (of 2 total)

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