October 31, 2018 at 10:10 am
I'd joining on two tables, but I'm getting weird results. I've been using a LEFT join. That will return all from A and only those that match from B.
SELECT a.co_num,
b.cust_po,
a.co_line,
a.cust_num,
a.qty_ordered,
a.item,
a.whse
FROM [EES_app].[dbo].[coitem] A
LEFT JOIN [EES_app].[dbo].[co] B
ON a.co_num = b.co_num
So [coitem] has multiple records and alI I really need from [co]table is the cust_po the join field is on the co_num , but the cust_co is null for a lot and then it shows up after a few thousand records.
I have added a screenshot of my query Join and another screeshot ( join2) showing that co_num 13037 does have a cust_po and is not null on that table.
Hope this all makes sense -Thanks for the help
Greg J
October 31, 2018 at 10:20 am
That second screenshot only shows that a.cust_po exists, not b.cust_po.
John
October 31, 2018 at 10:25 am
John Mitchell-245523 - Wednesday, October 31, 2018 10:20 AMThat second screenshot only shows that a.cust_po exists, not b.cust_po.John
John, not sure I understand....the 2nd screenshot is of the [co] table , just to show you it exists , but in the query it is aliased as "B" ?
October 31, 2018 at 10:26 am
I think you are looking at two different tables, [ESS_App].[dbo].[co] and [10.1.10.140\ESS_LIVE].[ESS_app].[dbo].[co]. That is like looking at apples and oranges.
October 31, 2018 at 10:42 am
Lynn Pettis - Wednesday, October 31, 2018 10:26 AMI think you are looking at two different tables, [ESS_App].[dbo].[co] and [10.1.10.140\ESS_LIVE].[ESS_app].[dbo].[co]. That is like looking at apples and oranges.
No same table I just edit out the other portion.
October 31, 2018 at 10:53 am
gjoelson 29755 - Wednesday, October 31, 2018 10:42 AMLynn Pettis - Wednesday, October 31, 2018 10:26 AMI think you are looking at two different tables, [ESS_App].[dbo].[co] and [10.1.10.140\ESS_LIVE].[ESS_app].[dbo].[co]. That is like looking at apples and oranges.No same table I just edit out the other portion.
Then please explain why in the images that in one query you use the three part naming convention [EES_App].[dbo].[co] and the other one the four part naming convention
[10.1.10.140\EES_Live].[EES_app].[dbo].[co]. Without having access to your systems this tells me you are looking at data on either two different servers or two different instances on the same server.
October 31, 2018 at 11:07 am
Also, please run the following and let us know what it returns:
select
[a].[co_num]
, .[cust_po]
, [a].[co_line]
, [a].[co_release]
, [a].[item]
, [a].[u_m]
, [a].[qty-ordered]
, [a].[whse]
, .[cust_seq]
, .[ship_code]
from
[10.1.10.140\EES_LIVE].[ESS_app].[dbo].[coitem] as [a]
left outer join [10.1.10.140\EES_LIVE].[ESS_app].[dbo].[co] as
on [a].[co_num] = .[co_num]
where
[a].[co_num] not like '%q0%'
and [a].[co_line] = 1
order by
[a].[co_num] desc;
go
October 31, 2018 at 12:00 pm
Lynn Pettis - Wednesday, October 31, 2018 11:07 AMAlso, please run the following and let us know what it returns:
select
[a].[co_num]
, .[cust_po]
, [a].[co_line]
, [a].[co_release]
, [a].[item]
, [a].[u_m]
, [a].[qty-ordered]
, [a].[whse]
, .[cust_seq]
, .[ship_code]
from
[10.1.10.140\EES_LIVE].[ESS_app].[dbo].[coitem] as [a]
left outer join [10.1.10.140\EES_LIVE].[ESS_app].[dbo].[co] as
on [a].[co_num] = .[co_num]
where
[a].[co_num] not like '%q0%'
and [a].[co_line] = 1
order by
[a].[co_num] desc;
go
LYNN, dang that worked !!!
dont think I've ever used a left outer join before , that's the only change I see ?
Thanks you !!
October 31, 2018 at 12:13 pm
gjoelson 29755 - Wednesday, October 31, 2018 12:00 PMLynn Pettis - Wednesday, October 31, 2018 11:07 AMAlso, please run the following and let us know what it returns:
select
[a].[co_num]
, .[cust_po]
, [a].[co_line]
, [a].[co_release]
, [a].[item]
, [a].[u_m]
, [a].[qty-ordered]
, [a].[whse]
, .[cust_seq]
, .[ship_code]
from
[10.1.10.140\EES_LIVE].[ESS_app].[dbo].[coitem] as [a]
left outer join [10.1.10.140\EES_LIVE].[ESS_app].[dbo].[co] as
on [a].[co_num] = .[co_num]
where
[a].[co_num] not like '%q0%'
and [a].[co_line] = 1
order by
[a].[co_num] desc;
goLYNN, dang that worked !!!
dont think I've ever used a left outer join before , that's the only change I see ?
Thanks you !!
So I'm looking up what the difference between the joins are it says that , LEFT JOIN and LEFT OUTER JOIN are the same thing ??
So what changed ?
October 31, 2018 at 12:31 pm
gjoelson 29755 - Wednesday, October 31, 2018 12:13 PMgjoelson 29755 - Wednesday, October 31, 2018 12:00 PMLynn Pettis - Wednesday, October 31, 2018 11:07 AMAlso, please run the following and let us know what it returns:
select
[a].[co_num]
, .[cust_po]
, [a].[co_line]
, [a].[co_release]
, [a].[item]
, [a].[u_m]
, [a].[qty-ordered]
, [a].[whse]
, .[cust_seq]
, .[ship_code]
from
[10.1.10.140\EES_LIVE].[ESS_app].[dbo].[coitem] as [a]
left outer join [10.1.10.140\EES_LIVE].[ESS_app].[dbo].[co] as
on [a].[co_num] = .[co_num]
where
[a].[co_num] not like '%q0%'
and [a].[co_line] = 1
order by
[a].[co_num] desc;
goLYNN, dang that worked !!!
dont think I've ever used a left outer join before , that's the only change I see ?
Thanks you !!
So I'm looking up what the difference between the joins are it says that , LEFT JOIN and LEFT OUTER JOIN are the same thing ??
So what changed ?
Take a close look at your query back a few posts and compare it to what I had you run.
Essentially you were querying from two different databases.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply