Hi,
I need to select all database names that are not contained in table tsm.dbo.DB_NOT_to_Backup, but are present in master..sysdatabases.
Can this be done using not equal joins?
I tried the query below, but it got me nowehere:
select a.name
from master..sysdatabases a inner join tsm.dbo.DB_NOT_to_Backup c on a.name<>c.DBname
order by a.name
G
Dear Friends,
In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
Please advice. Thanks.
warm regards
Rakin
Singapore.
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
Any ideas on the principles involved here.
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
View 3 Replies View RelatedHi,
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
Angel
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results
Table B Contains an Icon, this image is displayed in the results
Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA
UNION ALL
Select title, description, icon as image from tableB
UNION ALL
title, description, ( inner Join SELECT top(1)
from imageTableC where imagetableC.FK = tableC.PK)
as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
Can someone tell me why these do not equal? The rec_day is 10 ?
WHERE (CAST(DATEPART(Q, GETDATE()) * 3 - 2 AS VARCHAR(2)) + '/' + CAST(rec_day AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS VARCHAR(4))) = GETDATE()
on sql server
!= and <> are the same?
cause i cant see != on books online.
hi
does http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584332.html = http://www.wrox.com/WileyCDA/WroxTitle/productCd-076457955X.html ??
and what is the difference ??
and which one of these you advice me to read for beginner ??
thank you
Assume ID is an Integer field, which is faster (or is there any difference at all)?
Code Snippet
select * from <MyTable> where ID < 51
--or
select * from <MyTable> where ID <=50
SELECT website
FROM dbo.wce_contact
WHERE (NOT (Mail1Date IS NULL))
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail13Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus IS NULL OR IDStatus = '')
AND (NOT (Task LIKE '%x%') OR Task IS NULL)
AND (NOT (ExpressEmail IS NULL OR ExpressEmail = ''))
AND (NOT (WebSite IS NULL OR WebSite = ''))
AND (Mail17Date IS NULL OR Mail17Date = '')
AND (Mail18Date IS NULL)
AND (Mail1Date <= '20080421')
AND (NOT (RECORDOWNER = 'lbm') OR RECORDOWNER IS NULL)
How would i get the part in red to say mail1date less than or equal to 42 days back from todays date?
Hi All,
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
I have table 'A' containing Company information including the company zip code. I have table 'B' which is a table of ZipCodes. How can I get a result set of all the Zip Codes that exist in 'A' but not in 'B'?
Thank you
Hi,
I have a stored procedure that returns a string. How do I assign it to a variable?
I have currently
exec myStoredProc '2'
and I want something like
declare @myVar varchar(25)
set myVar = exec myStoredProc '2'
But I can' seem to figure out the syntax for assignment.
Thanks
I'm having a problem with what should be a simple TSQL statement. I have a
table which has a datetime field updated. After the update if I type
select * from patient_medication where rec_status_date = '11/10/2000'
it returns the rows I want.(All the dates have a time of 00:00:00.000.
But if I type
select * from patient_medication where rec_status_date <> '11/10/2000'
or select * from patient_medication where rec_status_date != '11/10/2000'
The rows that have a value are returned, but none of the null values
are returned. Will nulls not work with this comparison?
Thanks
Trying to do a simple query in SQL Server 7.0 to return data where not equal and get a cartesian.
Select person.person_id
from person, psnl
where person.person_id <> prsnl.person_id
Could this be an error with a SQL server setting?
Sorry if this is a total n00bie question but...
I have table A and table B
I want a field in table B to be equl to the primary key in table A, and i'm not sure how to do that.
Thanks
Hi there,
I am trying to update one table on server A/database A with new records from a server B/database B.
Both databases use the same collation. The key-fields of both table do use the same collation, but not the server.
Help: I was thinking, when both databases and the fields inside the tables do both use the same collation, then there will not upcome any conflict ?
Do I have to adapt the database collation, too ? This would'nt be nice as I am retreiving data from different servers with different server collations...
Anybody else got the same problem ?
Thx in advance
dajm
The SUBSTRING function returns @domain.com. I don't want any results to be displayed from my sql statement if it equals @myemail.com. I have other cases too that are not relevant to my question. I'm getting an error that says invalid syntax near <
SELECT DISTINCT CASE CUST_EMLAD_TX
WHEN SUBSTRING(CUST_EMLAD_TX, CHARINDEX('@',CUST_EMLAD_TX), LEN(CUST_EMLAD_TX)) <> '@myemail.com' THEN CUST_EMLAD_TX
END
FROM ...
Good day!
I have the qry, which is suppose to assigned records to active user (almost 15 users) equal, but it doesn’t- sometime it assigned more to some users and less to others. How could I modify my qry to make sure it assigns the records equals to each user? Please, see the qry below. I will appreciate any help
Thk
UPDATE
TBLFRAUDFINDER
SET
DATE_ASSIGNED=GETDATE(),
FRAUDANALYSTASSIGNED=@FRAUDANALYST
WHERE
FRAUDID=@FRAUDID OR
(FRAUDANALYSTASSIGNED IS NULL AND
((INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER) OR
(CUST_NM=@CUST_NM) OR
(ALERT_IDENTIFIER=@ALERT_IDENTIFIER) OR
(ACCT IN (SELECT
ACCT
FROM
TBLFRAUDFINDER
WHERE
INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER OR
CUST_NM=@CUST_NM OR
ALERT_IDENTIFIER=@ALERT_IDENTIFIER))
I have two procedures (old and new) that return queries and have to check that for every possible parameter they return the same data. Any hints?
Thanks, Pablo
comparing a value, my sql statement fails at the last bit i.e. )<>0I'm trying to compare the last three characters i.e. not equal to 0? error message
Msg 102, Level 15, State 1, Procedure StoredProcedure, Line 137
Incorrect syntax near '<'.
Basically I'm trying to validate the field name Code to have a letter followed by 3 numbers..
if LEFT (@Code, 1) NOT LIKE '[a-Z]%' OR ISNUMERIC (RIGHT(@Code,3)<> 0
Here is my stored proc: InsertAccount
Create PROCEDURE dbo.InsertAccount
@AccRisAsse bit,
@AccHldBll bit,
@Acctaxexm bit
AS
insert into TblAccount(AccRisAsse,AccHldBll,Acctaxexm)
values(@AccRisAsse,@AccHldBll,@Acctaxexm)
return scope_identity()
GO
And here is the code in .Net to run the proc and get back the scope_identity:
I delcare my sqlCommand sqlcmdInsNeAcc and connection from
system.data.Sqlclient, I open my connection. all goes well then I do:
sqlcmdInsNeAcc.type=commandtype.storedprocedure
sqlcmdInsNeAcc.CommandText = "InsertAccount"
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber",
SqlDbType.BigInt))
pm.Direction = ParameterDirection.ReturnValue
Dim kAccountNumber as integer
kAccountNumber = sqlcmdInsNeAcc.Parameters("@AccountNumber").Value
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccRisAsse",
SqlDbType.Bit))
pm.Value = myvalue1
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccHldBll",
SqlDbType.Bit))
pm.Value = myvalue2
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@Acctaxexm",
SqlDbType.Bit))
pm.Value = myvalue3
sqlcmdInsNeAcc.executenonquery
then when I try to display the value of my returned scope identity from the variable: kAccountNumber
ALL I GET IS ZERO although my row is created in the table with the right Account number
Thanks
I
have a if statement and a table with 2 atributes. They are description
and date. I will check if there is in the table some date like
01-01-2008.String test "01-01-2008";if (test.Equals(here I will a SELECT query like SELECT date FROM appointmentTable)){
textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date
Hi,
I'm using SQL SERVER 7.0.
I'm driving myself crazy on this one. I have 2 tables that look like this:
T1T2
C1C2C3C1C2C3
JOE1OTTAWAJOE1TORONTO
MARC1OTTAWAMARC4OTTAWA
GAVINNULLHALIFAXGAVIN3HALIFAX
DARRINNULLHALIFAXDARRIN3HALIFAX
DENISENULLPITTSBURGHDENISENULLPITTSBURGH
LOUISENULLRUSSELLLOUISE2RUSSELL
ANDREA3STITTSVILLEANDREANULLSTITTSVILLE
MARIO66PITTSBURGHGEORGE6KINCARDINE
LARRY6KINCARDINE
MARIO66PITTSBURGH
What I need to do is get all of the records from T2 that don't match EXACTLY to a record in T1. So I figured a LEFT OUTER JOIN should work:
SELECTT2.*
FROMT2 LEFT OUTER JOIN T1
ONT2.C1 = T1.C1
ANDT2.C2 = T1.C2
ANDT2.C3 = T1.C3
WHERET1.C1 IS NULL
But this statement returns the DENISE record when I do this (which has an EXACT match).
So, my thoughts took me to the NULL values in T1.C2 and T2.C2 for this record and I thought that, perhaps, the NULL values aren't being recognized as being equal (as they are UNKNOWN).
So I started digging around and found SET ANSI_NULLS OFF. I tried it but with no luck. Can you offer any insight on this one? What can I do to have NULL values recognized as being equal?
This is the result set that I would like to have returned in this example:
JOE1TORONTO
MARC4OTTAWA
GAVIN3HALIFAX
DARRIN3HALIFAX
LOUISE2RUSSELL
ANDREANULLSTITTSVILLE
GEORGE6KINCARDINE
LARRY6KINCARDINE
I've included a script to build and populate the tables below.
Any help on this will be greatly appreciated.
Thanks in advance,
Darrin
------------------------------------------------
IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T1'
)
DROP TABLE T1
GO
IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T2'
)
DROP TABLE T2
GO
CREATE TABLE [dbo].[T1] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T2] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO
INSERT T1 VALUES('JOE', '1', 'OTTAWA')
INSERT T1 VALUES('MARC', '1', 'OTTAWA')
INSERT T1 VALUES('GAVIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DARRIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T1 VALUES('LOUISE', NULL, 'RUSSELL')
INSERT T1 VALUES('ANDREA', '3', 'STITTSVILLE')
INSERT T1 VALUES('MARIO', '66', 'PITTSBURGH')
GO
INSERT T2 VALUES('JOE', '1', 'TORONTO')
INSERT T2 VALUES('MARC', '4', 'OTTAWA')
INSERT T2 VALUES('GAVIN', '3', 'HALIFAX')
INSERT T2 VALUES('DARRIN', '3', 'HALIFAX')
INSERT T2 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T2 VALUES('LOUISE', '2', 'RUSSELL')
INSERT T2 VALUES('ANDREA', NULL, 'STITTSVILLE')
INSERT T2 VALUES('GEORGE', NULL, 'KINCARDINE')
INSERT T2 VALUES('LARRY', NULL, 'KINCARDINE')
INSERT T2 VALUES('MARIO', '66', 'PITTSBURGH')
GO
I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.
p.item=i.item where
1001099548=1001099550
84162359=84198545
84532300=84532293
47547523=47547951
305545A3=87433653
87444977=87444975
left side coming from p.item = right side coming from c.item.
Here are my statements.
--#1 OPEN PO's
SELECT p.item
,(p.qty_ordered-p.qty_received) as POQtyRemaining
,i.item
,i.qty_on_hand
,p.po_num
[Code] ....
I have a case statement I need to refer to several times in my select statement and it's quite long. I would like to just refer to if by name. I tried to create a variable for the Case statement (see below) but I get an error that says, "
Sub query returned more than 1 value. This is not permitted when the sub query follows =, !=, <, <= , >, >= or when the sub query is used as an expression."
USE GP05
GO
DECLARE @EmpID nvarchar (6)
SET @EmpID = (SELECT CASE WHEN NOT(dbo.BE010130.EMPID_I IS NULL) THEN dbo.BE010130.EMPID_I
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND dbo.UPR00600.EMPLOYID IS NULL THEN dbo.UPR00500.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND dbo.UPR00500.EMPLOYID IS NULL AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00600.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00500.EMPLOYID
END FROM dbo.BE010130 FULL OUTER JOIN dbo.UPR00500 ON dbo.BE010130.EMPID_I = dbo.UPR00500.EMPLOYID AND dbo.BE010130.BENEFIT = dbo.UPR00500.DEDUCTON
FULL OUTER JOIN dbo.UPR00600 ON dbo.BE010130.BENEFIT = dbo.UPR00600.BENEFIT AND dbo.BE010130.EMPID_I = dbo.UPR00600.EMPLOYID)
SELECT @EmpID
I can get around this by simply retyping the case statement when I refer to it in the where clause, but I would prefer not to do this. Can someone point a newbie in the right direction? I have 5 case statements in this query and it starts to look pretty ugly when you have to retype them multiple times.
Thanks!
I have created and deployed a report model by using BI development Studio. When trying to build a tabular report with Report Buider I have to use the "not equal" condition as a filter. The query does not give the expected results.
let's suppose the values for a filed I want to filter on are: 1,2,3,4,5
If i run the filter as: field NotEqual to 6 then the results are not correct
But if instead of using the NotEqual condition I use
field equals to 1
or
field equals to 2
or
field equals to 3
or
field equals to 4
or
field equals to 5
Then it gives the correct results. Ok I now it sounds weird but anyone with similar experience using ther NotEqual condition in a filter?
Thanks,
Alberto
Hi group,
This morning we had an issue with a simple join between 2 tables e.g. table1 and table2.
Both tables have 1 column called 'fielda' with datatype varchar.
Let's assume the following values:
Table1:
hello
Hello
Table2:
hello
This statement joins the tables:
select a.fielda
from table1 a inner join table2 b on a.fielda = b.fielda
It returns:
hello
Hello
????????????????????????????????????????????????????????
What the $#@#$
Why doesn't it return 1 value:
hello
Any ideas?
The outcome is exactly what we want but I would expected to have to use the following:
select a.fielda
from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)
What if I wanted to return all EXACT matches? Then what? Change collation?
Hi, I´m having a critical problem.
I have two databases: persons and cars
In database persons I have a table
named persons_class1 as follows:
person_id char(13) not null [primary key]
name varchar(20) not null
In database cars I have a table
named cars_blue as follows:
car_id char(13) not null [primary key]
model varchar(20) not null
person_id char(13) not null
In my program the initial catalog is: cars
and I´m executing the following SQL:
select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id=persons.dbo.persons_class1.person_id
and the following error occurs:
Cannot resolve collation conflict for equal operation
I also tried like instead of = and the error is:
Cannot resolve collation conflict for like operation
what can I do?
Thanks in advance
Roland
Hey,
I want a search of name ='Jose' to find both Jose and José, but with
SELECT * FROM Users WHERE name LIKE '%Jose%'
i only get Jose, in "Inside SQL SQL" an article shows this:
If you want a search of name ='Jose' to find both Jose and José, you should choose accent insensitivity. Such a sort order defines all E-like characters as equal:
E=e=è=É=é=ê=ë
but how shut i do this ???
MSSQL 2000 SP 4
Server Collation: Latin1_General_CI_AS
Windows 2003 Server Standard
Thanks for Help !!!
Anyone bang into that?
RESTORED a 7.0 Datbase CP 52 to a 2k box
Built a "Privacy" db from scratch on 2k box
Looked at some table scripts and got
OHM - sql 7
SQL_Latin1_General_CP1_CI_AS NULL
Privacy - sql 2k
Latin1_General_CI_AS NULL
[
Ran this and got the error...any help?
select * from privacy..privacy_column p inner join ohm.information_schema.tables t ON p.table_name = t.table_name
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
Any ideas?
Hi, I´m having a critical problem.
I have two databases: persons and cars
In database persons I have a table
named persons_class1 as follows:
person_id char(13) not null [primary key]
name varchar(20) not null
In database cars I have a table
named cars_blue as follows:
car_id char(13) not null [primary key]
model varchar(20) not null
person_id char(13) not null
In my program the initial catalog is: cars
and I´m executing the following SQL:
select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id=persons.dbo.persons_class1.per son_id
and the following error occurs:
Cannot resolve collation conflict for equal operation
I also tried like instead of = and the error is:
Cannot resolve collation conflict for like operation
what can I do?
Thanks in advance
Roland