Cannot join sql tables correctly.

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
rckrchAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
Éric MoreauSenior .Net ConsultantCommented:
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)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

awking00Information Technology SpecialistCommented:
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.
0
Dustin SaundersDirector of OperationsCommented:
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?
0
rckrchAuthor 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,
0
rckrchAuthor 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,
0
rckrchAuthor 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,
0
rckrchAuthor Commented:
Thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.