Query with union

Hello Group!
I submmit a SP with a update query that use multiple UNION.
This SP fire multiple sub-threads for the same SPID and fail with deadlock condition.
There is a way for suppress that sub-threads ?
TIA,
Aldair.Need more info on your sp. I'd be leary about running update queries against Union joins. You might be better off using your union query to create a temporary table or table variable that holds the index keys of the values you want to update, and then update your values in a second step.

blindman|||The threads shoukd all enlist in the same transaction and not cause a deadlock on it's own.

If you think it is causing a problem the try it with maxdop=1|||Thanks for all.

My update comand that has a where condition with several UNIONs fail when run at the production server (more than one CPU) but it work fine at test server (single CPU). This look like a bug!

When I use the OPTION clause MAXDOP=1, it work at the production box.

Thank you for help.

Aldair.|||Do you have the latest service pack?
There have been a number of bugs fixed to do with this.|||Yes, I do.

Microsoft SQL Server 2000 - 8.00.760

This is the SP 3, isnt it ?

We use the SQL Profiler to see the procedures steps and during the update the SQL Server build a multi-thread plan (we can see it using SP_WHO: SPID= n, ECID= 0,1,2,3,4), and at this point the procedure failed with error 1205 (Dead Lock victim).

Thank you for your help.

Aldair

Query with multiple tables Use of JOIN vs WHERE

I have inherited a database, written a few years back and the people who
designed the stored procedures seem to do things differently than I learned
and it seems to work faster, but I cannot figure out why. I check the
execution plans and they appear identical, but the statistics show a HUGE
(to me) difference in reads (147 for the first method and 40 for the second)
. The second method takes about 10 seconds less to return the results. (O
f
course the code is wrapped in a stored procedure - but I was curious about
this "new" way of doing things so I extracted the select statements to do
comparisons).
I'm sure that the explanation is simple and is just an area of SQL coding I
hadn't been exposed to yet.
Any info appreciated,
Nancy
CODE:
It is a simple query to get a count joining 3 tables
I would normally do the query using:
Select count(CODE1)
from Table1
JOIN x_hcfa_cpt
ON
Table1.CLAIMNO = Table2.CLAIM_NUMBER
JOIN x_hcfa_cpt_mas
ON
Table1.TERM_NUMBER = Table3.TERM_NUMBER
where
Table1.TERM_NUMBER = 'asdftcdww'
AND
(Table1.STATUS='false'
or
Table1.STATUS='true')
and
Table1.FLAG='false'
But in the code I inherited they used:
Select count(CODE1)
from Table1, Table2, Table3
where
Table1.X_TERMINAL_NUMBER = 'asdftcdww'
AND
Table1.CLAIMNO = Table2.CLAIM_NUMBER
and
Table1.TERM_NUMBER = Table3.TERM_NUMBER
and
(Table1.STATUS='false'
or
Table1.STATUS='true')
and
Table1.FLAG ='false'> Select count(CODE1)
> from Table1, Table2, Table3
This is non-standard code and should be avoided (especially for outer joins
due to non-conforming behavior, but for inner joins as well). My suggestion
is to re-write the code with JOIN statements, and to avoid ambiguity, I make
it a standard practice to include the type of JOIN, so I would use the INNER
keyword as well (even though it is the default).|||There should be no difference between SQL-92 JOINs (1st statement) and
the older style (2nd statement).
I'm guessing that x_hcfa_cpt and x_hcfa_cpt_mas are Table2 and Table3 in
the first query?
Also, in the first query, the where clause uses Table1.TERM_NUMBER
whereas the 2nd query uses Table1.X_TERMINAL_NUMBER. Could that be the
difference in performance?
Nancy Lytle wrote:

>I have inherited a database, written a few years back and the people who
>designed the stored procedures seem to do things differently than I learned
>and it seems to work faster, but I cannot figure out why. I check the
>execution plans and they appear identical, but the statistics show a HUGE
>(to me) difference in reads (147 for the first method and 40 for the second
)
>. The second method takes about 10 seconds less to return the results. (O
f
>course the code is wrapped in a stored procedure - but I was curious about
>this "new" way of doing things so I extracted the select statements to do
>comparisons).
>I'm sure that the explanation is simple and is just an area of SQL coding I
>hadn't been exposed to yet.
>Any info appreciated,
>Nancy
>CODE:
>It is a simple query to get a count joining 3 tables
>I would normally do the query using:
>Select count(CODE1)
>from Table1
>JOIN Table2
>ON
>Table1.CLAIMNO = Table2.CLAIM_NUMBER
>JOIN Table3
>ON
>Table1.TERM_NUMBER = Table3.TERM_NUMBER
>where
>Table1.TERM_NUMBER = 'asdftcdww'
>AND
> (Table1.STATUS='false'
> or
> Table1.STATUS='true')
>and
> Table1.FLAG='false'
>But in the code I inherited they used:
>Select count(CODE1)
>from Table1, Table2, Table3
>where
>Table1.X_TERMINAL_NUMBER = 'asdftcdww'
>AND
>Table1.CLAIMNO = Table2.CLAIM_NUMBER
>and
>Table1.TERM_NUMBER = Table3.TERM_NUMBER
>and
>(Table1.STATUS='false'
>or
>Table1.STATUS='true')
>and
>Table1.FLAG ='false'
>
>|||On Tue, 13 Sep 2005 16:37:16 -0400, Nancy Lytle wrote:

>I have inherited a database, written a few years back and the people who
>designed the stored procedures seem to do things differently than I learned
>and it seems to work faster, but I cannot figure out why.
(snip)
Hi Nacny,
I'll assume that the different table names and column names are a result
of you renaming some tables and columns when preparing the post, and the
code you actually tested this on didn't have these differences :-)
The difference in the queries are the two different styles of join
notation. In old versions of SQL Server (note that I'm talking real old
here - older than SQL Server 6.5), only the version with the
comma-delimited list of tables is allowed. The more verbose version with
infixed join operators was added later, to adhere to the ANSI standard.
For inner joins, there is absolutely no difference between the two
versions. They are both defined in the ANSI standard, both acccepted by
SQL Server and they will both return the same results. They'll also use
the same execution plan, so that there's no performance difference
either.
For outer joins, things are different. The "old-style notation" (that
uses =* and *= in the WHERE clause to define inner and outer tables) is
ambiguous. It's not defined in the ANSI standard. MS has announced that
it will drop support for =* and *= in a future version. In fact, I
recall reading somewhere that SQL Server 2005 will only accept =* and *=
in the backward compatibility mode.

> I check the
>execution plans and they appear identical, but the statistics show a HUGE
>(to me) difference in reads (147 for the first method and 40 for the second
)
>. The second method takes about 10 seconds less to return the results.
Did you run both tests on an empty cache? I suspect not - and that's
what causes the difference.
Test it like this:
DECLARE @.start datetime
DECLARE @.end datetime
-- Flush all dirty buffers to disk
CHECKPOINT
-- Remove all previously read pages from the data cache
DBCC DROPCLEANBUFFERS
-- Remove all previously compiled execution plans as well
DBCC FREEPROCCACHE
-- Now start the real test
SET @.start = CURRENT_TIMESTAMP
#### ####
#### YOUR QUERY GOES HERE ####
#### ####
SET @.end = CURRENT_TIMESTAMP
SELECT @.start AS StartTime,
@.end AS EndTime,
DATEDIFF(ms, @.Start, @.End) AS "Elapsed (ms)"
If the code that you are testing returns many rows, change the SELECT to
a SELECT ... INTO #temp_table to eliminate the speed of the network and
the display speed of your client from the equation.
I'd be VERY surprised if you still get significant differences if you
test the queries like this. (Small differences are to be expected,
especially if the server you're running this on has other things to do
as well).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo, Trey and Aaron!
I guess there is more sql code I am going to have to change, the SP's (and
they are all named sp_ !) are dotted with uses of comma delimited lists of
tables for joins and usage of *=, not to mention tons of select *'s, and the
sp_ naming convention.
I used Hugo query and that helped me see the real difference between the
two, which is actually very slight and leans toward the use of JOINs.
Thanks again, this is a great group!
Nancy
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1fei1paaf5g8qo9ef509q9cqocdmds466@.
4ax.com...
> On Tue, 13 Sep 2005 16:37:16 -0400, Nancy Lytle wrote:
>
> (snip)
> Hi Nacny,
> I'll assume that the different table names and column names are a result
> of you renaming some tables and columns when preparing the post, and the
> code you actually tested this on didn't have these differences :-)
> The difference in the queries are the two different styles of join
> notation. In old versions of SQL Server (note that I'm talking real old
> here - older than SQL Server 6.5), only the version with the
> comma-delimited list of tables is allowed. The more verbose version with
> infixed join operators was added later, to adhere to the ANSI standard.
> For inner joins, there is absolutely no difference between the two
> versions. They are both defined in the ANSI standard, both acccepted by
> SQL Server and they will both return the same results. They'll also use
> the same execution plan, so that there's no performance difference
> either.
> For outer joins, things are different. The "old-style notation" (that
> uses =* and *= in the WHERE clause to define inner and outer tables) is
> ambiguous. It's not defined in the ANSI standard. MS has announced that
> it will drop support for =* and *= in a future version. In fact, I
> recall reading somewhere that SQL Server 2005 will only accept =* and *=
> in the backward compatibility mode.
>
> Did you run both tests on an empty cache? I suspect not - and that's
> what causes the difference.
> Test it like this:
> DECLARE @.start datetime
> DECLARE @.end datetime
> -- Flush all dirty buffers to disk
> CHECKPOINT
> -- Remove all previously read pages from the data cache
> DBCC DROPCLEANBUFFERS
> -- Remove all previously compiled execution plans as well
> DBCC FREEPROCCACHE
> -- Now start the real test
> SET @.start = CURRENT_TIMESTAMP
> #### ####
> #### YOUR QUERY GOES HERE ####
> #### ####
> SET @.end = CURRENT_TIMESTAMP
> SELECT @.start AS StartTime,
> @.end AS EndTime,
> DATEDIFF(ms, @.Start, @.End) AS "Elapsed (ms)"
> If the code that you are testing returns many rows, change the SELECT to
> a SELECT ... INTO #temp_table to eliminate the speed of the network and
> the display speed of your client from the equation.
> I'd be VERY surprised if you still get significant differences if you
> test the queries like this. (Small differences are to be expected,
> especially if the server you're running this on has other things to do
> as well).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 14 Sep 2005 08:59:40 -0400, Nancy Lytle wrote:

>Thanks, Hugo, Trey and Aaron!
>I guess there is more sql code I am going to have to change, the SP's (and
>they are all named sp_ !) are dotted with uses of comma delimited lists of
>tables for joins and usage of *=, not to mention tons of select *'s, and th
e
>sp_ naming convention.
>I used Hugo query and that helped me see the real difference between the
>two, which is actually very slight and leans toward the use of JOINs.
>Thanks again, this is a great group!
>Nancy
Hi Nancy,
I guess that the "very slight" difference you see falls within the
bounds of statistic inaccuracy. If you repeat the test a few times, you
should see that there really is no difference between the two.
As far as rewriting code, I'd say: find the right path between
religiously rewriting everything (costly, time-consuming, and will
introduce bugs, if only by typo's and copy/paste errors) on the one end,
and leaving working code untouched on the other end.
If you decide to start rewriting where it's needed most, then begin with
the use of =* and *= for outer joins, as they are on the deprecated
feature list. Next should be the sp_ prefix and the use of SELECT *
(both are performance killers in their own ways; both induce a risk of
unexpectedly breaking your code when some change is made elsewhere).
The join syntax for inner joins (i.e. the use of comma-delimited table
list without any =* or *=) should be last on your list, as this is only
a readability improvement (and not all experts would agree that it's an
improvement - there are a few SQL experts who think that the "old style"
join notation is often better, though I'm not one of them).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the suggestions.
I did a complete search and the locations for the *= code is in dynamic sql
written in .asp pages. So, since I am changing the dynamic sql to a stored
procedure, I can get rid of the *= and select * and created proper procedure
naming all at the same time.
I will wait to modify the names of the sp_'s that are already written until
I have a chance to sit down with the other programmers, etc, and we come up
with a plan. My initial thought was to simply recreate the SPs changing
only the name, so we would have essentially 2 sp's that did the same thing,
just one sp_ and one usp_ names. Then we could start cutting over the names
in the code without breaking anything, that couldn't be fixed almost
immediately.
But this is my first time really taking on a task like this, does this sound
like a plan?
Do you have any articles or books to recommend to a new DBA/developer?
Thanks again,
Nancy
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3rvgi15u21emupk0gfqe9pl5dpfk8kgl0s@.
4ax.com...
> On Wed, 14 Sep 2005 08:59:40 -0400, Nancy Lytle wrote:
>
> Hi Nancy,
> I guess that the "very slight" difference you see falls within the
> bounds of statistic inaccuracy. If you repeat the test a few times, you
> should see that there really is no difference between the two.
> As far as rewriting code, I'd say: find the right path between
> religiously rewriting everything (costly, time-consuming, and will
> introduce bugs, if only by typo's and copy/paste errors) on the one end,
> and leaving working code untouched on the other end.
> If you decide to start rewriting where it's needed most, then begin with
> the use of =* and *= for outer joins, as they are on the deprecated
> feature list. Next should be the sp_ prefix and the use of SELECT *
> (both are performance killers in their own ways; both induce a risk of
> unexpectedly breaking your code when some change is made elsewhere).
> The join syntax for inner joins (i.e. the use of comma-delimited table
> list without any =* or *=) should be last on your list, as this is only
> a readability improvement (and not all experts would agree that it's an
> improvement - there are a few SQL experts who think that the "old style"
> join notation is often better, though I'm not one of them).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 15 Sep 2005 09:51:15 -0400, Nancy Lytle wrote:

>Thanks for the suggestions.
>I did a complete search and the locations for the *= code is in dynamic sql
>written in .asp pages. So, since I am changing the dynamic sql to a stored
>procedure, I can get rid of the *= and select * and created proper procedur
e
>naming all at the same time.
Hi Nancy,
Wow, that's a major improvement - getting rid of two major pitfalls at
once!

>I will wait to modify the names of the sp_'s that are already written until
>I have a chance to sit down with the other programmers, etc, and we come up
>with a plan. My initial thought was to simply recreate the SPs changing
>only the name, so we would have essentially 2 sp's that did the same thing,
>just one sp_ and one usp_ names. Then we could start cutting over the name
s
>in the code without breaking anything, that couldn't be fixed almost
>immediately.
>But this is my first time really taking on a task like this, does this soun
d
>like a plan?
Discussing things with the developers is definitely a great idea. As
long as your modifications are invisible to them (such as replacing
dynamic =* crap with non-dynamic OUTER JOINs), you could do you work in
silence (though I'd even recommend communicating your actions in that
case). But if your changes are going toa ffect the developers (and they
will if you intend to eventually remove the badly named stored
procedures), they should be informed, and invited to participate.
But if you are renaming, then I'd just drop the prefix completely. I've
never managed to see the added value of
EXEC usp_MakeMonthlyReport
over
EXEC MakeMonthlyReport

>Do you have any articles or books to recommend to a new DBA/developer?
http://www.aspfaq.com/show.asp?id=2423
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query with multiple tables

How can i xquery the results from multiple tables?What exactly are you trying to do, combine the native XML or shredded ones?
Can you post your table DDL with some sample data? Note that XML typed
values cannot be compared, sorted or grouped and hence cannot be in direct
SQL predicates.
Anith|||I got a table with XML-Data about books, in an other table i stored XML-Data
about authors. Now i want to combine the data using xquery, but i do not kno
w
how to query over multiple tables.

Query with multiple ID to check and response...

Hi there, I tried to make it different as usual but i′m stacked into this problem:

Supose TABLE Details
ID_DET ID_CAR DETAILS
1 3 1,2,3,4,5,6
2 4 2,4
3 5 5,6,7,8

and
TABLE Details_Items
ID_DI DETAIL_NAME
1 Stereo HiFi CD
2 Alarm
3 AirConditioning
4 LeatherSeats
5 Pro Wires
6 Aluminium Wheels

The problem appears when i need to bring CAR DETAILS (NAME) from TABLE DETAIL_ITEMS.
Mi guess i that I should make something like:
SELECT * FROM DETAILS_NAME WHERE id_di = (( Array(i) FROM Details )) one by one...

I really dont know how to face it.

First I thought in bringing ALL details_Items (datafieldtext = id_di and datavaluetext=Details_names) into a dataview.
And then "somohow?" filter this dataview according with the Array previuosly splited by me with a For each function.

Then I thought "Perhaps" there is a simpliest way to do that using SQL views, o advanced SQL QUERYS.

and Finally I thought that creating a VIEW in for both TABLES would be great.

The point is that, neither 1,2,3 options, honestly , I dont know how to face them.

Thanks in advance, apologise my "rude" English grammar.

LUCAS ( From Argentina )

Are you saying that you are storing numeric values as a comma separated list in the DETAILS column? This is very bad database design. You should use a link table instead. Create a new table called DETAIL_IDS ?with two columns:?ID_DET and DETAIL_ID

eg.

?ID_DET ???DETAIL_ID
?1 ?????????????1
?1 ?????????????2
?1 ?????????????3
?1 ?????????????4
?1 ?????????????5
?1 ?????????????6
?2 ?????????????4
?2 ?????????????6

etc.|||

This is a very bad way to store IDs in string field because of speed and problems you have now. If you do not wont to change your database structure you can do something like this:

declare @.lcCommand as varchar(8000)

SELECT @.lcCommand = 'SELECT * FROM DETAILS_NAME WHERE id_di in (' + DETAILS + ') '
from Details
where ID_Car=3 -- for car with ID 3

Exec (@.lcCommand)

This is not recommended way to execute select statement but in your case is probably the simplest way. Another way would be to create table returned function which will convert comma delimited string to table and just select from this table. The advantage is that you will not build command string which is safest, and store procedures will be precompiled so works faster.

Thanks

JPazgier

sql

Query with multiple arguments?

Hi, I'm a bit stumped as to how to do this.
I have a string[] with a list of users, and I want to query my database to select only the users in this array and bind the datasource to a GridView, but I don't know how to write an SQL query to search for multiple results from the same field.

E.g. Say I have two results in my string[], fred and bob.
How can I select data from the database for just those two users - "SELECT * FROM tblUsers WHERE UserName='bob' AND ??";

IF this is possible, I also need to bind it to a gridview. I tried the following, but it didn't work as I needed it to:

for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}

That 'worked', but as I'm sure you can see, the data that was bound to the gridview was only the last result found, not the whole result set.

Any help is greatly appreciated.

schuminator:

for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}

try out as below

 String strUsers = String.Empty;for (int a = 0; a < userArray.Length; a++) strUsers = strUsers + @."'" + userArray[a] + @."',"; strUsers = strUsers.Substring(0, strUsers.Length - 1); conn.Open(); SqlCommand command =new SqlCommand("SELECT * FROM tblUsers WHERE UserName in (" + strUsers +")", conn); SqlDataReader reader = command.ExecuteReader(); grid.DataSource = reader; grid.DataBind(); conn.Close();

Good Luck./.

|||

Sorry, I tried to delete the thread but it was too late.
I got it sorted...it was so simple, I feel like an idiot!

"SELECT * FROM tblUsers WHERE UserName='fred' OR UserName='bob'" etc

I also wrote a little for loop to add another OR... to the end of the string when necessary.

|||

you can use above method also...

so instead of UserName='fred' OR UserName='bob'"......

if will formulate query using IN keywork as..

UserName in ('fred','bob')

|||

ahh ok awesome, thanks :)

Query using multiple tables

Hi, I have a problem which I thought it has a simple solution but now I'm not even sure it is possible.

I have 3 tablesClients <-ooClientContacts oo->Contacts
(the <-oo means one to may relation between the tables)

A Client may have related none, one or many Contact records. The table ClientContacts is the link that stores that information. The field ClientContacts.Category represents the type of the contact and it will be used in queries. It may be owner, accountant, employee, etc.

My goal is to run a query which will return

Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
WHERE (Clients.WorkOnHold = 0)

The result should return values for
Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress] if the Client has attached Contact records filtered by category,
and '','','' or <NULL>,<NULL>,<NULL> if the Client does not have any Contact records.

I tryed an INNER JOIN but it will return juts the records having contact information.

Any solutions are appreciated.
Thanks.

Clients

CREATE TABLE [Clients] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [varchar] (100),
[MailingStreet] [varchar] (50),
[MailingCity] [varchar] (35),
[MailingState] [varchar] (35) ,
[MailingZip] [varchar] (10),
[WorkOnHold] [bit] NULL ,
[ClientNotes] [varchar] (500),
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Contacts

CREATE TABLE [Contacts] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL ,
[JobTitle] [varchar] (50),
[BusinessStreet] [varchar] (50),
[BusinessCity] [varchar] (35),
[BusinessState] [varchar] (35),
[BusinessPhone] [varchar] (20),
[BusinessFax] [varchar] (20),
[E-mailAddress] [varchar] (255),
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
(
[ContactID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

ClientContacts

CREATE TABLE [ClientContacts] (
[ClientID] [int] NOT NULL ,
[ContactID] [int] NOT NULL ,
[Category] [varchar] (50),
CONSTRAINT [FK_ClientContacts_Clients] FOREIGN KEY
(
[ClientID]
) REFERENCES [Clients] (
[ClientID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_ClientContacts_Contacts] FOREIGN KEY
(
[ContactID]
) REFERENCES [Contacts] (
[ContactID]
) ON DELETE CASCADE
) ON [PRIMARY]
GO

The INNER JOIN I tryed but is not good. It returns just clients having contacts attached.

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState, Contacts.FirstName, Contacts.LastName,
 Contacts.[E-mailAddress]
FROM ClientContacts INNER JOIN
 Clients ON ClientContacts.ClientID = Clients.ClientID INNER JOIN
 Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold = 0)

You will need a to use LEFT OUTER JOIN from Clients to the other tables:

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState, Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
FROMClients LEFT OUTER JOIN
 ClientContacts ON ClientContacts.ClientID = Clients.ClientID INNER JOIN
 Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold = 0)

|||Thanks for the attempt.
I tried it and it is not good !
It still returns only Clients having Contacts attached.

Any other solutions?
Thanks.|||I think I found the solution.
The first join has to be a LEFT OUTER JOIN (as ehorn pointed), but the second one should be a FULL OUTER JOIN.

SELECT Clients.Company, Clients.MailingStreet, Clients.MailingCity, Clients.MailingState,
 Contacts.FirstName, Contacts.LastName, Contacts.[E-mailAddress]
FROM Clients
LEFT OUTER JOIN ClientContacts ON ClientContacts.ClientID = Clients.ClientID
FULL OUTER JOIN Contacts ON ClientContacts.ContactID = Contacts.ContactID
WHERE (Clients.WorkOnHold=0)

This code seems to work fine. I hope it is not just a coincidence the fact I get the result I expected.

Thanks.

Query to View with Many Columns

Hi All,

I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?

Does anyone have idea how to join table that can reflect both left and right join?

Table A Table B

Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5

A Jan 5 xxx A Jan 1 yyy

B Feb 3 C Mar 4

B Mar 4 C Apr 3

C Apr 2 D May 2

E Mar 1

Result Table

Column1 Column2 Column3 Column4 Column 5

A Jan 6 (= 5+1) xxx yyyy

B Feb 3

B Mar 4

C Mar 4

C Apr 5 (= 2+3)

D May 2

E Mar 1

So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).

Thanks for the help.

The VIEW will first create a virtual table containing ALL of the columns, and then you will get a resultset of just the columns requested.

That could be very inefficient. Most likely, it would be better to query against the underlaying table for just the columns required. Or create a separate VIEW for this purpose.

|||

Thanks for the answer .

The problem is this query will be executed by Report Builder, so all of those columns will be needed as user might select any combination to include in the report, so it's quite difficult to provide only certain column by creating another view. I already try to separate the View into multiple entity in Report Builder, but when it comes to the aggregate, some of the calculation is calculated wrongly in the Report Builder, and some complexity with multiple currency/exchange rate araise, that force me to add more column inside the view .

Any idea or suggestion for my situation? Thanks

|||

You haven't provided enough detail for anyone to offer cogent suggestions.

However, one thing comes to mind. You might be able to use a Stored Procedure that Creates a custom VIEW (from input parameters), and then returns the resultset from that VIEW.

Query to update 1 record in a duplicate set of records

How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.

Without more info it's hard to tell, but you just need to qualify what you want to update:

UPDATE Table
SET column = 'New Value'
WHERE column = 'youNeedADateHere'

AND orderid = 'Whatever'
GO

|||Thanks for your response but I can't specify an orderid because there are 3612 records and within those 3612 records some of the id's are dups. The query above would do a specific orderid and a specific date. The dates are all different I just want the most recent per orderid. Thank you anyway.|||

Something along the lines of:

UPDATE Table1

SET NewCol = 1

FROM Table1 AS i

INNER JOIN (SELECT OrderID, COUNT(*) AS c, MAX(OrderDate) AS OrderDate

FROM Table1

GROUP BY OrderID

HAVING COUNT(*) > 1) AS Dupes

ON i.OrderID = Dupes.OrderID

AND i.OrderDate = Dupes.OrderDate

|||Thank you that is close enough to what I was looking for. I appreciate your response

Query to return multiple date rows

HI there,

Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example:

1636 1073746475 342 2005-12-30 00:00:00.000
1636 1073746475 359 2006-03-10 00:00:00.000

This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please?

Thanks!!!!!

Sselect security,
count(distinct datevalue) as datecount
from [yourtable]
group by security
having count(*) > 1|||You handle all the tough questions...time to shovel snow|||Thanks that's a great help, much appreciated!!!

S

Query to return latest record, multiple join fields

Hi
I need to write a query that returns the latest value(s) from a table,
'grouped' by the primary key (multiple fields), and the criteria to
derive the latest record is also based on multiple fields.
I have put together the DDL below as a simplified example, and want to
write a query that returns the following resultset:
company--project--value--
1 1 'fifth value'
1 2 '.2 fifth value'
2 1 '2 fifth value'
(KEY: company + project)
(LATEST RECORD: year + batch + item)
Thanks for any help
Sean
---
CREATE TABLE mytable (company INT, project INT, [year] int, batch int,
item int, value varchar(35))
INSERT INTO mytable VALUES (1, 1, 2003, 1, 1, 'first value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 2, 'second value')
INSERT INTO mytable VALUES (1, 1, 2003, 1, 3, 'third value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 1, 'fourth value')
INSERT INTO mytable VALUES (1, 1, 2003, 2, 2, 'fifth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 1, 'sixth value')
INSERT INTO mytable VALUES (1, 1, 2002, 1, 2, 'seventh value')
INSERT INTO mytable VALUES (1, 1, 2002, 2, 1, 'eighth value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 1, '.2 first value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 2, '.2 second value')
INSERT INTO mytable VALUES (1, 2, 2003, 1, 3, '.2 third value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 1, '.2 fourth value')
INSERT INTO mytable VALUES (1, 2, 2003, 2, 2, '.2 fifth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 1, '.2 sixth value')
INSERT INTO mytable VALUES (1, 2, 2002, 1, 2, '.2 seventh value')
INSERT INTO mytable VALUES (1, 2, 2002, 2, 1, '.2 eighth value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 1, '2 first value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 2, '2 second value')
INSERT INTO mytable VALUES (2, 1, 2003, 1, 3, '2 third value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 1, '2 fourth value')
INSERT INTO mytable VALUES (2, 1, 2003, 2, 2, '2 fifth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 1, '2 sixth value')
INSERT INTO mytable VALUES (2, 1, 2002, 1, 2, '2 seventh value')
INSERT INTO mytable VALUES (2, 1, 2002, 2, 1, '2 eighth value')
---This table doesn't appear to have a primary key. I'll assume that the key is
supposed to be (company,project,year,batch,item). I've also assumed that the
batch and item numbers are in the range 0-999. If not, you'll have to amend
the YBI calculation accordingly.
SELECT T.company, T.project, T.value
FROM MyTable AS T
JOIN
(SELECT company, project,
MAX([year]*1000000+batch*1000+item) AS ybi
FROM Mytable
GROUP BY company, project) AS M
ON T.company = M.company
AND T.project = M.project
AND T.[year]*1000000+T.batch*1000+T.item = M.ybi
--
David Portas
--
Please reply only to the newsgroup
--sql

Query to concatenate results from multiple rows

I have a database where comments are stored in a separate table where the comment is split into max 80 char lengths and stored in separate rows.

eg.

RecordID Comment
001 This is a comment and the nex
001 t bit of the comment appears o
001 n the next line.
002 This is the start of the next com
002 ment.

I need a SQL query that will put the text back together again.

Many thanks
MUHow do you determine which order the segments should be assembled? Can they be put together in random order, or is there a definite sequence?

Do you want a solution that is simple, but SQL dialect specific, or do you want a generic solution that will work with most/all SQL dialects?

Do you want a solution for a single ID, or does it need to be able to work for the entire table in a single operation?

-PatP|||Pat,
Thanks for the response.

There is a LineNum field in the table to order the comments by.

The solution only needs to work with SQLServer.

Ideally I am looking for a solution that produces an entire set of rows showing details from a master table with the comment appearing from this table as a single field with the RecordID being used as the join field.

MarkU|||Ok, if you need to process multiple rows in a single set operation (ie SELECT statement), the best answer I've got is:CREATE TABLE #phrog (
recordId CHAR(3)
, comment VARCHAR(80)
, lineNum INT)

INSERT INTO #phrog (recordID, comment, lineNum)
SELECT '001', 'This is a comment and the nex', 1
UNION ALL SELECT '001', 't bit of the comment appears o', 2
UNION ALL SELECT '001', 'n the next line.', 3
UNION ALL SELECT '002', 'This is the start of the next com', 1
UNION ALL SELECT '002', 'ment.', 2

SELECT a.recordID, a.comment + Coalesce(b.comment, '') + Coalesce(c.comment, '')
FROM #phrog AS a
LEFT JOIN #phrog AS b
ON (b.recordID = a.recordID
AND b.lineNum = (SELECT Min(z1.lineNum)
FROM #phrog AS z1
WHERE z1.recordID = a.recordID
AND a.lineNum < z1.lineNum))
LEFT JOIN #phrog AS c
ON (c.recordID = a.recordID
AND c.lineNum = (SELECT Min(z1.lineNum)
FROM #phrog AS z1
WHERE z1.recordID = a.recordID
AND b.lineNum < z1.lineNum))
WHERE a.lineNum = (SELECT Min(z0.lineNum)
FROM #phrog AS z0
WHERE z0.recordID = a.recordID)

DROP TABLE #phrogBe forewarned that this code raises the kludge factor of the universe significantly, but it does work.

-PatP|||Many thanks for your help - I will check this out.

What I don't quite understand is that since I don't know upfront how many lines of comments there may be or what is in them, how can I do the UNION statements?

I was hoping that there would be some form of the UNION statement where I could say UNION ALL comment WHERE recordId = n (or similar).

MarkU|||On second thought, lets apply a very "Oracle-ish" solution. You could also use:CREATE TABLE dbo.phrog (
recordId CHAR(3)
, comment VARCHAR(80)
, lineNum INT)

INSERT INTO dbo.phrog (recordID, comment, lineNum)
SELECT '001', 'This is a comment and the nex', 1
UNION ALL SELECT '001', 't bit of the comment appears o', 2
UNION ALL SELECT '001', 'n the next line.', 3
UNION ALL SELECT '002', 'This is the start of the next com', 1
UNION ALL SELECT '002', 'ment.', 2
GO

CREATE FUNCTION dbo.phrogComment(@.recordID CHAR(3))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE
@.c VARCHAR(8000)
, @.r VARCHAR(8000)

SET @.r = ''

DECLARE z CURSOR FOR SELECT
comment
FROM dbo.phrog
WHERE recordID = @.recordID
ORDER BY lineNum

OPEN z
FETCH z INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z INTO @.c
END

CLOSE z
DEALLOCATE z

RETURN @.r
END
GO

SELECT a.recordID, dbo.PhrogComment(a.recordID)
FROM dbo.phrog AS a
GROUP BY a.recordID

DROP FUNCTION dbo.phrogComment
DROP TABLE dbo.phrogThis will grieviously disturb the relational purist (me included), but it will get the job done quickly and simply.

-PatP|||I tried the second bit of code on my own tables, and it almost works perfectly. The problem I have is that the concatenated field being returned is being truncated at 256 total characters/spaces, yet I need it to be larger.

I tried to use a CAST on the PhrogComment(a.ID), as well as changing the VARCHAR sizes for @.c and @.r and the RETURNS value, all to no avail.

Any suggestions on how I could tweak the code to make the result "larger"?

Thanks,

Mark|||'taint the SQL code what's cuttin' ya off. It's the client.

In Query Analyzer:

1) Press shift-control-o to bring up the Options window.
2) Click the results tab.
3) At the right edge, near the middle, type in whatever column width seems kozy but not extravagant.
4) Re-run your query for optimum viewing pleasure!

Sorry if I'm a bit punchy... Things could charitably be described as "interesting" today.

-PatP|||Praise God! I've been losing my mind for the last 24 hours (it's been - how did you say it? - "interesting" :-)

Thanks so much. I should've known to blame it on SQL Query Analyzer - I've had some queries not work (i.e., a query will return 0 rows and throw no errors) in the Analyzer yet the same query works (return the expected results) if cut and pasted into and then run as a stored procedure - go figure.

Then again, I'm an econ major so the problem is probably behind the keyboard...

Mark|||Are you just wanting to do this for one message at a time in your procedure or are you wanting to return several messages.

Query that adds data for every user

Dear Masters;

I have a Messages table; I use this table to post System Messages to my users. But what I don't know is how can I add data for multiple users. I mean I want to add same message for multiple users (ex: Please update your infos). In the belove table I have some messages for users 100 and 200; How can I add same (please update your infos) messages to both users?

Ex: Messages Table

ID UserID Msg

1 100 "Hello"

2 100 "Hi"

3 200 "Hello"

Thanks..

try this to add your message to current message

UPDATE Messages
SET msg=msg + ' Please update your infos '
where userID in (100,200)

or this to create new content for message

UPDATE Messages
SET msg = 'Please update your infos '
where userID in (100,200)

Thanks

|||

Thanks for repliying;

But may I write a select statement for example in the in() section?

Cause I may need to add for all users so I gues it should be something like "for" statement.. But if I can write select statement inside in() That may solve...

I'll try thanks

|||

Yes, you can try for example

where userID in (select UserID from messages where userID>100)

but your select statement should return only one column.

Thanks

|||I don't think you want an update statement for this purpose. If you update where UserID = 100, you will update both records 1 and 2. You will need to do multipl einsert statements. You can do this inside a stored procedure that you pass paramaters to (ie. parameter 1 is the message and parameter 2 is a comma seperated list of user ids). If you are not comfortable with stored procedures, you will need to construct and execute multiple insert statements in your code. Don't forget to use a transaction in case of failure.|||

Hi;

I guess I'm a little bit confused:) Could you explain me more deeply? I think I'm ok with stored procedures but what should this stored procedure include?

don't you think if J's solution works? I mean why shloud it update both records?

Thanks

|||

In your example table structure you have the same userID for 2 first records so f you do update for userID=100 both records will be updated. If you would like to do it for specific one you have to use ID column to select only one record. It was mistake or you can have two entries for the same userID in your table?

Thanks

|||

Oh i c;

Thanks, I will actually insert a new row I'm not gonna update so problem solved thanks...

Thanks to both masters;)

|||Here is an article on how to write a stored proc to take a comma seperated value (CSV) list into a stored proc. You should be able to adapt this for your own needs.

Query takes more time from user interface or in a network

I execute a pretty big sql query which joins multiple tables and I reviewed indexes on all these tables. I am happy with the result when I run the query using SSMS in the server locally i.e., where my SQL Server database is installed. It takes 4 seconds to get around 17000 records. If I run the same query in a network or from my desktop using SSMS i.e., i connect to the above mentioned SQL Server using SSMS, it takes more than 60 seconds. Not sure how to solve this. If someone could help me, it will be of great help.

Thanks.

Is the server local to you or installed in another office?

When you run the query on your machine enable the Client Statistics (second row of buttons at the top, next to the Actual Execution Plan button). What is the output from this?

Query table based on multiple keys

Hey,

I am having some confusion about how to formulate this particular
query.
I have 2 tables. Table A has 4 columns say a1,a2,a3,a4 with the
columns a1,a2,a4 forming the primary key. Table B again has 3 columns
with b1,b2,b3,b4 and like before, b1,b2 and b4 form the primary key.
All columns are of the same datatype in both tables. Now I want to get
rows from table A which are not present in table B. Whats the best way
of doing this?

Thanks

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict235166.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815725I'm no expert, so this probably isn't the most efficient way to do
this, but I think this will work:

select A.* from A, B
where A.a1 *= B.b1
and A.a2 *= B.b2
and A.a4 *= B.b4
and B.b1 is null

--Richard|||SELECT a1, a2, a3, a4
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.a1 = B.b1
AND A.a2 = B.b2
AND A.a3 = B.b3
AND A.a4*= B.b4 );|||
--CELKO-- wrote:
> SELECT a1, a2, a3, a4
> FROM A
> WHERE NOT EXISTS
> (SELECT *
> FROM B
> WHERE A.a1 = B.b1
> AND A.a2 = B.b2
> AND A.a3 = B.b3
> AND A.a4*= B.b4 );

Yeah, that works better than my version. I just tested a little more
and realized that mine doesn't actually do what I expected, but I can't
figure out why not.

--Richard|||--CELKO-- (jcelko212@.earthlink.net) writes:
> SELECT a1, a2, a3, a4
> FROM A
> WHERE NOT EXISTS
> (SELECT *
> FROM B
> WHERE A.a1 = B.b1
> AND A.a2 = B.b2
> AND A.a3 = B.b3
> AND A.a4*= B.b4 );

What is that *= doing on the last row?

The requirements were somewhat ambiguous, but one of these should do:

SELECT a1, a2, a3, a4
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.a1 = B.b1
AND A.a2 = B.b2
AND A.a4 = B.b4 );

(Rows identified by keys, the value in the non-key column a3/b3 may
be different.)

SELECT a1, a2, a3, a4
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.a1 = B.b1
AND A.a2 = B.b2
AND A.a4 = B.b4
AND A.a3 = B.b3 );

(Rows may be in both tables, but may have a difference in a3/b3.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||blueghost73@.yahoo.com (blueghost73@.yahoo.com) writes:
> I'm no expert, so this probably isn't the most efficient way to do
> this, but I think this will work:
> select A.* from A, B
> where A.a1 *= B.b1
> and A.a2 *= B.b2
> and A.a4 *= B.b4
> and B.b1 is null

*= is a older form of outer join which has all sorts of funny
quirkes with it. I am not going to find why this does not work.

Use the new ANSI syntax instead:

select A.*
from A
left join B ON A.a1 = B.b1
and A.a2 = B.b2
and A.a4 = B.b4
where and B.b1 is null

But I much prefer NOT EXISTS for this type of query, as it much better
expresses what you are looking for.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> What is that *= doing on the last row? <<

Arrrgh! Cut & paste error!