My query is not displaying all columns

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

My query is not displaying all columns

Author

Topic

maravig
Starting Member

14 Posts

Posted - 2015-02-09 : 08:03:08

I created the following simple query between two tables:

SELECT vru.User_Name0, vru.Full_User_Name0, vru.department0, vru.employeeNumber0, 
vru.title0, vru.manager0, vrs.location0, vrs.Netbios_Name0, vrs.Last_Logon_Timestamp0
  FROM v_R_User vru
  LEFT JOIN v_R_System vrs
	 ON vru.ResourceID = vrs.ResourceID
ORDER BY vrs.User_Name0

I don't know how to properly insert a pic, but basically I get output, except the last 3 columns. I've tried every variation of JOIN and I cannot seem to solve this issue. Any help would be greatly appreciated.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 08:51:53

Not sure what your problem is. I get all the columns. Here's my test with your code, but using table variables since I don't have your data:


declare @v_R_User table (ResourceID int, User_Name0 varchar(20), Full_User_Name0 varchar(20), department0 varchar(20), employeeNumber0 varchar(20), title0 varchar(20), manager0 varchar(20))
declare @v_R_System table (ResourceID int, location0 varchar(20), Netbios_Name0 varchar(20), Last_Logon_Timestamp0 varchar(20))

insert into @v_R_User(ResourceID) values(1)
insert into @v_R_System(ResourceID) values(1)


SELECT vru.User_Name0
     , vru.Full_User_Name0
     , vru.department0
     , vru.employeeNumber0
     , vru.title0
     , vru.manager0
     , vrs.location0
     , vrs.Netbios_Name0
     , vrs.Last_Logon_Timestamp0
  FROM @v_R_User vru
       LEFT JOIN @v_R_System vrs
           ON vru.ResourceID = vrs.ResourceID
  ORDER BY vru.User_Name0;

When I run this I get:


User_Name0  Full_User_Name0 department0 employeeNumber0 title0  manager0    location0   Netbios_Name0   Last_Logon_Timestamp0
NULL        NULL            NULL         NULL           NULL    NULL        NULL        NULL            NULL

All the columns are there.

maravig
Starting Member

14 Posts

Posted - 2015-02-09 : 10:36:36

I'm still relatively new to SQL and I don't have the DECLARE or insert into statements. Could that be why I'm getting some columns and not others?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 10:42:21

quote:
Originally posted by maravig

I'm still relatively new to SQL and I don't have the DECLARE or insert into statements. Could that be why I'm getting some columns and not others?

No, those are just there so that I can test your query. Basically, there's no way SQL would omit any of the columns in the SELECT. If the query works, you'll get all the columns, even if they contain nothing (as in my example)

jleitao
Posting Yak Master

100 Posts

Posted - 2015-02-09 : 10:44:26

you don't see the columns or the last 3 columns returns NULL?

------------------------
PS - Sorry my bad english

maravig
Starting Member

14 Posts

Posted - 2015-02-09 : 10:51:50

I tried your code and got nothing. My code, I can get everything but the last 2 columns. No matter what I do, that's the best I can get. Kind of frustrating, but I am a novice at this. I had to change location0 to l0 to correct the location field and that work, but it's not picking up any data from the v_R_SYSTEM table. I can't figure out why.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-09 : 10:57:23

My code is your code, except for the table variables. Are you saying that you did not get the same result I posted? If so, did you run all the code I posted?

Wait a minute. The first time you said you were missing three columns. Now you say you are missing two columns. However, I think you mean something else. I think you mean that the "missing" columns are empty (that is, NULL or blanks). As I said, there is no way for SQL to omit selected columns. However their contents could be empty.

Is that what is happening in your case?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources