Within a SELECT, how do I replace an empty or null value with a value from another table?

Hello,

I'm a beginner in SQL and I have been searching through the SQL Cookbook and Google but I can't seem to find an example of what I want to do. 

I want to create a report that will return names and emails using two of my tables. I want to use the email in my primary table in the select but if it is null or empty I want to replace it with an email from my secondary table.

Below is what I would like to do but I got a syntax error with it in SQL Server 2000.

SELECT MemberID As ID, 
MemberFirstName As FirstName,
MemberLastName As LastName,
(IF MemberEmail = '' THEN SELECT TOP 1 OtherEmail FROM OtherTable WHERE OtherID = MemberID) As Email
FROM PrimaryTable
Thanks for your time.

Jason 

 

0
jholland99
4/20/2008 9:10:08 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies
150 Views

Similar Articles

[PageSpeed] 44

Your not actually checking for NULL values.
 
'' Is an empty string which is not equal to a NULL value.
 
 

--
Please Click "Mark as Answer" if this post is helpful to you.

Visit My Blog http://www.mick-walker.co.uk
0
mickwalker
4/20/2008 9:35:14 PM

The trouble I'm having is is a syntax error that I'm getting and SQL Server will not even go as far as to execute the query.

I guess I need to know if the line below is correct syntax and if not what do I need to change to accomplish the output I'm looking for.

(IF MemberEmail = '' THEN SELECT TOP 1 OtherEmail FROM OtherTable WHERE OtherID = MemberID) As Email
  
0
jholland99
4/20/2008 10:00:58 PM
No its not correct, you need to read up on TSQL conditional statements

--
Please Click "Mark as Answer" if this post is helpful to you.

Visit My Blog http://www.mick-walker.co.uk
0
mickwalker
4/20/2008 10:21:10 PM

ops, mistake .... brb

 


Bryian Tan
MCP, MCAD
0
BryianTan
4/20/2008 10:48:21 PM

I'm making some progress but I'm not quite there yet, any help would be appreciated.

This is what I have so far but it looks like the CASE statements are only allowed to return results and not execute more statements. I would like to put something like "SELECT TOP 1 Email.Email As Email FROM Email WHERE Email.MemberID = MemberFamily.MemberID" where I have 'EmailIsNull' but I can't do that because it looks like you can only return results in a CASE statement.

SELECT MemberFamily.FamFirstName As FirstName,
       MemberFamily.FamLastName As LastName,
       CASE 
         WHEN MemberFamily.Email IS NULL THEN 'EmailIsNull'
         ELSE MemberFamily.Email
       END AS Email
FROM   MemberFamily

 I have been playing around with the IF/THEN statement but can't get the correct syntax. This is what I have so far that doesn't work:

SELECT MemberFamily.FamFirstName As FirstName,
       MemberFamily.FamLastName As LastName,
       IF MemberFamily.Email IS NULL
         BEGIN
           SELECT TOP 1 Email.Email As Email 
           FROM Email 
           WHERE Email.MemberID = MemberFamily.MemberID
         END
       ELSE
         BEGIN
           MemberFamily.Email As Email
         END
FROM   MemberFamily
 Thanks,

Jason

0
jholland99
4/21/2008 3:28:26 AM

You can do this, should work.

SELECT MemberFamily.FamFirstName As FirstName,
MemberFamily.FamLastName As LastName,
CASE
WHEN MemberFamily.Email IS NULL THEN (SELECT TOP 1 Email.Email FROM Email WHERE Email.MemberID = MemberFamily.MemberID)
ELSE MemberFamily.Email
END AS Email
FROM MemberFamily

 


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
4/21/2008 3:40:12 AM

It worked! THANKS SO MUCH!!!

I thought I tried that already but I must have had some stupid typo somewhere. woooo! Cool

 

0
jholland99
4/21/2008 3:53:19 AM

Yes Let me guess, you missed to include the brackets around select statement in CASE?Smile


Save our world, its all we have! A must watch video Pale Blue Dot

Please use the search feature of the forum before asking a question.
0
XPSCodes
4/21/2008 4:11:44 AM
Reply:

Similar Artilces:

SQL QUERY replace NULL value in a row with a value from the previous known value
Hi!! I have a table with no unique ID, I created an Id with ROW_NUMBER () OVER (ORDER BY column asc) rank and created a stored procedure with the rank and table. In this stored procedure I want to create an extra column that checks if the value from another columns contains value = 100 rank,Ifnummer,followingnr,productnr testing 2313,'6497269','001' ,412 , NULL 2314,'6497269','002' ,413 , NULL 2315,'6497269','003' ,100 , yes 2316,'6497269','004' ,430 ,NULL ...

Select values from one table based upon values in another...
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?Rex WinnCode-frog System Architects, Inc.VS 2005 Professional, C#, C++ Well... You might try reading up on some standard sql syntax... There are a number of different ways... Select t.f1,t.f2,t.f3 From tb1 t INNER JOIN tb2 t2 ON t.f1 = t2.f1 Where t2.f1 = 'condition'   Select t.f1,t.f2,t.f3 From tb1 t Where t.f1 IN (    Select t2.f1    From tb2 t2    where t2.f1 = 'condition')   Select t.f1,t.f2,t.f3 From tb1 t Where exists (select t2....

Add values to a table based on values in another table
When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated. Thanks You could do it in a trigger.  They're really not hard.  It's just SQL you want to run whenever a certain action happens on your table.  Pretty st...

Selecting value in one field then causes value in another field to be selected.
I have a web form which contains a date field, the date is populated using a DatePicker which is Javascript based. The form also has a drop down box containing 3 values. What I want to do is when a date has been selected that this will cause the dropdown list value to change to dated which is one of its 3 values. i.e. when the date field is not blank then the value will be selected in the dropdown. What I also want to happen is when the date has been selected and the dropdown value is dated then the date field and the dropdown field will be set to read only.Anyone any ideas how to this usin...

Select from table where value is in an array of values
Hello, all.  What I'm trying to do is limit my select statement depending on a variable number of parameters.  Currently, I use a dataset to pull the data from the database, but it seems that I can't add parameters outside of a clause.  I'll give you an example of what I had hoped to do.SELECT EmployeeID FROM Employees[@param]                        where [@param] would be generated given a selection, so it would be a string equaling "WHERE EmployeeID = x" and possib...

1) Null value in Foreign key 2) extra value to indicate all the values in PK table apply
I have two tables  1) tblCustomer (ID, Name, City)  2) tblemp (ID, NAME, Dept.ID, tblcustomer.ID) Both the tables have ID as PKA emp can be either assigned a) All customers b)single customer c) NO customer Pls note:- there will never be 2 or 3 customer linked to emp (my actuall requirement tables are different but to explain i am using the above tables) I know how to assign single customer......but had problem how to link all customers and "no customer" Please tell me if the following solution is right?1) I will manually insert a record in tblCustomer with id 0 as " all custom...

DropDownList bound to one table, selected value from another table
I posted this in another forum topic and I'm not getting any response.  I'm hoping maybe I will ge more help here since it pertains to a server control.  Hi I am having a very frustrating problem with a DropDownList.  The DropDownList is bound to one SQL Server 2005 table.  I do this progromatically with the following code:Public Sub EvaltrID() TryDim aType As String = txtAircraftType.SelectedValue Dim auth As String = Convert.ToInt32(MinLevel.Text) Dim active As String = "True"Dim iPilot As String = "True" Dim CrewPos As Strin...

Select the value from a field of one table and place it into another table.
Using VB.NET, VS2005, SQL Server 2000. In a Windows Form, how could I provide a means for a user to select the value from a field of one table (a primary key value) and place it into another table (as a foreign key value)? The goal is to "assign" a person from one table to a location in another table by placing the person's primary key value into the location's foreign key field via an easy to use "selection" mechanism like a DataGridView or something.Thanks in advance for any help or examples.David  this is an ASP.Net forum - - you might want to check out: http://forums.microsof...

DropDownList bound to one table, selected value from another table
Hi I am having a very frustrating problem with a DropDownList.  The DropDownList is bound to one SQL Server 2005 table.  I do this progromatically with the following code:Public Sub EvaltrID() TryDim aType As String = txtAircraftType.SelectedValue Dim auth As String = Convert.ToInt32(MinLevel.Text) Dim active As String = "True" Dim iPilot As String = "True"Dim CrewPos As String = txtCrewPos.SelectedValue Dim CrewPos1 As String = "SO" Dim instructSO As String = "True"Dim sesType As String = txtSessionType.Text Dim sesType1 As String ...

Dropdownlist selected value passes first value in list no matter what value is selected. why ?
 HI . I have a dropdownlist. Databinds ok but when I must pass a value as a Selected value it always passes the first value in the list even if I select another value. I tried to set the ddl to enableviewstate false but it doesn't change anything. Any ideas ? Code below, thanksLoading the ddl:       productCategories = CatalogAdmin.Admin_Product_Categories_NotIn(productId);        for (int i = 0; i < productCategories.Rows.Count; i++)        {       &...

Selecting values in DDLs with values from SQL DB
On my page I have four drop-down lists: startWeek, startYear, endWeek and endYear which sets a period of time.The default values, that must be displayed on page load, are selected from a table called Period in a SQL database using a Linq.DataContext class called MyContextClass. The periods (a period is a week in a given year) in this table can be active/inactive. In the code I select two active periods: The one with the lowest ID (the earliest period) and the one with the highest ID (the latest period). I then try to display these two periods in the four drop downs: the first two lists conta...

Copy values from one table to another table using LINQ to SQL?
Hello,I have two database tables that have different names but identical structure (columns).What is the easiest way to copy the values from one table to the other table using LINQ to SQL? Thanks,-- shawn shawn Hi, As far as I research, we could use "foreach"  to insert the data from one table to another table. The code is shown below.         //there exist two table list and listSecond        DataClassesDataContext dataClass = new DataClassesDataContext(); //create the instance of the DataCon...

Setting the value of a RadioButtonList based on a value in my sql table.
Hi folks, From a Sql database table I'm trying to display a RadioButtonList with two text values, "Buy" and "Sell". The actual numeric values in my table are 1 (Buy) or 2 (Sell). The name of the column in the table is "buy_sell". So how do I set the SELECTED="TRUE" property in the ListItem based on the value of my buy_sell column ? <asp:FormView ID="FormView1" runat="server" DataSourceID="ObjectDataSource1" OnInit="FormView1_Init"> .. .. <EditItemTemplate> <td><asp:RadioButtonList  DataSourceID="ObjectDataSource1" DataValueField="buy_sell" DataTextField...

hide only the dropdown value while selecting the another dropdown value
 hai  friend i am using two dropdown list if i select one dropdownlist the another dropdownlist value must be hidewhat can i do for this     Ramesh.Dhttp://rameshduraikannu.blogspot.com  u can do that using javascript..function Hide(){ var drop1 = document.getElementById('<%=dropdownlist1.ClientID%>')  var drop2 = document.getElementById('<%=dropdownlist2.ClientID%>')  if(...

Web resources about - Within a SELECT, how do I replace an empty or null value with a value from another table? - asp.net.sql-datasource

File:Replace this image male.svg - Wikipedia, the free encyclopedia
to an image (e.g. you photographed or drew it yourself) and would like Wikipedia to use it, you must release it under a free copyright license ...

Facebook Replaces Payments Emails With API
Facebook announced that it is scrapping the emails that it sends to developers containing information about payments , replacing it with a payments ...

Facebook’s New Ads Power Editor Replaces the Bulk Uploader with a Streamlined GUI
This week, Facebook launched its new Ads Power Editor desktop software for buyers who work directly with a Facebook ads representatives. The ...

Face Morph HD - Change and Replace Head in Pic Frame Hole with Pencil Portrait Effects! on the App Store ...
Get Face Morph HD - Change and Replace Head in Pic Frame Hole with Pencil Portrait Effects! on the App Store. See screenshots and ratings, and ...

NASA just chose SpaceX to replace the Shuttle for flying astronauts
jurvetson posted a photo: Elon's design goal = "the safest, most advanced crew vehicle ever flown.” With seats for seven . First manned flight ...

How to replace Roland Juno 106 Voice Chip 80017A 1 of 2 - YouTube
Own a Juno 106? You might encounter a dying voice chip. In this 2 part vid series I explain how to fix this problem. Don't be afraid its really ...

Khurram Khan ‘is going to be very difficult to replace’ for UAE ahead of World Twenty20 qualifiers
The post-Khurram era starts when the UAE face the hosts Scotland in the opening fixture of the World Twenty20 qualifier. The national team have ...

Change of the Day: Canadian Town Wants to Replace Its ‘Land of Rape & Honey’ Motto
The town of Tisdale in Saskatchewan has had a very unfortunate slogan for the past 60 years, and they’re finally looking for a new one. Visitors ...

ASI helps Cloud wholesaler replace Cisco hardware with Brocade rentals
ARNnet ASI helps Cloud wholesaler replace Cisco hardware with Brocade rentals ARNnet Cloud Plus is transitioning from Cisco to Brocade hardware ...

Notable names floated to replace Georgie Gardner on Today
Two names have raised eyebrows within television circles as Channel Nine prepares to announce a replacement newsreader on its breakfast television ...

Resources last updated: 12/22/2015 2:47:22 AM