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
 left outer join

Author  Topic 

mana
Posting Yak Master

102 Posts
Posted - 2014-09-08 : 09:13:08
hello,

i have the following code and i want to have all of the fields from dbo_Bewegungen_Inventur_Korrekturen and the related columns from dbo_v_NHM7_Kommissionier_Parameter_Teile.
can you help me please?

this is a code in ms access


SELECT dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung, dbo_Teile.Teil_Benennung, dbo_Teile.Teil_Nummer, First(dbo_v_ME_Montage_Adressen.Zu_Adresse) AS FirstOfZu_Adresse, First(dbo_v_ME_Montage_Adressen.Von_Adresse) AS FirstOfVon_Adresse, dbo_Lieferanten_Teile.Lieferanten_Nummer, dbo_Teile.Disponent, dbo_Bewegungen_Inventur_Korrekturen.Reason_Code, dbo_Bewegungen_Inventur_Korrekturen.Workcenter, First(dbo_v_NHM7_Kommissionier_Parameter_Teile.Kommissionier_Code) AS Kit, dbo_Lieferanten.Lieferanten_Name_1 AS Lieferanten_Name, dbo_Bewegungen_Inventur_Korrekturen.SumOfMenge, dbo_Bewegungen_Inventur_Korrekturen.Anz_Adjustments, dbo_Bewegungen_Inventur_Korrekturen.Wert
FROM (((dbo_Teile_Kosten_Standard INNER JOIN (dbo_Lieferanten_Teile INNER JOIN ((dbo_Teile INNER JOIN dbo_v_ME_Montage_Adressen ON dbo_Teile.Teil_Nummer = dbo_v_ME_Montage_Adressen.Teil_Nummer) INNER JOIN dbo_Bewegungen_Inventur_Korrekturen ON dbo_Teile.Teil_Nummer = dbo_Bewegungen_Inventur_Korrekturen.Teil_Nummer) ON dbo_Lieferanten_Teile.Teil_Nummer = dbo_Teile.Teil_Nummer) ON dbo_Teile_Kosten_Standard.Teil_Nummer = dbo_Teile.Teil_Nummer) INNER JOIN dbo_v_NHM7_Kommissionier_Parameter_Teile ON dbo_Teile.Teil_Nummer = dbo_v_NHM7_Kommissionier_Parameter_Teile.Teil_Nummer) INNER JOIN dbo_Lieferanten ON dbo_Lieferanten_Teile.Lieferanten_Nummer = dbo_Lieferanten.Lieferanten_Nummer) INNER JOIN dbo_v_Lieferanten_Preise ON (dbo_Teile.Teil_Nummer = dbo_v_Lieferanten_Preise.Teil_Nummer) AND (dbo_Lieferanten.Lieferanten_Nummer = dbo_v_Lieferanten_Preise.Lieferanten_Nummer)
GROUP BY dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung, dbo_Teile.Teil_Benennung, dbo_Teile.Teil_Nummer, dbo_Lieferanten_Teile.Lieferanten_Nummer, dbo_Teile.Disponent, dbo_Bewegungen_Inventur_Korrekturen.Reason_Code, dbo_Bewegungen_Inventur_Korrekturen.Workcenter, dbo_Lieferanten.Lieferanten_Name_1, dbo_Bewegungen_Inventur_Korrekturen.SumOfMenge, dbo_Bewegungen_Inventur_Korrekturen.Anz_Adjustments, dbo_Bewegungen_Inventur_Korrekturen.Wert, dbo_Lieferanten_Teile.Liefer_Quote
HAVING (((dbo_Lieferanten_Teile.Liefer_Quote)>0))
ORDER BY dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung DESC;

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-09-08 : 10:13:30
I had to reformat this to be able to be able to read it at all:


SELECT dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung
    ,dbo_Teile.Teil_Benennung
    ,dbo_Teile.Teil_Nummer
    ,First(dbo_v_ME_Montage_Adressen.Zu_Adresse) AS FirstOfZu_Adresse
    ,First(dbo_v_ME_Montage_Adressen.Von_Adresse) AS FirstOfVon_Adresse
    ,dbo_Lieferanten_Teile.Lieferanten_Nummer
    ,dbo_Teile.Disponent
    ,dbo_Bewegungen_Inventur_Korrekturen.Reason_Code
    ,dbo_Bewegungen_Inventur_Korrekturen.Workcenter
    ,First(dbo_v_NHM7_Kommissionier_Parameter_Teile.Kommissionier_Code) AS Kit
    ,dbo_Lieferanten.Lieferanten_Name_1 AS Lieferanten_Name
    ,dbo_Bewegungen_Inventur_Korrekturen.SumOfMenge
    ,dbo_Bewegungen_Inventur_Korrekturen.Anz_Adjustments
    ,dbo_Bewegungen_Inventur_Korrekturen.Wert
FROM (
    (
        (
            dbo_Teile_Kosten_Standard INNER JOIN (
                dbo_Lieferanten_Teile INNER JOIN (
                    (
                        dbo_Teile INNER JOIN dbo_v_ME_Montage_Adressen
                            ON dbo_Teile.Teil_Nummer = dbo_v_ME_Montage_Adressen.Teil_Nummer
                        ) INNER JOIN dbo_Bewegungen_Inventur_Korrekturen
                        ON dbo_Teile.Teil_Nummer = dbo_Bewegungen_Inventur_Korrekturen.Teil_Nummer
                    )
                    ON dbo_Lieferanten_Teile.Teil_Nummer = dbo_Teile.Teil_Nummer
                )
                ON dbo_Teile_Kosten_Standard.Teil_Nummer = dbo_Teile.Teil_Nummer
            ) INNER JOIN dbo_v_NHM7_Kommissionier_Parameter_Teile
            ON dbo_Teile.Teil_Nummer = dbo_v_NHM7_Kommissionier_Parameter_Teile.Teil_Nummer
        ) INNER JOIN dbo_Lieferanten
        ON dbo_Lieferanten_Teile.Lieferanten_Nummer = dbo_Lieferanten.Lieferanten_Nummer
    )
INNER JOIN dbo_v_Lieferanten_Preise
    ON (dbo_Teile.Teil_Nummer = dbo_v_Lieferanten_Preise.Teil_Nummer)
        AND (dbo_Lieferanten.Lieferanten_Nummer = dbo_v_Lieferanten_Preise.Lieferanten_Nummer)
GROUP BY dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung
    ,dbo_Teile.Teil_Benennung
    ,dbo_Teile.Teil_Nummer
    ,dbo_Lieferanten_Teile.Lieferanten_Nummer
    ,dbo_Teile.Disponent
    ,dbo_Bewegungen_Inventur_Korrekturen.Reason_Code
    ,dbo_Bewegungen_Inventur_Korrekturen.Workcenter
    ,dbo_Lieferanten.Lieferanten_Name_1
    ,dbo_Bewegungen_Inventur_Korrekturen.SumOfMenge
    ,dbo_Bewegungen_Inventur_Korrekturen.Anz_Adjustments
    ,dbo_Bewegungen_Inventur_Korrekturen.Wert
    ,dbo_Lieferanten_Teile.Liefer_Quote
HAVING (((dbo_Lieferanten_Teile.Liefer_Quote) > 0))
ORDER BY dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung DESC;



Now, what is the relationship between dbo_Bewegungen_Inventur_Korrekturen and dbo_v_NHM7_Kommissionier_Parameter_Teile?
Go to Top of Page

mana
Posting Yak Master

102 Posts
Posted - 2014-09-08 : 10:33:38
hello
thank you for reformatting
i want all of the teil nummers from dbo_Bewegungen_Inventur_Korrekturen and the related column (Kommissionier_Code)
from dbo_v_NHM7_Kommissionier_Parameter_Teile.
there are some teil nummer in dbo_Bewegungen_Inventur_Korrekturen those don't have Kommissionier_Code and i want to have them as well with empty Kommissionier_Code.

dbo_Bewegungen_Inventur_Korrekturen has the following columns:
Datum_Bewegung
Teil_Nummer
Workcenter
Reason_Code
Id_Nr
Menge
Mengeneinheit

and dbo_v_NHM7_Kommissionier_Parameter_Teile has the following columns:
Kommissionier_Code
Teil_Nummer
Teil_Benennung
Workcenter






quote:
Originally posted by gbritton

I had to reformat this to be able to be able to read it at all:


SELECT dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung
    ,dbo_Teile.Teil_Benennung
    ,dbo_Teile.Teil_Nummer
    ,First(dbo_v_ME_Montage_Adressen.Zu_Adresse) AS FirstOfZu_Adresse
    ,First(dbo_v_ME_Montage_Adressen.Von_Adresse) AS FirstOfVon_Adresse
    ,dbo_Lieferanten_Teile.Lieferanten_Nummer
    ,dbo_Teile.Disponent
    ,dbo_Bewegungen_Inventur_Korrekturen.Reason_Code
    ,dbo_Bewegungen_Inventur_Korrekturen.Workcenter
    ,First(dbo_v_NHM7_Kommissionier_Parameter_Teile.Kommissionier_Code) AS Kit
    ,dbo_Lieferanten.Lieferanten_Name_1 AS Lieferanten_Name
    ,dbo_Bewegungen_Inventur_Korrekturen.SumOfMenge
    ,dbo_Bewegungen_Inventur_Korrekturen.Anz_Adjustments
    ,dbo_Bewegungen_Inventur_Korrekturen.Wert
FROM (
    (
        (
            dbo_Teile_Kosten_Standard INNER JOIN (
                dbo_Lieferanten_Teile INNER JOIN (
                    (
                        dbo_Teile INNER JOIN dbo_v_ME_Montage_Adressen
                            ON dbo_Teile.Teil_Nummer = dbo_v_ME_Montage_Adressen.Teil_Nummer
                        ) INNER JOIN dbo_Bewegungen_Inventur_Korrekturen
                        ON dbo_Teile.Teil_Nummer = dbo_Bewegungen_Inventur_Korrekturen.Teil_Nummer
                    )
                    ON dbo_Lieferanten_Teile.Teil_Nummer = dbo_Teile.Teil_Nummer
                )
                ON dbo_Teile_Kosten_Standard.Teil_Nummer = dbo_Teile.Teil_Nummer
            ) INNER JOIN dbo_v_NHM7_Kommissionier_Parameter_Teile
            ON dbo_Teile.Teil_Nummer = dbo_v_NHM7_Kommissionier_Parameter_Teile.Teil_Nummer
        ) INNER JOIN dbo_Lieferanten
        ON dbo_Lieferanten_Teile.Lieferanten_Nummer = dbo_Lieferanten.Lieferanten_Nummer
    )
INNER JOIN dbo_v_Lieferanten_Preise
    ON (dbo_Teile.Teil_Nummer = dbo_v_Lieferanten_Preise.Teil_Nummer)
        AND (dbo_Lieferanten.Lieferanten_Nummer = dbo_v_Lieferanten_Preise.Lieferanten_Nummer)
GROUP BY dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung
    ,dbo_Teile.Teil_Benennung
    ,dbo_Teile.Teil_Nummer
    ,dbo_Lieferanten_Teile.Lieferanten_Nummer
    ,dbo_Teile.Disponent
    ,dbo_Bewegungen_Inventur_Korrekturen.Reason_Code
    ,dbo_Bewegungen_Inventur_Korrekturen.Workcenter
    ,dbo_Lieferanten.Lieferanten_Name_1
    ,dbo_Bewegungen_Inventur_Korrekturen.SumOfMenge
    ,dbo_Bewegungen_Inventur_Korrekturen.Anz_Adjustments
    ,dbo_Bewegungen_Inventur_Korrekturen.Wert
    ,dbo_Lieferanten_Teile.Liefer_Quote
HAVING (((dbo_Lieferanten_Teile.Liefer_Quote) > 0))
ORDER BY dbo_Bewegungen_Inventur_Korrekturen.Datum_Bewegung DESC;



Now, what is the relationship between dbo_Bewegungen_Inventur_Korrekturen and dbo_v_NHM7_Kommissionier_Parameter_Teile?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-09-08 : 11:45:23
OK, so what happens when you run this query? Does it give syntax errors? Does it execute and produce incorrect results? If so, what is wrong with the results and what would the correct results look like?

I see some ms-access specific code that needs some work. e.g.


First(dbo_v_ME_Montage_Adressen.Zu_Adresse)


According to the docs, this The function only returns the first value in the result set. However, you've only specified a table name and column (or, is dbo_v_ME_Montage_Adressen.Zu_Adresse a stored procedure?)

In any case, in Sql Server there is no First function. You can get the same result using a subquery with TOP and ORDER BY. SO, in your case (assuming that dbo_v_ME_Montage_Adressen) is a table:


(SELECT TOP(1) Zu_Adresse
 FROM dbo_v_ME_Montage_Adressen
 ORDER BY ...)


Note that you will have to specify something to order by for this to work consistently.
Go to Top of Page

mana
Posting Yak Master

102 Posts
Posted - 2014-09-09 : 04:03:36

hello
thanks for the response
dbo_v_ME_Montage_Adressen is a table
now the result is not like as i want
i want to have all of the teil nummer from

dbo_Bewegungen_Inventur_Korrekturen whether they have commisionier code or not from table dbo_v_NHM7_Kommissionier_Parameter_Teile but now i have just the teil numbers those have the commisionier code and i don't have the ones those don't have commisionier code but i want them as well with empty or null commisionier code








quote:
Originally posted by gbritton

OK, so what happens when you run this query? Does it give syntax errors? Does it execute and produce incorrect results? If so, what is wrong with the results and what would the correct results look like?

I see some ms-access specific code that needs some work. e.g.


First(dbo_v_ME_Montage_Adressen.Zu_Adresse)


According to the docs, this The function only returns the first value in the result set. However, you've only specified a table name and column (or, is dbo_v_ME_Montage_Adressen.Zu_Adresse a stored procedure?)

In any case, in Sql Server there is no First function. You can get the same result using a subquery with TOP and ORDER BY. SO, in your case (assuming that dbo_v_ME_Montage_Adressen) is a table:


(SELECT TOP(1) Zu_Adresse
 FROM dbo_v_ME_Montage_Adressen
 ORDER BY ...)


Note that you will have to specify something to order by for this to work consistently.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-09-09 : 07:43:52
OK then, you'll need to change


INNER JOIN dbo_v_Lieferanten_Preise
    ON (dbo_Teile.Teil_Nummer = dbo_v_Lieferanten_Preise.Teil_Nummer)
        AND (dbo_Lieferanten.Lieferanten_Nummer = dbo_v_Lieferanten_Preise.Lieferanten_Nummer)


to a LEFT JOIN
Go to Top of Page

mana
Posting Yak Master

102 Posts
Posted - 2014-09-09 : 09:13:55




thank you for the answer


i tried to write a view as below in sql server and now my problem is that i have several teil numbers in each day with the same von addresse and zu_addresse but i weant to have just the first one of von_addresse and zu_addresse also i want to have the first commisionier code as well , can you help me please?


SELECT v_NHM7_Kommissionier_Parameter_Teile_1.Kommissionier_Code, dbo.Bewegungen_Inventur_Korrekturen.Datum_Bewegung,
dbo.Bewegungen_Inventur_Korrekturen.Workcenter, dbo.Bewegungen_Inventur_Korrekturen.Reason_Code, dbo.Bewegungen_Inventur_Korrekturen.Id_Nr,
dbo.Bewegungen_Inventur_Korrekturen.Menge, dbo.Bewegungen_Inventur_Korrekturen.Mengeneinheit,
dbo.Bewegungen_Inventur_Korrekturen.Dokumenten_Nummer, dbo.Bewegungen_Inventur_Korrekturen.Zeit_Bewegung,
dbo.Bewegungen_Inventur_Korrekturen.SumOfMenge, dbo.Bewegungen_Inventur_Korrekturen.Anz_Adjustments, dbo.Bewegungen_Inventur_Korrekturen.Wert,
dbo.Teile.Teil_Benennung, dbo.Bewegungen_Inventur_Korrekturen.Teil_Nummer, dbo.v_ME_Montage_Adressen.Zu_Adresse,
dbo.v_ME_Montage_Adressen.Von_Adresse
FROM dbo.Teile INNER JOIN
dbo.Bewegungen_Inventur_Korrekturen ON dbo.Teile.Teil_Nummer = dbo.Bewegungen_Inventur_Korrekturen.Teil_Nummer INNER JOIN
dbo.v_ME_Montage_Adressen ON dbo.Teile.Teil_Nummer = dbo.v_ME_Montage_Adressen.Teil_Nummer LEFT OUTER JOIN
dbo.v_NHM7_Kommissionier_Parameter_Teile AS v_NHM7_Kommissionier_Parameter_Teile_1 ON
dbo.Bewegungen_Inventur_Korrekturen.Teil_Nummer = v_NHM7_Kommissionier_Parameter_Teile_1.Teil_Nummer







quote:
Originally posted by gbritton

OK then, you'll need to change


INNER JOIN dbo_v_Lieferanten_Preise
    ON (dbo_Teile.Teil_Nummer = dbo_v_Lieferanten_Preise.Teil_Nummer)
        AND (dbo_Lieferanten.Lieferanten_Nummer = dbo_v_Lieferanten_Preise.Lieferanten_Nummer)


to a LEFT JOIN
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-09-09 : 09:58:43
Are you getting other fields from dbo_v_Lieferanten_Preise besides addresses? If that's all you need, you can modify the left join I suggested a little bit


left join (
   select top (1) * from dbo_v_Lieferanten_Preise
   order by ...  -- you have to put something here that makes sense
   ) dbo_v_Lieferanten_Preise
   on dbo_Teile.Teil_Nummer = dbo_v_Lieferanten_Preise.Teil_Nummer
  and dbo_Lieferanten.Lieferanten_Nummer = dbo_v_Lieferanten_Preise.Lieferanten_Nummer
Go to Top of Page

mana
Posting Yak Master

102 Posts
Posted - 2014-09-09 : 10:43:14
hello,

you know my new query is the red one that i wrote above. can you help me in this query please? i am completely confused.








quote:
Originally posted by gbritton

Are you getting other fields from dbo_v_Lieferanten_Preise besides addresses? If that's all you need, you can modify the left join I suggested a little bit


left join (
   select top (1) * from dbo_v_Lieferanten_Preise
   order by ...  -- you have to put something here that makes sense
   ) dbo_v_Lieferanten_Preise
   on dbo_Teile.Teil_Nummer = dbo_v_Lieferanten_Preise.Teil_Nummer
  and dbo_Lieferanten.Lieferanten_Nummer = dbo_v_Lieferanten_Preise.Lieferanten_Nummer
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-09-09 : 11:28:09
OK -- can you post a sample of your output data and point out what's wrong with it and what it should look like
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site