Using Results Of First Query In Other Queries
Sep 1, 2004Hi,
I would like to use the result table of the first query in a number of other queries. How do I do this ?
Thanks.
ADVERTISEMENT
Need One Query To Obtain Results I Can Only Get With Two Queries
Jul 23, 2005I'm trying to devise a query for use on SQL Server 2000 that will dowhat was previously done with one query in MS Access. The MS Accessquery was like this:SELECT Count(*) as [Opened],Abs(Sum([Status] Like 'Cancel*')) As [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened)Order By Year(DateOpened), Month(DateOpened)Here were I'm at with SQL Server, TSQLSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Opened]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus not like 'Deleted'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yieldsMonthOpened======================Aug 2004503Sep 2004752Oct 2004828Nov 2004658Dec 2004533Jan 2005736Feb 2005707Mar 2005797Apr 2005412AndSelect Right(Convert(Char (11), Min(DateOpened), 106), 8) as [MonthOpened],Count(Status) as [Cancelled]FROM Detail_Dir_LocVWhere (Detail_Dir_LocV.DateOpened > '2004-8-01') andStatus like 'Cancelled%'Group By Year(DateOpened), Month(DateOpened) Order ByYear(DateOpened), Month(DateOpened)Which yields;MonthCancelled=========================Aug 200478Sep 2004105Oct 2004121Nov 2004106Dec 200475Jan 200582Feb 200571Mar 200594Apr 200533What is desired isMonthOpenedCancelled============================Aug 200450378Sep 2004752105Oct 2004828121Nov 2004658106Dec 200453375Jan 200573682Feb 200570771Mar 200579794Apr 200541233Any assistance would be appreciated.Cheers;Bill
View 3 Replies View RelatedCombining Queries/ Results
May 4, 2005I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table. However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results. What would be the best method to combine searches?At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries. Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)e.g.1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))Combined using my function creates:Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))
When I combine I'm drilling down, so if the first query returns a count of 400 (where the title OR subject contains 'run') and then the second query returns 600 records (where the title OR subject contains 'level') I need to combine so that I'm looking for records where the title contains both keywords 'run' AND 'level' OR else the subject contains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words. I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?
Combine Results From 2 Queries
Nov 9, 2005I'm trying to create a list of orders in my db that has been created correctly (some orders are not dealt with correctly...) An order should go from "open -> assigned" to "assigned -> responded" status.
I got the following query:
select org.name, count(order) AS correct, NULL AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
where
o1.status = 'Open -> Assigned'
and o2.status = 'Assigned -> Responded'
and o1.time_stamp < o2.time_stamp
This gives me a list of all organisations with the correct number of orders in the system...
But now I need to add the total number of tickets they got in the system. So I was thinking about a union with a query without the were constraints
UNION 'with the above query
select org.name, NULL AS correct, count(order) AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
..but that gives me a list like this:
name correct total
org1 324 NULL
org1 NULL 423
How can I combine them, or maybe doing it a better way?
Adding The Results Of 2 Queries
Jun 21, 2007Hi,
I have to queries that return tables with the same names. How do i add these 2 so it returns one table?
Thanks for your help.
Mike
Merge The Results Of Two Queries
Jul 20, 2005Hi all,Here is my problem, I have 3 tables :People-------------IDPeopleFirstnameLastnameCars------------IDPeopleCarnameBoats------------IDPeopleBoatname1 person can have 0 or n car/boatI want to a result set displaying : Firstname, Lastname, NumberOfCars,NumberOfBoatsI have two queries, but i want to merge the results in one. how can i dothis ?This one gives me FIRSTNAME, LASTNAME and CARCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Cars.CarName) AS CARCOUNTFROM dbo.People LEFT OUTER JOINdbo.Cars ON dbo.People.IDPeople = dbo.Cars.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThis one gives me FIRSTNAME, LASTNAME and BOATCOUNT------------------------------------SELECT dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastName,COUNT(dbo.Boats.BoatName) AS BOATCOUNTFROM dbo.People LEFT OUTER JOINdbo.Boats ON dbo.People.IDPeople = dbo.Boats.IDPeopleGROUP BY dbo.People.IDPeople, dbo.People.FirstName, dbo.People.LastNameThanks in advancePhil
View 2 Replies View Related'joining' Results Of 2 Queries
Jul 20, 2005Does anyone know how I can 'join' the results ofone SQL query to the bottom of another?Eg. I have two queries:1. SELECT Name, Surname FROM People WHERE Surname = SmithNAME SURNAMEAdam SmithJohn SmithMichael SmithSteve Smith2. SELECT Name, Surname FROM People WHERE Surname = JonesNAME SURNAMEBob JonesLarry JonesTom JonesWhat I want to produce is:NAME SURNAMEAdam SmithJohn SmithMichael SmithSteve SmithBob JonesLarry JonesTom JonesHowever, if I use UNION like this:SELECT Name, Surname FROM People WHERE Surname = SmithUNIONSELECT Name, Surname FROM People WHERE Surname = Jonesit mixes up all the results:NAME SURNAMEAdam SmithBob JonesJohn SmithLarry JonesMichael SmithSteve SmithTom Jones(I guess it's sorting by the first field, NAME).Is there a way to stop it sorting the results, so that itjust tacks the second query results to the bottom of thefirst query results?(I realise I could use "ORDER BY Surname" to get the same resultin this simple example, but for the more complicated queriesI want to use it won't work).Thanks for any help,Matt.
View 3 Replies View RelatedMDX Queries Return Different Results
May 29, 2008Hi,
I am new to MDX and I have created a query listed below, this returns the correct information from the cube. However when I split the query into a CREATE SET and Query the data returned is wrong. I need to include the set creation in the cube but this returns the wrong information. I thought that information returned by these two queries would be indentical can anyone explain please.
Thanks David
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0) on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
----------------------------------------------------------------------------------------------------------------------------------------
create SET [Finance].[LFL Stores List] AS
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0)
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
[LFL Stores List] on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
How To Merge Two Queries' Results?
Nov 12, 2007
hi,
my first query is:
"SELECT TBL_STOK.stok_adi, TBL_STOK.fiyat1 FROM TBL_STOK INNER JOIN" _
TBL_BARKOD ON TBL_STOK.stok_id = TBL_BARKOD.stok_id " _
where TBL_BARKOD.barkod=@barkod"
second query :
"SELECT TBL_STOKDEPO.fiyat1 FROM TBL_BARKOD left outer JOIN TBL_STOKDEPO ON TBL_BARKOD.stok_id = TBL_STOKDEPO.stok_id" _
where TBL_BARKOD.barkod=@barkod and TBL_STOKDEPO.depo_kod=@depokod "
i want to merge these queries' results.first query returns 2 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1)
second query returns 1 column (TBL_STOKDEPO.fiyat1) .but i want a query that returns 3 columns (TBL_STOK.stok_adi, TBL_STOK.fiyat1,TBL_STOKDEPO.fiyat1)
Join Results Of SQL Queries
Jan 25, 2008Hello all,
I have been using T-SQL for a while now although the majority of my work required relativley simple queries.
I just need to know is there a way to JOIN the results of several SELECT queries, maybe through the use of functions??
A reference to any online article would be most helpful.
Cheers,
Sean
Combining Results Of Two Similar Queries Into One Result Set?
Mar 5, 2012Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
Auto-Export Results Of 3 Queries To Excel
Oct 12, 2012I am running a SQL stored procedure which runs 3 queries on 3 different SQL tables. What is my best option to export the results of these 3 queries to excel?
If it matters they are all SELECT queries, and at most will return < 500 rows.
Queries With Different Statements - Show Results In Datagridview
Oct 19, 2015I have made a couple of queries I want to use into a Visual studio project, Now is my problem:
All my queries have different statements. I believe the best is to show the results in a Datagridview
How to view them there? I know how to do it... but I have got about 30 queries
Here is my code so far:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"
[Code] ....
Making A View That Shows The Results Of Several Different Queries.
Dec 21, 2005Hello,I am trying to create a view that shows the followingField1: Sum of Amounts from Table AField2: Count of Amounts from Table AField3: Sum of of Amounts from Table BField4: Count of Amounts from Table B......Field3: Sum of of Amounts from Table HField4: Count of Amounts from Table H......Things are a bit more complex but this is the gist.I am using SQL 2000.I know how to do this pretty easily using a stored procedure. But howcan I do it in a view? A SQL server won't meet my needs in thissituation.I tried OpenQuery ('myserver', 'exec myprocedure') but get the messagethat my server is not configured for data access. I tried the systemstored procedure to set data access to true but nothing seemed tohappen.I also tried Select * from (Select Statement1, select statement2)but got syntax error at the comma between statement1 and statement2.Trying to use select Statement1 as ABC to does not seem to work either.Is there a way to do what I want without making 15 views and then afinal view that shows them all together? I know I could probably dosomething by creating a ton of functions, but it really seems thisshould not be that hard...I am definitely open to any easy suggestions!Thanks,Ryan
View 3 Replies View RelatedWildly Different Results From Queries On The Same Database On Different Servers
Aug 30, 2007Hi,
We have a client who runs SQL Server 2000 queries on one database server and performance is approx. 4 seconds. If the database is backed up, no tables in the query or indexes on these tables are modified (we may run a small script that affects stored procedures, views, etc.) the query can run virtually forever.
The customer is runing a cluster and we are running a stand-alone. Although, the two environments that they run in and have these wildly different results in are the same.
The queries are not worth listing (join a couple of tables and views, select a few columns, put on a few conditions--nothing crazy).
Is this normal behavior for MS SQL Server?
I've personally seen where a database is backed up and query plans and performance are different from one server to another, but we are looking at extreme cases here. In fact, on the second server, the majority of the queries are faster and only a couple run very slowly.
Also, the query optimizer seems to be making poor decisions at this custoemer. For example, two tables will be cross joined (forming over 200 million records) and then table scans ensue. The process in some cases will take a 4 second query to 45 minutes.
To me none of this makes any sense. I've been working with SQL Server since 1997 and have not experienced any type of performance problems or variances of this magnitude. Although this is a 6GB database, SQL Server 7 ran on a terabyte without even blinking, so I wouldn't understand why this would have anything to do with it.
Also hampering our efforts is that we do not have easy access to this SQL Server database to get our hands on it and debug these issues.
Does anyone know of a way to examine these issues in a "system wide" manner to determine what the problems could be since the problems are not specific to the database (i.e. .bak file) but seem to be specific to the server?
They have also had database corruption (an index that wouldn't update) and had to roll back the database. Would that indicate that the MDF/LDF's are unstable? Is there a way to figure out if there is some type of MDF/LDF file structure corruption?
Thanks,
Henry.
Combining Two Queries Producing Unexpected Results
Mar 5, 2008I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.
The following query returns, for each store, the total number of records in the ProductInventory table:
SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
GROUP BY Stores.Name
The following query returns, for each store, the total number of records in the Customers table:
SELECT Stores.Name,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name
I combined the two queries:
SELECT Stores.Name,
COUNT(ProductInventory.ID) AS ProductInventoryItemCount,
COUNT(Customers.ID) AS CustomerCount
FROM Stores
LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID
LEFT JOIN Customers ON Stores.ID = Customers.StoreID
GROUP BY Stores.Name
When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected
What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
SQL 2012 :: Full-Text Queries Returning No Results
Jun 9, 2014So I'm trying out full-text indexing for the first time and, in particular, FileTables in SQL Server 2012. I've followed a Microsoft walkthrough and everything seems to be ok. However, when I query the table using the CONTAINS keyword, I get no results (a regular query to make sure there are records in the table returns the expected number of results).
I'm now trying to troubleshoot, and have been using the FULLTEXTCATALOGPROPERTY function, but I don't understand the results.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatlogName',N'ItemCount'), I get a result of 51. There are 96 documents in the NTFS folder where the documents are stored, and the table has 96 records, so I don't know where 51 is coming from. 55 of the documents are .DOC files, the rest are .PDF, and some (or maybe all) of the PDFs are scanned images of documents, which I don't expect to be indexed, so maybe that explains it. And in another thread in these forums, a poster suggests that the result for this function should be either 0 or 1, with 0 meaning that no documents are pending indexing, but maybe I've misunderstood that.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatalogName',N'UniqueKeyCount'), I get a result of 2. I have got two full-text indexes in this catalog (one on the FileTable, one on a regular table with FT enabled). Is this result therefore expected? Again, reading online seems to suggest that a result of 0 is desirable, but I don't understand why, and if it is I don't understand why my result is 2!
I've now also run SELECT* FROM sys.dm_fts_index_keywords(DB_ID('DatabaseName'), Object_ID('dbo.FileTableName)), which I believe is supposed to list all of the indexed words from the table specified. I get one row returned, as follows:
keyword: 0xFF
display_term: END OF FILE
column_id: 2
document_count: 40
So basically, it's not indexed any words at all. And why is the document count only 40 when there are 96 documents in the folder and table?
Transact SQL :: How To Join Results Of Two Queries By Matching Columns
Aug 10, 2015I have two queries as below;
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;
and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
Integration Services :: Joining Two Select Queries Results In One Row?
Jun 12, 2015I want to get output of below query in single row.
Select 'Name'
Select 'Surname'
Expected output is Name,Surname
Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?
Apr 1, 2007hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedT-SQL (SS2K8) :: Joining Results Of Two Queries Without Creating Temporary Tables?
Nov 16, 2014In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
[code]....
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.
Feb 12, 2008Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
Query Diff Results From Ent Manager Query And Query Analizer
May 28, 2008ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
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....?
Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns
Jul 30, 2015For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
Easy SQL Question. How To Display Query Results In Query Analyzer
Feb 12, 2008When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
Returning Results Based On One Query Into Existing Query
Aug 2, 2012linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
Transact SQL :: Use Query Results As Select Criteria For Another Query
Jul 10, 2015I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
How To Get Values From Queries, And Then Using In Another Query?
Jul 20, 2005HiI have the following tables and stored procedure. I need to pass a value tothe stored procedure and have it use the value in a query. After runningthat query it will return an ID which is then used in an insert statement.At present the values in @MovieId int, @UserID int are left empty (see myoriginal code at the bottom of posting). I think this is due to an issuewith when the sql is executed (?).I get the feeling I should have something like this instead, but not sure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieName@MovieId = exec MovieName2Id @MovieName-- Find UserID from UserEmail@UserEmail = exec Email2UserId @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)Thanks in advance.========MY CODE=============Tables:CREATE TABLE [dbo].[movies] ([movieID] [int] IDENTITY (1, 1) NOT NULL ,[movieName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[movieDateAdded] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[transactions] ([userID] [int] NULL ,[movieID] [int] NULL ,[FrameNumber] [int] NOT NULL ,[transDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[users] ([userID] [int] IDENTITY (1, 1) NOT NULL ,[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[userDateRegistered] [datetime] NULL) ON [PRIMARY]GOStored Procedure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieNameSELECT @MovieId = MovieIdFROM MoviesWHERE MovieName = @MovieName-- Find UserID from UserEmailSELECT @UserID = UserIDFROM UsersWHERE UserEmail = @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 2 Replies View RelatedCombine 2 Queries In To One (joint Query)
May 3, 2007I have these 2 queries that I need to combine into one. What is the best way of doing it? This website is made up of 293 tables so it gets confusing.
(Query 1)
SELECT category_products.category, category_products.product, category_products.position, data.base_price, data.custom_description, models.manufacturer_id, models.custom_search_text, models.name, models.image_location
FROM category_products, data, models
WHERE category_products.category = '36'
AND category_products.product = data.model_id
AND data.model_id = models.id
AND data.active = 'y'
$manufacturer_id=$data["manufacturer_id"];
(Query 2)
SELECT inventory_types.manufacturer_id, inventory_types.default_vendor_id, vendors.id, vendors.name
FROM inventory_types, vendors
WHERE inventory_types.manufacturer_id = '$manufacturer_id'
AND inventory_types.default_vendor_id = vendors.id
Inner Join Between 2 Queries Through Query Designer
Jul 24, 2015I have recently started working on Sql server management studio. I have been using MS access in the past. To link results of 2 queries in MS access I would open the query wizard and it would show me the list of saved queries and then I could join them as regular tables. Im trying to look for this option in management studio. When I open query designer in management studio I am only given the option to add existing tables, how can I add existing queries?
View 2 Replies View RelatedHow To Query Database With Multiple Queries
Jul 20, 2005I have the following fieldname in an Access_Table:Field 1 = Cust_ID (Primary key)Field 2 = DateField 3 = DescriptionField 4 = Inv_NoField 5 = AmountMy SQL Input syntax are:"Select distinct Cust_ID,Date,Description,Inv_No,Amount fromAccess_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date =Main.Date"It failed to generate the result I want,anywayMy "Required" Output is:Cust_ID Date Description Inv_No >30DYS >60DYS8000 21/05/2004 PC RAM 2008000 26/06/2004 Modem 180---------------------------------------------------------------Total: 180 200---------------------------------------------------------------8001 22/04/2004 Cable 508001 23/05/2004 HD 210---------------------------------------------------------------Total: 260---------------------------------------------------------------Grand Total: 180 460can anyone help? ThanksFrom:Cady Steldyn*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedOne Query Killing Performance For All Queries
Jan 15, 2008
We have an issue where a cube hasn't been designed properly - when someone queries it with Excel, it is doing a mega-crossjoin. When anyone else tries to do *anything* on the AS server (connect with management studio, etc.) it just hangs. We have to either track down the person running the query (via the flight recorder), or restart the service. Obviously the correct fix is to change the design of the cube - I plan on doing it asap. But it brings up this important question - is there a setting I can change to allow others to use the box while this is going on? Maybe some thread isolation, or parallelism? I'm just throwing out ideas, as I haven't experienced this part of AS administration yet.
Thanks in advance,
John