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)
 data not display in column

Author  Topic 

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-13 : 12:40:03
i've a problem with my database... after execute my sql command, its showed that my query executed is success but the data not display in column... anybody know y?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2008-01-13 : 12:52:13
It means your query did not return any results.

Perhaps if we could see the SQL command it would be easier to help.


Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-13 : 13:07:25
this is my query command

quote:
SELECT u.SysUser_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.Total_anual_maint_and_monitor
FROM PerformanceData AS p INNER JOIN
MonitoringCost AS m ON p.Performance_id = m.MonitoringCost_id INNER JOIN
Photovoltaic AS pv ON p.Performance_id = pv.PV_id AND m.MonitoringCost_id = pv.PV_id INNER JOIN
SysUser AS u ON p.Performance_id = u.SysUser_id AND pv.PV_id = u.SysUser_id AND m.MonitoringCost_id = u.SysUser_id



data cannot display also... but query executed is successful
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2008-01-13 : 17:15:34
Query executing only means it is correct in syntax.

Change INNER to LEFT and see if there are records. If the joining columns don't connect at all (or properly) you will get no records. If you don't see an error message than the syntax of the query is correct and your query is just written improperly and returning no results.



Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-13 : 23:28:54
quote:
Originally posted by ejoeyz_85

this is my query command

quote:
SELECT u.SysUser_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.Total_anual_maint_and_monitor
FROM PerformanceData AS p INNER JOIN
MonitoringCost AS m ON p.Performance_id = m.MonitoringCost_id INNER JOIN
Photovoltaic AS pv ON p.Performance_id = pv.PV_id AND m.MonitoringCost_id = pv.PV_id INNER JOIN
SysUser AS u ON p.Performance_id = u.SysUser_id AND pv.PV_id = u.SysUser_id AND m.MonitoringCost_id = u.SysUser_id



data cannot display also... but query executed is successful


Also check for presence of NULL values in the joining columns.
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-14 : 05:59:40
after i changeg INNER to LEFT... the data is display but in column u.sysuser_fname is not display even there have data in table... so how shud i do?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-14 : 06:09:19
quote:
Originally posted by ejoeyz_85

after i changeg INNER to LEFT... the data is display but in column u.sysuser_fname is not display even there have data in table... so how shud i do?

p.Performance_id = u.SysUser_id AND pv.PV_id = u.SysUser_id AND m.MonitoringCost_id = u.SysUser_id

look at for data in these columns and see if matching values exists.
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-14 : 06:18:08
ermm.... what shud i changed? i try to change LEFT to RIGHT but there is data in column u.sysuser_fname... but if i change to LEFT again, there is no data in column u.sysuser_fname... so maybe u can explain me detail.. i've no idea.. plzz
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-14 : 06:29:18
If you take RIGHT data will appear as it will take all data from SysUser regardless whether it matched the criteria given. The problem is surely with your matching condition (one i gave in blue earlier).
Can you send me some sample data from the tables and your expected result?

Also i found you are using same field twice in comparison.can i ask you why?
p.Performance_id = u.SysUser_id AND pv.PV_id = u.SysUser_id AND m.MonitoringCost_id = u.SysUser_id
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-14 : 07:00:57
This is my command

quote:
SELECT u.SysUser_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.Total_anual_maint_and_monitor
FROM PerformanceData AS p LEFT JOIN
MonitoringCost AS m ON p.Performance_id = m.MonitoringCost_id LEFT JOIN
Photovoltaic AS pv ON p.Performance_id = pv.PV_id AND m.MonitoringCost_id = pv.PV_id LEFT JOIN
SysUser AS u ON p.Performance_id = u.SysUser_id AND pv.PV_id = u.SysUser_id AND m.MonitoringCost_id = u.SysUser_id



This is the output data...

http://www.postimage.org/image.php?v=Pq4raslS


how to display data in column sysuser_fname?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-14 : 07:51:39
quote:
Originally posted by ejoeyz_85

This is my command

quote:
SELECT u.SysUser_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.Total_anual_maint_and_monitor
FROM PerformanceData AS p LEFT JOIN
MonitoringCost AS m ON p.Performance_id = m.MonitoringCost_id LEFT JOIN
Photovoltaic AS pv ON p.Performance_id = pv.PV_id AND m.MonitoringCost_id = pv.PV_id LEFT JOIN
SysUser AS u ON p.Performance_id = u.SysUser_id AND pv.PV_id = u.SysUser_id AND m.MonitoringCost_id = u.SysUser_id


This is the output data...

http://www.postimage.org/image.php?v=Pq4raslS


how to display data in column sysuser_fname?


You have given only end result. What i require is some data from left part of query i.e everything barring last join with SysUser table and also some sample data from SysUser. Or can you please take some random values from first part of query which you believe must return in result and check whether they exists in SysUser?
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-14 : 08:33:44
this is my comand to display data in table sysuser.

quote:
select * from sysuser



below is my result..

http://www.postimage.org/image.php?v=aV6D9qA


so, what shud i do?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-14 : 08:44:40
Can you check this:-

SELECT u.SysUser_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.Total_anual_maint_and_monitor
FROM PerformanceData AS p LEFT JOIN
MonitoringCost AS m ON p.Performance_id = m.MonitoringCost_id LEFT JOIN
Photovoltaic AS pv ON p.Performance_id = pv.PV_id AND m.MonitoringCost_id = pv.PV_id LEFT JOIN
WHERE p.Performance_id IN(2007123,2007211,2007888)
OR pv.PV_id IN (2007123,2007211,2007888)
OR m.MonitoringCost_id IN (2007123,2007211,2007888)

and see if it returns any data?
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-14 : 08:58:44
errmm..i did it... this is my new command..

quote:
SELECT u.SysUser_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.Total_anual_maint_and_monitor
FROM PerformanceData AS p LEFT JOIN
MonitoringCost AS m ON p.Performance_id = m.MonitoringCost_id LEFT JOIN
Photovoltaic AS pv ON p.Performance_id = pv.PV_id AND m.MonitoringCost_id = pv.PV_id LEFT JOIN
SysUser AS u ON p.Performance_id = u.SysUser_id AND pv.PV_id = u.SysUser_id AND m.MonitoringCost_id = u.SysUser_id
WHERE p.Performance_id IN(2007123,2007211,2007888)
OR pv.PV_id IN (2007123,2007211,2007888)
OR m.MonitoringCost_id IN (2007123,2007211,2007888)




then below is my result... data not display in table


http://www.postimage.org/image.php?v=aV6KSPr
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-14 : 10:01:39
quote:
Originally posted by ejoeyz_85

errmm..i did it... this is my new command..

quote:
SELECT u.SysUser_fname, pv.PV_address, p.Start_monitoring, p.Last_monitoring, p.Period_of_monitoring, m.Ongoing_maintenance,
m.Savings_for_inverter_replacement, m.Monitoring, m.Total_anual_maint_and_monitor
FROM PerformanceData AS p LEFT JOIN
MonitoringCost AS m ON p.Performance_id = m.MonitoringCost_id LEFT JOIN
Photovoltaic AS pv ON p.Performance_id = pv.PV_id AND m.MonitoringCost_id = pv.PV_id LEFT JOIN
SysUser AS u ON p.Performance_id = u.SysUser_id AND pv.PV_id = u.SysUser_id AND m.MonitoringCost_id = u.SysUser_id
WHERE p.Performance_id IN(2007123,2007211,2007888)
OR pv.PV_id IN (2007123,2007211,2007888)
OR m.MonitoringCost_id IN (2007123,2007211,2007888)




then below is my result... data not display in table


http://www.postimage.org/image.php?v=aV6KSPr


Thats it. The query doesnt return any records for ids in SysUser. Then how do you expect this query to join with SysUser and retrieve you expected result?
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-14 : 11:41:37
so how to retrieve?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-14 : 12:01:41
quote:
Originally posted by ejoeyz_85

so how to retrieve?

For answering this, i need to know what your requirement is, also structure of your tables along with some sample data on each and your expected o/p. Can you provide with this?
Go to Top of Page

ejoeyz_85
Yak Posting Veteran

54 Posts
Posted - 2008-01-14 : 12:47:35
i want to join four table which are table sysuser,photovoltaic, performance data n monitoring cost.... i want to view n merge all data.. my prob is only column sysuser not display the data.. i already posted all my comand before... n my sample also...


Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-01-14 : 13:18:00
sysuser is only displayed for data that he has in joining tables.
maybe you should learn how joins work... BOL = Books OnLine = SQL Server help and google has a lot of info on this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2008-01-14 : 13:19:22
The problem appears to be that records in one table do not actually join to one or
more of the others as you have written, and specifically to the more recent sample...the PerformanceData PerformanceID's in the sample do not exist in the Performance data table.


Since this query returns NO records:

SELECT	u.SysUser_fname, 
		pv.PV_address, 
		p.Start_monitoring, 
		p.Last_monitoring, 
		p.Period_of_monitoring, 
		m.Ongoing_maintenance, 
		m.Savings_for_inverter_replacement, 
		m.Monitoring, 
		m.Total_anual_maint_and_monitor
FROM PerformanceData AS p 
		LEFT JOIN 	MonitoringCost AS m ON	p.Performance_id = m.MonitoringCost_id 

		LEFT JOIN	Photovoltaic AS pv ON	p.Performance_id = pv.PV_id AND 
											m.MonitoringCost_id = pv.PV_id 

		LEFT JOIN	SysUser AS u ON			p.Performance_id = u.SysUser_id AND 
											pv.PV_id = u.SysUser_id AND 
											m.MonitoringCost_id = u.SysUser_id
WHERE p.Performance_id IN(2007123,2007211,2007888)
		OR pv.PV_id IN (2007123,2007211,2007888)
		OR m.MonitoringCost_id IN (2007123,2007211,2007888)


The tables PerformanceData,PhotoVoltaic, and MonitoringCost do not have any of the ID's 2007123,2007211, or 2007888

Since the tables do not appear have any of those ID's, how can that query produce ANY results? if the ID's are required to join the tables together....and they don't match eachother to begin with...that will produce zero records.




Poor planning on your part does not constitute an emergency on my part.
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