Finding Missing Records

I have 18 tables that are all related by the primary key. When I join all
the fields together for reporting, it only shows the records that have all
the data filled in. How can I find which tables don't have a record?
Thanks,
DrewLEFT JOIN
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>I have 18 tables that are all related by the primary key. When I join all
>the fields together for reporting, it only shows the records that have all
>the data filled in. How can I find which tables don't have a record?
> Thanks,
> Drew
>|||Didn't think it would be that easy! Thought I would have to use an EXIST
query for this, and couldn't figure out how to incorporate all the tables
into 1 query...
Thanks,
Drew
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
> LEFT JOIN
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>|||SELECT *
FROM Table1
LEFT JOIN Table2
WHERE Table2.Field1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||OOPS, left out the ON clause:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||>> When I join all the fields together for reporting, it only shows the
Depending on your requirements, you can use OUTER JOINs or correlated
subqueries to retrieve all rows in one table which does not have a matching
value for a joining column in another table.
Anith

finding length of text field

I need to find the length of a text field.The len function doesn't work with
text fields.
Is there an alternative?
Thanks!DATALENGTH()
HP wrote:
> I need to find the length of a text field.The len function doesn't work wi
th
> text fields.
> Is there an alternative?
> Thanks!|||Look in the BOL for DATALENGTH
HTH, jens Suessmeyer.|||HP (HP@.discussions.microsoft.com) writes:
> I need to find the length of a text field.The len function doesn't work
> with text fields.
As Jens and Trey said there is datalength().
In case you are using ntext, beware that datalength() returns the length
in bytes, and includes trailing blanks. For instance:
SELECT len(N'August '), datalength(N'August ')
Returns (6, 18).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Look in the BOL for DATALENGTH.
HTH, Jens Suessmeyer.

Finding duplicate values in fields

Hi everyone..

I have a table with about 40,000 records in it.
I want to find records where all of the values in all of the fields, except for the unique field are equal.

The records in this table are answers to a online survey.

So, I want to find all of the records where people answered the questions exactly the same as someone else.

Any help would be appreciated.

NickieWhat about this:

select list of fields
from yourtable
group by list of fields having count(*)>1

Finding duplicate entries in a "smart" way - by comparing first two words

What is the best way to compare two entries in a single table where
the two fields are "almost" the same?

For example, I would like to write a query that would compare the
first two words in a "company" field. If they are the same, I would
like to output them.

For example, "20th Century" and "20th Century Fox" in the company
field would be the same.

How do I do this? Do I need to use a cursor? Is it as simple as using
"Like?"Here's an example:

CREATE TABLE Sometable (company VARCHAR(20) PRIMARY KEY)

INSERT INTO Sometable VALUES ('20th Century')
INSERT INTO Sometable VALUES ('20th Century Fox')
INSERT INTO Sometable VALUES ('Something Else')

SELECT company
FROM Sometable AS S
JOIN
(SELECT first_two_words
FROM
(SELECT company,
LEFT(company,
CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))
AS first_two_words
FROM Sometable) AS X
GROUP BY first_two_words
HAVING COUNT(*)>1) AS T
ON T.first_two_words =
LEFT(company,CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))

--
David Portas
----
Please reply only to the newsgroup
--|||>> a query that would compare the first two words in a "company"
field [sic]. If they are the same, I would like to output them. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY,
company CHAR(35) NOT NULL,
...);

SELECT F1.foo_key, F2.foo_key
FROM Foobar AS F1, Foobar AS F2
WHERE CHARINDEX(F1.company, F2.company) = 1
AND F1.foo_key < F2.foo_key;

Finding Changed Records

I need to create a table that would be the result set of a comparison
between table a and table b? Table a and b first 2 fields will always be
the same (CustomerName and CustomerNumber). But if the Address1 field
changes in table a, I would like to throw that whole row into my
comparison table. Almost like a Select Into with a sub query that would
include a WHERE TableA.field <> TableB.field. I would need to do this
comparison for about 8 fields. Help appreciated for my syntax is pretty
bad. Thanks.

Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

Assuming that the first two fields are a primary key then you need to join
on the primary keys and difference the other columns:

e.g

INSERT INTO DIFFERENCES ( Col1, Col2 , Col3, Col4, Col5, COl6, Col7, Col8 )
SELECT A.Col1, A.Col2, A.Col3, A.Col4
FROM TableA A JOIN TableB B JOIN A.Col1 = B.Col1AND A.Col2 = B.Col2
WHERE A.COl3 <> B.Col3
OR A.Col4 <> B.Col4
OR A.Col5 <> B.Col5
OR A.Col6 <> B.Col6
OR A.Col7 <> B.Col7
OR A.Col8 <> B.Col8

John

"Steve Bishop" <steveb@.viper.com> wrote in message
news:4008ae65$0$70301$75868355@.news.frii.net...
> I need to create a table that would be the result set of a comparison
> between table a and table b? Table a and b first 2 fields will always be
> the same (CustomerName and CustomerNumber). But if the Address1 field
> changes in table a, I would like to throw that whole row into my
> comparison table. Almost like a Select Into with a sub query that would
> include a WHERE TableA.field <> TableB.field. I would need to do this
> comparison for about 8 fields. Help appreciated for my syntax is pretty
> bad. Thanks.
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Find website from email address

Hi,

I have 2 fields in a database: 'email' and 'website'.

In the majority of cases, the website field is not populated even though the
email address is. In 90% of cases, the website will be 'www.' followed by
whatever is after the '@.' symbol in the email address.

Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?

Thanks!Would anyone be able to help me with the SQL that would take whatever

Quote:

Originally Posted by

is after the '@.' sign in the email address, add it to 'www.' and
populate the 'website' field?

UPDATE yourtable
SET Site = SUBSTRING(yourtable.email, CHARINDEX('@.', yourtable.email) + 1,
LEN(yourtable.email) - CHARINDEX('@.', yourtable.email))

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype:pbsoftsolution|||Would anyone be able to help me with the SQL that would take whatever is

Quote:

Originally Posted by

after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?

Here's one method:

UPDATE dbo.MyTable
SET website =
CASE WHEN CHARINDEX('@.', email) 0 AND CHARINDEX('@.', email) + 1 <
LEN(email) THEN
'www.' + SUBSTRING(email, CHARINDEX('@.', email) + 1, 255)
ELSE
website
END
WHERE
website IS NULL OR
website = ''

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mintyman" <mintyman@.ntlworld.comwrote in message
news:f6gap5$qgb$1$8300dec7@.news.demon.co.uk...

Quote:

Originally Posted by

Hi,
>
I have 2 fields in a database: 'email' and 'website'.
>
In the majority of cases, the website field is not populated even though
the email address is. In 90% of cases, the website will be 'www.' followed
by whatever is after the '@.' symbol in the email address.
>
Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?
>
Thanks!
>

|||Cheers guys! Much appreciated :o)

"Mintyman" <mintyman@.ntlworld.comwrote in message
news:f6gap5$qgb$1$8300dec7@.news.demon.co.uk...

Quote:

Originally Posted by

Hi,
>
I have 2 fields in a database: 'email' and 'website'.
>
In the majority of cases, the website field is not populated even though
the email address is. In 90% of cases, the website will be 'www.' followed
by whatever is after the '@.' symbol in the email address.
>
Would anyone be able to help me with the SQL that would take whatever is
after the '@.' sign in the email address, add it to 'www.' and populate the
'website' field?
>
Thanks!
>

find unique

hey all,
i have a table which i would like to determine what fields make each record
unique? Unfortunately there is not a tableid.
thanks,
rodchar>> i have a table which i would like to determine what fields [sic] make each record [sic]
unique? Unfortunately there is not a tableid [sic]. <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. This is a pain to do after the
fact.
1) Remove redundant dupicate rows by inserting a "SELECT DISTINCT *
"query to a working table. This kindof crappy non-table probably has
accumulated garbage.
2) Pick the columns that should be keys from the data model. The data
model which you probably do not have.
3) Test this subset of columns with
SELECT 'Bad Choice'
FROM Foobar
GROUP BY <<list of columns>>
HAVING COUNT(*) > 1;
4) reduce the number of test columns when you get a winner.|||what's the [sic] mean?
"--CELKO--" wrote:

> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. This is a pain to do after the
> fact.
> 1) Remove redundant dupicate rows by inserting a "SELECT DISTINCT *
> "query to a working table. This kindof crappy non-table probably has
> accumulated garbage.
> 2) Pick the columns that should be keys from the data model. The data
> model which you probably do not have.
> 3) Test this subset of columns with
> SELECT 'Bad Choice'
> FROM Foobar
> GROUP BY <<list of columns>>
> HAVING COUNT(*) > 1;
> 4) reduce the number of test columns when you get a winner.
>|||rodchar wrote:
> what's the [sic] mean?
http://www.freesearch.co.uk/dictionary/sic
David Gugick
Quest Software
www.imceda.com
www.quest.com

Find UNICODE Data

How can you find UNICODE date within char & varchar fields in table.
I am in the midst of a migration and need to evaluate all the fields that
may have €
chinese, french and thus require more char space in the new structure.
This is a matter of great urgency so any help greatly appreciated.
Thanks
marcIf the fields are not a Unicode data type, then there is no Unicode data in
them. If the characters are not supported by the code page of the field
(defined by its by collation), they are lost (will show up as '?'). If they
are supported by the code page, you can search for them like you normally
would, or by using full-text search.
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:699A4648-85DC-4B5F-A876-6B223CD4A5DE@.microsoft.com...
> How can you find UNICODE date within char & varchar fields in table.
> I am in the midst of a migration and need to evaluate all the fields that
> may have ?
> chinese, french and thus require more char space in the new structure.
> This is a matter of great urgency so any help greatly appreciated.
> Thanks
> marc

Find the newest date of 2 date fields

I've 2 date fields clidlp,clidlc in my data base table. How do I find the newest dates between the fields? Thanks for your help!

Hello, you can use a CASE statement in your SQL Query

Eample:
SELECT CASE WHEN clidlp > clidlc THEN clidlp ELSE clidlc END AS NewestDate
FROM [Table]

find the lastest date among many fields

I have seven date fields, and I need to select the lastest date among the
seven fields.
Example
table1
ID, date1, date2, date3, date4,date5, date6, date7
Result should show '10/07/2005'
Thanks,
Culam
CREATE TABLE table1
(
id int,
date1 smalldatetime,
date2 smalldatetime,
date3 smalldatetime,
date4 smalldatetime,
date5 smalldatetime,
date6 smalldatetime,
date7 smalldatetime
)
INSERT INTO table1
(id, date1, date2, date3, date4, date5, date6, date7)
VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
'10/04/2005', '10/06/2005', '10/05/2005')7 date columns in a row looks suspiciously like poor normalization.
Anyway, try the following. Nulls will be ignored.
SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4 UNION ALL
SELECT date5 UNION ALL
SELECT date6 UNION ALL
SELECT date7) AS X)
AS maxdate
FROM Table1
David Portas
SQL Server MVP
--|||Culam,
I'm not a design expert but I think you may need to work on normalizing this
a bit.
You could try something like:
SELECT ID,
(SELECT MAX(DATEVALUE)
FROM
(SELECT DATE1 AS DATEVALUE FROM TABLE1 UNION ALL
SELECT DATE2 FROM TABLE1 UNION ALL
SELECT DATE3 FROM TABLE1 UNION ALL
SELECT DATE4 FROM TABLE1 UNION ALL
SELECT DATE5 FROM TABLE1 UNION ALL
SELECT DATE6 FROM TABLE1 UNION ALL
SELECT DATE7 FROM TABLE1)
AS Z)
FROM TABLE1
or use the MAX function with a temp table.
HTH
Jerry
"culam" <culam@.discussions.microsoft.com> wrote in message
news:49405690-F7F1-4E6A-BCCD-27407C0E35E7@.microsoft.com...
>I have seven date fields, and I need to select the lastest date among the
> seven fields.
> Example
> table1
> ID, date1, date2, date3, date4,date5, date6, date7
> Result should show '10/07/2005'
> Thanks,
> Culam
> CREATE TABLE table1
> (
> id int,
> date1 smalldatetime,
> date2 smalldatetime,
> date3 smalldatetime,
> date4 smalldatetime,
> date5 smalldatetime,
> date6 smalldatetime,
> date7 smalldatetime
> )
> INSERT INTO table1
> (id, date1, date2, date3, date4, date5, date6, date7)
> VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
> '10/04/2005', '10/06/2005', '10/05/2005')|||Try,
CREATE TABLE t1
(
id int,
date1 smalldatetime,
date2 smalldatetime,
date3 smalldatetime,
date4 smalldatetime,
date5 smalldatetime,
date6 smalldatetime,
date7 smalldatetime
)
INSERT INTO t1
(id, date1, date2, date3, date4, date5, date6, date7)
VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
'10/04/2005', '10/06/2005', '10/05/2005')
select [id], max(c1)
from
(
select [id], date1
from t1
union all
select [id], date2
from t1
union all
select [id], date3
from t1
union all
select [id], date4
from t1
union all
select [id], date5
from t1
union all
select [id], date6
from t1
union all
select [id], date7
from t1
) as t2([id], c1)
group by [id]
-- or
select
[id],
max(
case t2.c1
when 1 then date1
when 2 then date2
when 3 then date3
when 4 then date4
when 5 then date5
when 6 then date6
when 7 then date7
end
)
from t1 cross join (select 1 as c1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7)
as t2
group by [id]
drop table t1
go
AMB
"culam" wrote:

> I have seven date fields, and I need to select the lastest date among the
> seven fields.
> Example
> table1
> ID, date1, date2, date3, date4,date5, date6, date7
> Result should show '10/07/2005'
> Thanks,
> Culam
> CREATE TABLE table1
> (
> id int,
> date1 smalldatetime,
> date2 smalldatetime,
> date3 smalldatetime,
> date4 smalldatetime,
> date5 smalldatetime,
> date6 smalldatetime,
> date7 smalldatetime
> )
> INSERT INTO table1
> (id, date1, date2, date3, date4, date5, date6, date7)
> VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
> '10/04/2005', '10/06/2005', '10/05/2005')|||Jerry Spivey wrote:
> SELECT ID,
> (SELECT MAX(DATEVALUE)
> FROM
> (SELECT DATE1 AS DATEVALUE FROM TABLE1 UNION ALL
> SELECT DATE2 FROM TABLE1 UNION ALL
> SELECT DATE3 FROM TABLE1 UNION ALL
> SELECT DATE4 FROM TABLE1 UNION ALL
> SELECT DATE5 FROM TABLE1 UNION ALL
> SELECT DATE6 FROM TABLE1 UNION ALL
> SELECT DATE7 FROM TABLE1)
> AS Z)
> FROM TABLE1
>
Did you try that with more than one row of dates? If Culam wants just a
single maximum date then you may as well remove the outer part of the
query.
David Portas
SQL Server MVP
--|||Was just about to post how your code rocks and mine is would be sluggish
with all of the table scans! ;-) Dropped all of the extra FROM TABLE1.
Thanks for the follow up...always learning from you.
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129057820.822069.139720@.o13g2000cwo.googlegroups.com...
> Jerry Spivey wrote:
> Did you try that with more than one row of dates? If Culam wants just a
> single maximum date then you may as well remove the outer part of the
> query.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
Might want to change it up a bit for multiple rows to something like this:
SELECT TOP 1 X.ID, MAX(X.MAXDATE) AS 'MAX DATE'
FROM
(SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4 UNION ALL
SELECT date5 UNION ALL
SELECT date6 UNION ALL
SELECT date7) AS X)
AS maxdate
FROM Table1) AS X
GROUP BY X.ID
ORDER BY MAX(X.MAXDATE)DESC
Thoughts?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23xMgPgpzFHA.2792@.tk2msftngp13.phx.gbl...
> Was just about to post how your code rocks and mine is would be sluggish
> with all of the table scans! ;-) Dropped all of the extra FROM TABLE1.
> Thanks for the follow up...always learning from you.
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1129057820.822069.139720@.o13g2000cwo.googlegroups.com...
>|||Just my usualy caveats around TOP: Use an ORDER BY that's guaranteed to
be unique or specify TOP WITH TIES. Random results due to ties can be a
lot of hassle.
David Portas
SQL Server MVP
--

find records which are in between 2 dates....

I have a table Hostavailable, fields are
ID, HostID, fromDate, To date

I have to pass 2 dates form front end dt1 and dt2 (program requirement)

i have to get all the hostID available in between 2 dates(dt1 and dt2.)

Quote:

Originally Posted by nileshgarg

I have a table Hostavailable, fields are
ID, HostID, fromDate, To date

I have to pass 2 dates form front end dt1 and dt2 (program requirement)

i have to get all the hostID available in between 2 dates(dt1 and dt2.)

Hi Nilesh,

Welcome to TSDN!!

Clarify one thing, is dt1 and dt2 equal to from date and to date respectively??

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-Ash
Try,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:

> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA
|||Perfect! Thanks.
"Rick Sawtell" wrote:

> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-AshTry,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA|||Perfect! Thanks.
"Rick Sawtell" wrote:
> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> > I understand this might be trivial but;
> > What's a neat query to find if two fields together are unique and can be
> > used as a PK ?
> > Thanks,
> > -Ash
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash

Find if 2 fields together are unique

I understand this might be trivial but;
What's a neat query to find if two fields together are unique and can be
used as a PK ?
Thanks,
-AshTry,
if exists(select 1 from table1 group by col1, col2 having count(*) > 1)
print 'not unique'
else
print 'unique'
AMB
"Ash" wrote:

> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash|||"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash
Try this:
SELECT col_1, col_2, COUNT(*)
FROM tablename
GROUP BY col_1, col_2
HAVING COUNT(*) > 1
Rick Sawtell
MCT, MCSD, MCDBA|||Perfect! Thanks.
"Rick Sawtell" wrote:

> "Ash" <Ash@.discussions.microsoft.com> wrote in message
> news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> Try this:
> SELECT col_1, col_2, COUNT(*)
> FROM tablename
> GROUP BY col_1, col_2
> HAVING COUNT(*) > 1
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||select
ColA
,ColB
,count(*)
from
TestTable
group by
ColA
,ColB
having
count(*) = 1
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Ash" <Ash@.discussions.microsoft.com> wrote in message
news:F4990C25-3DCA-4504-BBB4-835F7263C550@.microsoft.com...
> I understand this might be trivial but;
> What's a neat query to find if two fields together are unique and can be
> used as a PK ?
> Thanks,
> -Ash

Find fields with an index

Hi.
I am trying to find a way to establish whether a field has an index on it or
not. This is relevant because I am writing code to loop through the fields i
n
a table and shrink columns according to the length of the longest item in
them, and you cannot alter a column that has an index on it. This is
terminating the loop whenever I hit an indexed field.
Cheers,
NeilNeil
Something like this
SELECT OBJECT_NAME(id) AS table_name,name,
COUNT(NULLIF(NULLIF(indid,0),255)) AS num_of_indexes
FROM sysindexes
WHERE OBJECTPROPERTY(id,'IsUserTable')=1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id,name, 'IsHypothetical') = 0
GROUP BY id,name
order by table_name
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:7B353578-5048-462C-AD61-51D160999687@.microsoft.com...
> Hi.
> I am trying to find a way to establish whether a field has an index on it
> or
> not. This is relevant because I am writing code to loop through the fields
> in
> a table and shrink columns according to the length of the longest item in
> them, and you cannot alter a column that has an index on it. This is
> terminating the loop whenever I hit an indexed field.
> Cheers,
> Neil
>
>|||Thanks Uri.
Unfortunately, I still need to find the fields that the indexes are on.
Given the slgihtly shoddy index naming protocol we are using, it's ly not
jsut a case of stripping off a substring of the index name.
Is there an easy way to find the keys for the index?
Aha! Just found the sysindexkeys table. Feel a bit daft.
Thanks for your help.
Neil
Cheers,
Neil
"Uri Dimant" wrote:

> Neil
> Something like this
> SELECT OBJECT_NAME(id) AS table_name,name,
> COUNT(NULLIF(NULLIF(indid,0),255)) AS num_of_indexes
> FROM sysindexes
> WHERE OBJECTPROPERTY(id,'IsUserTable')=1
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
> INDEXPROPERTY(id,name, 'IsHypothetical') = 0
> GROUP BY id,name
> order by table_name
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:7B353578-5048-462C-AD61-51D160999687@.microsoft.com...
>
>|||Hi Jones
Probably, this can help you.
select object_name(sysindexes.id),
index_col(object_name(sysindexes.id),indid,1)
from sysindexes
INNER JOIN sysobjects on sysobjects.id = sysindexes.id
where
sysobjects.xtype in ('u','v') and
sysindexes.status = 0 and
index_col(object_name(sysindexes.id),indid,1) IS NOT NULL
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NeilDJones" wrote:
> Thanks Uri.
> Unfortunately, I still need to find the fields that the indexes are on.
> Given the slgihtly shoddy index naming protocol we are using, it's ly n
ot
> jsut a case of stripping off a substring of the index name.
> Is there an easy way to find the keys for the index?
> Aha! Just found the sysindexkeys table. Feel a bit daft.
> Thanks for your help.
> Neil
> Cheers,
> Neil
> "Uri Dimant" wrote:
>|||Thanks Chandra, that does it nicely.
I found the details this way instead:
select a.name, b.name from sysobjects a
inner join syscolumns b on a.id = b.id
inner join sysindexkeys c on b.colid = c.colid and a.id = c.id
where a.name = 'tableA'
Cheers,
Neil
"Chandra" wrote:
> Hi Jones
> Probably, this can help you.
>
> select object_name(sysindexes.id),
> index_col(object_name(sysindexes.id),indid,1)
> from sysindexes
> INNER JOIN sysobjects on sysobjects.id = sysindexes.id
> where
> sysobjects.xtype in ('u','v') and
> sysindexes.status = 0 and
> index_col(object_name(sysindexes.id),indid,1) IS NOT NULL
> please let me know if u have any questions
>
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "NeilDJones" wrote:
>|||Yes jones
quite interesting! yes ur solution is correct. i tried for colid and
couldnot find it.
then, i checked how sp_help was doing it.
sp_help uses sp_helpindex,
and i got the code sniplett from that SP
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"NeilDJones" wrote:
> Thanks Chandra, that does it nicely.
> I found the details this way instead:
> select a.name, b.name from sysobjects a
> inner join syscolumns b on a.id = b.id
> inner join sysindexkeys c on b.colid = c.colid and a.id = c.id
> where a.name = 'tableA'
> Cheers,
> Neil
> "Chandra" wrote:
>

find fields

hi, how to find the fields have "&abc" as the caption in the database?
thanks...Do you mean the extended property, description?
http://www.aspfaq.com/2244
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"js" <js@.someone.com> wrote in message
news:ONSPaDVRFHA.2252@.TK2MSFTNGP15.phx.gbl...
> hi, how to find the fields have "&abc" as the caption in the database?
> thanks...
>
>|||Thanks Aaron,
I want fo find what fields have the "abc" in what tables as the field
captions.
Can I use a query to get them?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uQJWxnVRFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Do you mean the extended property, description?
> http://www.aspfaq.com/2244
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "js" <js@.someone.com> wrote in message
> news:ONSPaDVRFHA.2252@.TK2MSFTNGP15.phx.gbl...
>|||Hi,
Select TABLE_NAME,COLUMN_NAME from FORMATION_SCHEMA.COLUMNS where
COLUMN_NAME like '%ABC%'
You can also query system table SYSCOLUMNS
Thanks
Hari
SQL Server MVP
"js" <js@.someone.com> wrote in message
news:%23zQX4wVRFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Thanks Aaron,
> I want fo find what fields have the "abc" in what tables as the field
> captions.
> Can I use a query to get them?
>

> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uQJWxnVRFHA.3664@.TK2MSFTNGP15.phx.gbl...
>|||> I want fo find what fields have the "abc" in what tables as the field
> captions.
I don't know what you mean by "field captions"... there are no fields and
there are no specific things called captions. There are columns which have
a name and *can* have a description. Can you please be more specific about
what you are looking for.|||That's it. Thanks...
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:uP%230cIWRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Select TABLE_NAME,COLUMN_NAME from FORMATION_SCHEMA.COLUMNS where
> COLUMN_NAME like '%ABC%'
> You can also query system table SYSCOLUMNS
> Thanks
> Hari
> SQL Server MVP
>
> "js" <js@.someone.com> wrote in message
> news:%23zQX4wVRFHA.3476@.TK2MSFTNGP10.phx.gbl...
>
>

Find Exact word in Full-Text Search

Hi,

I have the fields like this.

Session1 Session2 Session3 Session4
---------------------------
SQL Server-Part1 SQL Server-Part2 ASP.Net CSS

C# SQL Server-Part3 ASP.Net Javascript

I have set the Full-Text to all the columns. For searching i wrote the below query

SELECT * FROM <TABLE NAME> WHERE CONTAINS(*,'"SQL Server-Part1"')

My Result expectation is: The First Record should come. But the result of the query bring the two records. It see the "SQL Server" string also. I need to find the exact word. How to do it?

Please answer me as soon as possible.

Ganesh.

Hi there,

Why don't you try this:

SELECT TOP 1 * FROM <TABLE NAME> WHERE CONTAINS(*,'"SQL Server-Part1"')

Hope this helps,

thanks,

Murthy here

|||

Murthy Puvvada:

Hi there,

Why don't you try this:

SELECT TOP 1 * FROM <TABLE NAME> WHERE CONTAINS(*,'"SQL Server-Part1"')

Hope this helps,

thanks,

Murthy here

Sorry, but I think that is bad sql advice!

There is no guarantee that the correct record will be returned first in the query. Next time, it could be the second record returned.

In fact, if there is no order by clause, the sql standard maintains that the order of records returned in indeterminate, meaning officially unpredictable. Any sub-sub-release of sql server (or any other sql database) is free to make internal performance enhancements that could result in the records being returned in a different order.

|||

On another note, I haven't used Full Text indexing yet. Sorry, I can'tgive definitive advice there. But I'm guessing, from the sample datathat you showed, that maybe some of the characters you typed are wildcards of some sort within the context of the Contains function. Beingignorant on Full Indexing, I would read up on the specifications forthe Contains function more carefully.

And why do you have both single and double quotes around the search string? It sure looks weird!

|||

Oh! No. I can not use SELECT TOP 1 . Because the table may have duplicate of the string in more than a time.

So, please give me the exact answer.

Thanks

|||

Again, you need to research the Contains function and what it does. It clearly brings back data that you do not expect it to.

Have you researched that function yet?

|||

I glanced at the online documentation for the contains function and the mdx language for the search parameter.

It looks like it treats a - character as an Except command in some circumstances. I noticed that you had a - in the text you were searching for.

Might want to do an experiment and see if that's the problem.

find datetime fields - swap year portion of datetime

I need to devise a t-sql script to:
1.) loop through all user tables in my db,
2.) determine whether each table has one or more datetime fields, and
if it does,
3.) swap the year portion of each rows datetime with a different year
(e.g., '1-Jan-1996' would become '1-Jan-2018')
I know how to do '1', have an idea how to do '3' using the DatePart
and CAST functions, but haven't had much luck figuring out '2', and
*then iterating row by row through the table's datetime rows to swap
in the new years. To further automate this, I envisioned adding a
table to the db that defined the mapping for years, i.e.,...
tblBlindedYears
YearNow |BecomesYear
1996 2018
1997 2008
(With this, I would look to eliminate leap year issues by making sure
I don't swap a non-leap year with a leap-year).
Any help GREATLY appreciated.To help you with the issue 2):
USE Northwind
GO
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE, COUNT(*) As 'NofCols'
FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE DATA_TYPE IN ('datetime','smaldatetime')
AND t.TABLE_TYPE='BASE TABLE'
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401021712.247c36da@.posting.google.com...
> I need to devise a t-sql script to:
> 1.) loop through all user tables in my db,
> 2.) determine whether each table has one or more datetime fields, and
> if it does,
> 3.) swap the year portion of each rows datetime with a different year
> (e.g., '1-Jan-1996' would become '1-Jan-2018')
> I know how to do '1', have an idea how to do '3' using the DatePart
> and CAST functions, but haven't had much luck figuring out '2', and
> *then iterating row by row through the table's datetime rows to swap
> in the new years. To further automate this, I envisioned adding a
> table to the db that defined the mapping for years, i.e.,...
> tblBlindedYears
> YearNow |BecomesYear
> 1996 2018
> 1997 2008
> (With this, I would look to eliminate leap year issues by making sure
> I don't swap a non-leap year with a leap-year).
> Any help GREATLY appreciated.|||This is very helpful, thank-you.
Now that I have this list of table names having counts of datetime
columns, any suggestions as to discovering *which* columns have the
datetime, and how I can begin printing these particular columns
values?
p.s. my results were:
dbo tbl10DayMeanAirTemp datetime 1
dbo tblHistoricalStationFlows datetime 1
dbo tblHistoricalStationWaterElevation datetime 1
dbo tblHistoricalStationWaterTemperatures datetime 1
dbo tblHydrologyModelWeeks datetime 2
dbo tblModelRuns datetime 5
dbo tblModelScenarios datetime 2
dbo tblXLFileLocations datetime 1
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:<Ok3w2rd0DHA.1532@.TK2MSFTNGP10.phx.gbl>...
> To help you with the issue 2):
> USE Northwind
> GO
> SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE, COUNT(*) As 'NofCols'
> FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
> ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
> WHERE DATA_TYPE IN ('datetime','smaldatetime')
> AND t.TABLE_TYPE='BASE TABLE'
> GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
> ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME, c.DATA_TYPE
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
> "JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
> news:ed564348.0401021712.247c36da@.posting.google.com...
> > I need to devise a t-sql script to:
> >
> > 1.) loop through all user tables in my db,
> > 2.) determine whether each table has one or more datetime fields, and
> > if it does,
> > 3.) swap the year portion of each rows datetime with a different year
> > (e.g., '1-Jan-1996' would become '1-Jan-2018')
> >
> > I know how to do '1', have an idea how to do '3' using the DatePart
> > and CAST functions, but haven't had much luck figuring out '2', and
> > *then iterating row by row through the table's datetime rows to swap
> > in the new years. To further automate this, I envisioned adding a
> > table to the db that defined the mapping for years, i.e.,...
> >
> > tblBlindedYears
> > YearNow |BecomesYear
> > 1996 2018
> > 1997 2008
> >
> > (With this, I would look to eliminate leap year issues by making sure
> > I don't swap a non-leap year with a leap-year).
> >
> > Any help GREATLY appreciated.|||JTWhaler (jonahturnedwhaler@.yahoo.com) writes:
> This is very helpful, thank-you.
> Now that I have this list of table names having counts of datetime
> columns, any suggestions as to discovering *which* columns have the
> datetime, and how I can begin printing these particular columns
> values?
>
Here is a query that returns the column names as well:
select o.name, c.name
from sysobjects o
join syscolumns c on o.id = c.id
join systypes t on c.type = t.type
where t.name in ('datetime', 'smalldatetime')
order by o.name, c.name
(I assuming that everything is owned by dbo.)
To run some general code that goes through all columns, you would probably
have to use dynamic SQL to build statements on the fly.
If you are not acquainted with dynamic SQL, I have an article on my
web site, http://www.sommarskog.se/dynamic_sql.html. The article may
seem dauntingly long, but the article is trying to tell you that
you should use this feature with care. But this seems to be a good
occasion for using dynamic SQL.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9466475B2A41Yazorman@.127.0.0.1>...
> JTWhaler (jonahturnedwhaler@.yahoo.com) writes:
> > This is very helpful, thank-you.
> >
> > Now that I have this list of table names having counts of datetime
> > columns, any suggestions as to discovering *which* columns have the
> > datetime, and how I can begin printing these particular columns
> > values?
> >
> Here is a query that returns the column names as well:
> select o.name, c.name
> from sysobjects o
> join syscolumns c on o.id = c.id
> join systypes t on c.type = t.type
> where t.name in ('datetime', 'smalldatetime')
> order by o.name, c.name
> (I assuming that everything is owned by dbo.)
> To run some general code that goes through all columns, you would probably
> have to use dynamic SQL to build statements on the fly.
> If you are not acquainted with dynamic SQL, I have an article on my
> web site, http://www.sommarskog.se/dynamic_sql.html. The article may
> seem dauntingly long, but the article is trying to tell you that
> you should use this feature with care. But this seems to be a good
> occasion for using dynamic SQL.
Thank-you very much. This is helpful, and I plan to read the dynamic SQL article.

Filtering values in ResultSet by setting range on complex index

Hi!
I have table with complex index on 5 fields. One of them is string filed. I want to implement some sort of filtering, by setting SetRange() in my SQLCeCommand. But i need to fileter only by one string field and to get the values starting with the input string value.
I tried to use such code:
...
command.SetRange(DbRangeOptions.Prefix, new object[] {null, null, null, "Com", null}, null);
resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
....
But it doesn't work. As a result i've got an empty result set.
Usage of simple index on one field and setting the correspondent range will solve problem, but i can't have such index due to project restrictions.
Is there any way to set prefix range only by one value of complex index? If not, please, explain me how does Prefix Range works.

Thanx

If filtering has the same behavior as seeking (which I am assuming - read: not tested) then you might want to consider putting the column with the non-null value as the first in the index. This will require that you recreate the index with the new column order.