SQL server connection: column

Showing posts with label column. Show all posts

Monday, March 19, 2012

another sp_repladdcolumn option question

Syntax
sp_repladdcolumn [ @.source_object = ] 'source_object'
, [ @.column = ] 'column' ]
[ , [ @.typetext = ] 'typetext' ]
[ , [ @.publication_to_add = ] 'publication_to_add' ]
[ , [ @.schema_change_script = ] 'schema_change_script' ]
[ , [ @.force_invalidate_snapshot = ] force_invalidate_snapshot ]
[ , [ @.force_reinit_subscription = ] force_reinit_subscription ]
I need clarification on the last 2 options here. I know it seems the name
says it all but its the ripple effect I'm not clear on... for example, if I
choose to invalidate the snapshot what happens? .. how is the merge repl
subscriber effected? and, for example, if I choose to reinitialize, when
does it happen and can I use the option to bring subscriber data back to the
publisher first? (like you can when using the GUI)
I have some basic assumptions about these last 2 options but I need to be
very clear on the why and when to use them? example scenarios would be very
helpful.
any info is appreciated. thanks.
basically there are some changes you can make to a publication which will
force a reinitialization, or invalidate the existing snapshot for new
subscribers.
If you make a change and set @.force_invalidate_snapshot to 0, you should
(but don't) get an error message which basically tells you the existing
snapshot will be invalidated.
If you set this to 1, you won't get this error message, as the proc assumes
you know your existing snapshot will be invalidated.
Same type of logic applies for the @.force_reinit_subscription parameter.
Setting it to a value of 0 will raise an error if the change is going to
break your publication/subscription and you have to re-initialize. Setting
it to 1 will assume you know what you are doing, no error is raised and the
publication/subscription is marked for reinitialization.
A bug will not raise the error. I reported this to Microsoft and a bug was
filed.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"djc" <noone@.nowhere.com> wrote in message
news:ufdiVedXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Syntax
> sp_repladdcolumn [ @.source_object = ] 'source_object'
> , [ @.column = ] 'column' ]
> [ , [ @.typetext = ] 'typetext' ]
> [ , [ @.publication_to_add = ] 'publication_to_add' ]
> [ , [ @.schema_change_script = ] 'schema_change_script' ]
> [ , [ @.force_invalidate_snapshot = ] force_invalidate_snapshot ]
> [ , [ @.force_reinit_subscription = ] force_reinit_subscription ]
> I need clarification on the last 2 options here. I know it seems the name
> says it all but its the ripple effect I'm not clear on... for example, if
I
> choose to invalidate the snapshot what happens? .. how is the merge repl
> subscriber effected? and, for example, if I choose to reinitialize, when
> does it happen and can I use the option to bring subscriber data back to
the
> publisher first? (like you can when using the GUI)
> I have some basic assumptions about these last 2 options but I need to be
> very clear on the why and when to use them? example scenarios would be
very
> helpful.
> any info is appreciated. thanks.
>
|||basically there are some changes you can make to a publication which will
force a reinitialization, or invalidate the existing snapshot for new
subscribers.
If you make a change and set @.force_invalidate_snapshot to 0, you should
(but don't) get an error message which basically tells you the existing
snapshot will be invalidated.
If you set this to 1, you won't get this error message, as the proc assumes
you know your existing snapshot will be invalidated.
Same type of logic applies for the @.force_reinit_subscription parameter.
Setting it to a value of 0 will raise an error if the change is going to
break your publication/subscription and you have to re-initialize. Setting
it to 1 will assume you know what you are doing, no error is raised and the
publication/subscription is marked for reinitialization.
A bug will not raise the error. I reported this to Microsoft and a bug was
filed.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"djc" <noone@.nowhere.com> wrote in message
news:ufdiVedXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Syntax
> sp_repladdcolumn [ @.source_object = ] 'source_object'
> , [ @.column = ] 'column' ]
> [ , [ @.typetext = ] 'typetext' ]
> [ , [ @.publication_to_add = ] 'publication_to_add' ]
> [ , [ @.schema_change_script = ] 'schema_change_script' ]
> [ , [ @.force_invalidate_snapshot = ] force_invalidate_snapshot ]
> [ , [ @.force_reinit_subscription = ] force_reinit_subscription ]
> I need clarification on the last 2 options here. I know it seems the name
> says it all but its the ripple effect I'm not clear on... for example, if
I
> choose to invalidate the snapshot what happens? .. how is the merge repl
> subscriber effected? and, for example, if I choose to reinitialize, when
> does it happen and can I use the option to bring subscriber data back to
the
> publisher first? (like you can when using the GUI)
> I have some basic assumptions about these last 2 options but I need to be
> very clear on the why and when to use them? example scenarios would be
very
> helpful.
> any info is appreciated. thanks.
>

Another Row After SubTotal in Matrix

I have a Matrix in Report, with SubTotals of rows and columns.

I want to add another row below the subtotal (as well as another column to the left) in order to display percentage. Could anyone please tell me how?

There is a work around. you need to add one more column and display percentage in that column, this would add a column after total column and would display the sum of percentage column.

Then you reduce the width of the column (the column that you have manually added) to 0.

|||

First, Thanks for your reply.

I Tried it, but it doesn't work.

When I set the width of the added column to 0, the total row becomes 0 too.

Sunday, March 11, 2012

Another Question: Replication

Just another quick question:
When you have published a article is it possible to change a column data type without drop the replication, thanks (Again)Yes, but you have to drop the article first before you change it. After you have made the change, then add the article back in the publication.|||Originally posted by joejcheng
Yes, but you have to drop the article first before you change it. After you have made the change, then add the article back in the publication.

Thanks again. Ed

Another problem with XQuery!

Hi all,

In my Sql Server 2005 database I have a column called OtherInformation which is an XML datatype. Within this column I have XML in the following format:

<Logs>

<Event Id="123">

<Information>Database crash</Information>

</Event>

</Logs>

The value of the Id attribute can change but I an trying to write a query that will retrieve the value of the Information element when the Id attribute of the Event element is 123. What I have so far is below but this returns the text regardless of what the attributes value is!

SELECT

LogId, OtherInformation.query('Logs/Event/Information/text()') As EventInformation

FROM

Logs

WHERE

OtherInformation.exist('Logs/Event[@.Id]') = '123'

Any help on this would be great. Thanks!

You need this query:

Code Snippet

SELECT LogId, OtherInformation.query('Logs/Event/Information/text()')

FROM Logs

WHERE OtherInformation.exist('Logs/Event[@.Id = "123"]') = 1;

|||Fantastic Martin. That works great!

Another NQ: Inserting Image

How can i insert images into column easily. Can i do this by Query Browser?Easily? If you have the hex representation of the image you can.
Otherwise you will likely need to use a API. Google it in their groups
server and you might find what you need:
http://groups-beta.google.com/group...into+sql+server
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"LacOniC" <iletisim@.bigfoot.com> wrote in message
news:exhH8ruOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> How can i insert images into column easily. Can i do this by Query
> Browser?
>

Thursday, March 8, 2012

another freetexttable question

i'm trying to usr two (or more) columns in the catalog in the select.
I can get all: FREETEXTTABLE(usr, * , @.term)
or 1 column: FREETEXTTABLE(usr, usrCompany , @.term)
but 2 or more: FREETEXTTABLE(usr, "usrCompany, usrBusDesc" , @.term)
doesn't work. I've seen in the book's on line that it can be done, but have
not found an example.
Any help appreciated! ...and Happy New Year!
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
Try
select * From FREETEXTTABLE(usr, (usrCompany, usrBusDesc) , @.term)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:5CB29272-DA9E-484F-BECE-CD63610A6736@.microsoft.com...
> i'm trying to usr two (or more) columns in the catalog in the select.
> I can get all: FREETEXTTABLE(usr, * , @.term)
> or 1 column: FREETEXTTABLE(usr, usrCompany , @.term)
> but 2 or more: FREETEXTTABLE(usr, "usrCompany, usrBusDesc" , @.term)
> doesn't work. I've seen in the book's on line that it can be done, but
> have
> not found an example.
> Any help appreciated! ...and Happy New Year!
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
|||thank's for responding and happy new year,
I did try that, but i keep getting: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Hilary Cotter" wrote:

> Try
> select * From FREETEXTTABLE(usr, (usrCompany, usrBusDesc) , @.term)
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:5CB29272-DA9E-484F-BECE-CD63610A6736@.microsoft.com...
>
>
|||Is this SQL 2000? You can only do this in SQL 2005. In SQL 2000 its one
column or all columns (when you use a *)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:2AF649A4-3375-4772-9DC9-E96FDC365309@.microsoft.com...[vbcol=seagreen]
> thank's for responding and happy new year,
> I did try that, but i keep getting: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '('.
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Hilary Cotter" wrote:
|||it's 2000, that's the answer!
Thank You!
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Hilary Cotter" wrote:

> Is this SQL 2000? You can only do this in SQL 2005. In SQL 2000 its one
> column or all columns (when you use a *)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:2AF649A4-3375-4772-9DC9-E96FDC365309@.microsoft.com...
>
>

Wednesday, March 7, 2012

Another Date time question

I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
convert (char (8), getdate(), 112)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Charlie" <ckerns@.hotmail.com> wrote in message =news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index (Primary key). I need to insert only the date part(not the time) so that when I run my DTS package it only inserts one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try:
convert (char (8), getdate(), 112)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Charlie" wrote in =message news:446c01c37baa$88=07dfa0$a601280a@.phx.gbl...I have a datetime column. This column has an index (Primary key). I =need to insert only the date part(not the time) so that when I run my DTS =package it only inserts one date (TODAY's DATE) without the time. How =can I insert today's date with only date part ?Thanks.

--=_NextPart_000_030E_01C37B89.95A72100--|||This will do it:
SELECT CAST(CONVERT(char, CURRENT_TIMESTAMP, 112) AS datetime)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Charlie" <ckerns@.hotmail.com> wrote in message
news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.

Friday, February 24, 2012

annotated schema and bulk loading into multiple tables...

Hi guys,
I'm trying to get XML data loaded into a set of tables using bulk load. The
child tables also have an XML column where I want to store portions of the
XML.
So far the I've got it correctly inserting data into the parent, and able to
insert the correct amount of rows in the child tables, but the data in the
child tables is empty... The child data is an identity column, a foreign key
pointing back to the parent row (empty!), and an XML data column holding the
contents of the xml fragment (also empty!).
Any help would be greatly appreciated!
Thanks.
Daniel.
Below is where I've got to so far...
SQL definitions for my parent table and a child table:
----
CREATE TABLE [dbo].[Foo](
[Foo_PK] [int] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
(
[Foo_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ChildOfFoo](
[ChildOfFoo_PK] [int] IDENTITY(1,1) NOT NULL,
[Foo_FK] [int] NULL,
[Data] [xml] NULL,
CONSTRAINT [PK_ChildOfFoo] PRIMARY KEY CLUSTERED
(
[ChildOfFoo_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Here's the annotated XSD:
----
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>

<sql:relationship name="HeaderWash"
parent="Foo"
parent-key="Foo_PK"
child="ChildOfFoo"
child-key="Foo_FK" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>

<xs:element name="Header"
sql:relation="Foo"
sql:key-fields="Foo_PK">
<xs:complexType>
<xs:sequence>
<xs:element name="Foo_PK" type="xs:integer"
minOccurs="0"
default="0" sql:identity="ignore"/>
<xs:element name="StartDateTime"
type="xs:dateTime" />
<xs:element name="EndDateTime"
type="xs:dateTime" />
</xs:sequence>
</xs:complexType>
</xs:element>

<xs:element name="Children" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Wash"
sql:relation="ChildOfFoo"
sql:key-fields="ChildOfFoo_PK"
sql:relationship="HeaderWash">
<xs:complexType>
<xs:sequence>
<xs:element name="ChildOfFoo_PK"
type="xs:string"
minOccurs="0"
default="0" sql:identity="ignore" />
<xs:element name="Foo_FK"
type="xs:string" />
<xs:element name="Wash"
sql:field="Data" sql:datatype="xml" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
And finally, (thanks for your patience!), a sample of XML data...
----
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Root>
<Header>
<StartDateTime>15 Jun 2007 08:07:00</StartDateTime>
<EndDateTime>15 Jun 2007 09:12:00</EndDateTime>
</Header>
<Children>

<ChildOfFoo>
<Phasetime>02:00</Phasetime>
<Heated>No</Heated>
</ChildOfFoo>
<ChildOfFoo>
<Quantityinjected>22.8 ml</Quantityinjected>
<Lowerlimit>61.1 ml</Lowerlimit>
<Upperlimit>82.7 ml</Upperlimit>
</ChildOfFoo>
<ChildOfFoo>
<Watertype>Cold water</Watertype>
<Numberofstages>1</Numberofstages>
</ChildOfFoo>
<ChildOfFoo>
<Quantityinjected>137.6 ml</Quantityinjected>
<Lowerlimit>122.3 ml</Lowerlimit>
<Upperlimit>165.4 ml</Upperlimit>
</ChildOfFoo>
<ChildOfFoo>
<Circulationpressure>Detected</Circulationpressure>
</ChildOfFoo>
<ChildOfFoo>
<Heated>No</Heated>
<Circulationpressure>Detected</Circulationpressure>
</ChildOfFoo>
</Children>
</Root>In case anyone elase wants this solution... The schema is modified as such:
1. When referencing a foreign key (as in the relationship) it needs to stay
in scope from when it is initially populated by the parent table, to each
time it is used. If you get a foreign key of null, you are most probably
using the FK out of scope.
2. Use the sql:overflow-field="<MyXmlColumn>" in the element that relates to
the table you want to store as XML. The sql:overflow-field field takes all
unreferenced XML in that node and stuffs it into the field you specify.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>

<sql:relationship name="HeaderWash"
parent="Foo"
parent-key="Foo_PK"
child="ChildOfFoo"
child-key="Foo_FK" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Root" sql:is-constant="1">
<xs:complexType>
<xs:sequence>

<xs:element name="Foo_PK" type="xs:integer" minOccurs="0"
default="0" sql:identity="ignore"/>
<xs:element name="StartDateTime" type="xs:dateTime" />
<xs:element name="EndDateTime" type="xs:dateTime" />

<xs:element name="Children" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" name="Wash"
sql:relation="ChildOfFoo"
sql:key-fields="ChildOfFoo_PK"
sql:relationship="HeaderWash"
sql:overflow-field="Data">
<xs:complexType>
<xs:sequence>
<xs:element name="ChildOfFoo_PK"
type="xs:string"
minOccurs="0" default="0"
sql:identity="ignore" />
<xs:element name="Foo_FK"
type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:annotation>
> <xs:appinfo>
> 
> <sql:relationship name="HeaderWash"
> parent="Foo"
> parent-key="Foo_PK"
> child="ChildOfFoo"
> child-key="Foo_FK" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Root" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> 
> <xs:element name="Header"
> sql:relation="Foo"
> sql:key-fields="Foo_PK">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Foo_PK" type="xs:integer"
> minOccurs="0"
> default="0" sql:identity="ignore"/>
> <xs:element name="StartDateTime"
> type="xs:dateTime" />
> <xs:element name="EndDateTime"
> type="xs:dateTime" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> 
> <xs:element name="Children" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="Wash"
> sql:relation="ChildOfFoo"
> sql:key-fields="ChildOfFoo_PK"
> sql:relationship="HeaderWash">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ChildOfFoo_PK"
> type="xs:string"
> minOccurs="0"
> default="0" sql:identity="ignore" />
> <xs:element name="Foo_FK"
> type="xs:string" />
> <xs:element name="Wash"
> sql:field="Data" sql:datatype="xml" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
"Daniel Bass" <danREMOVEbass@.blueCAPSbottle.comFIRST> wrote in message
news:%23gNnsRHxHHA.600@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> I'm trying to get XML data loaded into a set of tables using bulk load.
> The child tables also have an XML column where I want to store portions of
> the XML.
> So far the I've got it correctly inserting data into the parent, and able
> to insert the correct amount of rows in the child tables, but the data in
> the child tables is empty... The child data is an identity column, a
> foreign key pointing back to the parent row (empty!), and an XML data
> column holding the contents of the xml fragment (also empty!).
> Any help would be greatly appreciated!
> Thanks.
> Daniel.
>
>
> Below is where I've got to so far...
> SQL definitions for my parent table and a child table:
> ----
> CREATE TABLE [dbo].[Foo](
> [Foo_PK] [int] IDENTITY(1,1) NOT NULL,
> [StartDateTime] [datetime] NULL,
> [EndDateTime] [datetime] NULL,
> CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
> (
> [Foo_PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[ChildOfFoo](
> [ChildOfFoo_PK] [int] IDENTITY(1,1) NOT NULL,
> [Foo_FK] [int] NULL,
> [Data] [xml] NULL,
> CONSTRAINT [PK_ChildOfFoo] PRIMARY KEY CLUSTERED
> (
> [ChildOfFoo_PK] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> Here's the annotated XSD:
> ----
> <?xml version="1.0" encoding="utf-8"?>
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xs:annotation>
> <xs:appinfo>
> 
> <sql:relationship name="HeaderWash"
> parent="Foo"
> parent-key="Foo_PK"
> child="ChildOfFoo"
> child-key="Foo_FK" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="Root" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> 
> <xs:element name="Header"
> sql:relation="Foo"
> sql:key-fields="Foo_PK">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="Foo_PK" type="xs:integer"
> minOccurs="0"
> default="0" sql:identity="ignore"/>
> <xs:element name="StartDateTime"
> type="xs:dateTime" />
> <xs:element name="EndDateTime"
> type="xs:dateTime" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> 
> <xs:element name="Children" sql:is-constant="1">
> <xs:complexType>
> <xs:sequence>
> <xs:element maxOccurs="unbounded" name="Wash"
> sql:relation="ChildOfFoo"
> sql:key-fields="ChildOfFoo_PK"
> sql:relationship="HeaderWash">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="ChildOfFoo_PK"
> type="xs:string"
> minOccurs="0"
> default="0" sql:identity="ignore" />
> <xs:element name="Foo_FK"
> type="xs:string" />
> <xs:element name="Wash"
> sql:field="Data" sql:datatype="xml" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>
>
> And finally, (thanks for your patience!), a sample of XML data...
> ----
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <Root>
> <Header>
> <StartDateTime>15 Jun 2007 08:07:00</StartDateTime>
> <EndDateTime>15 Jun 2007 09:12:00</EndDateTime>
> </Header>
> <Children>
> 
> <ChildOfFoo>
> <Phasetime>02:00</Phasetime>
> <Heated>No</Heated>
> </ChildOfFoo>
> <ChildOfFoo>
> <Quantityinjected>22.8 ml</Quantityinjected>
> <Lowerlimit>61.1 ml</Lowerlimit>
> <Upperlimit>82.7 ml</Upperlimit>
> </ChildOfFoo>
> <ChildOfFoo>
> <Watertype>Cold water</Watertype>
> <Numberofstages>1</Numberofstages>
> </ChildOfFoo>
> <ChildOfFoo>
> <Quantityinjected>137.6 ml</Quantityinjected>
> <Lowerlimit>122.3 ml</Lowerlimit>
> <Upperlimit>165.4 ml</Upperlimit>
> </ChildOfFoo>
> <ChildOfFoo>
> <Circulationpressure>Detected</Circulationpressure>
> </ChildOfFoo>
> <ChildOfFoo>
> <Heated>No</Heated>
> <Circulationpressure>Detected</Circulationpressure>
> </ChildOfFoo>
> </Children>
> </Root>
>

AND query in single column

Hi group
I have a rather peculiar question, and I really don't know how to solve
this within an SQL statement:

Given a view (v), that results in:

IDX
------
1a
1b
2a
2c
3a

I'd like to query the view with something like:
SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:

ID
------
1

or in another case:
SELECT ID FROM v WHERE (X='a' OR X='c')
would give:

ID
------
1
2
3

how can this be done?

TIA
bernhard

--
www.daszeichen.ch
remove nixspam to replyBernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> I have a rather peculiar question, and I really don't know how to solve
> this within an SQL statement:
> Given a view (v), that results in:
> ID X
> ------
> 1 a
> 1 b
> 2 a
> 2 c
> 3 a
> I'd like to query the view with something like:
> SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:

SELECT ID FROM v WHERE X = 'a'
INTERSECT
SELECT ID FROM v WHERE X = 'b'

Except that that syntax only works in SQL 2005. For SQL 2000, you
could do:

SELECT ID FROM v a
WHERE X = 'a'
AND EXISTS (SELECT *
FROM v b
WHERE a.ID = b.ID
AND b.X = 'b')

> ID
> ------
> 1
> or in another case:
> SELECT ID FROM v WHERE (X='a' OR X='c')
> would give:

SELECT ID FROM v WHERE X = 'a'
UNION
SELECT ID FROM v WHERE X = 'b'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Assuming the (id, x) is unique. The following gives the first result
you asked for:

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

This operation is called Relational Division and you can find other
examples and discussion about it under that name.

Your second "OR" query:

SELECT id
FROM v
WHERE x IN ('A','C')
GROUP BY id

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> SELECT id
> FROM v
> WHERE x IN ('A','B')
> GROUP BY id
> HAVING COUNT(*)=2
> This operation is called Relational Division and you can find other
> examples and discussion about it under that name.
> Your second "OR" query:
> SELECT id
> FROM v
> WHERE x IN ('A','C')
> GROUP BY id

thanks a lot for your answer. Departing from your suggestion I have come
to the following solution (which seem to work for my purposes):

SELECT id
FROM v
WHERE x='a' AND EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

This can easily adopted to the 'or' case:

SELECT id
FROM v
WHERE x='a' OR EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

What do you think?

bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Bernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> thanks a lot for your answer. Departing from your suggestion I have come
> to the following solution (which seem to work for my purposes):
> SELECT id
> FROM v
> WHERE x='a' AND EXISTS
> (SELECT id FROM v WHERE x='b')
> GROUP BY id

This does not look good. If you have:

DX
------
1a
10b
2a
2c
3a

you will get back 1, 2 and 3. You need to correlate the subquery with
the main query. Because the subquery is not correlated to the main
query, the EXISTS is just binary flip-flop.

> This can easily adopted to the 'or' case:
> SELECT id
> FROM v
> WHERE x='a' OR EXISTS
> (SELECT id FROM v WHERE x='b')
> GROUP BY id

If there is one row with x = 'b', you will get back all id:s. Again,
this is becaues the subquery is not correlated.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
>>
>>SELECT id
>> FROM v
>> WHERE x='a' AND EXISTS
>> (SELECT id FROM v WHERE x='b')
>> GROUP BY id
>
> This does not look good. If you have:
> DX
> ------
> 1a
> 10b
> 2a
> 2c
> 3a
> you will get back 1, 2 and 3. You need to correlate the subquery with
> the main query. Because the subquery is not correlated to the main
> query, the EXISTS is just binary flip-flop.

true.. I just realised it as well... maybe I give your solution a try as
well. The thing is: this is all part of an other query, and I am pulling
the queries together via an ASP script, so they need to be simple. but
maybe may way of doing it is not simple ;-)

this

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

cheers
bernhard

--
www.daszeichen.ch
remove nixspam to reply|||David Portas wrote:
> Assuming the (id, x) is unique. The following gives the first result
> you asked for:
> SELECT id
> FROM v
> WHERE x IN ('A','B')

can this be formulated using a LIKE '%a%' statement as well?

> GROUP BY id
> HAVING COUNT(*)=2

--
www.daszeichen.ch
remove nixspam to reply|||> doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

Are you going to tell us what the error is, or just let us take a wild
guess?

It works for me if I run this code:
CREATE TABLE v (id INTEGER, x CHAR(1), PRIMARY KEY (id,x))

INSERT INTO v SELECT 1,'A' UNION SELECT 1,'B'

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

Result:

(2 row(s) affected)

id
----
1

(1 row(s) affected)

So what did you do differently? Please post some code that will
actually reproduce the problem, tell us the error message and the
version, edition and service pack you are using.

--
David Portas
SQL Server MVP
--|||David Portas wrote:
>>doesn't seem to work... x IN ('a','b') provokes an SQL Server error.
>
> Are you going to tell us what the error is, or just let us take a wild
> guess?

sorry.. my fault:
I used the expression
x IN (LIKE '%a%', LIKE '%b%') which did result in an error (incorrect
syntax error)

> So what did you do differently? Please post some code that will
> actually reproduce the problem, tell us the error message and the
> version, edition and service pack you are using.

CODE:
SELECT id
FROM v
WHERE x IN (LIKE '%a%', LIKE'%b%')
GROUP BY id
HAVING COUNT(*)=2

Error:
Incorrect Syntax near the keyword 'LIKE'

SQL Server 8.0 MSDE S.Pack 3/S.Pack 3a

cheers
Bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Maybe this is what you intended:

SELECT id
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
HAVING COUNT(*)=2

But are you sure you want to use LIKE here? This is not the same as
what you originally asked for because LIKE will catch any x that
*contains* the character "A" or "B", not just x = "A" or x = "B". If
that's what you want then that will probably invalidate my assumption
about the key of your table and my query won't work as I intended. You
may have to replace COUNT(*) with COUNT(DISTINCT x).

Note that the best way to get help with your problem is to post DDL (a
CREATE TABLE statement, including keys and constraints), sample data (a
few INSERT statements) and your required results. That way we won't
have to keep guessing exactly what you mean.

See: http://www.aspfaq.com/show.asp?id=2120

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> Maybe this is what you intended:
> SELECT id
> FROM v
> WHERE x LIKE '%a%' OR x LIKE '%b%'
> GROUP BY id
> HAVING COUNT(*)=2

no, as my ID is unique I only want records 'sharing' the same ID AND
fullfilling the AND LIKE criteria.

ID X
------
1 auvw
1 buvw
2 axyz
2 cxyz
3 aklm

your solution would result in:

ID
--
1
2
3

but I only want to get (in my AND-problem):

ID
--
1

> But are you sure you want to use LIKE here? This is not the same as
> what you originally asked for because LIKE will catch any x that
> *contains* the character "A" or "B", not just x = "A" or x = "B".

Yes. This is correct. I have formulated the problem in a more
'simplified' way. My problem is far more 'complicated'. But I have
followed your suggestion about the relaional divison, and think I've
found a way to solve the problem (In the 'real world' I need to check
for 3 criteria):

SELECT id
FROM v AS FG1
WHERE x LIKE '%a%' AND EXISTS
(SELECT *
FROM v AS FG2
WHERE FG1.id = FG2.id AND FG2.x LIKE "%b%" AND EXISTS
(SELECT * FROM v AS FG3
WHERE FG1.id = FG2.id AND FG3.id = FG1.id AND FG3.x LIKE "%c%"))

As far as I understand it (and tested it), this seems to cover my 'AND'
problem.

--
www.daszeichen.ch
remove nixspam to reply|||David Portas wrote:
> Maybe this is what you intended:
> SELECT id
> FROM v
> WHERE x LIKE '%a%' OR x LIKE '%b%'
> GROUP BY id
> HAVING COUNT(*)=2

sorry.. I was too quick with my reply.. your solution works as well :-)
puhhh.. long way to learn for me.
The only thing I don't understand is the HAVING COUNT(*)=2 here. Why is
that?

--
www.daszeichen.ch
remove nixspam to reply|||> your solution would result in:

> ID
> --
> 1
> 2
> 3

I don't think you tested that out. If you try it I think you will find
my query gives the result you asked for. Anyway it looks like your
version will work too. It's just rather more typing :-)

--
David Portas
SQL Server MVP
--|||David Portas wrote:
>>your solution would result in:
>
>>ID
>>--
>>1
>>2
>>3
>
> I don't think you tested that out. If you try it I think you will find
> my query gives the result you asked for. Anyway it looks like your
> version will work too. It's just rather more typing :-)

mine was too complicated, yours is perfect for my needs :-) Thanks a
lot. Now I have to implement this into another query... but thanks: it
works!

cheers
bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Bernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> David Portas wrote:
>> Maybe this is what you intended:
>>
>> SELECT id
>> FROM v
>> WHERE x LIKE '%a%' OR x LIKE '%b%'
>> GROUP BY id
>> HAVING COUNT(*)=2
>
> sorry.. I was too quick with my reply.. your solution works as well :-)
> puhhh.. long way to learn for me.
> The only thing I don't understand is the HAVING COUNT(*)=2 here. Why is
> that?

HAVING is like WHERE, but it is applied after GROUP BY. And for this
reason you can use aggregates in HAVING.

If you run

SELECT id, cnt = COUNT(*)
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
-- HAVING COUNT(*)=2

You will get:

id cnt
-- --
1 2
2 1
3 1

But you want the row with cnt = 2. Normally you do would do this with a
WHERE clause, but you cannot use COUNT(*) in a WHERE clause. Hence,
HAVING to the rescue.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

AND and One to many relationships

I have two tables in a one to many relationship, customers and invoices. The
invoices can be a variety of types marked by an invoice flag column (for
instance 1, 2 or 3). I want to retrieve customers, which only have invoices
of 1 AND 2. If there is an invoice of type 3 I don't want the customer
returned. How do I do this SQLSELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
--
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>I have two tables in a one to many relationship, customers and invoices.
>The invoices can be a variety of types marked by an invoice flag column
>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>customer returned. How do I do this SQL
>|||I can't get this to work. If the customer has invoices of type 1 and 2 I
want the customer returned. If i.InvoiceType IN (1,2) in the query and
customer has an invoice type of 3 I don't want the customer returned.
Does this make sense. Thanks.
SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>>I have two tables in a one to many relationship, customers and invoices.
>>The invoices can be a variety of types marked by an invoice flag column
>>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>>customer returned. How do I do this SQL
>|||SELECT <column list>
FROM Customers c
WHERE EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType IN (1,2))
AND NOT EXISTS
(SELECT NULL FROM Invoices i
WHERE c.CustomerID = i.CustomerID
AND i.InvoiceType = 3)
?
That gives you customers that have invoice with type either 1 or 2 but don't
have invoices of type 3.
--
Jacco Schalkwijk
SQL Server MVP
"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:u8eMEMjKFHA.2852@.TK2MSFTNGP14.phx.gbl...
>I can't get this to work. If the customer has invoices of type 1 and 2 I
>want the customer returned. If i.InvoiceType IN (1,2) in the query and
>customer has an invoice type of 3 I don't want the customer returned.
> Does this make sense. Thanks.
> SELECT <column list>
> FROM Customers c
> WHERE EXISTS
> (SELECT NULL FROM Invoices i
> WHERE c.CustomerID = i.CustomerID
> AND i.InvoiceType IN (1,2))
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23CPdaBjKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> SELECT <column list>
>> FROM Customers c
>> WHERE EXISTS
>> (SELECT NULL FROM Invoices i
>> WHERE c.CustomerID = i.CustomerID
>> AND i.InvoiceType IN (1,2))
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
>> news:eA0P$hiKFHA.1308@.TK2MSFTNGP15.phx.gbl...
>>I have two tables in a one to many relationship, customers and invoices.
>>The invoices can be a variety of types marked by an invoice flag column
>>(for instance 1, 2 or 3). I want to retrieve customers, which only have
>>invoices of 1 AND 2. If there is an invoice of type 3 I don't want the
>>customer returned. How do I do this SQL
>>
>|||select c.*
from customers c
where c.customerid not in (select customerid from invoices where
invoicetype = 3)
if you need other data from the invoice table then just do a join to
it.
i am presuming in this example that customers can have all three types,
but will have the same customerid on each row in the invoice table,
thus if you exclude the customers on an id level, then it will exclude
the ones that have any combination of 3 (1-2-3, 1-3, 2-3, 3)
hth,
hans|||Hi
As I have understood you need the list of customers who have invoices of
type 1 AND 2 so we can't use IN keyword because it is actually a type of OR.
Here is the query (I assumed invoice Type field is char):
select distinct A.idcustomer from
(select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
(select idinvoice,idcustomer from tblinvoice where type='2') B on
A.idcustomer=B.idcustomer
and not exists (select 'true' from tblinvoice C where
C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||Hi this is going in the right direction. To complicate things would there be
any way to make the query more dynamic. Say there were 5 invoices types and
I need to show customers with invoices of invoice type 1 AND 3 but then
wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
"Reza" <Reza@.discussions.microsoft.com> wrote in message
news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
> Hi
> As I have understood you need the list of customers who have invoices of
> type 1 AND 2 so we can't use IN keyword because it is actually a type of
> OR.
> Here is the query (I assumed invoice Type field is char):
> select distinct A.idcustomer from
> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
> (select idinvoice,idcustomer from tblinvoice where type='2') B on
> A.idcustomer=B.idcustomer
> and not exists (select 'true' from tblinvoice C where
> C.idcustomer=A.idcustomer and C.type not in ('1','2'))|||On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>Hi this is going in the right direction. To complicate things would there be
>any way to make the query more dynamic. Say there were 5 invoices types and
>I need to show customers with invoices of invoice type 1 AND 3 but then
>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
Hi Chris,
For full flexibility, create an extra table to hold the types you need.
Then, to find all customers with all requested types of invoice plus
possibly others, use
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
INNER JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
And to find all customers with all requested types of invoice, but no
others, you change this to
SELECT i.CustomerID
FROM (SELECT DISTINCT CustomerID, Type
FROM Invoices) AS i
LEFT JOIN TypesWanted AS t
ON t.Type = i.Type
GROUP BY i.CustomerID
HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
AND COUNT(*) = COUNT(t.Type)
Both above queries are untested. Post CREATE TABLE and INSERT statements
with test data if you want a tested solution.
>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>> Hi
>> As I have understood you need the list of customers who have invoices of
>> type 1 AND 2 so we can't use IN keyword because it is actually a type of
>> OR.
>> Here is the query (I assumed invoice Type field is char):
>> select distinct A.idcustomer from
>> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner join
>> (select idinvoice,idcustomer from tblinvoice where type='2') B on
>> A.idcustomer=B.idcustomer
>> and not exists (select 'true' from tblinvoice C where
>> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Where does the Types wanted table come from. Is this like a many to many
relationship with customers having many invoices and invoices types having
many invoices?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2j4j31lmk3p78au3eookj29outfd7uaei2@.4ax.com...
> On Thu, 17 Mar 2005 14:12:34 -0000, Chris Kennedy wrote:
>>Hi this is going in the right direction. To complicate things would there
>>be
>>any way to make the query more dynamic. Say there were 5 invoices types
>>and
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
> Hi Chris,
> For full flexibility, create an extra table to hold the types you need.
> Then, to find all customers with all requested types of invoice plus
> possibly others, use
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> INNER JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> And to find all customers with all requested types of invoice, but no
> others, you change this to
> SELECT i.CustomerID
> FROM (SELECT DISTINCT CustomerID, Type
> FROM Invoices) AS i
> LEFT JOIN TypesWanted AS t
> ON t.Type = i.Type
> GROUP BY i.CustomerID
> HAVING COUNT(*) = (SELECT COUNT(*) FROM TypesWanted)
> AND COUNT(*) = COUNT(t.Type)
> Both above queries are untested. Post CREATE TABLE and INSERT statements
> with test data if you want a tested solution.
>
>>"Reza" <Reza@.discussions.microsoft.com> wrote in message
>>news:EB715DA5-F950-41C5-9C1C-F9B616926561@.microsoft.com...
>> Hi
>> As I have understood you need the list of customers who have invoices of
>> type 1 AND 2 so we can't use IN keyword because it is actually a type of
>> OR.
>> Here is the query (I assumed invoice Type field is char):
>> select distinct A.idcustomer from
>> (select idinvoice,idcustomer from tblinvoice where type='1' ) A inner
>> join
>> (select idinvoice,idcustomer from tblinvoice where type='2') B on
>> A.idcustomer=B.idcustomer
>> and not exists (select 'true' from tblinvoice C where
>> C.idcustomer=A.idcustomer and C.type not in ('1','2'))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>Where does the Types wanted table come from. Is this like a many to many
>relationship with customers having many invoices and invoices types having
>many invoices?
Hi Chris,
The TypesWanted table is where you (temporarily) store the types you are
looking for. Your question was:
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
To find customer with types 1 and 3 and 5, just run
DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
INSERT TypesWanted (Type) VALUE(1);
INSERT TypesWanted (Type) VALUE(3);
INSERT TypesWanted (Type) VALUE(5);
And then run the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Works great. Cheers.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e23k311ik0teriodjc3ik2u6jie07kh8j4@.4ax.com...
> On Thu, 17 Mar 2005 16:16:24 -0000, Chris Kennedy wrote:
>>Where does the Types wanted table come from. Is this like a many to many
>>relationship with customers having many invoices and invoices types having
>>many invoices?
> Hi Chris,
> The TypesWanted table is where you (temporarily) store the types you are
> looking for. Your question was:
>>I need to show customers with invoices of invoice type 1 AND 3 but then
>>wanted to narrow it down even further to 1 AND 3 AND 5 and so on.
> To find customer with types 1 and 3 and 5, just run
> DELETE FROM TypesWanted; -- No where - everything gets dispatched of.
> INSERT TypesWanted (Type) VALUE(1);
> INSERT TypesWanted (Type) VALUE(3);
> INSERT TypesWanted (Type) VALUE(5);
> And then run the query.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 19, 2012

another sp_repladdcolumn option question

Another Row After SubTotal in Matrix

Sunday, March 11, 2012

Another Question: Replication

Another problem with XQuery!

Another NQ: Inserting Image

Thursday, March 8, 2012

another freetexttable question

Wednesday, March 7, 2012

Another Date time question

Friday, February 24, 2012

annotated schema and bulk loading into multiple tables...

AND query in single column

AND and One to many relationships

AND and One to many relationships

AND and One to many relationships

SQL server connection

Blog Archive

About Me