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 |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2004-07-16 : 14:19:21
|
| when I do this query I get a recodset with some records with first_name and last_name empty (because it compares the inner join with the tbl_pers_ids_detail table and retrieve records accordingly) and I want to change my query so that I will get the records which has data in for first_name or last_name and the rest I don't need.So please ...is there a way that i can change this query to get the required one.Basically eliminating the null recordsets for the entries in first_name and last_nameSELECT C.c_first_name AS first_name ,C.c_last_name AS last_name,DM3.tel_num AS home_phone,C.billing_addr AS mailing_address,C.billing_city AS mailing_city, C.billing_state AS mailing_state,C.billing_zip AS mailing_zipcode,C.c_ssn AS ssn_no, DM3.sec_tel_num AS work_phoneFROM pers_detail C INNER JOIN tbl_pers_ids_details L ON C.pers_id = L.pers_id LEFT OUTER JOIN tbl_tele DM3 ON DM3.pers_id= L.pers_id |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-16 : 14:25:02
|
WHERE C.c_first_name IS NOT NULL AND C.c_last_name IS NOT NULL Tara |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-16 : 14:26:51
|
| WHERE C.C_first_name IS NOT NULL AND C.c_last_name IS NOT NULL |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-16 : 14:27:22
|
| sniped by 49 seconds! |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-16 : 14:27:51
|
| Quick! Who can eliminate the NULL first and last names in the JOIN? |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-16 : 14:29:59
|
quote:
Originally posted by sqllearner
SELECT C.c_first_name AS first_name ,C.c_last_name AS last_name,DM3.tel_num AS home_phone,C.billing_addr AS mailing_address,C.billing_city AS mailing_city, C.billing_state AS mailing_state,C.billing_zip AS mailing_zipcode,C.c_ssn AS ssn_no, DM3.sec_tel_num AS work_phoneFROM pers_detail C INNER JOIN tbl_pers_ids_details L ON C.pers_id = L.pers_id LEFT OUTER JOIN tbl_tele DM3 ON DM3.pers_id= L.pers_id
SELECT do, re, me FROM pers_detail C INNER JOIN tbl_pers_ids_details L ON C.pers_id = L.pers_id AND C.c_first_name IS NOT NULL AND C.c_last_name IS NOT NULL |
|
|
|
|
|
|
|
|
Eliminating NULLS