query assistance: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

query help

I will try to be brief
I have two tables I am trying to join that share a foreign key.
the structures are:
table a: column table b: columns
foreign key employee ID employeeID
status earnings
effectivedate checkdate
each table can have multiple rows with the same employee ID. Table b can have multiple rows with the same checkdate. I am trying to query the two tables so I can get the sum of the earnings for a particular checkdate and the employees status at the time
of the check. Here is an example of the data and what I have written so far:
tableA:
employeeID STATUS Effectivedate
100 fulltime 01/01/03
100 parttime 01/01/04
100 fulltime 03/27/04
101 fulltime 01/01/03
101 parttime 04/01/04
tableB:
employeeID earnings checkdate
100 25.00 03/25/04
100 97.00 03/25/04
101 10.00 03/25/04
If I query with the employeeID it is no problem:
select tableB.employeeID, STATUS, Effectivedate, sum(earnings), checkdate
from tableA, tableB
where tableA.employeeID=tableB.employeeID
and effectivedate=(select max(effectivedate) from tableA where tableA.employeeID=100 and effectivdate <='03/25/04')
and checkdate='03/25/04'
group by tableB.employeeID, STATUS, Effectivedate, checkdate
but... to do this on my tables which have thousands and thousands of rows for each ID each month will be painful.
How can I write this query so that the select will return the sum of earnings by employeeID on a specific checkdate and the employee status on that date. I thought of using a cursor, but I am not advanced enough to write one. I am sure there has to be a
way to do this. Any help will be greatly appreciated!!!!
I realize I should have posted this in data mining, but I don't want to double post

sql

Query Help

I have two tables: Trans & History. For each record in Trans, there can be
many in History. I want to return records in the Trans table that have a
certain status in the History table but not other statuses. Here's an
example:
select transactions.transaction_id,
name
from transactions
inner join statusHistory SH1 on
transactions.transaction_id = SH1.transaction_id
where eMonth = '10' and eYear = '2005' and
SH1.status in ('Rec', 'R1Rec') and
SH1.status not in ('Coll', 'RTR')
The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
want to filter out those that contain either 'Coll' or 'RTR'On Wed, 23 Nov 2005 10:36:02 -0800, Eric wrote:

>I have two tables: Trans & History. For each record in Trans, there can b
e
>many in History. I want to return records in the Trans table that have a
>certain status in the History table but not other statuses. Here's an
>example:
>select transactions.transaction_id,
> name
>from transactions
>inner join statusHistory SH1 on
> transactions.transaction_id = SH1.transaction_id
>where eMonth = '10' and eYear = '2005' and
> SH1.status in ('Rec', 'R1Rec') and
> SH1.status not in ('Coll', 'RTR')
>
>The returned records contain both the status of 'Rec' and 'Coll/'RTR' but I
>want to filter out those that contain either 'Coll' or 'RTR'
Hi Eric,
Since you didn't post your table structure (CREATE TABLE statements),
sample data (INSERT statements) and required output, I'll have to do
some wild guess about which of your unprefixed columns belog to which
table. You'll probably have to make some changes. But here's a general
outline:
SELECT T.transaction_id, T.name
FROM transactions AS T
WHERE T.eMonth = '10'
AND T.eYear = '2005'
AND EXISTS
(SELECT *
FROM statusHistory AS SH1
WHERE SH1.transaction_id = T.transaction_id
AND SH1.status IN ('Rec', 'R1Rec'))
AND NOT EXISTS
(SELECT *
FROM statusHistory AS SH2
WHERE SH2.transaction_id = T.transaction_id
AND SH2.status IN ('Col1', 'RTR'))
BTW, why store a date in seperate columns eMonth and eYear? Isn't that
what the datetime datatype is for?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi There,
Use LEFT JOIN instead of Join
With Warm regards
Jatinder Singh

query help

I need a query to give me dbname, tablename,rowcount,size for all user dbs
and tables on a server
I know i can use sp_spaceused.. But want a query that I can then run on any
server
ThanksIf you are using SQL Server 2005, select your database, then Summary,
Report, Disk Usage and expand Disk Space Used by Tables.
Ben Nevarez, MCDBA, OCP
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23yeA8Dl8FHA.740@.TK2MSFTNGP11.phx.gbl...
>I need a query to give me dbname, tablename,rowcount,size for all user dbs
>and tables on a server
> I know i can use sp_spaceused.. But want a query that I can then run on
> any server
> Thanks
>|||I was looking for the TSQL and I am using SQL 2000
I want the result to look like
DBName TableName Size(KB)
DB1 T1 1200
DB1 T2 100
DB2 T1 50
and so on...
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:%236fY%23Km8FHA.1188@.TK2MSFTNGP12.phx.gbl...
> If you are using SQL Server 2005, select your database, then Summary,
> Report, Disk Usage and expand Disk Space Used by Tables.
> Ben Nevarez, MCDBA, OCP
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23yeA8Dl8FHA.740@.TK2MSFTNGP11.phx.gbl...
>|||Hassan (hassan@.hotmail.com) writes:
> I need a query to give me dbname, tablename,rowcount,size for all user dbs
> and tables on a server
> I know i can use sp_spaceused.. But want a query that I can then run on
> any server
sp_MSforeachdb 'SELECT db = ''?'', "table" = object_name(id), rows, size =
reserved * 8192 / 1000000 from sysindexes WHERE indid IN (0,1) ORDER BY 2'
You will get one result set per database.
Note that the procedure sp_MSforeachdb is undocumented and not supported.
(But it stilll works in SQL 2005; I just tested the above in SQL 2005.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
It lists the same tablenames for all databases :-(
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns971BB51D75D5Yazorman@.127.0.0.1...
> Hassan (hassan@.hotmail.com) writes:
> sp_MSforeachdb 'SELECT db = ''?'', "table" = object_name(id), rows, size =
> reserved * 8192 / 1000000 from sysindexes WHERE indid IN (0,1) ORDER BY 2'
> You will get one result set per database.
> Note that the procedure sp_MSforeachdb is undocumented and not supported.
> (But it stilll works in SQL 2005; I just tested the above in SQL 2005.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hassan (hassan@.hotmail.com) writes:
> It lists the same tablenames for all databases :-(
Oops! Change "sysindexes" to "?.dbo.sysindexes", and it should work
better.
The ? is a placeholder for the DB name in sp_MSforeachdb.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

query help

I have Two tables. Products and ProductInfo.
All the Products(productId) have entries in ProductInfo (FK productId)
I want to find out products whose entries do not exist in ProductInfo
Pls hlpSELECT ProductID --, ...
FROM Products p
LEFT OUTER JOIN ProductInfo pi
ON p.ProductID = pi.ProductID
WHERE pi.ProductID IS NULL
-- or
SELECT ProductID --, ...
FROM Products
WHERE ProductID NOT IN
(
SELECT ProductID
FROM ProductInfo
)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||Hi
SELECT <column lists> FROM Products WHERE NOT EXISTS
(SELECT * FROM ProductInfo WHERE Products.ProductId=ProductInfo.Productid )
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
> I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||SELECT ...
FROM Product AS p
WHERE NOT EXISTS(SELECT * FROM ProductInfo AS pi WHERE pi.productID = p.Prod
uctId)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||Hi , Aaron
> SELECT ProductID --, ...
> FROM Products
> WHERE ProductID NOT IN
> (
> SELECT ProductID
> FROM ProductInfo
> )
>
I have my doubt about this query because if the OP has ProductId IS NULL
we may get back a wrong output .Am I right
BTW why did you drop an acoount (ng) ? :-)
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:ux6n8fLUFHA.1796@.TK2MSFTNGP15.phx.gbl...
> SELECT ProductID --, ...
> FROM Products p
> LEFT OUTER JOIN ProductInfo pi
> ON p.ProductID = pi.ProductID
> WHERE pi.ProductID IS NULL
> -- or
> SELECT ProductID --, ...
> FROM Products
> WHERE ProductID NOT IN
> (
> SELECT ProductID
> FROM ProductInfo
> )
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
> news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>|||> I have my doubt about this query because if the OP has ProductId IS NULL
> we may get back a wrong output .Am I right
Possibly, but it was defined as a foreign key, so I figured the chance of it
being NULL was pretty small.

> BTW why did you drop an acoount (ng) ? :-)
Because there are a couple of dimwits in here who don't seem to know when to
shut up. Not you, of course.
A|||Yep, I sow a few posts. Sometimes it is really sorry that this NG is
unmoderated , should be a moderator here.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:egwAZpLUFHA.2892@.TK2MSFTNGP14.phx.gbl...
NULL
> Possibly, but it was defined as a foreign key, so I figured the chance of
it
> being NULL was pretty small.
>
> Because there are a couple of dimwits in here who don't seem to know when
to
> shut up. Not you, of course.
> A
>|||I agree with AB. Some people in the newsgroup dont know how to respect the
SQL MVPs. They contradict them, argue with them for no apparent reason and
talk to them in a demeaning way. I am not a MVP but I personally think
everyone should respect all the SQL MVPs because of their tremendous
knowledge and experience which they share to perform better in our jobs.
They give great design solutions, great answers and then actually point the
flaws when we are doing something wrong.
I read all of MVP posts and I always follow and implement their suggestions.
I am hoping others would learn to respect them.
Thanks
M
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEwG7gLUFHA.3840@.tk2msftngp13.phx.gbl...
> SELECT ...
> FROM Product AS p
> WHERE NOT EXISTS(SELECT * FROM ProductInfo AS pi WHERE pi.productID =
p.ProductId)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
> news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>

Wednesday, March 28, 2012

query help

I will try to be brief
I have two tables I am trying to join that share a foreign key.
the structures are:
table a: column table b: columns
foreign key employee ID employeeID
status earnings
effectivedate checkdate
each table can have multiple rows with the same employee ID. Table b can ha
ve multiple rows with the same checkdate. I am trying to query the two tabl
es so I can get the sum of the earnings for a particular checkdate and the e
mployees status at the time
of the check. Here is an example of the data and what I have written so far
:
tableA:
employeeID STATUS Effectivedate
100 fulltime 01/01/03
100 parttime 01/01/04
100 fulltime 03/27/04
101 fulltime 01/01/03
101 parttime 04/01/04
tableB:
employeeID earnings checkdate
100 25.00 03/25/04
100 97.00 03/25/04
101 10.00 03/25/04
If I query with the employeeID it is no problem:
select tableB.employeeID, STATUS, Effectivedate, sum(earnings), checkdate
from tableA, tableB
where tableA.employeeID=tableB.employeeID
and effectivedate=(select max(effectivedate) from tableA where tableA.employ
eeID=100 and effectivdate <='03/25/04')
and checkdate='03/25/04'
group by tableB.employeeID, STATUS, Effectivedate, checkdate
but... to do this on my tables which have thousands and thousands of rows f
or each ID each month will be painful.
How can I write this query so that the select will return the sum of earning
s by employeeID on a specific checkdate and the employee status on that date
. I thought of using a cursor, but I am not advanced enough to write one.
I am sure there has to be a
way to do this. Any help will be greatly appreciated!!!!I realize I should have posted this in data mining, but I don't want to doub
le post

Query Help

Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Now that I look at my own post it looks easy. Just query term end date for
terms ending in the next 60 days. The problem I'm having though is what if
those users have been re-elected to another term or another person has
already been selected to replace them? Perhaps I need another field. Maybe
a "replaced (boolean)" field in the User_mm_committee table that is set to
true when a user is re-elected or replaced? Is there any other way to do
it?
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a Committee for a specified Term, at which point they are either
> re-elected or replaced. I want to produce a Query that shows me this - a
> record for each vacancy that will be created in the next 60 days (a term
> expires). An example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>|||Try:
select
u.Name
, c.CommitteeName -- assumes this column exists
, uc.TermEndDate
from
Users u
join
User_mm_Committee uc on u uc.UserID = u.UserID
join
Committees c on c.CommitteeId = uc.CommitteeID
where
uc.TermEndDate < dateadd (dd, 60, getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Here's a brain-twister that I'm having a tough time wrapping my head around.
I have 3 tables. A Users table. A Committee table. And a
User_mm_Committee table (many-to-many join). Users can serve on multiple
committees, and of course committees have many users. Each User serves on a
Committee for a specified Term, at which point they are either re-elected or
replaced. I want to produce a Query that shows me this - a record for each
vacancy that will be created in the next 60 days (a term expires). An
example of some records returned by the query:
Name | Committee | Term Expiration Date
John Doe | City Committee | 6-30-06
Jane Doe | Green County Board | 7-15-06
Here's the pertinant table info.
Table: User
UserID (PK)
Name, etc.
Table: Committee
CommitteeID (PK)
CommitteeMembers (number of members committee needs to have at all times)
CommitteeTermLength (length of term in years)
Table: User_mm_Committee
CommitteeID (PK)
UserID (FK)
CommitteeID (FK)
TermBeginDate
TermEndDate
Thanks for any help!|||Thanks for the help, Tom. I think I can get this to work now.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:upaed5aiGHA.2220@.TK2MSFTNGP05.phx.gbl...
> Try:
> select
> u.Name
> , c.CommitteeName -- assumes this column exists
> , uc.TermEndDate
> from
> Users u
> join
> User_mm_Committee uc on u uc.UserID = u.UserID
> join
> Committees c on c.CommitteeId = uc.CommitteeID
> where
> uc.TermEndDate < dateadd (dd, 60, getdate())
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:ekmGO0aiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Here's a brain-twister that I'm having a tough time wrapping my head
> around.
> I have 3 tables. A Users table. A Committee table. And a
> User_mm_Committee table (many-to-many join). Users can serve on multiple
> committees, and of course committees have many users. Each User serves on
> a
> Committee for a specified Term, at which point they are either re-elected
> or
> replaced. I want to produce a Query that shows me this - a record for
> each
> vacancy that will be created in the next 60 days (a term expires). An
> example of some records returned by the query:
> Name | Committee | Term Expiration Date
> John Doe | City Committee | 6-30-06
> Jane Doe | Green County Board | 7-15-06
> Here's the pertinant table info.
> Table: User
> UserID (PK)
> Name, etc.
> Table: Committee
> CommitteeID (PK)
> CommitteeMembers (number of members committee needs to have at all times)
> CommitteeTermLength (length of term in years)
> Table: User_mm_Committee
> CommitteeID (PK)
> UserID (FK)
> CommitteeID (FK)
> TermBeginDate
> TermEndDate
> Thanks for any help!
>sql

Query Help

Query help

I need to update 6 column on 1 table depending on 2 other
columns on another table (All 8 columns exists on both
tables)
here is what I am trying to write and it gives me error:
Update Table1
Set start_date = (Select start_date from Table2),
end_date = (Select end_date from Table2),
user1 = (Select user1 from Table2),
user2 = (Select user2 from Table2),
user3 = (Select user3 from Table2),
user4 = (Select user4 from Table2)
FROM Table2
Where Table2.project = Table1.project AND
Table2.Pjt_entity = Table1.Pjt_entity
Thanks for any help.Hi,
Try this,
Update Table1
Set a.start_date = b.start_date ,
a.end_date = b.end_date,
a.user1 = b.user1,
a.user2 = b.user2,
a.user3 = b.user3,
a.user4 = b.user4
FROM Table1 a,Table2 b
Where a.project = b.project
AND a..Pjt_entity = b.Pjt_entity
Tahnks
Hari
MCDBA
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:2d2001c486d0$5a97bdb0$a301280a@.phx.gbl...
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.|||Todd wrote:
> I need to update 6 column on 1 table depending on 2 other
> columns on another table (All 8 columns exists on both
> tables)
> here is what I am trying to write and it gives me error:
> Update Table1
> Set start_date = (Select start_date from Table2),
> end_date = (Select end_date from Table2),
> user1 = (Select user1 from Table2),
> user2 = (Select user2 from Table2),
> user3 = (Select user3 from Table2),
> user4 = (Select user4 from Table2)
> FROM Table2
> Where Table2.project = Table1.project AND
> Table2.Pjt_entity = Table1.Pjt_entity
> Thanks for any help.
Well if you have a 1:1 between the tables, you just need to specify the
column to update:
Update Table1
Set start_date = b.start_date,
end_date = b.end_date,
etc...
FROM Table2 b
Where b.project = Table1.project AND
b.Pjt_entity = Table1.Pjt_entity
David G.|||David G. wrote:
> Todd wrote:
>> I need to update 6 column on 1 table depending on 2 other
>> columns on another table (All 8 columns exists on both
>> tables)
>> here is what I am trying to write and it gives me error:
>> Update Table1
>> Set start_date = (Select start_date from Table2),
>> end_date = (Select end_date from Table2),
>> user1 = (Select user1 from Table2),
>> user2 = (Select user2 from Table2),
>> user3 = (Select user3 from Table2),
>> user4 = (Select user4 from Table2)
>> FROM Table2
>> Where Table2.project = Table1.project AND
>> Table2.Pjt_entity = Table1.Pjt_entity
>> Thanks for any help.
> Well if you have a 1:1 between the tables, you just need to specify
> the column to update:
> Update Table1
> Set start_date = b.start_date,
> end_date = b.end_date,
> etc...
> FROM Table2 b
> Where b.project = Table1.project AND
> b.Pjt_entity = Table1.Pjt_entity
Left off a table in the FROM clause. See Hari's post instead.
--
David G.

Query help

Hello everyone,
I need some help building a query using three tables and I am having
difficulty writing the query.
Here are the tables:

GENERAL:
GID - primary key
PARCEL
EDITDATE
MAPCODE

SALES:
GID - foreign key
SLSDATE
SLSAMOUNT

RESIDENCE:
GID - foreign key
OCCUPANCY
LIVINGAREA

The relationship is one-to-many from the General table to both the Sales and
the Residence tables. I want to allow users to query by every field in each
of these tables. The problem is that this is a Sales search, so there must
be a record in the Sales table before it searches all other criteria. How
do I setup the joins for this?
Thanks!
MVMMVM (nospam@.nospam.org) writes:
> Hello everyone,
> I need some help building a query using three tables and I am having
> difficulty writing the query.
> Here are the tables:
> GENERAL:
> GID - primary key
> PARCEL
> EDITDATE
> MAPCODE
>
> SALES:
> GID - foreign key
> SLSDATE
> SLSAMOUNT
>
> RESIDENCE:
> GID - foreign key
> OCCUPANCY
> LIVINGAREA
>
> The relationship is one-to-many from the General table to both the Sales
> and the Residence tables. I want to allow users to query by every field
> in each of these tables. The problem is that this is a Sales search, so
> there must be a record in the Sales table before it searches all other
> criteria. How do I setup the joins for this?

The standard recommendation for this type of question is that you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it easy to copy and paste and develop a tested solution.

With incomplete information, it goes down to guessworks, which may be
less accurate.

One thing is not clear to me what the result set should look like. If you
have something like:

SELECT ...
FROM general g
JOIN sales s ON g.GID = s.GID
JOIN residence r ON g.GID = r.GID

and for a certain row in general, there are 13 rows in sales and 7
rows in residence, you will get 91 rows in the result with all
combinations of sales and residence. Since this is probably not what
you want, the query about is not the right one. But since I don't know
what you want, I don't what is the right.

Of course, to only find rows in general + residence that have some
match in sales, you can do:

SELECT ...
FROM general g
JOIN residence ON g.GID = r.GID
WHERE EXISTS (SELECT *
FROM sales s
WHERE s.GID = g.GID
AND s.SLSDATE >= '20050201'
AND s.SLSDATE < '20050301')

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So if you only want to search for things with a row in SALES, you can
start there and outer join the other tables. However, since SALES.GID
is a foreign key to GENERAL, you will always have a row in GENERAL as
well -- so you could start there, also.

I think you want something like this:

select ...
from SALE s
join GENERAL g
on s.GID = g.GID
left outer join RESIDENCE r
on g.GID = r.GID
where ...|||Gentlemen,
Thank you. That is exactly what I needed.

Query Help

What I want to generate is a list of all users and if they belong to a
site or not. Below is what I have for tables, sample data and the
results. I am a newb with writing my own SQL and could use a few
pointers and help.

For example if I have the following data:

Table: [Users]
UserIDUserName
1Bob
2Joe
3Sam

Table: [Users_Sites]
UserIDSiteID
12
32

Table: [Sites]
SiteIDSiteName
1Flower
2Spring
3Bush

If I pick Site 1 I get the list:

UserIDSiteID
<null>1
<null>1
<null>1

If I pick Site 2 I get the list:

UserIDSiteID
12
<null>2
32

Thanks in advance...Will (wrhighfield@.hotmail.com) writes:
> What I want to generate is a list of all users and if they belong to a
> site or not. Below is what I have for tables, sample data and the
> results. I am a newb with writing my own SQL and could use a few
> pointers and help.

SELECT us.SiteID, u.UserID
FROM Users_Sites us
LEFT JOIN Users u ON us.UserID = u.UserID
WHERE us.SiteID = @.siteid

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Monday, March 26, 2012

Query hangs during a seperate write

When writing 100's of records to 1 or more tables within a transaction, the
server seems to hang any query trying to read from those tables until either
a Timeout or the write process completes. I'm not doing a lock.
The writes and reads are separate applications.
What would cause this?How are you doing the write?
AMB
"Joe" wrote:

> When writing 100's of records to 1 or more tables within a transaction, th
e
> server seems to hang any query trying to read from those tables until eith
er
> a Timeout or the write process completes. I'm not doing a lock.
> The writes and reads are separate applications.
> What would cause this?
>
>|||using ADO.NET. The DataAdaptor is doing Inserts into the tables.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9960EB96-B274-4A6C-85C0-3BB485E1D411@.microsoft.com...
> How are you doing the write?
>
> AMB
>
> "Joe" wrote:
>
the
either|||Do you know the isolation level being used in the transaction?
You can use Profiler to trace the locks, Lock:Acquired / Lock:Escalation and
Lock:Released.
AMB
"Joe" wrote:

> using ADO.NET. The DataAdaptor is doing Inserts into the tables.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:9960EB96-B274-4A6C-85C0-3BB485E1D411@.microsoft.com...
> the
> either
>
>|||Use with (nolock) on your select statements, for ex.
SELECT au_lname FROM authors WITH (NOLOCK)
This will ensure that select stmt. does the dirty rather than acquiring
an shared lock while the exclusive lock is on.|||You may have a deadlock, and insert is relying on an ID inserted in another
table which itself is waiting for the hanging insert to finish.
If it happens all the time - simply check your logic does the flow work? -
you not done something silly in a transaction.
Otherwise if its intermitant find what process blocks it and syncronise the
order which both processes use DB tables. Try and minimise the time spent in
transactions.
Use profiler to / or current actvity pane in Enterprise man to explore the
processes
Just an approach I've found useful.

Query gurus please?

Hi,

I'm trying to put together a query which will combine a number of values from several database tables. Currently, there are two tables in question, one of products, and one of order details.

tblProducts:
ProductID
Product_Category
Product_Weight

tblDetails:
DetailID
Order_ID (linked to an order profile table)
Product_ID (linked to tblProducts.ProductID)
Product_Category
Detail_Quantity

What I need to achieve is a query (or several queries) that will retrieve the total weight of a detail record (ie tblProducts.ProductWeight * tblDetails.Detail_Category) and then group this by Product_Category. What you end up with is the total weight of each category of product - this will be used to calculate freight costs.

I can construct a query to retrieve total quantities by category, and total weight per detail row, but how can I combine these?

Retrieve total weight per detail row:

SELECT tblProducts.ProductID, tblProducts.Product_Weight, tblDetails.Detail_Quantity, (tblDetails.Detail_Quantity*tblProducts.Product_We ight) AS FreightWeight, tblDetails.DetailID, tblDetails.Order_ID
FROM tblProducts INNER JOIN tblDetails ON tblProducts.ProductID = tblDetails.Product_ID
WHERE (((tblDetails.Order_ID)= *xxx* ));

Retrieve total number of products ordered by category

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity) AS TotalQuantity
FROM tblDetails
GROUP BY tblDetails.Product_Category;

Any suggestions would be great!

BenHow about:

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity*tblProducts.Product _Weight) AS TotalWeight
FROM tblDetails
INNER JOIN tblDetails ON tblProducts.ProductID = tblDetails.Product_ID
GROUP BY tblDetails.Product_Category;|||Thanks Andrew,

When I try to run this query, I get a Syntax error in the JOIN statement. I've tried recreating this (unfortunately in Access) and I get an error which I can't understand:

'You tried to execute a query that does not include the specified expression 'Product_ID' as part of an aggregate function'

This error is repeated for any fields included, other than those in the SUM function. Is this just due to wiggy JetSQL? Your query structure makes sense to me, but I can't make it work.

Any other ideas?

Thanks again,

Ben|||No, that doesn't make sense to me. I presume you fixed the error in my code?

SELECT tblDetails.Product_Category, Sum(tblDetails.Detail_Quantity*tblProducts.Product _Weight) AS TotalWeight
FROM tblDetails
INNER JOIN tblProducts ON tblProducts.ProductID = tblDetails.Product_ID
GROUP BY tblDetails.Product_Category;|||Andrew - you're a legend!

I thought I had fixed that error, but I succeeded only in compounding the problem. This query of yours works a treat. Thanks very much.

All the best,

Ben

Query goes from 1 second to 40 minutes with only 1 more record?

I have a very strange problem where I have a simple select count(*) with a handle of of joined tables that works fine and returns a count of records in under 1 second when the criteria in the where clause limits the count to 36984. But if I change the criteria to where the number of records would be 1 higher, 36985, the query takes ~40 minutes!

Here's the query

SELECT count(*)
FROM AT JOIN Al ON Al.AlID= AT.ALID
JOIN DProfile ON Al.DProfileID= DProfile.DProfileID
JOIN Label ON DProfile.LabelID = Label.LabelID
JOIN PricingTier ON PricingTier.LabelID = DProfile.LabelID
JOIN PricingTarget on PricingTarget.PricingTargetID = PricingTier.PricingTargetID
JOIN ATP ON ATP.PricingTierID = PricingTier.PricingTierID
WHERE PricingTarget.Target = 'AT'
AND PricingTier.MaxAgeInDays = 0
AND ATP.LengthMultiple = 0
AND AT.ATID > 408095
AND AT.ATID < 451199

Notice the part of the where clause in red. This is how I'm changing the number of rows that select count(*) should find.

Here's the output from showplan

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[AL].[ALID])=([MGN2].[dbo].[AT].[ALID]))
|--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[PricingTier].[PricingTargetID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([MGN2].[dbo].[ATP].[PricingTierID]))
| | | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATP].[PK_ATPID]), WHERE:([MGN2].[dbo].[ATP].[LengthMultiple]=(0)))
| | | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), SEEK:([MGN2].[dbo].[PricingTier].[PricingTierID]=[MGN2].[dbo].[ATP].[PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), SEEK:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408095) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

Now, if I change the part of the where clause from 'AND AT.ATID > 408095' to 'AND AT.ATID > 408094', increase the number returned by one, the query goes from taking 1 second to 40 minutes. Here's the showplan text that is different, when the only difference in the query is changing the number in the where clause.

|--Compute Scalar(DEFINE:([Expr1021]=CONVERT_IMPLICIT(int,[Expr1028],0)))
|--Stream Aggregate(DEFINE:([Expr1028]=Count(*)))
|--Merge Join(Inner Join, MERGE:([MGN2].[dbo].[PricingTarget].[PricingTargetID])=([MGN2].[dbo].[PricingTier].[PricingTargetID]), RESIDUAL:([MGN2].[dbo].[PricingTarget].[PricingTargetID]=[MGN2].[dbo].[PricingTier].[PricingTargetID]))
|--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTarget].[PK_PricingTargetID]), WHERE:([MGN2].[dbo].[PricingTarget].[Target]='AT') ORDERED FORWARD)
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[AL].[ALID]=[MGN2].[dbo].[AT].[ALID]))
|--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[ATPrice].[PricingTierID]=[MGN2].[dbo].[PricingTier].[PricingTierID]))
| |--Sort(ORDER BY:([MGN2].[dbo].[PricingTier].[PricingTargetID] ASC))
| | |--Hash Match(Inner Join, HASH:([MGN2].[dbo].[DProfile].[DProfileID])=([MGN2].[dbo].[AL].[DProfileID]), RESIDUAL:([MGN2].[dbo].[DProfile].[DProfileID]=[MGN2].[dbo].[AL].[DProfileID]))
| | |--Nested Loops(Inner Join, WHERE:([MGN2].[dbo].[PricingTier].[LabelID]=[MGN2].[dbo].[DProfile].[LabelID]))
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[PricingTier].[PK_PricingTierID]), WHERE:([MGN2].[dbo].[PricingTier].[MaxAgeInDays]=(0)) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[DProfile].[PK_D_Profiles]))
| | |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[AL].[PK_AL]))
| |--Clustered Index Scan(OBJECT:([MGN2].[dbo].[ATPrice].[PK_ATPriceID]), WHERE:([MGN2].[dbo].[ATPrice].[LengthMultiple]=(0)))
|--Clustered Index Seek(OBJECT:([MGN2].[dbo].[AT].[PK_Track]), SEEK:([MGN2].[dbo].[AT].[ATID] > (408094) AND [MGN2].[dbo].[AT].[ATID] < (451199)) ORDERED FORWARD)

I tried increasing the size of the TempDB from the default of 8 MB to 1000 MB for both the files, but it behaves the exact same way. I'm at a loss as to what is causing this dramatic difference for such a simple query. Anyone have an idea?

Thanks!

By the way, this is on SQL Server 2005, if that helps any.|||

Jim:

Good post. I appreciate your inclusion of the plan and the research you put into your question. It appears to me that you are at a plan "crossover" point. What I mean is that you are at the point where the mere addition of one more record to the output range causes the query optimizer to choose a different query plan. In your plan I see many "clustered index scans". A cover index might be appropriate to improve the performance of your query. Can you post the indexes, keys and unique constraints of all of your tables?

What follows below is academic. I am not sure whether I need to include this or not because it looks like you have already done this with your testing, so it looks like you already have a good handle on this. If this is of no interest, just skip the rest.

Dave

I created a mock-up table with 32767 rows of data. One of the columns is a "testDate" column with an associated index. After creating the table I performed a number of select queries with SHOWPLAN_TEXT turned on so that I could discover the filter criteria at which the query plan switched from "INDEX SEEK" to "CLUSTERED INDEX SCAN". I found that when I switch the filter date from "1/31/7" to "1/30/7" that the plan switched. Therefore I ran these two queries with SHOWPLAN _TEXT turned on to illustrate the query plan "crossover" point. I think that your query has a similar issue. My main question to you is was it your intent to find the crossover or is this something that jumped up and bit you? OUCH!

if exists
( select 0 from sysobjects
where type = 'U'
and id = object_id ('dbo.crossoverTest')
)
drop table dbo.crossoverTest
go
create table dbo.crossoverTest
( rid integer
constraint pk_crossoverTest primary key,
filler char (200),
testDate datetime
)
go
create index testDate_ndx
on dbo.crossoverTest (testDate)
go
insert into dbo.crossoverTest
select iter,
'Filler',
dateadd (mi, -17*iter, convert(datetime, '2/3/7'))
from (
select 256*b.number + a.number as iter
from master.dbo.spt_values a (nolock)
inner join master.dbo.spt_values b (nolock)
on a.[name] is null
and b.[name] is null
and b.number <= 127
and a.number <= 255
and 256*b.number + a.number > 0
) as small_iterator
go
update statistics dbo.crossoverTest
go
set showplan_text on
go
select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/31/7'
go
-- StmtText
-- --
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([tempdb].[dbo].[crossoverTest]) WITH PREFETCH)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[crossoverTest].[testDate_ndx]), SEEK:([crossoverTest].[testDate] >= 'Jan 31 2007 12:00AM') ORDERED FORWARD)
-- Table 'crossoverTest'. Scan count 1, logical reads 813, physical reads 0, read-ahead reads 0.
go
select rid,
left (filler, 10) as Filler,
testDate
from crossoverTest
where testDate >= '1/30/7'
go
-- StmtText
--
-- |--Compute Scalar(DEFINE:([Expr1002]=substring(Convert([crossoverTest].[filler]), 1, 10)))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[crossoverTest].[pk_crossoverTest]), WHERE:([crossoverTest].[testDate]>='Jan 30 2007 12:00AM'))
-- Table 'crossoverTest'. Scan count 1, logical reads 913, physical reads 0, read-ahead reads 0.

go
set showplan_text off
go

|||

Thanks Dave,

Ultimately, a simple index was the issue. What perplexed me was just that the difference between the queries when it returned one more row was soooo dramatic. And to answer your question, I had just come upon the crossover issue through trial and error. I hadnt noticed the query plans were different until I got to the point that 1 row was the difference between success and failure.

Friday, March 23, 2012

query from 3 tables

hi,

I have 3 tables
[Credit Memo - Expense Details] - which i refer to CMED
[Credit Memo - Project Change Request] - which i refer to CMPCR
[Credit Memo Table] - which i refer to CM

CM is connected to CMED by 1 field: CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])

CM is connected to CMPCR by 1 field: CM.[PCR # (if applicable)] = CMPCR.[PCR Number]

CMED and CMPCR are not related

My goal query: show all the fields in CM (and (if invoice columns match in CMED, show that) or (if pcr number columns match in CMPCR, show that) )

So if there is only a match with CMPCR the CMED fields should be blank.

I am thinking of a union but just couldn't get it.

Below is the code to get all the records where CM invoice matches AND pcr matches
i need it to be OR

SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM INNER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
INNER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];

MS access 2003 - i hate it but i jsut came on board and this is already setup/

Thankshow about to use outer join?

SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM LEFT OUTER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
LEFT OUTER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.|||you mentioned the database is ms access, I believe there's equivalent of OUTER JOIN in Access. I'm not sure but I think it's LEFT JOIN instead of LEFT OUTER JOIN|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.

by the way, this solution can't work. idea is OK but you have to use cartesian product instead of inner join. then using combination of AND, OR conditions defined in WHERE clause you'll retrieve what you need without using UNION. but for now forget this solution and try to find equivalent of LEFT OUTER JOIN in your DB server.|||i disagree, madafaka

peter's union suggestion works perfectly, you should try it

it's also possible to simulate a full outer join with a (suitably coded) union of left and right joins|||maybe it works but why use UNION and put together 2 or more selects if you can retrieve your date using one select.|||why? because it might be way faster, that's why :)|||why? because it might be way faster, that's why :)
Now you're wrong. The performance is the reason I avoid using UNION.

"When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset."

"Sometimes you might want to merge two or more sets of data resulting from two or more queries using UNION. For example:"

SELECT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name2 = some_value

"This same query can be rewritten, like the following example, and when doing so, performance will be boosted:"

SELECT DISTINCT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value OR column_name2 = some_value|||dude, we were talking about a UNION to simulate a LEFT OUTER JOIN, versus your suggestion of a cartesian product to simulate a LEFT OUTER JOIN|||ok, I didn't realise this. but this solutions are silly anyway. I can't immagine how those select statements would look like, if you're joining 3 tables and you can't use OUTER JOIN. I believe there must be some OUTER JOIN equivalent in every standard SQL database.|||Just a reply to some of the topics mentioned in previous posts:

- Using "UNION ALL" instead of "UNION" avoids the "SELECT DISTINCT" performance overhead; in that case, a "UNION" emulation of an OUTER JOIN is in principle equally performant. (Most of the time, OUTER JOIN will be a bit more performant, since only a single pass has to be made through the left table, but in rare cases the UNION ALL solution may be more performant, especially when lots of rows have no matching row).

- There are SQL database systems lacking the OUTER JOIN syntax, especially older versions: e.g. Oracle before version 8, DB2 before version 6. Nobody uses these nowadays, but who knows ...

- Performance is not necessarily boosted when using "OR" instead of "UNION ALL" !
To the contrary: the two queries in a UNION ALL may use indexes, while as a rule-of-thumb an OR condition never uses indexed access.

- The equivalent of the querySELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a LEFT OUTER JOIN tableb AS b ON a.c2 = b.c2where a.c2 is a foreign key and b.c2 is the corresponding primary key, isSELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a, tableb AS b WHERE a.c2 = b.c2
UNION ALL
SELECT a.c1, a.c2, NULL, NULL
FROM tablea AS a WHERE a.c2 IS NULLWhen a.c2 is not a foreign key, the condition in the second query becomesWHERE NOT EXISTS (SELECT 1 FROM tableb WHERE c2 = a.c2)

- A FULL OUTER JOIN can always be emulated with a "UNION ALL" of three queries, one on the inner join, one on the first table (as above), and one on the second table.|||Whatever you guys decide which way is the best - i know that the way Madafaka first used works for me. So thankssql

Query Foreign Key Columns and Tables

I am trying to query the database to get me the foreign key columns and the tables they belong to.

I have:

The name of the table

I need:

The name of the column in thetarget table

The name of the column in thereferenced table

The name of thereferenced table

Any help would be great, thanks

You can use the Management Studio diagramming tool to create it easily but through code it can get complex, the link below will take you in the right directions. Hope this helps.

http://www.sqlservercentral.com/columnists/rlobo/foreignkeys.asp

|||You'd better use system procedure to do this,?which?is?always recommended. For example:

use northwind
go
EXEC sp_helpconstraint Orders

If the result set doesn't fit your need, you can perform query directly on the sysforeighkeys table, but this is not recommended for tons of reasons. For exampe:

DECLARE @.tblName sysname
SET @.tblName='Orders'

SELECT OBJECT_NAME(fkeyid) AS TargetTable,OBJECT_NAME(rkeyid) AS ReferencedTable,
OBJECT_NAME(constid) AS FKName,COL_NAME(fkeyid,fkey) AS TargetColumn,
COL_NAME(rkeyid,rkey) AS ReferencedColumn
FROM sysforeignkeys
WHERE fkeyid=OBJECT_ID(@.tblName)|||

I found the perfect solution this yesterday actually,

I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

|||

BurnChrome:
I found the perfect solution this yesterday actually,
I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

I am glad to see your probelm is resolved.

|||

BurnChrome:
I found the perfect solution this yesterday actually,
I joined the sys.foreign_keys and sys.foreign_key_columns and used COL_NAME and OBJECT_NAME and got what I was looking for.

I am glad to see your probelm is resolved.

Query for tables.

how can i fetch somw rows having same field value?

my table is like:

name id

x 1

y 2

z 2

w 5

so how can i get rows y and z for id=id ?

This is a Transact-SQL question and should be asked over there... To save you some time though:

select name, id from [table] a, [table] b
where a.id = b.id and a.name <> b.name

Will return:

x 2
y 2|||

thx sir...

lemme modify for others...

select t1.* from table1 t1,table1 t2 where t1.id=t2.id and t1.name<>t2.name

query for tables without PK

I have a database that I want to use transactional replication on but several of the tables don't have primary keys - is there a quick query that I can run that will return a list of the tables without PK's?
Thanks in advance for the help!
Kevin
Use the OBJECTPROPERTY function (the TableHasPrimaryKey option). See SQL
Server Books Online for more information.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:398BB944-469B-4C1B-AD9C-0E3500909484@.microsoft.com...
I have a database that I want to use transactional replication on but
several of the tables don't have primary keys - is there a quick query that
I can run that will return a list of the tables without PK's?
Thanks in advance for the help!
Kevin
|||Kevin,
assuming all objects are owned by dbo, some variation on the theme of:
select * from information_schema.tables
where table_type = 'BASE TABLE'
and table_name not in (select table_name from
information_schema.table_constraints where
constraint_type='Primary Key')
should do you.
Regards,
Paul Ibison
|||Thanks Paul - that worked great! I was close but the subselect was killing me!

Query for Tables in a Database...

Good Morning

Shopping for help writing a query for my VB Program to execute against
SQL Server. Here is what I have so far:

SELECT name
FROM sysobjects
WHERE (xtype = 'U')

Is there a way to add to that query to get the list of tables that have the properties COST and PARTNUMBER?

Thanks,
EdUSE Northwind
GO

SELECT *
FROM INFORMATION_SCHEMA.Tables t
WHERE EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Columns c
WHERE t.TABLE_NAME = c.TABLE_NAME
AND c.COLUMN_NAME IN ('Parts','Cost'))
GO|||thanks...

It took me a bit to translate your guidance into what SQL Server wanted.
I ended up with:

select name from sysobjects t where exists( select * from syscolumns c where t.id = c.id
and c.name in ('PartNumber', 'Cost'))
order by name

Thanks,
Ed|||Did you just try and cut and paste the code?

Look up INFORMATION_SCHEMA in Books online...

You want to use those over the system tables.

These are views, which use the system tables...|||IC what you mean now...

Here is what we have now.

SELECT distinct A.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
and b.column_name in ('Partnumber', 'Cost')
ORDER BY A.TABLE_NAME

Thanks,
Ed|||Obviously you found out that the join will produce dupTablenames (because of the columns)...why didn't you just use what I gave you?|||the first time I tried it, no results were given. Thought I had to do some
heeby geeby to get it ready for use for my database. But then I noticed
the Parts column reference instead of Partnumber. Changed that and now it runs and returne the proper results.

Thanks,
ED

Query for Table structure

Hi,
I'm looking for some code that will pull back a list all tables in a database that meet a certain requirement - in this case all tables that have an identity column.
Can anyone point me in the right direction?
Cheers
Gregselect object_name(id),* from syscolumns
where autoval is not null

Query for Row count

I am trying to create a header file that lists the
table_name, row_count and date. The count for tables are
from the tables themselves (Basic row count)except 2
tables that have join statement. How can I include the 2
with the join statements with the rest of them ?
SELECT A.name AS name, B.rows AS [row count],
LEFT(GETDATE(), 12) AS date
FROM sysobjects A
JOIN sysindexes B ON A.id = B.id
WHERE A.type = 'U'AND A.name in
('table1','table2','table3','table4','table5','tab le6','tab
le7','table8')
AND B.indid < 2
ORDER BY A.name
--One of the table with the join
SELECT Count(*)
fromT9 table9
left join T10 table10
on table9.customer_id = table10.oid
T.I.A
If it's a left join, why not just use the count for table9 ?
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:926601c496a5$921c8790$a301280a@.phx.gbl...
> I am trying to create a header file that lists the
> table_name, row_count and date. The count for tables are
> from the tables themselves (Basic row count)except 2
> tables that have join statement. How can I include the 2
> with the join statements with the rest of them ?
>
> SELECT A.name AS name, B.rows AS [row count],
> LEFT(GETDATE(), 12) AS date
> FROM sysobjects A
> JOIN sysindexes B ON A.id = B.id
> WHERE A.type = 'U'AND A.name in
> ('table1','table2','table3','table4','table5','tab le6','tab
> le7','table8')
> AND B.indid < 2
> ORDER BY A.name
>
> --One of the table with the join
> SELECT Count(*)
> from T9 table9
> left join T10 table10
> on table9.customer_id = table10.oid
> T.I.A
|||Because I am getting a different count 1034 in one and
1029 in the other one. Can the cause be something else ?
Thanks.

>--Original Message--
>If it's a left join, why not just use the count for
table9 ?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:926601c496a5$921c8790$a301280a@.phx.gbl...
('table1','table2','table3','table4','table5','tab le6','tab
>
>.
>
|||Well, the count from your left join is not going to be stored in sysobjects,
because your left join is not a table and doesn't have any indexes!
What you could do is create a view, and then do something like:
SELECT 'SELECT t = '''+TABLE_NAME+''', c = COUNT(*) FROM '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN ('table1', ... , 'table8', 'view_table9_LJ_table10')
Run that, then copy the script from lower pane to top pane, and run it.
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:91e101c496aa$06e898d0$a501280a@.phx.gbl...
> Because I am getting a different count 1034 in one and
> 1029 in the other one. Can the cause be something else ?

Friday, March 30, 2012

query help

Query Help

query help

query help

Wednesday, March 28, 2012

query help

Query Help

Query Help

Query help

Query help

Query Help

Monday, March 26, 2012

Query hangs during a seperate write

Query gurus please?

Query goes from 1 second to 40 minutes with only 1 more record?

Friday, March 23, 2012

query from 3 tables

Query Foreign Key Columns and Tables

Query for tables.

query for tables without PK

Query for Tables in a Database...

Query for Table structure

Query for Row count

query assistance

Blog Archive

About Me