Problem with join and null fields

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

Problem with join and null fields

Author

Topic

Flaterik
Starting Member

26 Posts

Posted - 2011-06-17 : 05:12:33

Hi people, i have a problem with a join
In one table i have 4200 records. When i join this table with another one i get as result just the not null value also if i use Outer join.

For example

I have Cd_ar , cd_type in table X

In table y i have cd_type , description

I want to make a join with x.cd_type = y.cd_type and have as a result cd_ar and description

For some cd_type ther isn't a description, the value is empty or null.

I want that the join have all the results of table X with null or empty in description if ther isn't on table Y.

the world is strange but people are crazy

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-17 : 06:12:19

using a left join will do that

Flaterik
Starting Member

26 Posts

Posted - 2011-06-17 : 06:57:06

Already made it..but it seems that the result is made just by the records with a joined full records (no null or empty are in that column in result)

the world is strange but people are crazy

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-17 : 07:10:57

quote:
Originally posted by Flaterik

Already made it..but it seems that the result is made just by the records with a joined full records (no null or empty are in that column in result)

the world is strange but people are crazy

post some sample data,so that we could understand you more clearly...

Flaterik
Starting Member

26 Posts

Posted - 2011-06-17 : 08:41:49

SELECT dbo.AppsDevCus.DevCus_serial AS Seriale, dbo.AppsDevCus.DevCus_anag AS [codice soggetto], dbo.AppsDevCus.DevCus_descr AS descrizione,
dbo.AppsDevCus.DevCus_dtExpWarman AS [Data fine garanzia Trend], dbo.AppsDevCus.DevCus_Free2 AS [Versione RIP],
dbo.AppsDevCus.DevCus_dtEndWarSup AS [Data fine Garanzia Fornitore], dbo.AppsDevCus.DevCus_dtExpWarhw AS [Data uscita attrezzatura],
dbo.AppsDevCus.DevCus_Free1 AS [Codice articolo vero], dbo.AppsDevCus.DevCus_dtiniwarman AS [Data ingresso attrezzatura],
dbo.BabelData.bd_Data AS Expr1
FROM dbo.BabelData RIGHT OUTER JOIN
dbo.AppsDevCus ON dbo.BabelData.bd_Code = dbo.AppsDevCus.DevCus_Free7
WHERE (dbo.BabelData.bd_Language = 'ITA') AND (dbo.BabelData.bd_Type = 'inks')

This is our query. the table apsdevcus have 4864 records , after this join remain only 719

I try also the left join but is the same

the world is strange but people are crazy

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-06-17 : 09:11:45

quote:
Originally posted by Flaterik

SELECT dbo.AppsDevCus.DevCus_serial AS Seriale, dbo.AppsDevCus.DevCus_anag AS [codice soggetto], dbo.AppsDevCus.DevCus_descr AS descrizione,
dbo.AppsDevCus.DevCus_dtExpWarman AS [Data fine garanzia Trend], dbo.AppsDevCus.DevCus_Free2 AS [Versione RIP],
dbo.AppsDevCus.DevCus_dtEndWarSup AS [Data fine Garanzia Fornitore], dbo.AppsDevCus.DevCus_dtExpWarhw AS [Data uscita attrezzatura],
dbo.AppsDevCus.DevCus_Free1 AS [Codice articolo vero], dbo.AppsDevCus.DevCus_dtiniwarman AS [Data ingresso attrezzatura],
dbo.BabelData.bd_Data AS Expr1
FROM dbo.BabelData RIGHT OUTER JOIN
dbo.AppsDevCus ON dbo.BabelData.bd_Code = dbo.AppsDevCus.DevCus_Free7
WHERE (dbo.BabelData.bd_Language = 'ITA') AND (dbo.BabelData.bd_Type = 'inks')

This is our query. the table apsdevcus have 4864 records , after this join remain only 719

I try also the left join but is the same

the world is strange but people are crazy

dont you have a common column to join both the tables...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-17 : 09:18:59

When you use a right outer join and then use the left table in the where clause, it is exactly as though you were doing an inner join (in most cases). So you probably want to do this:

SELECT
   dbo.AppsDevCus.DevCus_serial AS Seriale,
   dbo.AppsDevCus.DevCus_anag AS [codice soggetto],
   dbo.AppsDevCus.DevCus_descr AS descrizione,
   dbo.AppsDevCus.DevCus_dtExpWarman AS [Data fine garanzia Trend],
   dbo.AppsDevCus.DevCus_Free2 AS [Versione RIP],
   dbo.AppsDevCus.DevCus_dtEndWarSup AS [Data fine Garanzia Fornitore],
   dbo.AppsDevCus.DevCus_dtExpWarhw AS [Data uscita attrezzatura],
   dbo.AppsDevCus.DevCus_Free1 AS [Codice articolo vero],
   dbo.AppsDevCus.DevCus_dtiniwarman AS [Data ingresso attrezzatura],
   dbo.BabelData.bd_Data AS Expr1
FROM
   dbo.BabelData
   RIGHT OUTER JOIN dbo.AppsDevCus
      ON  dbo.BabelData.bd_Code = dbo.AppsDevCus.DevCus_Free7
      AND (dbo.BabelData.bd_Language = 'ITA')
      AND (dbo.BabelData.bd_Type = 'inks')

This article is well-worth reading: http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

Flaterik
Starting Member

26 Posts

Posted - 2011-06-17 : 09:22:14

Yes , the common column are bd_Code and DevCus_Free7.
They contain the same value type but not the same number of record.

The babeldata.bd_code contain 4 record . The AppsDevCus table does'nt have devcus_free7 filled on every records, only some record have this field.
Where is not present, i want to have a null or empty value but i want to have the value

the world is strange but people are crazy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources