July 12, 2017 at 2:56 pm
I want to select all the columns from tblA and two columns from tblB:
select A.field1, A.field2, A.field, B.Cost, B.totalcost
from tblA
left join tblB
on tblA.id = tblB.id
I get all the rows from tblA with the additional two columns from tblB but the fields are Null, If I select just all the rows from tblB I can see there are values in the two columns I'm pulling from tblB. Where am I messing up in my Join Statement.
July 12, 2017 at 3:05 pm
Please explain what records you want from Table B. An outer join returns nulls from Table B if there are no records that match those in A.
Compare
SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM a LEFT JOIN b ON a.Field1 = b.Field1
and
SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM a RIGHT JOIN b ON a.Field1 = b.Field1
and
SELECT a.Field1, a.Field2, b.Field1, b.Field2
FROM a INNER JOIN b ON a.Field1 = b.Field1
July 12, 2017 at 3:09 pm
Are you sure that tblB.id is the proper foreign key column to join bacl to tblA.id with? If you join on the wrong column then yes the results for tblB columns may be NULL or non-sensical
July 12, 2017 at 3:52 pm
Yes I'm sure that's the key(id) and the columns I want from tblB are Cost & TotalCost. Their are no null values in that column.
July 12, 2017 at 3:57 pm
Any chance you could post some CREATE TABLE and INSERT scripts so we can all play with the same data?
July 12, 2017 at 4:03 pm
As a thought, does this query give you any results:SELECT *
FROM tblA
WHERE tblA.id NOT IN (SELECT id
FROM tblB)
SELECT *
FROM tblB
WHERE tblB.id NOT IN (SELECT id
FROM tblA)
That will give you all of the ID's in tblA that are not in tblB followed by all of the ID's in tblB that are not in tblA.
I'm willing to bet you have some that do not match up.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 13, 2017 at 2:24 pm
First of all, I hope you know that columns and fields are totally different. In spite of your choice of bad naming conventions. Yes, your "TblA" is what is called the preserved table of an outer join. That means all of the rows are returned.
"TblB" is the unpreserved table. If it can match in the join condition (the ON clause), then it behaves like a regular inner join. If there is no match, then the unpreserved columns are filled with nulls (regardless of what the original column constraints were).
But what we need is some DDL. I'm very scared by your by your query because of what it implies. What is cost? And why do you carry a total? Total should be computed, not materialize in the table. The prefix "tbl" is a design flaw called a Tibble and we laugh at it; you've mixed data and metadata. Then there's no such thing as a generic "id" in non-RDBMS. We have no idea what any sample data looks like because you posted none. My initial thought is that your poorly named "TblB" should not exist. It looks like the cost of whatever it is should have been in your first table, and your total cost should be computed in a query. But without any DDL we have no idea. Would you like to try again and follow forum rules with a new posting?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply