Transact SQL :: Query 2 Tables Using Where Exist
Jun 2, 2015
I need to show maxInspectionDate in my result but I can't figure out how to do it.
SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description,
E.AssetType, E.WorkingLimits
FROM Equipment E
WHERE EXISTS
(SELECT t.equipmentID, r.maxInspectionDate
FROM (
SELECT equipmentID, MAX(nextInspectionDate) as maxInspectionDate
FROM equipmentInspection
GROUP BY equipmentID
) r
INNER JOIN equipmentInspection t
ON t.equipmentID = r.equipmentID AND t.nextInspectionDate = r.maxInspectionDate)
View 9 Replies
ADVERTISEMENT
Aug 19, 2015
I need to get all distinct values from 10 different tables that exist in the field [favoritesport] And each table holds close to 50K records so I am looking at 500,000 records to get distinct values for. Would the fastest, less intrusive way of achieving this be to just create a UNION ALL so run
Select Distinct([favoritesport]) from table1
Union
Select Distinct([favoritesport]) from table2
Union
Select Distinct([favoritesport]) from table3
etc etc etc
View 2 Replies
View Related
Apr 27, 2015
Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
View 8 Replies
View Related
Aug 24, 2015
select count(cars.carid)
from
Cars left
join
RentalOrders
on
cars.CarID=RentalOrders.CarRef
where carid not
in(selectRentalOrders.CarRef
from
RentalOrders)
when I wrote this above-query for sofiacarrental_v2.2 it shows 30 in the result but when I changed it this query to that:
select count(cars.carid)
from
Cars left
join
RentalOrders
on
cars.CarID=RentalOrders.CarRef
where not exists
(select
RentalOrders.CarRef
from
RentalOrders)
I replaced not in with not exists it showed 0 in the result.there is any point in term of using them or I made a mistake in the second query?
View 2 Replies
View Related
Sep 27, 2011
i need to write a query that insert in A table if and only if the row does not exist already in the table (the source is a select statement).
i tried the following:
insert into [A]
select * from [B]
where Not EXISTS (select * from [A])
table [A] is still empty, but it does not insert any thing! .. what i know is that the EXIST checks if the selected row exists in the subquery, ain't ?
View 9 Replies
View Related
Sep 16, 2015
We have a maintenance plan running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Here is the script that we are running for rebuild or re-org.
/*
Script to handle index maintenance
Tuning constants are set in-line current values are;
SET @MinFragmentation
SET @MaxFragmentation
SET @TrivialPageCount
[code]....
View 19 Replies
View Related
Sep 16, 2015
In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).
View 4 Replies
View Related
Mar 14, 2008
When I am initializing a transactional replication from SQL 2005 to Oracle 10g, SQL tries to drop tables that don't exist. The properties option for the articles specifically states "if the name is in use:". The name is not in use, yet SQL still tries to drop non-existent tables, which causes the replication to halt. Anyone seen this before, or have any ideas what to do about it?
Thanks!
View 10 Replies
View Related
Dec 11, 1998
I have two tables that will not drop in a Database. I'm running SQL 6.5 on a Compaq Proliant.
Symptoms: Both tables show up in sysobjects, sysindexes, and syscolumns. Both allow SELECT, sp_rename, and truncate, but just hang ISQL or the Enterprise Manager when I try to drop them. I ran a DBCC CheckTable on them with no errors reported. Any ideas??
View 2 Replies
View Related
Nov 23, 2005
Hi,
I want to get the results of a query between 2 tables that exist in the same server but in 2 different databases. I want this query to be executed in a stored procedure (use the reults in a cursor), or if it's possible to be used in an ado dataset or a simple dataset in Delphi. Can this be done also if the two datases (MSSQL) exist in different servers??
Can anyone show an example of how to accomplish these tasks??
Best Regards,
Manolis Perrakis
View 9 Replies
View Related
Aug 30, 2006
I have two SPs, call them Daily and Weekly. Weekly will always callDaily, but Daily can run on its own. I currently use a global temptable because certain things I do with it won't work with a local temptable or table variable.I have been trying to get code so that if the table already exists, itjust keeps going and uses it, but creates the temp table if it doesn'texist. Unfortunately, no matter how I try to do it, it always attemptsto create it, raising an error and breaking the code.create table ##load_file_log (id int identity(1,1),contentsvarchar(1000))insert into ##load_file_log (contents) values ('test record')IF object_id('tempdb..##load_file_log') IS not NULLprint 'exists'ELSEcreate table ##load_file_log (id int identity(1,1),contentsvarchar(1000))select * from ##load_file_logdrop table ##load_file_logIf I change it to IS NULL, the same error occurs (Server: Msg 2714,Level 16, State 1, Line 7There is already an object named '##load_file_log' in the database.)I have found one way to do it, but it seems a bit...clunky.IF object_id('tempdb..##load_file_log') IS NULLexec ('create table ##load_file_log (id int identity(1,1),contentsvarchar(1000))')I'll use that for now, but is there something I missed?Thanks.
View 4 Replies
View Related
Sep 28, 2007
Hi! Is there a way to delete a record from multiple tables at the same time? Thanks for the help!
View 6 Replies
View Related
Mar 31, 2008
Hello.
I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server.
Could anyone help me construct this. Im pretty novice at SQL.
Thanks.
View 7 Replies
View Related
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
Mar 24, 2008
What SQL statement can i run to return a list of new tables or columns that exist in the Sales Database on Server A, which do not exist in the Sales Database in Server B.
Server A and Server B are linked servers.
I am using SQL Server 2005. Thanks.
View 1 Replies
View Related
Aug 13, 2007
Hi all:
for example: testDataContext db=new testDataContext();var res=db.tables.single(p=>p.columnName=="hi");
if there is a record in the database, it works well, but if there isn't , it will throw an exception, then , How could I know the record exists or not ? I don't think exception is a resonable way. and in my opinion, there should be--------even must be ------a resonable way , to evaluate the query result to a bool variable, then program could judge the bool variable like :
if(bExist) show("yes, I find it");else show("sorry, the record doesn't exist in the database");
I can't imagine I got the bool variable via exception...
thanks to all..
View 1 Replies
View Related
Dec 27, 2001
Hello,
I have a dynamic query in the stored procedure, and the code looks something like this:
SET @section_test = 'SELECT sectioncode FROM ' + @tablename + ' WHERE sectioncode = "' + @condition + '"'
EXEC (@section_test)
What I need to do is try to check if the query returns any values using EXISTS (possibly), but at the same time I don't want to return the results of that dynamic query's select statement in my stored procedure. Is it possible?
Thanks.
Regards,
Katya
View 1 Replies
View Related
Dec 3, 2013
I have data as below: select ID,code,go,back from tableA
ID code go back
2 C US UK
2 A UK US
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
I only want to pull out whole ID group which code Z exist. I tried as below:
select ID,code,go,back from tableA A
where exists
( select ID,go,back
from tableA B with (nolock)
where A.ID = B.ID
and A.code='Z')
But it only appear:
ID code go back
1 Z UK US
How can I appear with the group as well?
ID code go back
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
View 3 Replies
View Related
Jun 2, 2004
I have two tables:
1. tblProducts
columns: ProductID, ProductName
2. tblProductCodes
columns: ProductID, CustomerID, ProductCode
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Any help is highly appreciated.
View 4 Replies
View Related
Nov 24, 2005
Hello all,
I have a branch a data, now i need to search through a database to check whther it is exist in that database or not, any syggestion?
Example:
Now i have data 123, 234, 345. Let say data 123 and 234 is exist in that database, but data 345 is not exist in that database.
What SQL am i suitable to use to get those result exist(123, 234) and those result not exist (345)?
The database structure is someting like this:
ID NAME O_NAME
-- ----- --------
1 120 123
2 234 234
3 345 345
1) The data consider 'exist' if it exist in column NAME or column O_NAME (either one).
2) The data consider 'not exist' if it not exist in neither NAME column nor O_NAME column
After the SQL query, someting is expected:
Exist:
ID NAME O_NAME
-- ----- --------
1 120 123
2 234 234
Not Exist:
ID NAME O_NAME
-- ----- --------
3 345 345
I'll appreciate if anyone can provide me a solution..Thanks!
View 3 Replies
View Related
Jul 30, 2015
For 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?
View 13 Replies
View Related
Oct 21, 2015
In our production database we are looking top 10 columns in all the tables, for this using the below script, output was showing only one table how we get the all the tables top 10 ...
select top 10 * from ProductIdList
View 6 Replies
View Related
Jul 6, 2015
I have 4 table
TBLA
id
1
2
3
----
TBLB
ID COUNT
1 2
2 2
3 2
----
TBLC
ID COUNT
1 2
2 2
----
TBLD
ID COUNT
1 3
2 3
NEED TO GET OUT PUT LIKE BELOW
ID aCOUNT bCOUNT cCOUNT dCOUNT
1 2
2 3
2 2
2 2
3 2
View 2 Replies
View Related
Jul 10, 2015
I 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.
View 6 Replies
View Related
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
Jul 22, 2015
I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.
View 10 Replies
View Related
Oct 5, 2015
We have two tables.
Table1:
Servers | Numbers
------------
Server1 | 1
Server1 | 2
Server1 | 3
Server2 | 1
Server2 | 2
Server2 | 4
Server3 | 2
Server3 | 5
Server3 | 9
Server3 | 7
.....
Table2:
| Numbers
-----------
NULL | 1
NULL | 2
NULL | 3
I need to select Server1, Server2, Server3 and other servers that does not have correct value in Table2. Results should return server name and number that server does not have like:
Server2 | 3
Server3 | 1
Server3 | 3
Table1 is updated time to time, Table2 - static table. The best would be to avoid loop or cursor. Is that possible to get these results in one query?
View 4 Replies
View Related
Oct 11, 2015
I want to know one small query..
id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did
10 to
I deleted some records now my table have below mentioned rows..
id Name
1 hi
2 how
4 you
6 can
8 not
10 to
I want to know the missing records in my table.
OUTPUT IS. 3,7,9
how can i do that using sql query.
View 11 Replies
View Related
Jun 3, 2015
I have two global temp tables in my stored procedure.Is it possible for me to make the execution of a stored procedure dynamic. Based on a flag parameter value I should get the result set from first temp table and viceversa ex: If my flag is 0.I should be able to get the result set from ##temp1 (select * from ##temp1).If my flag is 1.I should be able to get the result set from ##temp2 (select * from ##temp2).
View 4 Replies
View Related
Jun 29, 2015
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
View 6 Replies
View Related
Oct 26, 2015
I have two temp. tables. I am trying to show the agents how makes the sales and the ones how didnt make sales based on the time that they clock in. One table is called #sales which has only the agents that make sales and other tables is #hours which has both agents that do not make sale. the problem is that I can not get both agents to show on my report. I tried different ways but I could not. #sales table uses (select statement from AmountStats table that stores only the agents who make sale). #hours table uses different tables to store all gents who makes sale and ones that are not making sale.
declare
@start datetime,
@end datetime,
@campaignId uniqueidentifier,
@campaignName varchar(250),
@segment varchar(50)
set @start = '2015-10-07'
set @end = '2015-10-20'
[code]....
how to merge these two columns from #sales and #hours tables without duplication.
View 6 Replies
View Related
Oct 14, 2015
How to set ddl triggers for specific tables, instead of on entire database.
View 2 Replies
View Related
Nov 6, 2015
I have two tbles that have ItemName and their bill amount
a) tblLunch
which shows records like below
Invoice Item Amount
--------------------------------------------
1 COFFEE 1000.00
2 TEA 2000.00
3 ICE CREAM 1000.00
b) tblDinner
which shows records like below
Invoice Item Amount
------------------------------------------------------------
1 COFFEE 1000.00
2 TEA 2000.00
3 PASTA 1000.00
I want to perform a query that should SUM Amount Columns by Grouping the Item from both the tables, so we could get the following result
Item Amount
------------------------------
COFFEE 2000.00
TEA 4000.00
ICE CREAM 1000.00
PASTA 1000.00
View 3 Replies
View Related