JOIN problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

JOIN problem

Author

Topic

Marteijn
Starting Member

28 Posts

Posted - 2011-04-13 : 03:33:56

Hi,

I have a problem joining some tables. If I run the sql below, I miss 1.337 records. I located the problem in the tables GEBR1, GEBR2 en GEBR3. There are (lots of) NULL values in the primary key (gebruikscode) from those tables. I used a LEFT JOIN but in that case all NULL values from those tables will not appear. I tried to use RIGHT JOINS, INNER JOINS, OUTER JOINS but I can't get the SQL right. Can you help me?

; WITH MUTRED AS

(SELECT mut.id,
mut.code,
mut.omschrijving

FROM fact_BAG_MutReden AS mut),

RUIMTE AS
(SELECT openr.openbareruimte_id,
openr.straatcode,
openr.naam,
openr.status_id,
openr.woonplaats_id,
openr.openbareruimtetype,
openr.modification

FROM fact_BAG_OpenbRuimten AS openr

WHERE openr.modification is null),

VBE_KOP AS
(SELECT vbeadr.verblijfseenheid_id,
vbeadr.verblijfseenheidvolgnummer,
vbeadr.adres_id,
vbeadr.indhoofdadres

FROM fact_BAG_VerblEenheid_Adres AS vbeadr),

VBE_TAB AS
(SELECT vbe.verblijfseenheid_id,
vbe.verblijfseenheidvolgnummer,
vbe.gebruik1,
vbe.gebruik2,
vbe.gebruik3,
vbe.indauthentiek,
vbe.modification

FROM fact_BAG_VerblEenheid AS vbe

WHERE vbe.modification is null),

WIJK AS
(SELECT DISTINCT wk.wijk_id,
wk.wijkcode,
wk.naam

FROM fact_BAG_WIJK AS WK),

BUURT AS
(SELECT DISTINCT bt.buurt_id,
bt.buurtcode,
bt.naam

FROM fact_BAG_BUURT AS BT),

GEBR1 AS
(SELECT GA.gebruikscode,
GA.omschrijving

FROM fact_BAG_Gebruikscode AS GA),

GEBR2 AS
(SELECT GB.gebruikscode,
GB.omschrijving

FROM fact_BAG_Gebruikscode AS GB),

GEBR3 AS
(SELECT GC.gebruikscode,
GC.omschrijving

FROM fact_BAG_Gebruikscode AS GC),

WOONPL AS
(SELECT WP.woonplaats_id,
WP.naam,
WP.modification

FROM fact_BAG_Woonplaats AS WP

WHERE WP.modification is null)

SELECT adr.adres_id,
adr.adresnummer,
adr.adrestype,
adr.omschr_type,
adr.statuscode,
adr.omschr_status,
adr.wijkcode,
W.naam AS wijknaam,
adr.buurtcode,
B.naam AS buurtnaam,
adr.openbareruimte_id,
R.naam AS straatnaam,
adr.huisnummer,
adr.huisletter,
adr.toevoeging,
adr.aanduiding,
adr.locatieomschrijving,
adr.postcode_num,
adr.postcode_alf,
WPL.naam as woonplaats,
adr.datumopvoer,
adr.datumafvoer,
adr.dd_document,
adr.documentnummer,
adr.indauthentiek,
adr.modification,
adr.mutatiereden,
M.code,
M.omschrijving,
VK.*,
VT.*,
G1.omschrijving AS gebr1,
G2.omschrijving AS gebr2,
G3.omschrijving AS gebr3

FROM fact_BAG_Adressen AS adr LEFT JOIN
MUTRED M ON adr.mutatiereden = M.id LEFT JOIN
RUIMTE R ON adr.openbareruimte_id = R.openbareruimte_id LEFT JOIN
VBE_KOP VK ON adr.adres_id = VK.adres_id LEFT JOIN
VBE_TAB VT ON vk.verblijfseenheid_id = vt.verblijfseenheid_id LEFT JOIN
GEBR1 G1 ON G1.gebruikscode = VT.gebruik1 LEFT JOIN
GEBR2 G2 ON G2.gebruikscode = VT.gebruik2 LEFT JOIN
GEBR3 G3 ON G3.gebruikscode = VT.gebruik3 LEFT JOIN
WIJK W ON adr.wijkcode = W.wijk_id LEFT JOIN
BUURT B ON adr.buurtcode = B.buurt_id LEFT JOIN
WOONPL WPL ON adr.woonplaats_id = WPL.woonplaats_id

WHERE adr.modification is null AND
vk.verblijfseenheidvolgnummer = vt.verblijfseenheidvolgnummer

ORDER BY R.naam,
adr.huisnummer,
adr.huisletter,
adr.toevoeging,
adr.aanduiding

Thank you!
Marteijn

PS. Using Visual Studio 2005.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-13 : 03:39:38

Post table GEBR1, GEBR2 en GEBR3 values with few columns and null value columns and what output u expect.

Raghu' S

Marteijn
Starting Member

28 Posts

Posted - 2011-04-13 : 04:06:35

quote:
Originally posted by raghuveer125

Post table GEBR1, GEBR2 en GEBR3 values with few columns and null value columns and what output u expect.

Raghu' S

GEBR1, 2 and 3 are the same table.
gebruikcode contains a value from 1 to 11.
omschrijving is a description.

Maintable is fact_BAG_Adressen. fact_BAG_VerblEenheid is connected to that table via fact_BAG_VerblEenheid_Adres.
In fact_BAG_VerblEenheid gebruik1, gebruik2 and gebruik3 are filled. Always gebruik1 is filled, gebruik2 and gebruik3 are filled rarely.
In 1.337 times there is no (matching) record in fact_BAG_VerblEenheid_Adres but i still want to see all records from fact_BAG_Adressen.
Just realising the problem is not at the tables GEBR1, 2 and 3 but at fact_BAG_VerblEenheid_Adres...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources