Cannot join sql tables correctly.

rckrch
rckrch used Ask the Experts™
on
Can't join tables correctly in SQL.  Query needs to join Quest_No and WorkCell_Q from left table to Findings, CorrectiveAction, Owner, TargetDate and CompDate from right table to match as shown in the Final Desired Joined Table shown at the bottom.

I have re-fraised the question the best I could.

The attached file shows the tables I am trying to join and the resulting table I am trying to achieve.

Thanks in advance for the help.
                  

The following query does not work.  It returns all rows in the left table and all rows in right table.

WCBindCommand.CommandText = "SELECT LPA_UpdatedWC_Q.Quest_No, LPA_UpdatedWC_Q.WorkCell_Q, LPA_WorkCell_Audit.Findings, LPA_WorkCell_Audit.CorrectiveAction, LPA_WorkCell_Audit.Owner, Format(LPA_WorkCell_Audit.TargetDate, '" & DFormat & "') AS TargetDate, Format(LPA_WorkCell_Audit.CompDate, '" & DFormat & "') AS CompDate, LPA_WorkCell_Audit.Status, LPA_WorkCell_Audit.Signoff FROM LPA_UpdatedWC_Q LEFT JOIN LPA_WorkCell_Audit ON LPA_UpdatedWC_Q.Quest_No=LPA_WorkCell_Audit.Quest_No WHERE LPA_UpdatedWC_Q.Department=@Department AND LPA_UpdatedWC_Q.Program=@Program AND LPA_UpdatedWC_Q.Operation=@Operation AND (LPA_UpdatedWC_Q.AudLevel=@AudLevel OR LPA_UpdatedWC_Q.AudLevel='0') AND (LPA_WorkCell_Audit.Auditor=@Auditor OR LPA_WorkCell_Audit.DueDate=@DueDate)"  
            WCBindCommand.Parameters("@Department").Value = Dept
            WCBindCommand.Parameters("@Program").Value = Prog
            WCBindCommand.Parameters("@Operation").Value = Oper
            WCBindCommand.Parameters("@AudLevel").Value = LevelDrpDn.SelectedValue
            WCBindCommand.Parameters("@Auditor").Value = AuditorDrpDn.SelectedValue
            WCBindCommand.Parameters("@CurrentDate").Value = DateDrpDn.SelectedValue
            WCDataAdapter.SelectCommand = WCBindCommand
            WCDataAdapter.Fill(WCDataTable)

Open in new window

TblExample-Join.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Please format your post. Add code into [code] tags (use the CODE button). Post a concise and complete example; include table DDL and INSERT data statements as a single runnable T-SQL script instead of that unreadable text monster?

Also rephrase your question. What does "Right table only has rows for certain columns.  Left table has all rows for certain (different) columns in table." mean? A row as always values or NULL for all columns.
Senior .Net Consultant
Top Expert 2016
Commented:
Really hard to read!

I would say that this line (currently in the WHERE clause):
AND (LPA_WorkCell_Audit.Auditor=@Auditor OR LPA_WorkCell_Audit.DueDate=@DueDate)

Open in new window


belongs to the JOIN condition (move it to the ON clause)
awking00Information Technology Specialist

Commented:
Why was the "right" table data and the desired final table removed? They are needed to provide a test case. Also, I recall that the final table showed no values for the "right" table although they existed for the WC2 Quest_No. Can you explain the criteria used to determine that result?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

awking00Information Technology Specialist

Commented:
Sorry, I didn't see the right table and the final table in the spreadsheet when I initially looked at it. I would still like an explanation of why the WC2 Quest_No only shows the WorkCell_Q field.
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
I think we're missing some data, for example your Excel tables don't include Status or Signoff.

Are you trying to get the most recent update for each Quest_No?

If I run
SELECT lpauwc.Quest_No
,lpauwc.WorkCell_Q
,lpawca.Findings
,lpawca.CorrectiveAction
,lpawca.Owner
,lpawca.DueDate
,Format(lpawca.TargetDate, '" & DFormat & "') AS TargetDate
,Format(lpawca.CompDate, '" & DFormat & "') AS CompDate
FROM LPA_UpdatedWC_Q lpauwc
LEFT JOIN LPA_WorkCell_Audit lpawca ON lpauwc.Quest_No = lpawca.Quest_No
WHERE lpauwc.Department = 'Extrusion'
AND lpauwc.Program = 'Area 1'
AND lpauwc.Operation = 'Extrusion Line 1'
AND (lpauwc.AudLevel = 1 OR lpauwc.AudLevel = 0)
AND (lpawca.Auditor = 'Ca…' OR lpawca.DueDate = '1/1/1900')

Open in new window


in my SSMS on tables of your Excel data, I get only the one epected result.  But it's not clear what your actual criteria are, because the other WC numbers don't have any matching criteria.

If you're getting back all results unfiltered, then something must be qualifying it in your WHERE clause.  Have you done a profiler trace or do you have logging that shows that the expected parameters are actually being passed in?

Author

Commented:
Thanks for the reply awking00.

I have 2 tables that serve 2 purposes.  The first table (Left) shows the columns that are constructed for an audit - questions that must be answered during a review.  This table lists all possible questions for the review - hense WC1, WC2, ...  The questions are constructed from a previous page in the project - no need to go into that, it is working fine.  This page displays all of those questions and the reviewer is to activate this page with the pre-defined questions to perform the review.  In doing so the reviewer will identify  findings for some of the questions but not all - findings, correctiveaction, ... etc.  That is why some of the findings column, corrective column, etc. are filled in and some are not.  

So what the user is seeing in this case is a gridview that combines the questions and the results in one.

Does this answer your question?

Thanks,

Author

Commented:
Thanks for the reply Dustin,

In this query I want to use the most recent Quest_No update for each question the user sees.

Thanks,

Author

Commented:
Dustin,

Yes, I did not include the status and signoff in the tables on the excel file because they are null and it would just add to the confusion when trying to explain the issue.

Also, I don't know what you  mean by profiler trace.

I am not a SQL programmer.

Thanks,

Author

Commented:
Thanks for the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial