Select Data Only If It Is There

Mar 23, 2008

Here's my table:

--------------------------
Teams
--------------------------
ID | Name | City | State
--------------------------

I want to output the Teams in this format:

Name
City, State

However, some teams don't have a name, so I want to output them like this:

City, State Team
City, State

And some teams don't have a name or a city:

State Team
State

And then there are teams who have a name, but not a city:

Name
State

I'd like to do this all in one select statement. Is there a way to do something like:
SELECT Name IF EXISTS, City IF EXISTS, State IF EXISTS
FROM TEAMS

View 5 Replies


ADVERTISEMENT

DB Engine :: Unable To Select Data From A Table Even After Providing Select Access

Aug 28, 2015

I am unable to the access on table even after providing the SELECT permission on table.

Used Query by me :

Here Test is schema ; Card is table ; User is Satish

To grant select on Table

GRANT SELECT ON TEST.Card  TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST  TO Satish.

View 8 Replies View Related

'Select Top 10 * ...' Returns Data But 'Select * ..' Does Not

Dec 7, 2006

HiI have a query that is performing very strangely.I f I put a top statement in it returns rows,soSelect top 10 * from .......returns 10 rowsbut without it then no data is returnedSelect * from ..........returns 0 rows.

View 1 Replies View Related

Insert Data To A Table Where Select Data From Another SERVER

Oct 8, 2007

I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name)
Dim selectSQL As StringDim con As SqlConnection
Dim cmd As SqlCommand
Dim reader As SqlDataReader
Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;")
Dim username As String
username = Request.QueryString("username")
selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con)
con.Open()
reader = cmd.ExecuteReader()
lbl.Text = selectSQLCatch ex As Exception
lbl.Text = ex.Message
Finally
If (Not con Is Nothing) Then
con.Close()
con.Dispose()
End If
End Try
End Sub
'------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
May i know that how do i retrieve data from  [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
 

View 1 Replies View Related

Problem Showing Data From Select Line With Another Select Line In It

Apr 7, 2007

Hi
If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
Then i get Images(6)Images(1)Images(1)
But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this
Images(1)Images(1)
But i cant get it to work.

View 2 Replies View Related

Is It Possible To Select Data From Data Sorce

Apr 18, 2007

As title...

If it is, what syntax should I refer to?



Regards,

Ricky.

View 3 Replies View Related

How To Select Data

Oct 24, 2005

my table ha follow structure..SalaryTable:Value Salary------------Jan 1000Feb 2000Mar 3000Apr 4000i want the o/p as..Jan Feb Mar Apr1000 2000 3000 4000Pls give me a query for thisRegards,Satheesh

View 1 Replies View Related

Please Help Me! How To Select My Data In SQL Server

Jul 14, 2007

 I have a table "tbNews" in my database with N_ID, N_Content...
eg:
N_ID         N_Content
1              Hello
3              How are you?
14            Are you there?
23            Can you help me?
32             ABC
33            CDE
36           EFG
If I have my N_ID = n (with n=1,3...) , can I select my records with next 1,2  or pre 1,2
eg: I have my N_ID=23, -->can I have got N_ID=3, 14 or N_ID=32, 33 ?
N_ID         N_Content
3              How are you?
14            Are you there?
or
32             ABC
33            CDE
Please help me have a Select clause!
Thanhs all. 

View 3 Replies View Related

How Do Select Data From My Database By IDs

Dec 14, 2007

How do i select data from my Database "HPDB.mdf" where the ID is 13, 14, 15?

I try to do it in the aspx way, but i got a conversion error:
Conversion failed when converting the nvarchar value '13, 14' to data type int.

asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="%$ ConnectionStrings:csHPDB %"
SelectCommand="SELECT * FROM [mgr_table] WHERE ([ID] IN (@ID))"
SelectParameters
asp:Parameter DefaultValue="13, 14, 15" Name="ID" /
/SelectParameters
/asp:SqlDataSource
asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
/asp:GridView

Anybody know how to solve this or do it in Visual Basic way? please help me. Thanks.

View 10 Replies View Related

Select Last 6 Month Data In Sql

Mar 24, 2008

select * from tbl where nmonth between datepart(mm,DATEADD(month, -2, getdate())) and datepart(mm,getdate())
this query is returning my data  between 1(jan) and 3 (mar)
if i change my query to get all datas between nov to mar
select * from tbl where nmonth between datepart(mm,DATEADD(month, -4, getdate())) and datepart(mm,getdate())
this query is not returning data since the month between 11 to 3
Guys,Help me out to correct this syntax.
 
 
 
 
 

View 4 Replies View Related

Select Certain Data From One Cell

Jun 13, 2006

Hi!
Anyone knows how to select
only certain data from one data field?

Lets say i have this field which captures
Member Name and ID.
The ID is in parantheses "()".
I only want the Member name.
How do I select this from the table?

E.G.:
Field: Data
MName: John Doe (123)

I need to select only "John Doe".
Any help is deeply appreciated.
Thank you!!

View 1 Replies View Related

Replacing Data From SELECT/WHERE

Jun 26, 2001

Hi, and tanks for taking time to read this question. I am still a novice at SQL (Server 2000)programming.

Is there a way to replace the contents of a column of a table derivied from SELECT and WHERE statements?

For example,

SELECT * FROM table1
WHERE column1 = 'text'

and replace 'text' with say, 'newtext'

Any help would be appreciated!

Regards,

Chandran

View 5 Replies View Related

Different Select Abilities Where No Data

Sep 8, 2004

I'm really in need of help with this one!!
I am writing a script which will provide me with financial info.
This will then be used to upload to a management system.
I have tried using the following code;
select
year.mem_desc as year,
line.mem_name as line,
unit.mem_name as unit,
ver.mem_name,
version =
Case
when ver.mem_name like 'Actual' then 'Actual Input'
when ver.mem_name like 'Forecast' then 'Actual Input'
when ver.mem_name not like 'Actual' then ver.mem_name
end,
cust1.mem_name as product,
cust2.mem_name as costcentre,
cust3.mem_name geographic_market,
cust4.mem_name as segment,
Jan =
case
when Jan is null and ver.mem_name like 'Actual'
then select Jan from finloc where year.mem_desc like year and line.mem_name like line and
unit.mem_name like unit and ver.mem_name like 'Forecast'
when ver.mem_name like 'Forecast'
then select Jan from finloc where year.mem_desc like year and line.mem_name like line and
unit.mem_name like unit and ver.mem_name like 'Actual'
when Jan is not null then convert(varchar(50),Jan)
end,

This doesnt work!! I need to be able to say that if the columns are blank or null for Actual then take Forecast, a kind of merge statement is needed but I dont know how to write one, can someone please please help!!

View 2 Replies View Related

Select Last Months Data

Aug 5, 2004

Hi, i am trying to create a t-sql statement that will retrieve last months data (ie. if i run the query on 9th August, i only want to retrieve Julys data, 1st Sept will retrieve all of Augusts data etc). The query will be used once a month to populate a table, can anyone advise me on the correct where clause to use ?

Thanks in advance

Dave

View 3 Replies View Related

SELECT Last Modified Data

Sep 24, 2004

Hi,

I have data that sometimes will be modify. when modify the new data will be insert and the old data still remain inside the database. The same item have their own unique id. So I want to query the data that last modified. How?



Thanks

View 8 Replies View Related

Select Similar Data

Aug 15, 2012

finding is queries to find duplicate data.Basically I have a picklist table in a database and I have discovered that there are what looks like duplicate data (because the name is the same) but there is a different number on the end, as you can see from an example below.

NO, ENTRY
24, John Doe|26|05768
24, John Doe|26|5768

Do you know if there is a sql query that can be ran against this table that will look through the ENTRY column and select fields that are similar and not duplicates (duplicates can't exist due to PK constraints)

View 8 Replies View Related

Select Data From Excel

May 22, 2008

Hello,

Is there a good way in SQL to select records that have been sent in an excel spreadsheet and import them into a temporary table?

I have a list of order codes that have different seg codes for example:

Order CodeZSeg
7625 ZBL
717009 ZBL
3277 ZCI
8144 ZCI

Each order code is unique and is classifed into a zseq. Depending on this zseg I need to update an existing table with the following field names and values for each order code:

For example for an order code classified as a ZBL I need to insert into an existing table that uses order code as the PK

Value Code: Patrac
Value Description: Patient Race
Value Type: ST
Units: None
Template Name: Lab_Master_
Field Name: AOE_BL_Race

There are multiple Value codes for each Zseg that will be inserted for each order code.

Can someone tell me the best way to do something like this?

View 1 Replies View Related

Select Only One Data In A Field

Jun 9, 2008

Hi there,
Need help..
I have a table name Changekitlist and have fields name CKNumber,ck_ID,ck_Name, etc.. Now, I plan to see each CKNumber one at a time so that I can put it in an array which I'll use in VB6 program.

However, If I use the command: "SELECT CKNumber from Changekitlist"
it displays all the CKNumber list that I have (CK001 to CK500) and I cannot store it in an array properly because it display all CKNumber at once and store all the 500 CKNumbers at once in the array.

What will I do so I can display one CK Number at a time, store in an array and loop so that I'll get the next CKNumber and store it again.. Need your advice please. Thanks!

View 12 Replies View Related

How To Select Data From Table

Dec 25, 2014

I am designing a webpge using asp.net with sql server 2005 in back end there is table with following columns :

username, name, phone1, phone2, age and some others columns are there for storing user's details.

I would like to select in such a way so that i enter

more than one user name seperated with commas in textbox

in selection result i get all the phone numbers associated with that usernames.

For example

UsernamesNamePhone1Phone2
abc@yahoo.comAbhi93344592879334459288
def@yahoo.comSony9935199351
efg@yahoo.comTony98351983519835198351

Select Phone1,Phone2 from tblregistration where usernames=abc@yahoo.com,def@yahoo.com,efg@yahoo.com

In result i like all the phone numbers

93344592879334459288
9935199351
98351983519835198351

View 1 Replies View Related

Select Data From Top N Columns

Aug 8, 2005

Suppose you have table with many columns and often you need data from first 15 or 20 columns. In that case you have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want

Here is the procedure



Create procedure TopNcolumns (@tableName varchar(100),@n int)
as
Declare @s varchar(2000)
set @s=''
If exists(Select * from information_Schema.tables where table_name=@tablename and table_type='Base Table')
Begin
If @n>=0
Begin
set rowcount @n
Select @s=@s+','+ column_name from information_schema.columns
where table_name=@tablename order by ordinal_position
Set rowcount 0
Set @s=substring(@s,2,len(@s)-1)
Exec('Select '+@s+' from '+@tablename)
End
else
Select 'Negative values are not allowed' as Error
End
else
Select 'Table '+@tableName+' does not exist' as Error



Madhivanan

Failing to plan is Planning to fail

View 3 Replies View Related

Select A Subset Of Data

Feb 13, 2007

How would I write a select statement that would return multiple fields in a records based on a a distinct of one of those fiels.

Example

Table Name : Sales Table
Field Name : Name Address Phone Zip Sale
Rec1: Peter Smith 12 Market St 999-999-9999 12345 99.99
Rec2: John Jones 73 Broadway 999-999-8888 12345 12.34
Rec3: Charle Brown 42 Peanuts Ave 999-999-7777 12345 34.56
Rec4: Peter Smith 12 Market St 999-999-6666 12345 67.89
Rec5: John Jone 73 Broadway 999-999-5555 12345 36.52

How would I be able to return the columns Name Address and Phone based on the distinct of Name.

View 4 Replies View Related

Got The Data With Select, How To Update?

May 13, 2007

Hi all, have a pretty simple query. but am new to workin in multiple tables. This statement shows
Items in table1 that exists in table2 that match a filter.

I would like to change this query to update the TOBEDELETED column to 'F' based on the same column and filter match.


SELECT ITEMS.ITEMNO, ITEMS.TOBEDELETED
FROM ITEMS
INNER JOIN QLOG
ON ITEMS.ITEMNO=QLOG.SOURCEID
WHERE QLOG.SOURCE= 'ITEMS'

Thanks all in advance

View 4 Replies View Related

Select Common Data

Sep 21, 2007

Hi - I'm trying to construct a select statement from the following tables(see example)

_________________________________
|Customer | Product |Shop |
|----------|----------|---------|
|Customer1 | Milk |Dairy |
|Customer2 | Cream |Dairy |
|Customer3 | Milk |Dairy |
|Customer1 | Trainers |Sports|
|Customer2 | Football |Sports|
_________________________________

REQUIRED RESULT SET: Milk, Cream.

Basically I want to select all of the Products (no duplicates) where EVERY customer(1, 2 & 3) have bought a from a common shop type: i.e 'Trainers' and 'Football' should not be selected as Customer3 has not bought any goods from a sports store.

This is an example of a larger problem where there can be numerous products, customers and shops. Here's a DESCRIBE of the relevent columns in each of the tables:

CUSTOMER:
customerID

PRODUCT:
productID
customerID
shopID

SHOP:
shopID

This looks like it should be easy but my SQL isn't the best ;-)

I'd really appreciate any help you could give!!

Cheers!

View 1 Replies View Related

Select Data From String

Dec 6, 2007

I am trying to extract the numbers only from a string field any help would be great.

field is phone number (123)123-2584 and need to display 1231232584
thanks

View 1 Replies View Related

How To Select Data From Different Databases

Jan 10, 2008

Hi I'm trying to select data from different databases, Actually databases have the same structure and tables but different data, so I want to mix data from specific tables of all databases.
Any help would be great

View 5 Replies View Related

Select Data From Two Tables

Jan 17, 2008

I have numerous tables in my db and would like to do this: (not sql below just simple explanation'

select contacts from wce_contact where idstatus is like 'london%' and select the same contacts where notes are not blank in the table wce_history

Hope it makes sense?

I just don't know how to query two tables at once...

View 3 Replies View Related

Help Select Data Type

Jul 23, 2007

Hi Everyone,



I have a table in my database that stores info to process a 'Parts Rush' request, so a user can get there part expedited. I want to have a field in my table named 'Status'.



The four status' are:

Pending

Approved

Declined

Coachback



The question is should I use integers to represent the status (ie. 1 = Pending) or should I set that field to varchar and store the actual word?



I'm just trying to develop my SQL skills and what to exercise good practices. I am using SQL Server 2005 Express.



Thank You

-Sam

View 4 Replies View Related

SELECT INTO Using A Data-flow

Sep 13, 2005

Hi,

View 18 Replies View Related

Loop In Sql Select's Data

Apr 28, 2008



Hi,

I want to create an select query and loop it thru , process couple things like finding maximum,count values
for every row...

it is easy to do it with sprocs , I can open couple cursors and do it but How can I do it SSIS transformations?

thanks,
J

View 9 Replies View Related

Select Size Of Data Used Per Row

May 10, 2007

Hi



I'm trying to figure out how to retrieve the size of the data used per record, not the allocated size per row.

Any pointers would be helpfull.

Thanks in advance!



Rgds,

Worf

View 1 Replies View Related

How To Select Data For A Given Month Or All Records

Mar 29, 2007

I posted this question last night and thought I had an answer.  I have a date field.  I want to be able to filter records by the month of the date.  To do this, I pass the integer of the month.  But I also want to be able to return all the records if no month integer is passed.  So functionally,select * from table where (MONTH([AD ENDS]) = @month)will return all records where [Ad Ends] is in January if @month = 1 and all records if @month is empty.  The solution I got last night was to use select * from table where (MONTH([AD ENDS]) = ISNULL(@month, MONTH([AD ENDS]))) If @month is null, all records are returned.  I was focused on another aspect of the page when this was posted.  It worked in the designer, so I thought I was set.  This morning I realized I don't know how to pass a null variable in a querystring.  Since a querystring is a string, it probably can't be done.  Another suggestions was to change this programmatically.   But is there a way to do this in the dataset?  I'm using SQL Server 2005, a strongly typed dataset and the designer.Diane  The answer i got last night was to  

View 4 Replies View Related

How To Select && Insert Data In The Same Table

May 24, 2007

Hi
   I have a table called Version and its attributes are Version_ID, Project_ID , Hospital_ ID , Date_Created and comments. I want to select the data by Version_ID,Project_ID and  Hospital_ID and the selected data is inserted in the same table(Version) as new row .
Table: Version (Version_ID(Primary_key), Project_ID(Foreign_Key),Hospital_ID(Foreign_Key),Date_Created,Comments). 
Iam using Visual Web Developer Express and SQL Server 2005. Iam doing on asp.net 2.0.
Could anyone please send me the code asp.net 2.0 for the above problem.

View 6 Replies View Related

Using SQLDataSource To Insert And Select Data

Dec 12, 2007

I'm a traditional asp guy and I'm having a heck of a time getting my arms around this SQLDataSource provided in ASP.NET 2.0
I've setup the Connection String and successfully used the SQLDataSource.Insert method. 
I can't for the life of me figure out how to use the SQLDataSource.Select command. 
I want to get the @@IDENTITY of the last record inserted. 
Example:
---Works Fine---
SQLDataSource.InsertCommand = "Insert into Engines(Type, Description)values('" & DrpType.Text & "', '" & txtDescription.Text & "')"
SQLDataSource.Insert()
--- End Works Fine------- Doesn't Work---
SQLDataSource.SelectCommand = "Select @@IDENTITY as 'Identity'"    
set RecordSetVar = SQLDataSource.Select <--- this wants some kind of arguements
--- End Doesn't Work---
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved