Select Statement To Pull Columns From Same Table For Different Ids

Oct 9, 2007

I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.

The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.

Thanks

View 1 Replies

Select Statement To Pull SQL Server Information

May 6, 2008

Sorry if this is rudimentary.

I need a select statement to retrieve what version of sql server is running and whether it is enterprise or standard.

I know I could just right click but I have hundreds of servers to go through so this should save alot of time.

Thanks,
Kraig

View 2 Replies View Related

DISTINCT Only Certain Columns In On Table And Pull Their IDs

Mar 9, 2007

Please help.� (I am using ASP, VB, SQL)I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.

View 5 Replies View Related

Group / Union Statement - Pull Unique Records From A Large Table

Sep 22, 2014

I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.

What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).

I have tried 2 statements:

#1:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
GROUP BY tblTable1.Name, tblTable1.INDate
ORDER BY tblTable1.Name;

#2:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));

Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.

Example:

NameINOUT
John Smith1/1/20141/2/2014
John Smith1/1/2014(blank)

I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?

View 1 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

SQL Server 2012 :: Select Statement That Take Upper Table And Select Lower Table

Jul 31, 2014

I need to write a select statement that take the upper table and select the lower table.

View 3 Replies View Related

Reading Columns After Select Statement

Apr 28, 2004

Afer I am getting a DataSet from SELECT* statement I would like to know the value of each field
How do I do that?

View 1 Replies View Related

Is It Possible To Trim The Columns In Select Statement

Oct 7, 2004

Can you please tell me, is it possible to Trim the columns in a select statement:

select eventid,referenceno,EventDesciption,modulename,moduleid,created_by,CONVERT(varchar(10),eventdate,101) as eventdate from MYTable

Thank you very much.

View 1 Replies View Related

Help With Totalling Columns In SQL Select Statement

Feb 18, 2005

Maybe its a friday, but I can't figure out how to total the returned results so I just get a sum of the 'Items' and 'Total'. I thought it was Compute but that did not work!

I'm pulling data from 2 tables, Order and Order_Item and what Im trying to achieve is to total up how many items were sold and the total. Each one of the rows below are for each order.

I must be doing something wrong, can anyone help?

SELECTsum(Order_Item.Quantity) as Items,
sum(Order_Item.Price*Order_Item.Quantity)-[Order].Discount as Total
FROM[Order]
JOINOrder_Item ON Order_Item.[OrderID] = [Order].[ID]
GROUP BY[Order].[ID],
[Order].OrderRef,
[Order].CustomerID,
[Order].ReceivedDate,
[Order].Postage,
[Order].Discount
ORDER BY [Order].ReceivedDate DESC

Items Total
==== =====
114.9900
113.5920
333.6750
227.1840
567.9600

View 3 Replies View Related

Grouping Columns From Select Statement

Jan 23, 2015

I am retrieving some data from Invoices, Customers and Companies tables as follows, but would like to make the customerName and the Companies.Name as single column such Name and similarly for customerID/companyID and customerCode/companyCode.

Code:
with cte
as
(
selectdistinct i.invoiceNumber, itemID, customers.customerID, Companies.companyID
,SUM(net_weight) as totalWeight, rate
,(select SUM(net_weight) * rate) as amount

[code]....

View 6 Replies View Related

Is There A Way To Create Columns From A Row Value In A Select Statement?

Jan 30, 2008

Hi!

I have a select question that look like this:

DECLARE @ID uniqueidentifier;

SET @ID = '40bd3052-60f4-414a-99df-ca882c128493';

SELECT

rp.ID AS ReportPackId

,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier

,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier

FROM

ReportPack rp

INNER JOIN ReportPackAdditionalParty rpap

ON rpap.fk_ReportPack = rp.ID

WHERE rp.ID = @ID

The result a get when running the select question is:

ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId2

My problem is that the result I want is the following:

ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
AdditionalPartyIdentifier

40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
addPartyId2

I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?

View 5 Replies View Related

Computed Columns Used In Select Statement

Nov 12, 2007

Does tsql allow sth like

Select col1*col2 as ComputedColumn, ComputedColumn + 2 as NewColumn
From T_Table

THis is possible in Access.

View 5 Replies View Related

Using One Alias For Mutiple Columns In A SELECT Statement

Feb 8, 2006

Hi all,Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?SELECT RTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.], LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building], LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street], LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District], LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town], LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County], RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]ThanksTryst

View 2 Replies View Related

SELECT Statement With 2 Columns Originating From The Same Fields

Jul 19, 2006

Hello,

I've been busy all night searching and reading trying to figure out how I can do the following.

I have a table that tracks user IDs in multiple fields. When I select records from this table I need a way to resolve those ID fields back into user names by referencing the users table. SQL statement thus far...

SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???

How do I resolve theTable.UserIDA and theTable.UserIDB back to Users.Username so that the records returned fill the fields NameA and NameB?

Your help is greatly appreciated.

Thanks,

-Mike
Ontario, Canada

View 5 Replies View Related

Using One Alias For Mutiple Columns In A SELECT Statement

Feb 8, 2006

Hi all,

Is this at all possible? In the following query I have mutiple columns in my SELECT statement that each have their own alias. Is it possible that I can use just one Alias for all these columns (such as Address), and if so how is it done?

Code:

SELECTRTRIM(ISNULL(ta.house_no_flat, '')) as [Target - Flat No.],
LOWER(RTRIM(ISNULL(ta.building, ''))) as [Target - Building],
LOWER(RTRIM(ISNULL(ta.road_street, ''))) as [Target - Street],
LOWER(RTRIM(ISNULL(ta.district, ''))) as [Target - District],
LOWER(RTRIM(ISNULL(ta.town, ''))) as [Target - Town],
LOWER(RTRIM(ISNULL(ta.county, ''))) as [Target - County],
RTRIM(ISNULL(ta.postcode, '')) as [Target - PostCode]

Thanks

Tryst

View 2 Replies View Related

Transact SQL :: Run Select Statement That Has (Text Already Placed Into Columns)

Sep 10, 2015

Created a new scheduled job using Transact-SQL.

Running a basic script SELECT FROM WHERE

As of now, it places it into one column, for all the fields.

I need the fields to be placed in their respective columns. Tried saving it as .xls and .csv. Same issue. Running SQL Server 2008R2.

View 2 Replies View Related

Need Gridview To Display Different Columns Based On New SELECT Statement

Apr 23, 2008

I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great.
My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around.
So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting.
I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions?
Thanks,
Brad

View 9 Replies View Related

No Data In Select Statement / It Still Adds Number To The Columns

Feb 5, 2015

I have this update statement I am trying to use, to update a table. My problem is if there is no data in the select statement, it still adds number to the columns. How can I have this update statement work to put blank value in if there are no counts?

UPDATE T_AXA_BreakDown_Claims
SET [Claim Count Conm] = t2.[Claim Count Conm]
FROM T_AXA_BreakDown_Claims t1
INNER JOIN
(select

[code]....

View 2 Replies View Related

SQL 2012 :: Allow For User Provided Number Of Columns In Select Statement

Aug 20, 2014

I am currently trying to write a query that pulls a summation of item specific data from sales orders. For simplicity's sake, the column structure can be something like the following...

Item#,
PoundsDuringWeekNumber (this would be the current week number out of the 52 weeks in a year and the pounds of the item during that week)

However, those are not going to be the only 2 columns. The idea of the query is that the user would be able to provide the query a date range (say 2 months) and the columns would then morph into the following...

Item#,
PoundsDuringWeekNumber (current),
PoundsDuringWeekNumber(current - 1),
PoundsDuringWeekNumber (current - 2),
etc. etc.
PoundsDuringWeekNumber(current - 8)

Initial ideas where to create a function to execute the summation of the pounds during the date range of the week in question and execute it across the columns, but with the indeterminable number of columns, the query would not know how many times to execute the function.

View 1 Replies View Related

SELECT Statement With Two Retrieved Columns Originating From The Same Dbase Fields

Jul 20, 2006

Hello,

I've been busy all night searching and reading trying to figure out how I can do the following.

I have a table that tracks user IDs in multiple fields. When I select
records from this table I need a way to resolve those ID fields back
into user names by referencing the users table. SQL statement thus
far...

SELECT A.Username as NameA, B.Username As NameB, FROM theTable, Users As A, Users As B WHERE theTable.UserIDA ???

How do I resolve theTable.UserIDA and theTable.UserIDB back to
Users.Username so that the records returned fill the fields NameA and
NameB?

Your help is greatly appreciated.

Thanks,

-Mike

View 5 Replies View Related

Select Statement Where One Of Columns Assigns Sequential Value To Each Row Based On Item Number

Jul 14, 2014

I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:

item_no seq_no note doc_no
ABC 1 blah 1
ABC 2 blahh 1
ABC 3 bla3 1
XYZ 1 more n 2
XYZ 2 another 2
EFG 1 blahhh 3

View 2 Replies View Related

Transact SQL :: Transpose Part Of Rows To Columns In Single Select Statement

Aug 31, 2015

Below. I have also pasted the current result of this query and the desired result.

Query can be updated to get the desired result as given below?

Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'

Current Result which I am getting with this query

OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01

Desired/Expected Result for which I need updated query

OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3

5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01

Using DB2.

View 2 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

Multi Statement Table-valued UDF Without Declaring Columns?

May 6, 2007

Hey
I have created a multi-statement
table valued function

alter
function
fn_x(@x int)returns
@tbl table
(
          position int identity primary key,          i
int)
as
begin
insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          returnend

Is it possible skipping the
definition of the table columns (the light blue
part)?I need to return a different
structure based on a parameter.Dropping those lines throws an
error "incorrect syntax near 'as'"
The other solution is declaring
each udf separately as one statement udf.Thanks

View 2 Replies View Related

Update Multiple Columns In One Table With Case Statement

Nov 15, 2013

I want to update multiple column in one table using with case statement. i need query pls..

stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL

View 2 Replies View Related

How To Create A Query To Pull That Specific Row And All 50 Columns

Feb 7, 2012

I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.

View 3 Replies View Related

Ssis Cannot Pull Columns From Stored Procedure?

Sep 6, 2007

hi,

i have the following stored proc which returns a resultset at the end, i have an SSIS package that calls this stored proc and outputs the result to a file. However, the package fails because it cannot pull the columns for the schema because of the return lines in the middle of the stored proc. If i remove the it works fine, pulls hte columns as normal. but if i leave them in ssis cannot get the columns. what can i do to get around this?

ALTER PROCEDURE [dbo].[uspCreateBrightPointFile]

AS

SET nocount ON

IF EXISTS (SELECT TOP 1 *

FROM brightpointfile)

TRUNCATE TABLE brightpointfile

-- Get the order information from the database where vendorconfirmationID = 0

INSERT INTO brightpointfile

SELECT o.orderid,

o.requestid,

'295193' AS araccountnumber,

o.orderdate,

'PRIORITY' AS shipmethod,

'Asurion Dobson' AS billname,

'PO Box 110808' AS billaddress1,

'Attn Account Receivable' AS billaddress2,

' ' AS billaddress3,

'Nashville' AS billcity,

'TN' AS billstate,

'37222' AS billzip,

c.fullname AS shipname,

Replicate(' ',100) AS shipaddress1,

Replicate(' ',100) AS shipaddress2,

' ' AS shipaddress3,

Replicate(' ',40) AS shipcity,

' ' AS shipstate,

Replicate(' ',10) AS shipzip,

'1' AS linenumber,

ve.sku AS itemcode,

o.quantity AS qty,

r.typeid,

r.customerid,

0 AS addressfound

FROM [order] o WITH (NoLock)

JOIN request r WITH (NoLock)

ON o.requestid = r.requestid

JOIN customer c WITH (NoLock)

ON r.customerid = c.customerid

JOIN (SELECT [subequipid],

[subid],

[clientequipid],

[serialno],

[statusid],

[startdate],

[enddate],

[createdate],

[createuserid]

FROM subequip s1 WITH (NoLock)

WHERE s1.statusid = 1

AND s1.startdate = (SELECT MAX(s2.startdate)

FROM subequip s2 WITH (NoLock)

WHERE s2.statusid = 1

AND s2.subid = s1.subid)) se

ON r.subid = se.subid

JOIN vendorequip ve WITH (NoLock)

ON se.clientequipid = ve.clientequipid

JOIN clientequip ce WITH (NoLock)

ON ce.clientequipid = se.clientequipid

WHERE vendorconfirmationid IS NULL -- order was never sent to CellStar

AND ve.typeid IN (1) -- only pull direct fulfillment (not store fulfillment)

AND ve.vendorid IN (2) -- only pull vendor = CellStar Insurance

AND o.orderdate > '2007-08-01' -- only pull orders after 08/01/2007

--IF @@ERROR <> 0

--RETURN 1

--First use the address types of 2

UPDATE brightpointfile WITH (ROWLOCK)

SET shipaddress1 = b.address,

shipaddress2 = b.address2,

shipcity = b.city,

shipstate = b.stateid,

shipzip = b.zipcode,

addressfound = 1

FROM customeraddress a WITH (NOLOCK),

address b WITH (NOLOCK)

WHERE a.customerid = brightpointfile.customerid

AND b.addressid = a.addressid

AND a.typeid = 2

--IF @@ERROR <> 0

--RETURN 2

--Update the rest where the address type is 1 and there is no type 2

UPDATE brightpointfile WITH (ROWLOCK)

SET shipaddress1 = b.address,

shipaddress2 = b.address2,

shipcity = b.city,

shipstate = b.stateid,

shipzip = b.zipcode

FROM customeraddress a WITH (NOLOCK),

address b WITH (NOLOCK)

WHERE a.customerid = brightpointfile.customerid

AND b.addressid = a.addressid

AND a.typeid = 1

AND brightpointfile.addressfound = 0

--IF @@ERROR <> 0

--RETURN 3

--Select all the records from the temp table, plus union in 2 extra records required by the client

SELECT *

FROM (SELECT orderid,

requestid,

araccountnumber,

orderdate,

shipmethod,

billname,

billaddress1,

billaddress2,

billaddress3,

billcity,

billstate,

billzip,

shipname,

shipaddress1,

shipaddress2,

shipaddress3,

shipcity,

shipstate,

shipzip,

1 AS linenumber,

itemcode,

qty,

0 AS additional

FROM Brightpointfile WITH (nolock)

UNION ALL

SELECT orderid,

requestid,

araccountnumber,

orderdate,

shipmethod,

billname,

billaddress1,

billaddress2,

billaddress3,

billcity,

billstate,

billzip,

shipname,

shipaddress1,

shipaddress2,

shipaddress3,

shipcity,

shipstate,

shipzip,

brightpointdefaultsku.linenumber,

brightpointdefaultsku.sku,

1,

1 AS additional

FROM Brightpointdefaultsku WITH (nolock),

Brightpointfile WITH (nolock)) a

ORDER BY orderid,

additional

IF @@ERROR <> 0

RETURN 1

RETURN 0

View 3 Replies View Related

Select Columns In 1 Table With Provision In 2nd Table

Jan 20, 2014

I have two tables with the common column-name/ The first one include such columns as the name of football team, its country and budget. The second one include the team name, victories (quantity of it), lost games, and season. So I have the task to select such column as name, victories, lost games in 2nd table but only that ones that has budget over 1 mln? How to build select statement SELECT name, victories, lost g/ from TABLE2 WHERE budget >1000000 from TABLE1? Or should I equates the table1.name=table2.name??

View 6 Replies View Related

Using Result Of A Select Statement From One Table To Add Data To A Different Table.

Mar 17, 2008

I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?

View 3 Replies View Related

How To Select All Columns Except One Column From A Table ?

Oct 6, 2006

Hi

I can't figure out how to do this and hope you guys can give me a hint....

I want to select all columns from a table except one column:

For example,

A table has 20 columns : {1,2,3.....20}

To select all of columns in a table, i could use
select * from table;

However, I want to select only column {1....19}

I do not want to type the column names in one by one in the SQL query,

is there a way to achieve this in a short form of Select?

Thanks,

View 15 Replies View Related

Select From Table Only Columns That Exist In Both Tables

Mar 28, 2008

Hi everyone.

I am stuck on this for quite a while. Lets say i have 2 tables.

Table 1 with these columns:

Serial_Num
Product
Price

Table 2 with this column only:
Product

I need to create a VIEW that will show me all the the data in Table 1 but only for the column that exists in table 2. Example:

Something like this:

select (all the columns from table 2)
from table 1

I need it to be dynamic because columns could be added to both tables anytime.
Thanks.

View 5 Replies View Related

Help With Select Statement - From XML Table

May 6, 2008

I'm trying to get the <Title> node value returned in the select statement below, but cant quite get it to work. I have the <ID> node being returned in the statement, but not the title. Any help is apprecited.

param ids = '<Collection><Content><ID>1</ID><Title>Document 1</Title></Content>< Content><ID>2</ID><Title>Document 2</Title></Content></Collection>'

CREATE PROCEDURE [GetDownloads](@Ids xml) AS

DECLARE @ResearchDocuments TABLE (ID int)

INSERT INTO @ResearchDocuments (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Ids.nodes('/Collection/Content/ID') as ParamValues(ID)

SELECT * FROM research_downloads INNER JOIN @ResearchDocuments rd ON research_downloads.doc_id = rd.ID Where research_downloads.post_sf = 0

View 5 Replies View Related

Help With Select Statement - From XML Table

May 7, 2008

View 6 Replies View Related