Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to join in as a partial column

Author  Topic 

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-10-02 : 18:36:59
We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)
whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done. How am I to add this in here?

SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-02 : 18:40:34
What is the data type? Could you show us a data example?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-10-02 : 18:56:42
the data is char 8 for the 3 tables
and it is char 10 for the 4rth that we need to add in.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-10-02 : 20:00:15
[code]SELECT
T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM
ASTDTA.OEORHDOH T01
LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD#
LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
LEFT OUTER JOIN
MFH1MHL0 M
ON T01.OHORD# = left(M.MHORDR, 8)
-- I'm assuming left 8 chars
WHERE
T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC[/code]

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2013-10-03 : 09:17:17
Thanks very much BK.

I am getting this to work but it's not bringing the column we need, this MHZONE

SELECT

T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#,
M.MHZONE
FROM
ASTDTA.OEORHDOH T01
LEFT OUTER JOIN
ASTDTA.OEINDLID T02

ON T01.OHORD# = T02.IDORD#
LEFT OUTER JOIN

ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
LEFT OUTER JOIN
SHP4VAR27F.MFH1MH M
ON T01.OHORD# = left(M.MHORDR, 8)
WHERE


T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101



Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-03 : 12:46:49
What does "it's not bringing the column we need" mean? Please be more clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -