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.
Go to Top of Page

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?
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page
   

- Advertisement -