Tough Correlated Subquery Issue

Aug 29, 2006

I am running 2 versions of a correlated subquery. The two version
differ slightly in design but differ tremendously in performance....if
anyone can answer this, you would be awesome.

The "bad" query attempts to build a result set using a correlated
subquery. The part causing the error is that the correlated subquery
is part of a derived table (joining 3 tables). Trying to run the query
takes a long time and the more records in the source table, the
performance is exponentially worse. When i change the derived table to
a fixed table, the query runs fast.

I look at the Execution Plan in Query Analyzer and the majority of time
is taken by the Clustered Index Seek and by the Nested Loops/Inner
Join.
************************************************** ************************************************** ******
here is the "bad" query:
************************************************** ************************************************** ******
SELECT licenseKey, (
SELECT TOP 1 mi.id FROM messages mi
INNER JOIN identities i ON i.id=mi.identityid
INNER JOIN licenses l on i.licenseid=l.id
WHERE l.licenseKey = t1.licenseKey AND category = 'usage'
ORDER BY mi.created DESC
) as messageid
FROM licenses T1
************************************************** ************************************************** ******
here is the "good" query
************************************************** ************************************************** ******
SELECT licenseKey, (
SELECT TOP 1 t2.id FROM temptable T2
WHERE t2.licenseKey = t1.licenseKey
ORDER BY t2.created DESC
) as messageid
FROM licenses T1
************************************************** ************************************************** ******

Thank you in advance

View 5 Replies


ADVERTISEMENT

Correlated Subquery

Jun 6, 2008

 HiI was trying an example of subquery Create Table #Temp(    PK_ID int identity(1,1),    sName Varchar(50) )Create Table #TempAddress(    PK_ID int identity(1,1),    PersonID int,    Address Varchar(100))Insert Into #Temp Values('Karan')Insert Into #Temp Values('Gupta')Insert Into #Temp Values('Karan')Insert Into #Temp Values('Karan')Insert Into #TempAddress Values(1,'Address1')Insert Into #TempAddress Values(2,'Address2')Insert Into #TempAddress Values(3,'Address3')select PK_ID from #Temp a where (select PK_ID from #TempAddress b where a.PK_ID = b.PersonID)Drop Table #TempDrop Table #TempAddressBut I am getting an errorMsg 4145, Level 15, State 1, Line 29An expression of non-boolean type specified in a context where a condition is expected, near 'Drop'. Am i doing something wrong.Kindly adviceRegardsKaran  

View 1 Replies View Related

Correlated Subquery

Apr 28, 2000

Hello,

I need to come up with a query (I really don't want to use cursors) that will perform the following:

I have a message table that houses multiple users with multiple messages...for every user, I need to delete every message after the 20th oldest message.

Here are the pertinent fields: userid,message_id,message_date

I really appreciate your help,Rob

View 2 Replies View Related

Correlated Subquery

Nov 8, 2007

This really a question of approach more than anything else.

The situation is as follows:

I have a set of data that should contain one row for every company for every date in a supplied date range. Any companies that do not have a row for all dates, or have null values in certain required fields, should be dropped from the series all together.

So that...Given the date range 11/1/2007 to 11/2/2007 and the data set

Code:


11/1/2007 CompanyA req1 req2 req3 req4
11/2/2007 CompanyA req1 req2 null req4
11/1/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyC req1 null req3 req4



Only the two rows for CompanyB would ultimately be delivered.

On a tip I looked into correlated subqueries but that doesn't seem to fit. Does anyone else have an approach that might be best for a situation like this?

View 1 Replies View Related

Correlated Subquery

Mar 24, 2007

Use a correlated subquery to show the titles that have sales. Show title name, title id and quantity for each table?
Above is the original question.

My understanding below
I think two tables sales and titles. The title_name, title_id is in the titles table. Quantity is in the sales table. My question is, how can i write a correlated subquery to show titles that have sales?Any feed back is would be thankfull.

View 8 Replies View Related

Correlated Subquery Help!

Mar 24, 2008

I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!

select * from st_Meta_Field MF
INNER JOIN st_field F ON MF.Field_ID = F.Field_ID
where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY')
AND F.Record_State = 1 AND MF.Record_State = 1

select max(display_row),subset_value
from st_Meta_Field where table_id = 5 AND Display_Row < 500
group by Subset_Value

View 2 Replies View Related

Correlated Subquery Help!

Mar 24, 2008

I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!

select * from st_Meta_Field MF
INNER JOIN st_field F ON MF.Field_ID = F.Field_ID
where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY')
AND F.Record_State = 1 AND MF.Record_State = 1

select max(display_row),subset_value
from st_Meta_Field where table_id = 5 AND Display_Row < 500
group by Subset_Value

View 3 Replies View Related

Correlated Subquery In SQL Server

Mar 14, 2002

Hi, the following query works in Oracle, how do I do it in SQL Server? Thanks.


UPDATE table1 a SET a.newid =
(SELECT b.newid
FROM table2 b
WHERE a.id = b.id)


Basically, if table 1 and 2 have the same value in the "id" column, then I update the "newid" column in table a to match that of "newid" in table b.

View 1 Replies View Related

Correlated Subquery From Two Tables

Jan 19, 2014

I've been through my textbook, online articles, youtube ... you name it! Every reference to a correlated subquery that I have found involves only one table.

Two quick questions:

1. Is the below considered a correlated subquery?
2. Can you use a JOIN in an embedded SELECT statement? I ask because I have errors near the WHERE keyword in both subqueries.

USE MyGuitarShop
SELECT EmailAddress,
(SELECT MIN(OrderDate) FROM Orders JOIN Customers WHERE Orders.CustomerID = Customers.CustomerID) AS OldestOrder,
(SELECT Orders.OrderID FROM Orders JOIN OrderItems WHERE Orders.OrderID = OrderItems.OrderID) AS OrderID
FROM Customers
GROUP BY Customers.EmailAddress

View 10 Replies View Related

Multicolumn Correlated Subquery?

May 10, 2006

Hi,I have a history table with about 400 million rows, with a uniquecomposite nonclustered index on two columns (object id and time period)which is used for most of the queries into the table, and an identitycolumn for the clustered primary key.Many of my queries use correlated subqueries to pull unique historyrows from the history table for each of a set of objects from theobject table, for instance, pulling the earliest history row for eachobject in a set. These correlated subqueries reference the object tableand return the primary key of the history table, e.g.:select *from lp_object linner join lp_object_history hon h.lp_object_id = l.lp_idwhere l.lp_set_id = 'SOME_LITERAL'and h.lp_id = (select top 1 lp_idfrom lp_object_historywhere lp_object_id = l.lp_idand lp_some_column > 0order by lp_time_period)Now, if lp_some_column is not indexed, this query has no choice but toread the entirety of every single history row for every object in theset where lp_set_id = 'SOME_LITERAL', so that it can determine iflp_some_column > 0, and because the history table is clustered by theidentity column rather than the ID of the relevant object whose historywe're tracking, the reads take forever - they have to bop all aroundthe disk. The sets I deal with tend to have about 5K objects in themand about 200K associated history rows.I'm considering reclustering by the (object id, time period) index, butthen my queries will need an extra bookmark lookup step to get the rowdata from the identity value returned by the correlated subquery. Ithink it will still be faster, though, so I will probably build a copyof the table with the alternative clustering scheme to run someperformance tests.What I'm wondering is, if I were to dispense with the identity columnaltogether and replace it with a composite primary key of (object id,time period), would I be still be able to use my correlated subqueries?Because then there wouldn't be a single column that uniquely identifieseach row in the history table and I don't think SQL Server supportsmulticolumn correlated subqueries.Thanks for reading,Seth

View 4 Replies View Related

Correlated Subquery Efficiency

Jul 20, 2005

Hello All,I have a SQL Query with multiple correlated Subqueries in it. When itgets executed it runs rather slow due to the size of the QT table.Does anybody have any suggestions how to alter this query to make itrun faster, or any index suggestions to assist it with.Query is as follows:SELECT SH_ORDER, SH_CUST, SH_ADD_DATE, SH_CUST_REF, SH_DESC, SH_EXCL,(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (JU_PROC_GRP < 2)AND (QT_QUOTE_JOB = 0))AS [PREPCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 1)) AS [MATCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 3)) AS [OUTCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND((JU_CATEG = 0) OR (JU_CATEG = 2) OR (JU_CATEG = 4))) AS [LABCOST]FROM SHWHERE SH_ADD_DATE = '5/FEB/2004'thanks a lot for any helpJason

View 1 Replies View Related

Trouble With Correlated Subquery, Example Using Northwind

Aug 13, 2005

Suppose I have this query, which shows each order and the price of the most expensive item in each order:

SELECT Ord.OrderID, Ord.OrderDate,     MAX(OrdDet.UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details] AS OrdDet     INNER JOIN     Northwind.dbo.Orders AS Ord    ON Ord.OrderID = OrdDet.OrderID
GROUP BY Ord.OrderID, Ord.OrderDate

I need to also show the ProductID that has MaxUnitPrice from the Order
Details.  I can't just add ProductID to the select list because
I'd have to group by it, and then I'd get a row for each product,
instead of a row for each order... I think I need a correlated subquery
but can't work out how to do it!

View 1 Replies View Related

Correlated Subquery With Distinct Record

Jul 20, 2005

record_id Status Due_date549In Progress2004-06-02 00:00:00.000549Not Started2004-06-07 00:00:00.000549Not Started2004-06-08 00:00:00.000549Waiting 2004-05-31 00:00:00.000549Waiting 2004-06-04 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-06 00:00:00.000551Completed2004-05-07 00:00:00.000551Completed2004-05-10 00:00:00.000551Not Started1900-01-01 00:00:00.000552Not Started1900-01-01 00:00:00.000Hi I have this table with 3 columns.. What I want isDistinct(record_id),max(due_date) and Status.. I tried thisselect distinct(record_id),status,(due_date) from table1 where(due_date) in(select max(due_date) from table1 as A where a.record_id=record_idand a.due_date is not null group by a.record_id,status)So the result that I want isRecord Status Max(due_date)549Not Started2004-06-09 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-10 00:00:00.000Any help is appreciated..ThanksAJ

View 3 Replies View Related

Correlated Subquery And Date Filtering Problem

Oct 6, 2006

Note the following sql query. It contains two separate queries, an correlated subquery and outer query to work against the results of the subquery. Its purpose is twofold (1) get the TOP n ranked field entities using a certain value, (2) return all records for those entities.

SELECT MasterLoanID, NoteNumber, LendingOfficer,OriginalAmount, ReviewSampling FROM MasterLoanData WHERE Import_AsOfDate = '2006-05-31' AND BankID = '1' AND clientID = 1 AND LendingOfficer IN(SELECT TOP 3 LendingOfficer FROM MasterLoanData WHERE Import_AsofDate = '2006-05-31' AND ClientID = 1 AND BankID = '1' GROUP BY LendingOfficer ORDER BY SUM(OriginalAmount) DESC) ORDER BY LendingOfficer, Notenumber

Note that both queries need to filter the same fields -- import_AsofDate, BankID, and ClientID -- in order produce accurate results. Separate indexes exist for all three fields. Both queries work against the Sql Express database, however, when I combine them in a sql statement, Sql Express seems totally lost -- the query runs but never finishes -- I have to abort execution!

I've isolated the problem down to referencing of the import_AsofDate field in the outer query WHERE clause. If I remove that field reference from the outer WHERE clause, the query works quickly -- in seconds, however the results aren't accurate because I'm not getting a filter against the correct Import_AsofDate value. Note too that the same Access database executes the same query in seconds! Sql Express just seems totally confused by date reference contained in both WHERE clauses. I'd say that signifies a definite problem in Sql Express.

Has anyone experienced a similar problem with a correlated subquery and the same date field being referenced in both WHERE clauses of each query? I don't currently have a full fledged Sql Server database to test this query against, but seems as though it should work.

Rick

View 7 Replies View Related

Correlated Subquery Column Referencing Outer Date Range

Aug 25, 2006







Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?

Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)

select
OrderDate,
ShipCountry,
ShipCity,
Freight,
/* how do I get to the outer query's date range ? */
(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
Northwind.dbo.Orders O
where
ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'


Thanks

View 9 Replies View Related

Correlated Subquery - Column Prefix 'OJ' Does Not Match With A Table Name Or Alias Name Used In The Query.

Feb 2, 2007

I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE (
OuterID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL,
InnerID int NULL
)

INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2

DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)

INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3

INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5

INSERT @Inner VALUES (4, 2, 1) -- InnerID 6


-- UPDATE Outer Table with best match from Inner table
UPDATE @Outer SET
InnerID = IJ.InnerID
FROM @Outer OJ
INNER JOIN
(
SELECT TOP 1 I.*
FROM @Inner I
WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID
AND IsNull(I.CompID, OJ.CompID) = OJ.CompID
AND IsNull(I.FormID, OJ.FormID) = OJ.FormID
ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC
) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID)
AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID)
AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer
The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5.
Can anyone help me? Thanks in advance.

View 6 Replies View Related

Correlated Subquery - Sql Server 2000 Verses Sql Server 2005

Nov 14, 2006

Original code (works in Sql Server 2000 but has error in Sql Server 2005) --
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))

Error message in Sql Server 2005 --
Server: Msg 8180, Level 16, State 1, Line 38
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.

Corrected code --

AND SUBSTRING(CONVERT(CHAR,A_ED.EFFDT,121), 1, 10) <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))

View 10 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Apr 26, 2008

hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues]    @Uid intASBEGIN    SET NOCOUNT ON;        select                                  DATEPART(year, c.fy)as fy,                                                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1%      JRF' ) as survivorship,                (select contribeamount from wh_contribute where and contribename like  'Gross Earnings' and ) as ytdgross,                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5%      JRP') as totalcontrib,                                                       from    wh_contribute c                       where    c.uid=@Uid                 Order by fy Asc .....what is the wrong here??  " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap... 

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Jul 20, 2005

I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View 3 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

Mar 6, 2008

I am getting an error as

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

while running the following query.





SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,

LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,

LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,

LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,

ManagerName=(SELECT E.FirstName+' '+E.LastName

FROM EmployeeDetails E

INNER JOIN LUP_EmpProject

ON E.Empid=LUP_EmpProject.Empid

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid

WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)



FROM EmployeeDetails

INNER JOIN LUP_EmpDepartment

ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid

INNER JOIN LUP_FIX_DeptDetails

ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid

AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date

FROM LUP_EmpDepartment

WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid

ORDER BY LUP_EmpDepartment.Date DESC)

INNER JOIN LUP_EmpDesignation

ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid

INNER JOIN LUP_FIX_DesigDetails

ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid

AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date

FROM LUP_EmpDesignation

WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid

ORDER BY LUP_EmpDesignation.Date DESC)

INNER JOIN LUP_EmpProject

ON EmployeeDetails.Empid=LUP_EmpProject.Empid

AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate

FROM LUP_EmpProject

WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid

ORDER BY LUP_EmpProject.StartDate DESC)

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid



WHERE EmployeeDetails.Empid=1

PLEASE HELP.................

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

May 14, 2008

Hi,

I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Any ideas as to why??

SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')

Thanks

Jacquues

View 4 Replies View Related

Subquery Returned More Than 1 Value But Work FINE (field_xxx=subquery)

Jul 19, 2007

Hi guys,



A have a problem that I need understand.... I have 2 server with the same configuration...



SERVER01:

-----------------------------------------------------

Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)

Mar 27 2006 11:51:52

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



sp_dboption 'BB_XXXXX'

The following options are set:

-----------------------------------

trunc. log on chkpt.

auto create statistics

auto update statistics

********************************

SERVER02:

-----------------------------------------------------

Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)

Mar 27 2006 11:51:52

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



sp_dboption 'BB_XXXXX'

The following options are set:

-----------------------------------

trunc. log on chkpt.

auto create statistics

auto update statistics



OK, the problem is that if a run the below query in server01, i get error 512:



Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.



But, if run the same query in the server02, the query work fine -.



I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.



Any idea WHY?



SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,

dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,

dbo.opf_saldo_ctb_opc_flx.cd_classificacao,

dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,

dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,

'SD' ds_status_operacao,

dbo.opf_header_operacao.ds_tipo_opcao ,

dbo.opf_header_operacao.id_empresa,

dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,

0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,

0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,

0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,

0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,

0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,

0 vl_naoexec_premio,

sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,

sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,

sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,

sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,

sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf

FROM dbo.opf_saldo_ctb_opc_flx,

dbo.opf_header_operacao ,

dbo.opf_movimento_operacao

WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'

and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )

and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )

and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o

where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )

and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')

group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,

dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,

dbo.opf_saldo_ctb_opc_flx.cd_classificacao,

dbo.opf_movimento_operacao.ds_tipo_transacao,

dbo.opf_header_operacao.ds_tipo_transacao ,

ds_status_operacao,

dbo.opf_header_operacao.ds_tipo_opcao ,

dbo.opf_header_operacao.id_empresa,

dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente



Thanks

Nilton Pinheiro

View 9 Replies View Related

Adding Product Of A Subquery To A Subquery Fails?

Jul 6, 2014

I am trying to add the results of both of these queries together:

The purpose of the first query is to find the number of nulls in the TimeZone column.

Query 1:

SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename

The purpose of the second query is to find results in the AAST, AST, etc timezones.

Query 2:

SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')

Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.

What I came up with (from research) is:

SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))

I get a msq 102, level 15, state 1 error.

I also tried

SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results

but I still get an error. For the exact details see:

[URL]

NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.

View 6 Replies View Related

This Is A Tough One

Jun 27, 2007

The following table is counts of the patients that are currently in these statuses. For example, there are 4 people in Triage/Greenhttp://www.helixpoint.com/sql/TriageStatusGrid.jpgIf you look at a screen shot of the tables below, Red/Yellow/Green... These are in the Priority table. Red Being "Immediate or ID of 1http://www.helixpoint.com/sql/db.gifNow the grid and the db do not match...but here is a scenario from the db screen shot.. Patient 1, 2, 3, and 4 are currently in Sector 2 (transport)Patient 1, 3, and 4 are currently in Priority 1(Immediate)Here is a kicker. Patient 4 can not be counted because he has a dischargeDateTime in the Patient table.So here are the counts I need to get:So there are 3 patients in the transport column ( Patient 1, 2, 3) “4 has been discharged�2 patients in the immediate/Red column ( Patient 1, 3) “4 has been discharged�I would need to return a number 2 for the Transport/Red columnHow can I do this sql? Would I do this in multiple sql calls? Can you give me an example?This is what I triedIt does not seem to use the current status SELECT COUNT(*) AS Expr1FROM Patient LEFT OUTER JOINSectorHistory ON Patient.PatientID = SectorHistory.PatientID LEFT OUTER JOINPriorityHistory ON Patient.PatientID = PriorityHistory.PatientIDWHERE (Patient.DischargeDateTime IS NULL) AND (PriorityHistory.PriorityID = 1) AND (SectorHistory.SectorID = 2)

View 1 Replies View Related

Tough Question

Jun 25, 2004

I have an employee table, which i have to join with itself because I have to findout someones primary boss. An employee can have several bosses...


This is an example of the table.

Emp_Id 1
Emp_name John
Boss 'n/a'

Emp_Id 2
Emp_name Peter
Boss 1

Emp_Id 3
Emp_name Mary
Boss_Id 1

Emp_Id 3
Emp_name Mary
Boss_Id 2

I know this isnt a normalized table, but anyway... for each boss that an employee has there is one record depicting the employes boss.

In this case Mary has 2 bosses, John and Peter.


What I need is a query that returns the employee information and the primary boss( in this case the boss with the lowest id)

So for mary the query would return

Emp_iD, Emp_name, Boss_Id
3 Mary 1

Anyone know how could i do this?

View 2 Replies View Related

Tough Problem...

Jun 29, 2004

I need a query that will return data in the following format.

Col1 Col2 Count
---------------
A | B | 1
A | C | 2
A | D | 3
A | E | 4
B | A | 1
B | B | 2
B | C | 3
--------------

In other words I want to group the results by col1 and when col1 one changes I want to restart my rowcount. I also want to return the row count for each record.

I have tried many different methods, but I am starting to think that this is not even possible.

Any ideas?

Thanks...

View 13 Replies View Related

Tough Question

Apr 21, 2008

Ok, I'm needing to write a view for a 3rd party scripting tool to call. Apparently this scrpting tool cannot call a function or SP.

Here is the problem. The view needs to take a notes col and a date col
and concat them together as one col and the cus acct # as 2nd col.
There will be several rows of notes to one cus acct #.

So if a normal select against this returns

note date acct
================================
txt1 1/1/00 1
txt2 1/2/00 2
txt3 1/3/00 1

I need to return this through a view

notes acct
===========================
txt1 1/1/00, txt3 1/3/00 1
txt2 1/2/00 2

I realize there are several ways to do this in a SP or function
but through a view?

View 2 Replies View Related

Tough Query

Feb 13, 2004

Hi,

I have a table with a couple of million rows. Each row as its datetime field spilt between numerous columns (year, month, day, hour... it's a datawarehouse fact table).

Here's what I'd like to do in a query:

If a value is missing (in the column value) it's set to -999. What I'd like to do is to set this column to the value of the hour before this one. For example, here's some data before and after an update:

BEFORE:
MONTH DAY HOUR VALUE
==================
01 31 21 33.5
01 31 22 -999
03 07 24 87.6
03 08 01 -999

AFTER:
MONTH DAY HOUR VALUE
==================
01 31 21 33.5
01 31 22 33.5
03 07 24 87.6
03 08 01 87.6

Here's the complete design of my table
IDENT nvarchar 50
THEDATE datetime
THEDAY int
THEMONTH int
THEYEAR int
THEVALUE real
SOURCE nvarchar 255

How can I do this?

Thanks,

Skip.

View 2 Replies View Related

Tough Little Trigger

Jul 23, 2005

Given the following 3 Tables:CREATE TABLE [Company] ([CompanyID] [int] NOT NULL ,[DateTimeCreated] [datetime] NOT NULL CONSTRAINT[DF_Company_DateTimeCreated] DEFAULT (getdate()),[DateTimeModified] [datetime] NULL ,CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED([CompanyID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [CompanyOffice] ([CompanyID] [int] NOT NULL ,[OfficeID] [int] NOT NULL ,[IsActive] [bit] NOT NULL ,CONSTRAINT [PK_CompanyOffice] PRIMARY KEY CLUSTERED([CompanyID],[OfficeID]) ON [PRIMARY] ,CONSTRAINT [FK_CompanyOffice_Company] FOREIGN KEY([CompanyID]) REFERENCES [Company] ([CompanyID]),CONSTRAINT [FK_CompanyOffice_Office] FOREIGN KEY([OfficeID]) REFERENCES [Office] ([OfficeID])) ON [PRIMARY]GOCREATE TABLE [Office] ([OfficeID] [int] NOT NULL ,[DateTimeCreated] [datetime] NOT NULL CONSTRAINT[DF_Office_DateTimeCreated] DEFAULT (getdate()),[DateTimeModified] [datetime] NULL ,[FullOfficeName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,CONSTRAINT [PK_Office] PRIMARY KEY CLUSTERED([OfficeID]) ON [PRIMARY]) ON [PRIMARY]GOThe CompanyOffice.dbo.IsActive bit field is supposed to be marked"true" for 1 record per a given Office (i.e. there can only be a single"Active" Company for any given Office). I decided the best way toenforce is through a trigger...My initial thoughts were a toggling effect (similar to the behaviorthat a radio button exhibits)... which would work like a champ for aSingle Row Insert or Update but for a Multi Row Insert/Update not thatstaight forward... I fooled around a little with some complicatedsub-queries that did not pan out. The only other way to do this is toutilize a cursor (at least that I can think of). Because of theoverhead with a cursor, I find this incredibly undesirable.My secondary thought was to just restrict an Insert or Update Statementthat leaves the Table in an "error" state (2 or 0 Active Companies peran Office). Then I realized that if the "Toggling Trigger" did notexist from above, it will often be the case that the Table would haveto be left in an "error" state for a short while, until a second updatestatement is run. (example, I insert a new active Company in theCompanyOffice table for an Office, then I go to the other activeCompany record for this Office and set the IsActive flag to false...for that short period of time between the 2 statement the DB is an"error" state, because there are 2 Active Companies for that singleOffice.) That makes this solution very undesirable.Any suggestions?Thanks in Advance --Rich

View 7 Replies View Related

Tough Sql Query

Feb 18, 2006

I am going mad with this Query. I need to join 3 Tables. Their FormatsareVouchers[VoucherID] [uniqueidentifier] NOT NULL ,[VoucherTypeID] [int] NOT NULL ,[VoucherNo] [int] NULL ,[VoucherDate] [datetime] NOT NULL ,[VoucherNarration] [varchar] (255)CONSTRAINT [PK_Vouchers] PRIMARY KEY CLUSTERED([VoucherID]) ON [PRIMARY]Ledgers[LedgerID] [int] IDENTITY (1, 1) NOT NULL ,[LedgerName] [varchar] (50) COLLATECONSTRAINT [PK_Ledgers] PRIMARY KEY CLUSTERED([LedgerID]) ON [PRIMARY]CREATE TABLE [Transactions] ([TransactionID] [uniqueidentifier] NOT NULL ,[VoucherID] [uniqueidentifier] NOT NULL ,[ByTo] [char] (1)[LedgerID] [int] NOT NULL ,[Credit] [money] NOT NULL ,[Debit] [money] NOT NULL ,CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED([TransactionID]) ON [PRIMARY] ,CONSTRAINT [FK_Transactions_Ledgers] FOREIGN KEY([LedgerID]) REFERENCES [Ledgers] ([LedgerID]),CONSTRAINT [FK_Transactions_Vouchers] FOREIGN KEY([VoucherID]) REFERENCES [Vouchers] ([VoucherID])) ON [PRIMARY]GOThe Required Output isID VoucherNo VoucherDate LedgerName Amount1 1 2001-09-03 Bank-1 2400.002 2 2001-09-03 Cash 600.003 3 2001-09-03 TAX A/C 0.004 4 2001-09-03 Bank-1 4000.005 5 2001-09-03 Bank-10.00But, I am getting More than One row from the transactions table. I justneed the first matching rowID VoucherNo VoucherDate LedgerName Amount1 1 2001-09-03 Bank-1 2400.002 2 2001-09-03 Cash 600.003 3 2001-09-03 TAX A/C 0.004 4 2001-09-03 Bank-1 4000.005 4 2001-09-03 Cash 400.006 5 2001-09-03 Bank-1 0.007 5 2001-09-03 Cash 5035.00The Query I am using isSELECTdbo.Vouchers2001.VoucherID,dbo.Vouchers2001.VoucherNo,dbo.Vouchers2001.VoucherDate,dbo.Ledgers.LedgerName,SUM(dbo.Transactions2001.Debit) AS AmountFROM dbo.Vouchers2001 INNER JOINdbo.Transactions2001ON dbo.Vouchers2001.VoucherID =dbo.Transactions2001.VoucherID INNER JOINdbo.Ledgers ON dbo.Transactions2001.LedgerID =dbo.Ledgers.LedgerIDWHERE (dbo.Vouchers2001.VoucherTypeID = 1)GROUP BY dbo.Vouchers2001.VoucherID,dbo.Ledgers.LedgerName,dbo.Vouchers2001.VoucherDate,dbo.Vouchers2001.VoucherNo,dbo.Vouchers2001.VoucherTypeIDORDER BY dbo.Vouchers2001.VoucherID,dbo.Ledgers.LedgerName,dbo.Vouchers2001.VoucherDate,dbo.Vouchers2001.VoucherNoPlz help Out*** Sent via Developersdex http://www.developersdex.com ***

View 12 Replies View Related

Tough Query?

Aug 5, 2006

The following data set is building inspection visits. It consists ofmultiple visits (2+) made to the same building on the same day.I want to get a list of visits made to the same building on the same day,but by different employees, and for different visit codes (eg records 5-6,or 9-11)Here's the table=====================================CREATE TABLE VISITS(VISITID NUMBER(5,0) NOT NULL ,BLDGCODE VARCHAR2(10) NOT NULL ,VISITDATE DATE NOT NULL ,EMPID NUMBER(5,0) NOT NULL ,VISITCODE VARCHAR2(5) NOT NULL);ALTER TABLE VISITSADD CONSTRAINT PK_VISITS PRIMARY KEY(VISITID);CREATE UNIQUE INDEX UIDX_VISITS ON VISITS(BLDGCODE,VISITDATE,EMPID,VISITCODE);=====================================And here's the data:=====================================VISITID,BLDGCODE,VISITDATE,EMPID,VISITCODE1, BLDG1, 10/18/2005, 128, V62, BLDG1, 10/18/2005, 128, V93, BLDG2, 1/24/2006, 128, V84, BLDG2, 1/24/2006, 165, V225, BLDG3, 2/15/2006, 13, V146, BLDG3, 2/15/2006, 143, V87, BLDG4, 8/1/2006, 319, V98, BLDG4, 8/1/2006, 390, V99, BLDG4, 8/2/2006, 319, V910, BLDG4, 8/2/2006, 390, V911, BLDG4, 8/2/2006, 390, V812, BLDG5, 8/28/2006, 318, V1113, BLDG5, 8/28/2006, 376, V1114, BLDG5, 8/29/2006, 318, V1115, BLDG5, 8/29/2006, 334, V1116, BLDG5, 8/29/2006, 376, V1117, BLDG5, 8/30/2006, 318, V1118, BLDG5, 8/30/2006, 376, V1119, BLDG5, 8/30/2006, 334, V1120, BLDG5, 8/31/2006, 318, V1121, BLDG5, 8/31/2006, 376, V1122, BLDG5, 8/31/2006, 334, V1123, BLDG6, 10/11/2005, 323, V1924, BLDG6, 10/11/2005, 323, V2725, BLDG6, 11/8/2005, 323, V826, BLDG6, 11/8/2005, 323, V2727, BLDG7, 10/18/2005, 323, V328, BLDG7, 10/18/2005, 323, V2729, BLDG7, 11/14/2005, 14, V330, BLDG7, 11/14/2005, 323, V331, BLDG7, 11/14/2005, 143, V332, BLDG7, 12/15/2005, 143, V333, BLDG7, 12/15/2005, 323, V334, BLDG8, 3/8/2006, 15, V2335, BLDG8, 3/8/2006, 120, V2336, BLDG9, 5/22/2006, 25, V237, BLDG9, 5/22/2006, 391, V1438, BLDG10, 11/3/2005, 310, V639, BLDG10, 11/3/2005, 310, V840, BLDG10, 3/15/2006, 139, V2841, BLDG10, 3/15/2006, 310, V2842, BLDG10, 3/16/2006, 139, V2843, BLDG10, 3/16/2006, 310, V2844, BLDG11, 11/3/2005, 323, V2245, BLDG11, 11/3/2005, 323, V2746, BLDG12, 4/18/2006, 71, V247, BLDG12, 4/18/2006, 337, V1348, BLDG12, 4/19/2006, 71, V249, BLDG12, 4/19/2006, 337, V1350, BLDG13, 10/3/2005, 142, V2251, BLDG13, 10/3/2005, 142, V2752, BLDG14, 5/23/2006, 32, V2353, BLDG14, 5/23/2006, 139, V2354, BLDG14, 5/24/2006, 32, V2355, BLDG14, 5/24/2006, 139, V2356, BLDG15, 5/30/2006, 141, V1757, BLDG15, 5/30/2006, 141, V2258, BLDG16, 6/1/2006, 71, V1859, BLDG16, 6/1/2006, 336, V1860, BLDG16, 6/1/2006, 123, V1861, BLDG17, 2/21/2006, 34, V862, BLDG17, 2/21/2006, 34, V1963, BLDG18, 12/14/2005, 141, V764, BLDG18, 12/14/2005, 141, V1765, BLDG19, 10/18/2005, 320, V1466, BLDG19, 10/18/2005, 320, V1667, BLDG20, 3/6/2006, 141, V868, BLDG20, 3/6/2006, 141, V2269, BLDG21, 10/11/2005, 324, V670, BLDG21, 10/11/2005, 324, V771, BLDG22, 7/10/2006, 38, V2372, BLDG22, 7/10/2006, 252, V1173, BLDG22, 7/11/2006, 38, V2374, BLDG22, 7/11/2006, 252, V1175, BLDG22, 7/11/2006, 142, V2276, BLDG23, 11/10/2005, 308, V777, BLDG23, 11/10/2005, 308, V878, BLDG23, 5/11/2006, 308, V879, BLDG23, 5/11/2006, 391, V280, BLDG24, 3/23/2006, 143, V2481, BLDG24, 3/23/2006, 155, V282, BLDG24, 3/24/2006, 143, V2483, BLDG24, 3/24/2006, 155, V2584, BLDG25, 10/3/2005, 31, V1485, BLDG25, 10/3/2005, 31, V1986, BLDG26, 2/20/2006, 31, V1487, BLDG26, 2/20/2006, 31, V2288, BLDG27, 2/15/2006, 13, V1489, BLDG27, 2/15/2006, 143, V890, BLDG28, 10/12/2005, 141, V891, BLDG28, 10/12/2005, 141, V1792, BLDG29, 10/4/2005, 32, V2293, BLDG29, 10/4/2005, 310, V294, BLDG30, 9/12/2005, 53, V2395, BLDG30, 9/12/2005, 123, V2196, BLDG30, 9/12/2005, 141, V2397, BLDG30, 9/13/2005, 53, V2398, BLDG30, 9/13/2005, 141, V2399, BLDG30, 9/13/2005, 123, V21100, BLDG30, 9/14/2005, 53, V23101, BLDG30, 9/14/2005, 141, V23102, BLDG30, 9/14/2005, 123, V21103, BLDG31, 2/14/2006, 13, V14104, BLDG31, 2/14/2006, 143, V8105, BLDG32, 11/1/2005, 320, V3106, BLDG32, 11/1/2005, 320, V27107, BLDG33, 11/3/2005, 34, V7108, BLDG33, 11/3/2005, 34, V19109, BLDG34, 7/10/2006, 37, V23110, BLDG34, 7/10/2006, 62, V23111, BLDG34, 7/11/2006, 37, V23112, BLDG34, 7/11/2006, 62, V23113, BLDG34, 7/12/2006, 37, V23114, BLDG34, 7/12/2006, 62, V23115, BLDG35, 11/21/2005, 78, V27116, BLDG35, 11/21/2005, 334, V8117, BLDG36, 7/10/2006, 358, V1118, BLDG36, 7/10/2006, 358, V8119, BLDG37, 8/14/2006, 50, V14120, BLDG37, 8/14/2006, 71, V11121, BLDG37, 8/15/2006, 50, V14122, BLDG37, 8/15/2006, 71, V11123, BLDG38, 9/13/2005, 130, V6124, BLDG38, 9/13/2005, 130, V8125, BLDG39, 2/22/2006, 34, V8126, BLDG39, 2/22/2006, 34, V14127, BLDG40, 2/14/2006, 13, V14128, BLDG40, 2/14/2006, 143, V8129, BLDG41, 5/22/2006, 252, V17130, BLDG41, 5/22/2006, 326, V17131, BLDG41, 5/23/2006, 252, V17132, BLDG41, 5/23/2006, 326, V17133, BLDG42, 7/10/2006, 309, V2134, BLDG42, 7/10/2006, 318, V23135, BLDG42, 7/11/2006, 309, V2136, BLDG42, 7/11/2006, 318, V23137, BLDG42, 7/12/2006, 309, V2138, BLDG42, 7/12/2006, 318, V23139, BLDG43, 10/18/2005, 206, V8140, BLDG43, 10/18/2005, 206, V14141, BLDG44, 3/9/2006, 142, V24142, BLDG44, 3/9/2006, 233, V23143, BLDG44, 3/9/2006, 319, V24144, BLDG44, 3/10/2006, 142, V24145, BLDG44, 3/10/2006, 319, V24146, BLDG44, 3/10/2006, 233, V23147, BLDG45, 9/15/2005, 128, V6148, BLDG45, 9/15/2005, 128, V9149, BLDG46, 5/24/2006, 25, V2150, BLDG46, 5/24/2006, 391, V8151, BLDG47, 1/17/2006, 321, V6152, BLDG47, 1/17/2006, 321, V22153, BLDG48, 7/13/2006, 38, V18154, BLDG48, 7/13/2006, 318, V11155, BLDG49, 7/12/2006, 142, V23156, BLDG49, 7/12/2006, 263, V23157, BLDG50, 4/11/2006, 62, V24158, BLDG50, 4/11/2006, 142, V24159, BLDG50, 4/12/2006, 62, V24160, BLDG50, 4/12/2006, 142, V24161, BLDG51, 10/13/2005, 78, V13162, BLDG51, 10/13/2005, 325, V13163, BLDG52, 5/2/2006, 145, V9164, BLDG52, 5/2/2006, 390, V12165, BLDG52, 5/2/2006, 390, V9166, BLDG52, 5/3/2006, 145, V8167, BLDG52, 5/3/2006, 390, V9168, BLDG52, 5/3/2006, 390, V12169, BLDG53, 12/14/2005, 76, V9170, BLDG53, 12/14/2005, 322, V9171, BLDG53, 12/15/2005, 76, V9172, BLDG53, 12/15/2005, 322, V9173, BLDG53, 12/15/2005, 322, V22174, BLDG54, 9/6/2005, 323, V3175, BLDG54, 9/6/2005, 323, V27176, BLDG54, 12/13/2005, 323, V22177, BLDG54, 12/13/2005, 323, V27178, BLDG55, 9/6/2005, 129, V21179, BLDG55, 9/6/2005, 233, V23180, BLDG55, 9/7/2005, 38, V23181, BLDG55, 9/7/2005, 233, V23182, BLDG55, 9/7/2005, 142, V23183, BLDG55, 9/7/2005, 129, V21184, BLDG55, 9/8/2005, 38, V23185, BLDG55, 9/8/2005, 233, V23186, BLDG55, 9/8/2005, 142, V23187, BLDG55, 9/8/2005, 129, V21188, BLDG55, 9/9/2005, 129, V21189, BLDG55, 9/9/2005, 233, V23190, BLDG55, 6/20/2006, 142, V8191, BLDG55, 6/20/2006, 142, V29192, BLDG56, 6/28/2006, 131, V13193, BLDG56, 6/28/2006, 319, V13194, BLDG56, 6/29/2006, 131, V13195, BLDG56, 6/29/2006, 319, V13196, BLDG57, 11/8/2005, 320, V22197, BLDG57, 11/8/2005, 320, V27198, BLDG58, 1/25/2006, 13, V3199, BLDG58, 1/25/2006, 14, V13200, BLDG59, 11/29/2005, 233, V9201, BLDG59, 11/29/2005, 233, V14202, BLDG60, 2/8/2006, 323, V22203, BLDG60, 2/8/2006, 323, V27204, BLDG61, 1/17/2006, 166, V3205, BLDG61, 1/17/2006, 166, V22206, BLDG62, 9/27/2005, 320, V3207, BLDG62, 9/27/2005, 320, V22208, BLDG62, 2/21/2006, 115, V9209, BLDG62, 2/21/2006, 320, V9210, BLDG62, 2/22/2006, 115, V9211, BLDG62, 2/22/2006, 320, V9212, BLDG63, 11/14/2005, 87, V11213, BLDG63, 11/14/2005, 129, V27214, BLDG63, 11/14/2005, 323, V27215, BLDG63, 11/15/2005, 129, V11216, BLDG63, 11/15/2005, 143, V11217, BLDG63, 11/16/2005, 129, V11218, BLDG63, 11/16/2005, 143, V11219, BLDG63, 11/17/2005, 129, V11220, BLDG63, 11/17/2005, 143, V11221, BLDG63, 11/18/2005, 129, V27222, BLDG63, 11/18/2005, 143, V11223, BLDG64, 6/7/2006, 253, V2224, BLDG64, 6/7/2006, 391, V6225, BLDG65, 6/7/2006, 253, V2226, BLDG65, 6/7/2006, 391, V14227, BLDG66, 1/11/2006, 39, V25228, BLDG66, 1/11/2006, 141, V25229, BLDG66, 1/12/2006, 39, V25230, BLDG66, 1/12/2006, 141, V25231, BLDG66, 3/20/2006, 39, V23232, BLDG66, 3/20/2006, 76, V23233, BLDG66, 3/21/2006, 39, V23234, BLDG66, 3/21/2006, 115, V23235, BLDG66, 3/21/2006, 76, V23236, BLDG66, 3/22/2006, 39, V23237, BLDG66, 3/22/2006, 115, V23238, BLDG66, 3/22/2006, 76, V23239, BLDG67, 5/26/2006, 141, V7240, BLDG67, 5/26/2006, 141, V17241, BLDG68, 12/21/2005, 141, V8242, BLDG68, 12/21/2005, 141, V17243, BLDG69, 5/23/2006, 50, V3244, BLDG69, 5/23/2006, 50, V8245, BLDG70, 2/1/2006, 114, V17246, BLDG70, 2/1/2006, 114, V22247, BLDG71, 10/11/2005, 131, V8248, BLDG71, 10/11/2005, 334, V8249, BLDG71, 3/10/2006, 334, V8250, BLDG71, 3/10/2006, 334, V22251, BLDG72, 7/31/2006, 398, V2252, BLDG72, 7/31/2006, 398, V22253, BLDG73, 11/30/2005, 129, V23254, BLDG73, 11/30/2005, 326, V24255, BLDG74, 11/29/2005, 143, V22256, BLDG74, 11/29/2005, 143, V23257, BLDG75, 4/26/2006, 12, V18258, BLDG75, 4/26/2006, 17, V18259, BLDG76, 6/6/2006, 320, V14260, BLDG76, 6/6/2006, 320, V15261, BLDG77, 1/10/2006, 78, V2262, BLDG77, 1/10/2006, 325, V9263, BLDG77, 1/11/2006, 78, V2264, BLDG77, 1/11/2006, 325, V8265, BLDG77, 1/11/2006, 325, V6266, BLDG78, 5/17/2006, 141, V17267, BLDG78, 5/17/2006, 141, V22268, BLDG79, 9/13/2005, 37, V19269, BLDG79, 9/13/2005, 318, V19270, BLDG80, 12/20/2005, 34, V13271, BLDG80, 12/20/2005, 250, V13272, BLDG81, 4/19/2006, 25, V22273, BLDG81, 4/19/2006, 391, V2274, BLDG82, 5/3/2006, 108, V14275, BLDG82, 5/3/2006, 391, V2276, BLDG83, 6/19/2006, 36, V8277, BLDG83, 6/19/2006, 393, V8278, BLDG84, 2/13/2006, 13, V14279, BLDG84, 2/13/2006, 143, V8280, BLDG85, 5/8/2006, 308, V8281, BLDG85, 5/8/2006, 391, V2282, BLDG86, 4/25/2006, 322, V8283, BLDG86, 4/25/2006, 322, V22284, BLDG87, 7/14/2006, 322, V13285, BLDG87, 7/14/2006, 322, V15286, BLDG88, 2/2/2006, 322, V8287, BLDG88, 2/2/2006, 322, V22288, BLDG89, 4/13/2006, 390, V2289, BLDG89, 4/13/2006, 390, V8290, BLDG90, 10/12/2005, 131, V8291, BLDG90, 10/12/2005, 334, V8292, BLDG91, 6/26/2006, 131, V22293, BLDG91, 6/26/2006, 319, V22294, BLDG91, 6/27/2006, 131, V22295, BLDG91, 6/27/2006, 319, V22296, BLDG92, 3/1/2006, 39, V23297, BLDG92, 3/1/2006, 141, V23298, BLDG92, 3/2/2006, 39, V23299, BLDG92, 3/2/2006, 115, V23300, BLDG92, 3/2/2006, 141, V23301, BLDG92, 3/3/2006, 39, V23302, BLDG92, 3/3/2006, 141, V23303, BLDG92, 3/3/2006, 115, V23304, BLDG92, 7/20/2006, 115, V23305, BLDG92, 7/20/2006, 141, V25306, BLDG92, 7/21/2006, 115, V23307, BLDG92, 7/21/2006, 141, V25308, BLDG93, 5/8/2006, 78, V2309, BLDG93, 5/8/2006, 325, V9310, BLDG93, 5/9/2006, 78, V2311, BLDG93, 5/9/2006, 78, V9312, BLDG93, 5/9/2006, 325, V9313, BLDG94, 6/19/2006, 128, V9314, BLDG94, 6/19/2006, 358, V9315, BLDG94, 6/20/2006, 128, V9316, BLDG94, 6/20/2006, 358, V9317, BLDG95, 6/6/2006, 253, V2318, BLDG95, 6/6/2006, 391, V14319, BLDG96, 6/6/2006, 253, V2320, BLDG96, 6/6/2006, 391, V14321, BLDG97, 6/5/2006, 253, V7322, BLDG97, 6/5/2006, 391, V7323, BLDG98, 1/24/2006, 322, V9324, BLDG98, 1/24/2006, 322, V22325, BLDG99, 10/12/2005, 323, V7326, BLDG99, 10/12/2005, 323, V15327, BLDG100, 12/21/2005, 320, V14328, BLDG100, 12/21/2005, 320, V22329, BLDG100, 2/23/2006, 115, V9330, BLDG100, 2/23/2006, 320, V9331, BLDG100, 2/24/2006, 115, V9332, BLDG100, 2/24/2006, 320, V9333, BLDG101, 2/22/2006, 115, V9334, BLDG101, 2/22/2006, 320, V9335, BLDG101, 2/23/2006, 115, V9336, BLDG101, 2/23/2006, 320, V9337, BLDG102, 10/13/2005, 131, V8338, BLDG102, 10/13/2005, 334, V8339, BLDG103, 1/12/2006, 119, V7340, BLDG103, 1/12/2006, 119, V22341, BLDG104, 5/17/2006, 233, V23342, BLDG104, 5/17/2006, 243, V23343, BLDG104, 5/18/2006, 233, V23344, BLDG104, 5/18/2006, 243, V23345, BLDG105, 11/22/2005, 309, V6346, BLDG105, 11/22/2005, 309, V22347, BLDG106, 1/12/2006, 166, V8348, BLDG106, 1/12/2006, 166, V22349, BLDG107, 9/27/2005, 206, V7350, BLDG107, 9/27/2005, 206, V20351, BLDG108, 4/12/2006, 322, V14352, BLDG108, 4/12/2006, 322, V22353, BLDG109, 3/27/2006, 17, V11354, BLDG109, 3/27/2006, 358, V11355, BLDG109, 3/27/2006, 127, V11356, BLDG109, 3/27/2006, 142, V11357, BLDG109, 3/27/2006, 144, V11358, BLDG109, 3/27/2006, 318, V11359, BLDG109, 3/27/2006, 129, V11360, BLDG109, 3/28/2006, 17, V11361, BLDG109, 3/28/2006, 115, V11362, BLDG109, 3/28/2006, 358, V11363, BLDG109, 3/28/2006, 334, V11364, BLDG109, 3/28/2006, 323, V11365, BLDG109, 3/28/2006, 318, V11366, BLDG109, 3/28/2006, 144, V11367, BLDG109, 3/28/2006, 142, V11368, BLDG109, 3/28/2006, 129, V11369, BLDG109, 3/28/2006, 127, V11370, BLDG109, 3/29/2006, 17, V11371, BLDG109, 3/29/2006, 323, V11372, BLDG109, 3/29/2006, 358, V11373, BLDG109, 3/29/2006, 334, V11374, BLDG109, 3/29/2006, 318, V11375, BLDG109, 3/29/2006, 144, V11376, BLDG109, 3/29/2006, 142, V11377, BLDG109, 3/29/2006, 129, V11378, BLDG109, 3/29/2006, 127, V11379, BLDG109, 3/29/2006, 115, V11380, BLDG109, 3/30/2006, 17, V11381, BLDG109, 3/30/2006, 129, V11382, BLDG109, 3/30/2006, 358, V11383, BLDG109, 3/30/2006, 334, V11384, BLDG109, 3/30/2006, 323, V11385, BLDG109, 3/30/2006, 318, V11386, BLDG109, 3/30/2006, 144, V11387, BLDG109, 3/30/2006, 142, V11388, BLDG109, 3/30/2006, 127, V11389, BLDG109, 3/30/2006, 115, V11390, BLDG109, 3/31/2006, 17, V11391, BLDG109, 3/31/2006, 318, V11392, BLDG109, 3/31/2006, 358, V11393, BLDG109, 3/31/2006, 144, V11394, BLDG109, 3/31/2006, 142, V11395, BLDG109, 3/31/2006, 129, V11396, BLDG109, 3/31/2006, 127, V11397, BLDG109, 3/31/2006, 115, V11398, BLDG110, 11/7/2005, 320, V22399, BLDG110, 11/7/2005, 320, V27400, BLDG111, 10/12/2005, 146, V8401, BLDG111, 10/12/2005, 146, V22402, BLDG112, 2/14/2006, 141, V8403, BLDG112, 2/14/2006, 141, V22404, BLDG113, 2/15/2006, 145, V9405, BLDG113, 2/15/2006, 233, V9406, BLDG113, 2/16/2006, 145, V8407, BLDG113, 2/16/2006, 233, V9408, BLDG114, 1/26/2006, 310, V6409, BLDG114, 1/26/2006, 310, V8410, BLDG115, 5/5/2006, 36, V9411, BLDG115, 5/5/2006, 376, V9412, BLDG115, 7/5/2006, 36, V9413, BLDG115, 7/5/2006, 376, V9414, BLDG115, 7/6/2006, 36, V9415, BLDG115, 7/6/2006, 376, V9416, BLDG116, 4/12/2006, 34, V4417, BLDG116, 4/12/2006, 34, V13418, BLDG117, 12/27/2005, 323, V13419, BLDG117, 12/27/2005, 323, V27420, BLDG117, 2/17/2006, 14, V3421, BLDG117, 2/17/2006, 323, V3422, BLDG118, 1/31/2006, 308, V17423, BLDG118, 1/31/2006, 308, V22424, BLDG119, 5/9/2006, 308, V8425, BLDG119, 5/9/2006, 391, V2426, BLDG120, 11/8/2005, 233, V28427, BLDG120, 11/8/2005, 233, V29428, BLDG121, 12/15/2005, 141, V8429, BLDG121, 12/15/2005, 141, V17430, BLDG122, 5/23/2006, 25, V2431, BLDG122, 5/23/2006, 391, V22432, BLDG123, 4/20/2006, 25, V14433, BLDG123, 4/20/2006, 391, V2434, BLDG124, 4/17/2006, 25, V22435, BLDG124, 4/17/2006, 391, V2436, BLDG125, 4/18/2006, 25, V22437, BLDG125, 4/18/2006, 391, V2438, BLDG126, 10/18/2005, 13, V19439, BLDG126, 10/18/2005, 13, V22440, BLDG127, 5/10/2006, 308, V8441, BLDG127, 5/10/2006, 391, V2442, BLDG128, 1/10/2006, 78, V11443, BLDG128, 1/10/2006, 233, V23444, BLDG129, 11/8/2005, 78, V9445, BLDG129, 11/8/2005, 325, V9446, BLDG129, 11/9/2005, 78, V9447, BLDG129, 11/9/2005, 325, V9448, BLDG130, 10/18/2005, 325, V28449, BLDG130, 10/18/2005, 334, V9450, BLDG130, 11/9/2005, 78, V9451, BLDG130, 11/9/2005, 334, V9452, BLDG130, 11/9/2005, 325, V9453, BLDG130, 11/10/2005, 78, V9454, BLDG130, 11/10/2005, 325, V9455, BLDG130, 11/10/2005, 334, V6456, BLDG131, 5/30/2006, 50, V3457, BLDG131, 5/30/2006, 50, V8458, BLDG132, 5/24/2006, 31, V6459, BLDG132, 5/24/2006, 31, V22460, BLDG133, 2/7/2006, 114, V17461, BLDG133, 2/7/2006, 114, V22462, BLDG134, 6/21/2006, 62, V9463, BLDG134, 6/21/2006, 309, V28464, BLDG135, 5/10/2006, 78, V9465, BLDG135, 5/10/2006, 325, V9466, BLDG135, 5/11/2006, 78, V9467, BLDG135, 5/11/2006, 325, V9468, BLDG136, 12/15/2005, 129, V23469, BLDG136, 12/15/2005, 233, V23470, BLDG137, 5/17/2006, 129, V6471, BLDG137, 5/17/2006, 129, V8472, BLDG138, 3/8/2006, 336, V8473, BLDG138, 3/8/2006, 336, V22474, BLDG139, 3/20/2006, 129, V14475, BLDG139, 3/20/2006, 129, V19476, BLDG140, 3/22/2006, 52, V29477, BLDG140, 3/22/2006, 334, V29478, BLDG140, 3/23/2006, 52, V29479, BLDG140, 3/23/2006, 334, V29480, BLDG141, 1/4/2006, 78, V22481, BLDG141, 1/4/2006, 334, V22482, BLDG142, 9/7/2005, 52, V8483, BLDG142, 9/7/2005, 52, V10484, BLDG143, 5/4/2006, 78, V28485, BLDG143, 5/4/2006, 334, V29486, BLDG144, 5/11/2006, 50, V3487, BLDG144, 5/11/2006, 50, V8488, BLDG144, 5/12/2006, 50, V7489, BLDG144, 5/12/2006, 50, V8490, BLDG145, 10/4/2005, 119, V16491, BLDG145, 10/4/2005, 119, V19492, BLDG146, 6/6/2006, 34, V7493, BLDG146, 6/6/2006, 34, V8494, BLDG147, 9/14/2005, 36, V28495, BLDG147, 9/14/2005, 324, V5496, BLDG148, 9/14/2005, 322, V14497, BLDG148, 9/14/2005, 322, V15498, BLDG149, 6/20/2006, 123, V8499, BLDG149, 6/20/2006, 123, V22500, BLDG150, 5/16/2006, 12, V18501, BLDG150, 5/16/2006, 318, V18502, BLDG151, 4/17/2006, 62, V23503, BLDG151, 4/17/2006, 142, V23504, BLDG151, 4/17/2006, 318, V23505, BLDG151, 4/17/2006, 154, V2506, BLDG151, 4/18/2006, 62, V23507, BLDG151, 4/18/2006, 318, V23508, BLDG151, 4/18/2006, 142, V23509, BLDG151, 4/18/2006, 154, V2510, BLDG151, 4/19/2006, 62, V22511, BLDG151, 4/19/2006, 318, V23512, BLDG151, 4/19/2006, 154, V2513, BLDG151, 4/19/2006, 142, V23514, BLDG152, 10/20/2005, 320, V14515, BLDG152, 10/20/2005, 320, V15516, BLDG153, 7/11/2006, 334, V8517, BLDG153, 7/11/2006, 334, V9518, BLDG154, 2/1/2006, 53, V23519, BLDG154, 2/1/2006, 323, V23520, BLDG154, 2/2/2006, 53, V23521, BLDG154, 2/2/2006, 323, V23522, BLDG154, 2/3/2006, 53, V23523, BLDG154, 2/3/2006, 323, V27524, BLDG154, 2/3/2006, 323, V23525, BLDG154, 7/12/2006, 53, V24526, BLDG154, 7/12/2006, 139, V26527, BLDG154, 7/12/2006, 141, V25528, BLDG154, 7/13/2006, 53, V24529, BLDG154, 7/13/2006, 139, V26530, BLDG154, 7/13/2006, 141, V25531, BLDG154, 7/14/2006, 53, V24532, BLDG154, 7/14/2006, 141, V25533, BLDG154, 7/14/2006, 139, V26534, BLDG155, 9/14/2005, 323, V7535, BLDG155, 9/14/2005, 323, V27536, BLDG156, 9/12/2005, 165, V6537, BLDG156, 9/12/2005, 165, V8538, BLDG157, 10/27/2005, 32, V13539, BLDG157, 10/27/2005, 310, V13540, BLDG158, 11/2/2005, 320, V14541, BLDG158, 11/2/2005, 320, V15542, BLDG158, 2/20/2006, 115, V9543, BLDG158, 2/20/2006, 320, V9544, BLDG158, 2/21/2006, 115, V9545, BLDG158, 2/21/2006, 320, V9546, BLDG158, 4/26/2006, 320, V3547, BLDG158, 4/26/2006, 320, V17548, BLDG159, 3/14/2006, 336, V14549, BLDG159, 3/14/2006, 336, V22550, BLDG160, 3/15/2006, 336, V8551, BLDG160, 3/15/2006, 336, V14552, BLDG160, 8/24/2006, 336, V8553, BLDG160, 8/24/2006, 336, V27554, BLDG161, 1/5/2006, 253, V6555, BLDG161, 1/5/2006, 253, V17556, BLDG162, 3/13/2006, 141, V9557, BLDG162, 3/13/2006, 141, V22558, BLDG163, 6/29/2006, 78, V6559, BLDG163, 6/29/2006, 78, V9560, BLDG164, 12/12/2005, 76, V9561, BLDG164, 12/12/2005, 322, V9562, BLDG164, 12/13/2005, 76, V9563, BLDG164, 12/13/2005, 322, V22564, BLDG164, 12/13/2005, 322, V9565, BLDG165, 1/11/2006, 166, V8566, BLDG165, 1/11/2006, 166, V22567, BLDG166, 5/24/2006, 141, V17568, BLDG166, 5/24/2006, 141, V22569, BLDG167, 1/11/2006, 165, V8570, BLDG167, 1/11/2006, 165, V22571, BLDG168, 10/18/2005, 244, V14572, BLDG168, 10/18/2005, 309, V14573, BLDG169, 1/31/2006, 144, V8574, BLDG169, 1/31/2006, 144, V22575, BLDG170, 6/6/2006, 123, V18576, BLDG170, 6/6/2006, 383, V18577, BLDG171, 1/17/2006, 263, V6578, BLDG171, 1/17/2006, 263, V9579, BLDG172, 8/10/2006, 233, V9580, BLDG172, 8/10/2006, 376, V9581, BLDG172, 8/11/2006, 233, V9582, BLDG172, 8/11/2006, 376, V9583, BLDG173, 4/4/2006, 131, V13584, BLDG173, 4/4/2006, 144, V13585, BLDG174, 7/4/2006, 383, V4586, BLDG174, 7/4/2006, 383, V8=====================================Thanks

View 9 Replies View Related

Tough Problem, Need Help

Jul 20, 2005

I have a very strange database with a very strange problem.Consider 4 tables:Table1:----------------Table1ID INT PKTable2ID INT FKTable3ID INT FKOrderNo VARCHAR(50)Table2----------------Table2ID INT PKTable4ID INT FKTable3----------------Table3ID INT PKTable2ID INT FKTable4----------------Table4ID INT PKOrderTotal VARCHAR(50)With Data:Table1:------------1 1 NULL 900012 2 NULL 900023 NULL 1 900034 NULL 2 90004Table2:------------1 12 1Table3:------------1 12 2Table4:------------1 5002 1000Table1 can have either a Table2ID OR a Table3ID but not both.This is the query I'm attempting:---------------------------SELECT dbo.Table1.OrderNo, dbo.Table4.OrderTotalFROM dbo.Table1 LEFT OUTER JOINdbo.Table4 INNER JOINdbo.Table2 ON dbo.Table4.Table4ID =dbo.Table2.Table4ID INNER JOINdbo.Table3 ON dbo.Table2.Table2ID =dbo.Table3.Table3ID ON dbo.Table1.Table2ID = dbo.Table2.Table2ID ANDdbo.Table1.Table3ID = dbo.Table3.Table3IDWhich gives me:---------------------------90001 NULL90002 NULL90003 NULL90004 NULLWhen I really want:----------------------------90001 50090002 50090003 50090003 1000 (NOT 500)I don't know how to do this. Are any of you sql guru's up to thechallenge?Thanks in advance-Mattp.s. sql to recreate tables includedCREATE TABLE [dbo].[Table1] ([Table1ID] [int] IDENTITY (1, 1) NOT NULL ,[OrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Table2ID] [int] NULL ,[Table3ID] [int] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table2] ([Table2ID] [int] IDENTITY (1, 1) NOT NULL ,[Table4ID] [int] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table3] ([Table3ID] [int] IDENTITY (1, 1) NOT NULL ,[Table2ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Table4] ([Table4ID] [int] IDENTITY (1, 1) NOT NULL ,[OrderTotal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOALTER TABLE [dbo].[Table1] WITH NOCHECK ADDCONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED([Table1ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table2] WITH NOCHECK ADDCONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED([Table2ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table3] WITH NOCHECK ADDCONSTRAINT [PK_Table3] PRIMARY KEY CLUSTERED([Table3ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table4] WITH NOCHECK ADDCONSTRAINT [PK_Table4] PRIMARY KEY CLUSTERED([Table4ID]) ON [PRIMARY]GOALTER TABLE [dbo].[Table1] ADDCONSTRAINT [FK_Table1_Table2] FOREIGN KEY([Table2ID]) REFERENCES [dbo].[Table2] ([Table2ID]),CONSTRAINT [FK_Table1_Table3] FOREIGN KEY([Table3ID]) REFERENCES [dbo].[Table3] ([Table3ID])GOALTER TABLE [dbo].[Table3] ADDCONSTRAINT [FK_Table3_Table2] FOREIGN KEY([Table3ID]) REFERENCES [dbo].[Table2] ([Table2ID])GO

View 6 Replies View Related

Please Help With This Tough SQL Query

Jul 20, 2005

I've been trying this one for 2-3 hours and can't figure it out. I'deappreciate any help or pointers in the right direction. Thanks.QueryI need the query to return me all the lottery names and results thathave the latest date in the database for that particular game and forthe state [AZ]. So the return data from the data below data would be:Result:--------------------------AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18AZ Atlantic PayDay 2004-08-05 15-51-59-75AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6Example Table "Lottery":----------------------------------------------------State|Game | Date | ResultsAZ Atlantic 6/49 2004-08-04 5-16-17-26-38-44 46AZ Atlantic 6/49 2004-08-07 3-6-8-12-19-24 18AZ Atlantic PayDay 2004-07-29 2-23-62-77AZ Atlantic PayDay 2004-08-05 15-51-59-75AZ Atlantic Tag 2004-08-04 5-8-9-1-2-3AZ Atlantic Tag 2004-08-08 4-6-1-6-7-6

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved