.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SELECT statement to return NULL by matching data from another table.

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

View Complete Post

More Related Resource Links

Select Sql does not return the data eventhough the data does in particular table.


Hi Dude,

I am new to Sql server 2005. I have a table in which contains many data. I need to take particular data. For example, I want to take value of 110652.795813712 from FTEBASEPAY column .  So i have wrote the sql statment like in below.

SELECT * From tblEmployees where FTEBASEPAY='53842.7782655718'

But i am not able to get the particular value. Manually i have seen the tblEmployees table, in which contains the particular data ('53842.7782655718').

When i execute the above select statement, there is no result for it. Please let me know anyone face the same problem? Please give a solution for it. What i have to for overcome this issue?I have to give one more information, the FTEBASEPAY dataType is float in tblEmployees table.

Thanks in Advance


Error: "Select statements included within a function cannot return data to a client"

hi all, Not sure if there is an easier way to do this but this is my first ever Function I created so far. I am getting the error: "Select statements included within a function cannot return data to a client" All I am trying to do is ...if the date is NOT passed then apply NULL as the default value and run the SELECT statement. If the date IS NOT null then run a different SELECT statement. CREATE FUNCTION [SchemaTest].[fn_Test] ( @instance as uniqueidentifier ,@date as datetime = null ) RETURNS varchar(8000) AS BEGIN If (@date is null) --@date = null ?? Begin Select * From Uds.RealtimeLogging Where instanceID = @instance End Else Begin Select * From Uds.RealtimeLogging Where instanceID = @instance and [timeStamp] > @date End Return @stepIds END Thank you!JCD

select data from table where it contain (.) ???


hi all

i have Price column in my table ( tbl1)

1.2           1.44         1              1.66

how can i select the prices that dont contain (.) ??

and then update it to add (.0) to it ???

this mean i will find (1) then convert it to ( 1.0)

please help me ;)

Conditional insert: If select return rows, insert rows to table otherwise insert specific row indica

This is what I have

Insert into ReportDetail( Partcipantid, Reportid)  

select distinct ParticipantID , 9 from OpenCredit

      except select ParticipantID, 9 from StoreCredit where Closed = 0

 Issue is that when above select statement returns no row, it seems like no record is

Can I display Data from a SQL Select Statement to a Label


            sql_select = "SELECT auid FROM tbl_auAccount WHERE username='" & IUN & "'"

            'Add Error Trapping Later
            con = Server.CreateObject("ADODB.Connection")

            'Execute the SQL Command

            'Close the connection
            con = Nothing

Now this works and I was able to put the value into a dropdown list box, but I need the value returned placed into a label such as:

Delete Statement with nested select in where clause deletes all entries in a table


I have fallen into a trap unadvertedly causing all records in a table being deleted. Can anybody tell me, if this a the intended behaviour of SQL Server 2008 R2 or does anybody know a method how to safely discover those kinds of typos in advance?

Regards, Markus

Here follows a script reproducing the behaviour:









Table Valued Function in the select statement


Hi, I have created a table valued function

dbo.FnTaskGrp(@p1 int,@p2 datetime)..

I got the result by executing,

SELECT * FROM dbo.FnTaskGrp(1,'1-jan-2010')

But For this ,

select * from dbo.FnTaskGrp(tab.WflTask,tab.WflModDt)

                  select WflTask,WflModDt from WorkFlow


I got the Error,

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'tab'.

Isn't Possible in SQLSERVER 2005?









SQL Select With Variable Table - Using with ODBC Data Source


Hello all,

Having difficulty with an idea for a SQL query today.  I am working with a data set that points to an ODBC data source.  The data source has several TRANSACTIONmmyyyy tables (mm - month, yyyy - year ), each holding data for a particular month, going back several years. Each table has identical fields and data types.

If I were to run the query below for just one month ( say I took out mmyyyy and plugged in a month and year, 052010 ) it will pull totals for the AccountNum I specify.


mmyyyy.AcctNum, SUM
mmyyyy.Amount ) As
 "Current Month"

TRANSACTION mmyyyy.Region = TOTALS.Region AND
TRANSACTION mmyyyy.Region

Return records from 1 table where no matching in 2nd


Hi.  I'm having trouble getting all the results I need with a query and hope its just my lack of experience.

Tables are Employee, Performance_log and Performance.

I need to see all Employees that have a performance_Log entry within a date range.  (done)

Then I was asked to include employees that do not have an entry in the Performance Log table but only if they don't have an entry within the date range previously mentioned.

I feel like I am almost there.  I can display all employees with performance logs and filter by date.   I can display all employees without performance logs within the same query.  My trouble is when an employee has a performance log outside of the date range.  The record should be included but its not.  I've tried using not exists() and not in() in the where but haven't figured it out yet.

Here is the query (the 4th line of the OR is a work in progress and is changing by the minute as I search for the solution)

The dates will eventually be replaced with variables and saved as a stored procedure to be called by Crystal.

  Employee.Employee_Id, Employee.Department, Employee.Active, Employee.NC_Type, Employee.NC_Full_Name,
  Performance_Log.NC_Performance_Note, Perfo

Saving primary key constraint when creating new table using SELECT INTO statement - SQL Server 2008


Using SQL Server 2008:

We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

I use SELECT INTO statements to create the tables in the EXTRACT schema.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?


Saving primary key constraint when creating new table using SELECT INTO statement - SQL Server 2008


Using SQL Server 2008:

We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

I use SELECT INTO statements to create the tables in the EXTRACT database.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?


problem with SQL select statement when variable are NULL


Hello all,

I'm struggling with a strange problem.

Let me first start with giving you my SQLdatasource:


      <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT ID_PLS, Checked, Userid, DONE, Entity, Environment, TO_DO, Description, HOW_TO, LINK_TO_PROC, Overtime, Processing_date, Driver FROM PLS_DAILY WHERE (Entity = @Entity) AND (Environment = @Environment) OR (Entity = @Entity2) AND (Environment = @Environment2)">
               <asp:SessionParameter DefaultValue="" Name="Entity" SessionField="eses_entity" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Entity2" SessionField="eb_entity" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Environment2" SessionField="eb_environment" 
                   Type="String" />
               <asp:SessionParameter DefaultValue="" Name="Environment" SessionField="eses_environment" 
                   Type="String" />

Then let me give you the sessionfield parameters:

    Protected Sub Button1_Click(ByVa

ListView DataPager with SQL-Statements that already return only paged data.



As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.

Checkboxes from data table


I have a data table that contains some values for Certifications.  This table can be updated in a different part of my application.  On the web page I am currently working on, I would like to have check boxes appear for every value in the data table.  I have been struggling with this trying to use a For Each loop and I cannot get it to work for anything.  Any suggestions? 

I am using the following Razor syntax to obtain my data (I know this works because I can display it in a WebGrid):


var db = Database.Open("MyConn");

How to get data from table based on four table



I need to know how to show top 30 records from four table

with fastest speed.. in ms sql server 2005..

hope You do the needfull


Posting data to ASMX and return Object for jQuery


I am using fluent NHibernate with WebForms and I am trying to build a page where I allow a user to post a status update on their profile page and I a using a .asmx WebService to post the data to the database and then returnig a StatusUpdate instance to the page to be used by jQuery. I have a couple problems.

1) First off when I return a string from the WebService(i was testing) the textbox where the user enters their status doesnt empty the contents. And since the page doesnt refresh even if I manally clear out the textbox and put in something else, it still posts the previous status to the database again. How do I fix this?

2) Secondly, when I return the StatusUpdate Object from the Webservice I cant the results to display anything. Like I sad, Im using jQuery to make an AJX call to the WebService.

Here is my code:

User Profile Page:

Status: <asp:Label ID="status" runat="server"></asp:Label><span style="font-size:.7em;"><asp:Label ID="statustime" runat="server"></asp:Label></span><br />
<textarea id="statusBox" rows="3" cols="40"></textarea><br />
<input type="button" value="Update Status" onclick="SetStatus(); return false;

Data Points: Deny Table Access to the Entity Framework Without Causing a Mutiny


Julie Lerman shows database administrators how to limit access to databases from the Entity Framework by allowing it to work only with views and stored procedures instead of tables-without impacting application code or alienating developers.

Julie Lerman

MSDN Magazine August 2010

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend