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.
| Author |
Topic |
|
ajaykumarkumar
Starting Member
1 Post |
 Posted - 2011-08-10 : 06:32:42
|
| Hi everyone, Can any one do me a favor, i m having problem with SQL join, Problem is, suppose there are 2 tables, first is having fields ID,Name and second table has ID, age. Where ID is common field. Suppose there are 10 records in table first and 8 records in table second. Now when i apply a LEFT JOIN to get name,age from both of tables. I get 10 names from first table and 8 rows + 2 rows(null) values from Second table. I want to handle these NULLS via SQL query. I want to apply a default value instead of NULL. How it can be achieved. Help me out.Thanks in AdvanceMBSW |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-08-10 : 06:39:44
|
Select...age = isnull(age, 18),...FromCorey I Has Returned!! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-10 : 07:40:03
|
| Keep in mind that using LEFT JOIN will only ensure you return all the records in first table. so if there are some records in second table but not in first they will get ignored. (though that seems unlikely from your table structures)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-08-10 : 10:02:39
|
| Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you? Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL. >> suppose there are 2 tables, first is having fields ID, Name and second table has ID, age. Where ID is common field. <<Where is the DDL? That is just basic Netiquette. But why don't you know that fields are not columns, rows are not records and there is no concept of a “common field” (there are referenced and referencing tables)? Likewise, RDBMS does not use vague names like “name” and there is no generic “id” in a valid data model. We do not store a computed value like “age”; we would keep “birth_date” instead of the age at this point in time. That is basic RDBMS and you do not learn RDBMS in a forum. >> Suppose there are 10 records [sic] in table first and 8 records [sic] in table second. <<The table cardinality has nothing to do with the problem>> Now when I apply a LEFT JOIN to get name, age from both of tables. I get 10 names from first table and 8 rows + 2 rows(NULL) values [sic: NULLs are the lack of a value] from Second table. I want to handle these NULLS via SQL query. I want to apply a default value instead of NULL. How it can be achieved. Help me out. <<The short answer is:COALESCE (DATEDIFF (YEAR, birth_date, CURRENT_TIMESTAMP), <default value>) AS something_age The better answer is get a copy of the MANGA GUIDE TO DATABASE, which is the best intro book on RDBMS and SQL I know. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
Handling Null Value through JOIN SQL Query