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)
 What is wrong with SELECT

Author  Topic 

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-03 : 09:55:33
I am having a problem with a SELECT query. I have had to add a relationship to one table, and I want to get the text from the 2nd table in relationship to the number in the main table.

Old Query that reurned all records it was supposed to. In this query the field position_name did not have a null or blank value.

SELECT  fieldman_number
		, (last_name + ', ' + first_name) AS fullName
		, first_name
		, last_name
		, status
		, address1
		, address2
		, address3
		, city
		, county
		, state
		, zip
		, email
		, home_phone
		, work_phone
		, extension
		, pager
		, cell_phone
		, car_phone
		, fax
		, affiliation
		, position_name
		, division_manager_flag
		,  region_manager_flag
FROM fm_fieldman_master 
WHERE (status = 'Active') OR (status = 'Inactive') 
ORDER BY fullName

In the new query that includes an INNER JOIN, if there is a null or blank value in the position_number field, that row is not returned, but it should be.

SELECT     fm_fieldman_master.fieldman_number
		, (fm_fieldman_master.last_name + ' ,' + fm_fieldman_master.first_name) AS fullName
		, fm_fieldman_master.status
		, fm_fieldman_master.address1
		, fm_fieldman_master.address2
		, fm_fieldman_master.address3
		, fm_fieldman_master.city
		, fm_fieldman_master.county
		, fm_fieldman_master.state
		, fm_fieldman_master.zip
		, fm_fieldman_master.email
		, fm_fieldman_master.home_phone
		, fm_fieldman_master.work_phone
		, fm_fieldman_master.extension
		, fm_fieldman_master.pager
		, fm_fieldman_master.cell_phone
		, fm_fieldman_master.car_phone
		, fm_fieldman_master.fax
		, fm_fieldman_master.affiliation
		, fm_fieldman_positions.position_name
		, fm_fieldman_master.division_manager_flag
		, fm_fieldman_master.region_manager_flag
FROM         fm_fieldman_positions INNER JOIN
              fm_fieldman_master ON fm_fieldman_positions.fieldman_position_number = fm_fieldman_master.position_number
WHERE	  (status = 'Active') OR (status = 'Inactive') 
ORDER BY  fullName



What am I missing or doing wrong?

Thanks for the help
CoachBarker

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts
Posted - 2009-05-03 : 10:07:32
fm_fieldman_master ON ISNULL(fm_fieldman_positions.fieldman_position_number,0) = ISNULL(fm_fieldman_master.position_number,0)

OR

fm_fieldman_master ON (fm_fieldman_positions.fieldman_position_number = fm_fieldman_master.position_number) OR (fm_fieldman_positions.fieldman_position_number IS NULL AND fm_fieldman_master.position_number IS NULL)
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-03 : 10:18:18
Nope returns the same results, 87 records, the query should be returning 99 records.

Thanks for the help
CoachBarker
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2009-05-03 : 10:38:00
If that column is null you will not get a result with INNER JOIN. You'll have to use a LEFT JOIN.
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-03 : 13:07:00
So then as long as I have a value in there greater than zero then all rows will be returned. Easy enough to do.

Thanks for the help
CoachBarker
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-04 : 09:05:06
Thinking about that last post, I really do not want to have to always have a value in that filed, if no value is slected the default value will be 0, but I still want ALL records returned?

Thanks for the help
CoachBarker
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-05-04 : 09:19:45
use left join like suggested earlier.
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-04 : 11:37:19
If you mean like so:
FROM fm_fieldman_positions INNER JOIN
fm_fieldman_master ON fm_fieldman_positions.fieldman_position_number = fm_fieldman_master.position_number

if a value is 0 that row is not returned.

Thanks for the help
CoachBarker
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-05-04 : 11:42:55
replace INNER JOIN with LEFT OUTER JOIN
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-04 : 12:14:00
Pasted it back in wrong,

FROM fm_fieldman_positions LEFT OUTER JOIN
fm_fieldman_master ON fm_fieldman_positions.position_number = fm_fieldman_master.position_number

still doesn't return a row with a 0 in the field.

Thanks for the help
CoachBarker
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-05-04 : 12:48:54
can you post the full query.
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-04 : 12:55:24
Sure can

SELECT     fm_fieldman_master.fieldman_number
		, (fm_fieldman_master.last_name + ' ,' + fm_fieldman_master.first_name) AS fullName
		, fm_fieldman_master.last_name
		, fm_fieldman_master.first_name
		, fm_fieldman_master.status
		, fm_fieldman_master.address1
		, fm_fieldman_master.address2
		, fm_fieldman_master.address3
		, fm_fieldman_master.city
		, fm_fieldman_master.county
		, fm_fieldman_master.state
		, fm_fieldman_master.zip
		, fm_fieldman_master.email
		, fm_fieldman_master.home_phone
		, fm_fieldman_master.work_phone
		, fm_fieldman_master.extension
		, fm_fieldman_master.pager
		, fm_fieldman_master.cell_phone
		, fm_fieldman_master.car_phone
		, fm_fieldman_master.fax
		, fm_fieldman_master.affiliation
		, fm_fieldman_positions.position_name
		, fm_fieldman_master.division_manager_flag
		, fm_fieldman_master.region_manager_flag
--FROM    fm_fieldman_positions INNER JOIN
--        fm_fieldman_master ON fm_fieldman_positions.position_number = fm_fieldman_master.position_number

FROM         fm_fieldman_positions LEFT OUTER JOIN
              fm_fieldman_master ON fm_fieldman_positions.position_number = fm_fieldman_master.position_number
WHERE	  (status = 'Active') OR (status = 'Inactive') 
ORDER BY  fullName


Thanks for the help
CoachBarker
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-05-04 : 13:01:37
in the above query, as long as the status is 'active' or 'inactive', that row will be returned. If the problem is the display, meaning its getting displayed as NULL, use ISNULL function to replace it with the value you want.
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-04 : 13:37:31
Returned rows has nothing to do with active or inactive. It has to do with "fm_fieldman_positions.position_name"

Table Positions
# Name Status
1 Area Supervisor A
2 Membership A
3 QC Specialist A
4 Organics A
5 Calibration A
6 Manager A

If a person is not one of the above positions, he/she is then a Division or Regional Manager(1 person can be both) but I do not want the position 6 Manager A to have to be in the table, under position these people would have NULL for position but still be in the returned rows.
If I have a null value in the position.name field, I still want that row returned with all the others.


Thanks for the help
CoachBarker
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-05-04 : 13:53:53
Returned rows have everything to do with Active or Inactive, that's what your WHERE clause is requiring. Why don't you want the postion 6 manager to be in the table? Do you wamt all the records from fm_fieldman_positions returned, or all the records from fm_fieldman_master returned. Please post sample data and expected output so we can help you better.

Jim
Go to Top of Page

CoachBarker
Posting Yak Master

170 Posts
Posted - 2009-05-04 : 14:16:14
All records from fieldman_master, Active and Inactive are not the issue here, the issue is the field position_name. The Manager positon does not need to be in the table because there are 2 types of managers, divisional and regional, if you are a manager you can be one or both types. The managers field is taken care of using a checkbox. So if you are a manager then the field position_name would be NULL.
quote:


# FirstName Status Position_name Div_Man Reg_Man
1 ALBERT Inactive 1 NULL NULL
8 BRETT Active 0 NULL NULL
9 BURKETT Active 4 NULL NULL
10 CALVIN Active NULL NULL Y
11 CARL Inactive 0 NULL NULL
12 CHRIS Active 0 NULL NULL
16 CINDY Outside 0 NULL NULL
17 CLAYTON Active 3 NULL NULL
18 CLYDE Active NULL NULL Y
24 DAVE Outside 0 NULL NULL
25 DAVE Active 0 NULL NULL
26 DAVE Active NULL Y Y
27 DAVID Active 0 NULL NULL
28 DAVID Active 2 NULL NULL
29 DAVID Active 0 NULL NULL
30 DAVID Active NULL NULL Y

Table of Positions
quote:

# Name Status
0 Area Supervisor A
1 Membership A
2 QC Specialist A
3 Organics A
4 Calibration A
5 Manager A


the query would return all records but those that are "outside" for status.

Thanks for the help
CoachBarker
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2009-05-04 : 14:24:45
I'm not 100% sure I'm following the logic, but maybe this gets you closer. If you can post data with table creation and insert statements that we can run queries against with the expected results, that would probably help:
SELECT
	*,
	COALESCE(P.position_name, 'Regional Manager') AS position_name
FROM
	fm_fieldman_master AS M
LEFT OUTER JOIN
	fm_fieldman_positions AS P
	ON M.fieldman_position_number = P.fieldman_position_number 
	AND P.fieldman_position_number <> 5
WHERE	  
	M.status IN ('Active', 'Inactive') 
ORDER BY  
	M.fullName
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