Return Result From One Query As A Column In Other Query

Jun 3, 2004

I'm having a bit of a trouble explaining what I'm trying to do here.





I have 3 "source" tables and a "connecting" table that I'm going to use





tblContacts - with contactID, ContactName etc


tblGroups - with GroupID, GroupName


tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)





They are related in a table called


tblContactsGroupConnection - with ContactID, GroupID and SubGroupID





One contact can be related to many subgroups.


What I want is a list of all contacts, with their IDs, names and what groups they are related to:





ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]


ContactID, ContactName, [SubGroupName1, SubGroupName3]


ContactID, ContactName, [SubGroupName3]





I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)





Kirikiri

View 1 Replies


ADVERTISEMENT

Same Query Gives Result With Different Column Sequence When Used In Query Analyzer

Feb 25, 2012

When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.

result in Excel 2003 SQL OLE DB query

col-A col-B col-C
values...

Result with Query Analyzer and VBA Macro

col-c col-B col-A
values...

View 3 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

A Query Sentence Return A Puzzle Result

Apr 28, 2006

the talbe row like this:
  ID   Name   Scoe 11     Tome    20 12    Jack   30 11     Tome    40 12   Jack   10 13   John    10 
My query command like this:
Select T1.Id,T1.Name,T2.mathfrom st T1right  join(Select Id as Id2,Sum(Math) as Math from St  group by id) T2on T1.id=t2.id2where t1.id = t2.id2
While the reuslt is :
Id      Name   Score
11    Tom        60
11    Tom        60
12    Jake        40
12    Jack        40
13    John        10   
 
I am wonder :the T1 gives a table with six rows, the T2 gives a table with three rows,  and I use RIGHT JOIN to connect the two table,the result should be a table with only three rows.I tried INNER JOIN, the result is same.
but why ? please help me !
 
 

View 1 Replies View Related

Return A Result Set From A SELECT Query In A Function?

Jan 21, 2008

Hello all:

How can I return the result of a SELECT statement from a stored procedure (function)?

CREATE FUNCTION returnAllAuthors ()
RETURNS (what do i put here??)
BEGIN

// Is this right??
RETURN SELECT * FROM authors

END


Thanks!

View 12 Replies View Related

Why Does AVG Query Not Return The Correct DOUBLE Result

Jan 3, 2008

I'm using AVG in a simple query but the results are being returned as INTEGERs and not the correct DOUBLE form.

AVG is being used on integer fields in a .mdf database eg: "SELECT AVG(intField) AS Results FROM myDB"

Thanks,

Geoff

View 1 Replies View Related

Need Sql To Return The Result Of A Query As Comma Seperated Values.

Jun 28, 2005

Hi,   I need a sql that returns the query result as comma seperated list of values, instead of several rows. Below is the scenario...   Table Name - Customer   Columns - CustomerID, Join DateSay below is the data of Customer table ...CustomerID    JoinDate1                   04/01/20052                   01/03/20033                   06/02/20044                   01/05/20025                   09/07/2005Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()This gives the below result as two rows.CustomerID15But i need to get the result as '1,5' (comma seperated list of resulting values).Any help is highly appreciatedThanks in AdvanceRamesh

View 4 Replies View Related

Please Help Me To Optimize This Sql Query, It Takes 28 Seconds To Return Result. Please Give Me A Tips Where I Went Wrong?

Aug 21, 2006

SELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;

View 6 Replies View Related

Column Name As The Result Of A Query?

Jan 11, 2005

Simple example would look like that in MS SQL

SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)

So that the display is

English
a

as we assume that

SELECT language_name FROM language WHERE language_id = 1

returns only English

I have tried to that with a variable but it does not work

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that

LOL

I just tried another way as i was going to post

declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"

and it worked!

Posting anyway so people might answer with a better solution with no variable

Thanks a lot

Mordan

View 1 Replies View Related

Identity Column In Query Result

Jul 20, 2005

I am firing a query like:SELECT TaskName, StartDate FROMTasksWHERE StartDate >= '01-Aug-2003'Now the result comes as:TaskName StartDate--------------------------Task1 01-Aug-2003Task5 10-Oct-2003etc.Now what I want is this:Sl. No. TaskName StartDate----------------------------------1 Task1 01-Aug-20032 Task5 10-Oct-2003How do I get the Sl. No. field (it does not exist in the table).Can I do it?-surajit

View 11 Replies View Related

Store Query Result To Column

Nov 23, 2007

Hi,

I'm wondering is it possible to store query result to column directly?
for example I have 2 tables

Cache
----------
Cache_ID (int)
Cache_content (Text)
cache_created (Datetime)



Orders
-------------
Order_ID
Order_amount
Order_Date
Order_Delivered


and the query is
select * from order where order_date = '2007-11-23' and 'Order_delivered='N'

I want the result to be stored in cache table either csv or xml
I'm using sql 2000 server.

any idea?

View 7 Replies View Related

Inserting Serial No Column In Result Of A Query

Apr 21, 2005

Hello Friends
                     My problem is
                     Suppose Query is - Select * from tbl_Employee
                     TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST
                     I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.
                     Serial   Name Post
                        1      XYZ   QER
                        2      SDF   OPO
                        3      WER   IPO
            If any body knows please post the solution its urgent.

View 11 Replies View Related

Adding Autonumber Column To A Query Result

Apr 23, 2004

I have a query that returns a list of results from the database and insert the results into a datatable. Is there any way to add a column to the query result that has autonumbers in it. for example the first record would have 1 the next 2, the next 3, and so on?

View 4 Replies View Related

Assign Return Of A Query As Column Name

Feb 4, 2014

Is there a way to achieve that? I have a query with a subquery and I want to assign the return from the query as a column name in the subquery.

something like that where WorkDay is the return from the main query

as Convert(Varchar(10),WorkDay,110)

View 2 Replies View Related

Looping Through Query Result Column And PRINT To Log File....

May 24, 2007

i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!

Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....

Thanks in advance

View 1 Replies View Related

Derived Column Based On Result Of Oracle Query

Sep 18, 2007

Hi,

I need to create a derived column for each row in a SQL dataset.

This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).

One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?


Thanks

View 5 Replies View Related

Return The Results Of A Select Query In A Column Of Another Select Query.

Feb 8, 2008

Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?

View 9 Replies View Related

SQL Server 2012 :: How To Subtract A Column From A Query Result And Add Difference To Another

Mar 20, 2015

I have a query which provides the below result set:

1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013

I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.

1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80

[code]...

View 5 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

Re-display Result Set Without Re-running Query In Query Analyzer?

Apr 9, 2006

I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.

View 6 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

Save Query Result At CSV Or TXT File From The Query

Dec 10, 2007

We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.

Thanks
Amit K Patel

View 7 Replies View Related

How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).

Jan 28, 2008

Hi, 
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. 
I have three tables namely – Forums,Topics and Threads  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC 
Whose result set is as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  
On modifying the query to:- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC 
I get the result set as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
4
Swings
p@q.com
2008-01-27 15:12:51.000

I want the result set as follows:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. 
The scripts for creating the tables and inserting test data is as follows in an already created database:- 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO 
CREATE TABLE [dbo].[Forums] (
            [forumid] [int] IDENTITY (1, 1) NOT NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Threads] (
            [threadid] [int] IDENTITY (1, 1) NOT NULL ,
            [topicid] [int] NOT NULL ,
            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [replies] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Topics] (
            [topicid] [int] IDENTITY (1, 1) NOT NULL ,
            [forumid] [int] NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Forums] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [forumid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Threads] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [threadid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [topicid]
            )  ON [PRIMARY]
GO  
ALTER TABLE [dbo].[Threads] ADD
             FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO 
ALTER TABLE [dbo].[Topics] ADD
             FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO  
------------------------------------------------------ 
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum'); 
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion'); 
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
 

View 7 Replies View Related

Getting Result Of Query Inside Another Query

May 22, 2007

Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.

View 4 Replies View Related

How To Make The SSMSE To Return Whole Records Without Any Close Query Form And Re-create Query Form Operation?

Dec 25, 2007

Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:

Select * from Table1

It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.

Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?

Thanks a lot!

And Merry X'max!!!

View 4 Replies View Related

Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results

Sep 22, 2015

-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo

[code]....

This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

View 2 Replies View Related

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?

Nov 15, 2007

do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like

SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)

View 1 Replies View Related

Naming Query Column Name Of Resulting Table Name From Query

Oct 23, 2014

I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..

Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.

I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..

View 14 Replies View Related

Can I Do An Sql Query Then Use The Result Of That In Another Query...

Aug 29, 2007

for instance i need to find value of storyId from one table then make sure i do not pull that record from another table. is this possible

View 6 Replies View Related

How To Use Result Of One Query In Another Query?

May 15, 2007

Hello,



I have a Data Flow.



I have 2 databases.



I need



for example



1. Get a user name from Database 1 (Table 1)

2. Get a user name ID from Database 2 based on the user name from above. (Table 1) (it will be query using INNER JOIN)

3. Use the user name ID result in my Data Flow.



What control can I use for it and how?

Should I use Lookup? If yes - how can I use the result from the first query?



So, In the end I should have User name ID for each user name in my new table which will be in Database 2.



Thanks.

View 3 Replies View Related

Return Subquery Result For Only First Row In Result

Apr 7, 2015

I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.

declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday

select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],

[code]....

In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.

View 6 Replies View Related

How To Get Sum() Result In Query?

Mar 15, 2008

mail_delivery_master----------------------ml_id       ml_from_mail_id                          ml_to_mail_id                                ml_user_id      ml_subject                     ml_content             ml_file_attached  ml_no_of_file_attached ----------- ---------------------------------------- -------------- ------------ --------------------------------------------------------------------------------------------------------------------------------1           aa                                       bb                                                cc              dd                             ee                                                                                                                                                                                                                                                                            2           joe@hotmail.com                          tt@gmail.com,                                   JOHNYJP         Forum answer                   somebody answer          0                1                                                                                                                                                                                                                                                                      3           joe@hotmail.com                          mailme@hotmail.com,                             JOHNYJP         Forum answer                   somebody answer          -1               0                                                                                                                                                                                                                                                              4           joe@hotmail.com                          mailme@hotmail.com,janani@ajsquare.net,         JOHNYJP         Forum answer                   somebody answer           0                0                                                                                                                                                                                                                                                             5           joe@hotmail.com                          janani@ajsquare.net,                            JOHNYJP         Forum answer                   somebody answer          0                0                                                                                                                                                                                                                                                               6           joe@hotmail.com                          dff@gg.com,                                     JOHNYJP         Forum answer                   somebody answer           0                0                                                                                                                                                                                                                                                           7           joe@hotmail.com                          tt@gmail.com,janani@ajsquare.net,               JOHNYJP         Forum answer                   somebody answer          0                0                                                                                                                                                                                                                                                             8           Durai@gamial.com                         yogesh@gamail.com                               durai           test                           test                      0                0                                                                                                                                                                                                                                                                9           janani@ajsquare.net                      devi@ajsquare.net                               janu            test                           hi                        0                0                                                                                                                                                                                                                                                             10          janani@ajsquare.net                      devi@ajsquare.net                               JANANI          test                           Miss u                   -1               2                mail_attachementsml_id       ml_file_name                 ml_file_format       ml_file_size      ml_file_select_from                                                                                  ----------- ---------------------------- -------------- ------------ ------------------------------------------ 1           chocolate                       .jpg                         114528            F: empchocolate.pjg                                                                                2           Tiger Caspian Blue.jpg          .jpg                     1114407      F:imageTiger Caspian Blue.jpg                                                                      3           Autumn.jpg                      .jpg                         66287        F:imageAutumn.jpg                                                                                  4           Ascent.jpg                      .jpg                           63244        F:imageAscent.jpg                                                                                  5           Azul.jpg                        .jpg                              61365        F:imageAzul.jpg                                                                                    6           daisy.jpg                       .jpg                             8197         F:imagedaisy.jpg                                                                                   7           Stonehenge.jpg                  .jpg                       59600        F:imageStonehenge.jpg                                                                              i want the result :ml_id , ml_from_mail_id, ml_to_mail_id  from mail_delivery_master   ,,,, and sum(ml_file_size) for that mail from mail_attachements give me the code solution regards samuel chandradoss      

View 2 Replies View Related







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