MS SQL Server :: Solve This Query (mind Blowing Query)

SQL Query Question, Mind Puzzler?

Aug 8, 2001

Hi,

I have an interesting query problem. The result needs to be a single recordset as from a select statement, here's the prerequisites:

Tables:
tShip
tPerson
tAddress
tCertificate, has a field dtmExp (datetime)

where each Ship has one or more Persons which have one or more Addresses and each ship has one or more certificates.

Now, I need the query to return all ships, all persons and all addresses for those persons BUT ONLY the certificate with the latest expiration date (sort of like the result from a 'select top 1 from tCertificate order by dtmExp desc')

How can this be packed into one query? using inner joins on all tables will return multiple rows for each certficate for a ship, which it must not.

Any help greatly appreciated!

-. Balt

View 2 Replies View Related

Mind Boggling / How Can You Query Self Referencing Tables? (self Referencing Foreign Keys)

Jun 15, 2006

For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID . . . . . . . . . . int
Name . . . . . . . . . . . nvarchar(50)
ManagerID . . . . . . . . . . . int

If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?

View 6 Replies View Related

How To Solve This Query?

Oct 1, 2007

Hi,there are three tables:1) department---------------ID (primary key)name2) reports----------ID (p. key)depIDuserID3) users--------ID (p.key)nameI want to get in one query how many distinct users have made a report for each department.E.g.: table reports may look like this:ID depID userID1 1 12 1 13 1 34 3 65 4 8This gives:for dep 1: 2 distinct usersfor dep 2: 0for dep 3: 1 distinct userfor dep 4: 1 distinct userThanks for helpTartuffe

View 5 Replies View Related

Solve This Query

Jun 8, 2007

Hello friends,

I want to find only fifth row from a table please give me better solution.

thanks in adavance

regards
Harsh

View 4 Replies View Related

Plz Solve This Query

Feb 5, 2004

chiranjeevi
raveendra
sreenivasarao
koteswar
manoj
sangeetharanika

Here my query is
in the above names i want to find out the persons whoz names consist of exact 2 a's
eg output:
raveendra

assume that the names are ename and table name is emp.

Thanks in advance
Plz mail this qury to chiru_y2k@yahoo.com

Chiranjeevi

View 2 Replies View Related

Solve This Query

Feb 9, 2004

Hi,
I have two database servers and in this one is consists of tables and another one is for stored procedures ...
Here is my proble...
I want to access the stored procedures from my application(.net) ...

How to do...

Thanks in Advance

Chiru

View 1 Replies View Related

Please Solve This Query

Feb 21, 2007

please solve this query

first table second table

loginname categoryid
categoryid categoryname

first table data

nisar (1,2,3,4)

second table data

1 Bearbase
2 Amunation
3 Training
4 Workorder

Now I want cross tab report like that which will show user name nisar has this category

Nisar bearbase amunation traiaing workorder

View 3 Replies View Related

Please Solve This Query

Feb 21, 2007

please solve this query

first table ============= second table

loginname ============= categoryid
categoryid ============= categoryname

first table data

nisar=====1,2,3,4)

second table data

1==== Bearbase
2==== Amunation
3==== Training
4==== Workorder

Now I want cross tab report like that which will show user name nisar has this category

Nisar ==bearbase=== amunation === traiaing === workorder

View 1 Replies View Related

Please Solve This Query

Apr 24, 2007

hi anyone can please solve this query

I have table1 like this

locationid locationname
1 london
2 bombay

and in table2 like this

personname fromlocation tolocation
john 1 2
ryan 2 3

now i want to udpated table 2's from location and tolocation colum with all the all related value in table1

Regards
Monika

View 4 Replies View Related

Please Solve This Query

May 19, 2007

Hi please solve this

DBF file

Id 1
Building 21b
Population 4

Sql server table

Building 21b
Population8

Now I want to execute a sql script when ever I will run this script
It will not only transfer data between sql server 2000 and dbase file but Also update the corresponding population value in dbf table, which has same building value In sql server table

View 1 Replies View Related

How To Solve This Sql Query

Mar 26, 2008

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_idcust_name owner_rep_id
1harry 3
2msas 2

AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_idab_name owner_rep_id
1abc1
2def2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_idot_nameowner_rep_id
1xyz2
2cdf3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_idowner_name
1henry
2hologa
3tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_iddt_idproject_typeproject_idtime_st
103/23/08AB Corporate11.20
2 03/23/08AB Corporate23.25
303/21/08AB Corporate 12.25
403/23/08AB Plant14.35
503/23/08AB Plant24.50
603/23/08Other Project13.24
703/23/08Other Project24.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_iddt_id project_type project_id time_st owner_rep_id owner_name
103/23/08 AB Corporate 11.203tmw
2 03/23/08 AB Corporate23.252hologa
303/21/08 AB Corporate 12.253tmw
403/23/08 AB Plant14.351henry
503/23/08 AB Plant24.502hologa
603/23/08 Other Project13.242hologa
703/23/08 Other Project24.353tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.

uday

View 10 Replies View Related

Can Anyone Solve This Sql Query?

Oct 16, 2006

hi friends i need help in this sql queryi have table like,id fid__ _____autonumber textand i am storing values likeid fid___________________________________1 1,2,3,4,52 11,12,13,14,15now to find values i am using querysql = SELECT * FROM test12 WHERE `fid` LIKE ('%1%')only problem in this query is it is selecting 1 and 11 and i requireonly 1 as i am giving one in %1%now any one have answer of this question then plz plz tell me ........

View 4 Replies View Related

How To Use Joins To Solve This Sql Query

Mar 27, 2008

My tables and data as follows

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2

AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name
1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw
2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa
3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw
4 03/23/08 AB Plant 1 4.35 -- 1 -- henry
5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa
6 03/23/08 Other Project 1 3.24 -- -- 2 hologa
7 03/23/08 Other Project 2 4.35 -- -- 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.

uday

View 1 Replies View Related

Using Joins How To Solve This Sql Query

Mar 27, 2008

My tables and data as follows

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2

AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name
1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw
2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa
3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw
4 03/23/08 AB Plant 1 4.35 -- 1 -- henry
5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa
6 03/23/08 Other Project 1 3.24 -- -- 2 hologa
7 03/23/08 Other Project 2 4.35 -- -- 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=�� AB Corporate�� left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=�� AB Plant�� left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=�� Other Project�� inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.

View 1 Replies View Related

Plz Help To Solve This Sql Query (MS Sql 2005)

Mar 26, 2008

my tables as follows and data as follows

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2

AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st owner_rep_id owner_name
1 03/23/08 AB Corporate 1 1.20 3 tmw
2 03/23/08 AB Corporate 2 3.25 2 hologa
3 03/21/08 AB Corporate 1 2.25 3 tmw
4 03/23/08 AB Plant 1 4.35 1 henry
5 03/23/08 AB Plant 2 4.50 2 hologa
6 03/23/08 Other Project 1 3.24 2 hologa
7 03/23/08 Other Project 2 4.35 3 tmw

Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project

I write query for this is as follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=�� AB Corporate�� left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=�� AB Plant�� left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=�� Other Project�� inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)

So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.

View 3 Replies View Related

Whats The Best Query To Solve This Problem?

Apr 5, 2008

Hi Guys,

I have create a sales delivery printout with the following example information
example below gives a delivery and the two sales orders that delievry is related to.

Two tables are T1 = delivery, T2 = orders

Delivery (T1)

linenum Item delievry Qty Ordernum orderlinenum DeliveryNumber
1 A 10 600 1 5001
2 B 5 600 2 5001
3 c 12 605 1 5001
4 P 1 5001

ORDER (T2) number 600

linemum Item OrderQty orderno
1 A 20 600
2 B 10 600
3 A 5 600
ORDER (T2) number 605
linemum Item OrderQty orderno
1 C 20 605
2 B 20 605
3 F 5 605

I want to print a delivery note with the all items on that delivery (delivered items) plus other items on orders which ware not delivered. so the result of the query should look like below

Result
NO Item delievry Qty Ordernum orderlinenum ORDER QTY
1 A 10 600 1 20
2 B 5 600 2 10
3 c 12 605 1 20
4 P 1
5 A 600 3 5
6 B 605 2 20
7 F 605 3 5

You can ignore the first column of results which is just the row number.

other conditions

1. Some times the delivery is not based on any sales order
2. Delivery can base on 0,1,2,3 or any number of sales orders.
3. result should provide all rows from the delivery and all rows from sales orders except the rows in the delivery.

can any one help?

Cheers

Sonny

View 1 Replies View Related

DB Engine :: How To Solve Multiple Rows Result From Inner Join Query

Dec 3, 2015

I have 3 tables:

TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,

[Code] ....

I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )

But am getting a multiple result if the order purchase has more than 1 item :

SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3

View 5 Replies View Related

Query Runs Fine In Query Analyser But Not The Query Debugger

Dec 19, 2003

I'm running a query, actually its an insert that works when using the TSQL below.

However when I try to use the debugger to step through and using the exact same values as those below I get the following error:

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

Its Killing me because everything else works, but this. Can somebody help.

DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,

@BeenRead NVARCHAR(10),-- = NULL

@FK_UserIDList NVARCHAR(4000)-- = NULL

--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'

SET @FK_UserIDList = '1,2,3'

--AS

--SET NOCOUNT ON

SET NOCOUNT ON

SET XACT_ABORT ON

BEGIN TRANSACTION

SET @Date = GETDATE()

-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)

SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,

@Job_Date,
@Start,
@Finish

-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY

-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList

-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT

COMMIT TRANSACTION

--------------------------------------------------
GO

View 1 Replies View Related

Query Diff Results From Ent Manager Query And Query Analizer

May 28, 2008

ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)

SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007

but in query analizer I get the result of

12/31/2006

Why the different dates

View 4 Replies View Related

Mind Boggling Order By

Nov 21, 2006

Hello everyone, this is my first time to these boards. I've been running all around for the last few days trying to solve a problem. So far, on 3 forums, we haven't been able to find a solution.

I am using MS SQL 2005 Workgroup. I have catalog of events on my site. Each event can have a little as 0 up to an unlimited amount of Photos attached to them(in a seperate table.) Here's the basic breakdown.

catalog
-----------
id - PK
act_name
(price and other such info)

Photos
-----------
id - PK
path_to_photo
event_id - FK(catalog.id)

What I'm trying to do is select all the events in the catalog, and order them by the number of photos they have in the photos table. The idea is to get the events that have photos at the top of the list.

here is the query that I'm using right now.

Code:

SELECTCOUNT(photos.id) AS PhotoCount, catalog.id AS item_id, catalog.company, catalog.act_name,
catalog.location, catalog.price_adult,
catalog.price_child, catalog.short_description, catalog.photo, catalog.children_allowed,
catalog.long_description, catalog.online,
catalog.act_type, event_types.act_type AS evt_type,
event_types.id AS event_id, catalog.rank, catalog.length, catalog.bullets,
photos.id
FROM catalog, event_types, photos
WHERE catalog.act_type = event_types.id
AND photos.event_id = catalog.id
GROUP BY COUNT(photos.id), catalog.id, catalog.company, catalog.act_name
ORDER BY photos.id, catalog.id, catalog.company, catalog.act_name

Which returns the following error.
Column 'catalog.location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

No matter what I do, I always seem to get an error. I've removed the Count in the select list, and that tells me that I cannot call Aggregate functions in the Group By list.

Anyone got an idea what I'm doing wrong?

Thanks in advance,
Morgan

View 3 Replies View Related

Set My Mind At Ease On EXISTS

Aug 16, 2006

SELECT
[List1].[TITLE],
[List1].[FORENAME],
[List1].[SURNAME],
[List1].[JOB]
FROM List1
WHERE EXISTS(
SELECT [List2].[forename] FROM [List2]
WHERE [List2].[forename] = [List1].[FORENAME]
AND [List2].[surname] = [List1].[SURNAME]
AND [List2].[email] = [List1].[EMAIL]
)

I am on the right track here, aren't I?

That query will.. sort of... loop through List1, get the "current" records firstname, surname and email, and then go and see if there's any single record in List2 that contains exactly that combination of values in those fields. It's a stupid question but I'm not the brightest of sparks and BOL leaves me even more confused. I'm just worried in case it's going "well, record 34 matches the current firstname and record 96 matches the current email, so EXISTS returns true".

Assuming I've got it right, though - many of the records that I know to be duplicates have a NULL email address. And the query above seems to be automatically removing them. If there's a record in both List1 and List2 with identical firstname and surname, and both have NULL email addresses - should the query above pick it up? If not, how can I include those records?

View 9 Replies View Related

Would Anyone Mind Taking A Look? Odd Code Behaviour

Feb 21, 2008

I have the following bit of code that is an onclick event to save information in text boxes and list boxes. I cut out a bunch that was irrelevant to this because ALL items in the text boxes save fine. My biggest question that should lead to the answers for the rest of the code, is for lines 53-57... That stored proceedure does not appear to run. All it is set to do is and the code is DEFINITELY getting passed into it but it almost seems like it isnt and therefoe isnt doing anything. I get no errors, just nothing happens. I would of thought at the very least, it should run this proceedure and delete the information from the table even if it wouldnt save the new information based on the listboxes.
I am not sure if I am explaining this correctly, but if anyone has any thoughts, I would greatly appreciate it.
The delete proceedure that isnt actually making a changeALTER PROCEDURE [dbo].[Delete_Team_Data]
(
@Code as int
) AS

DELETE from tblSectyData2 where Code = @Code
DELETE from tblSectyData2 where SectyCode = @Code
The actual code
1
2
3 protected void SaveChanges(object sender, EventArgs e)
4 {
5
6 string selectedEmployee = "";
7 selectedEmployee = EmployeeList.SelectedValue;
8
9
10
11 string fName = "";
12 //Snipped out a bunch of code relating to the text boxes
13 string secretaryCode = "";
14
15 fName = txtFName.Text;
16 //Snipped out a bunch of code relating to the text boxes
17 secretaryCode = TeamList2.SelectedValue.ToString();
18 int selemp = Convert.ToInt32(selectedEmployee);
19
20
21
22
23 String Conn = (string)Application["Facebook"];
24 SqlConnection IntranetConnection;
25 SqlDataReader IntranetReader;
26 IntranetConnection = new SqlConnection(Conn);
27 //SaveEmpChanges works properly.
28 SqlCommand SaveEmpChanges = new SqlCommand("Exec dbo.Edit_Employee_Data '" + prefix + "','" + lName + "','" + fName + "','" + mName + "','" + pos + "','" + dept + "','" + directdial + "','" + ext + "','" + fax + "','" + hphone + "','" + cphone + "','" + partner + "','" + timekeeper + "','" + notary + "','" + practice + "','" + saddress + "','" + sphone + "','" + lnl + "','" + bar + "','" + oemail + "','" + haddresscom + "','" + haddress + "','" + hcity + "','" + hstate + "','" + hzip + "','" + school + "','" + degree + "','" + status + "','" + floor + "','" + code + "','" + email + "','" + language + "'", IntranetConnection);
29 //Delete Team does NOT work
30 SqlCommand DeleteTeam = new SqlCommand("Exec dbo.Delete_Team_Data '" + selemp + "'", IntranetConnection);
31 //GetEmployeeType does work
32 SqlCommand GetEmployeeType= new SqlCommand("Select EmpType from tblMain2 where Code = '" + selectedEmployee + "'", IntranetConnection);
33
34 IntranetConnection.Open();
35 IntranetReader = SaveEmpChanges.ExecuteReader();
36 IntranetReader.Close();
37 IntranetConnection.Close();
38
39
40
41
42 int count = 0;
43 string LinkT = "";
44 string LinkT2 = "";
45
46 string etype = "";
47
48 lselemp.Text = selectedEmployee;
49 //Basically, while the page loads fine, it does NOTHING below this line... Ive comments out sections, I have also put in a bunch of labels in to show the variables being passed, it all seems fine.
50
51
52
53 IntranetConnection.Open();
54 IntranetReader = DeleteTeam.ExecuteReader();
55 IntranetReader.Close();
56 IntranetConnection.Close();
57
58
59 IntranetConnection.Open();
60 IntranetReader = GetEmployeeType.ExecuteReader();
61 while (IntranetReader.Read())
62 {
63 etype = IntranetReader["EmpType"].ToString();
64 }
65 IntranetReader.Close();
66 IntranetConnection.Close();
67
68 int end = Convert.ToInt32(TeamList2.Items.Count);
69 string teamcode = "";
70
71 while (count < end)
72 {
73
74 teamcode = TeamList2.Items[count].Value.ToString();
75
76 if (etype == "S")
77 {
78 LinkT = "Secretary";
79 LinkT2 = "Works with";
80 }
81
82 else if (etype == "O")
83 {
84 LinkT = "Works with";
85 LinkT2 = "Secretary";
86 }
87
88
89
90 //This command does not work
91 SqlCommand saveTeam = new SqlCommand("Exec dbo.Add_Team_Data '" + selemp + "','" + teamcode + "','" + LinkT + "','" + LinkT2 + "'", IntranetConnection);
92
93
94 IntranetConnection.Open();
95 IntranetReader = saveTeam.ExecuteReader();
96 IntranetReader.Close();
97 IntranetConnection.Close();
98
99
100 count++;
101 }
102
103
104
105
106 Response.Redirect("ManageEmployeeDirectory.aspx");
107 }
108

View 6 Replies View Related

Mind Boggling! Database Design Help

Dec 10, 2007

I'm trying to create a "Self-Referential, Many-to-Many" relationship under the Database Diagrams section in Sql Server 2005 express and I'm having a heck of a time figuring out where to click and edit to create what I want.

The url below links to the pictorial represenation of what i'm trying to create. Any help is greatly appreciated. Thanks!
http://www.communitymx.com/content/source/A1A63/diag.gif

View 7 Replies View Related

Artificial Mind - Part One - Basic Architecture And Cognitive Structure Is Now Available

Oct 5, 2006

It's a dream of human beings to build machines that can think andbehave like human beings. The most important part of of such a machineis an artificial mind that can emulate the cognitive processing ofhuman mind.This book, "Next Generation Artificial Intelligence, Artificial Mind -Part One - Basic Architecture and Cognitive Structure" introduces abasic artificial mind architecture and computational model forcognitive processing. Inside the book, three important cognitiveprocess modeling components, mental objects network (MON),associative-learning mechanisms and a concept formation principle areintroduced. Based on the architecture and the computational model, onecan develop his own model of artificial mind according to his ownspecific requirements.The first edition of Artificial Mind - Part One is now available forpurchase from the author's personal web site. The price of the e-bookis USD7.00 (seven US dollars). An evaluation edition of this e-book isalso available for download from the web site.The author's personal web site:http://www.geocities.com/tomwingmak/

View 1 Replies View Related

[Query] - Query Designer Encountered A Query Error:Unspecified Error

Jan 22, 2001

Hi,

I get this error dialog when I try to open all the rows of any table from Enterprise manager..

Any help would be really appreciated..

Thanks,
-Srini.

View 1 Replies View Related

Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.

May 24, 2007

SQL Server 2005 9.0.3161 on Win 2k3 R2

I receive the following error:

"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."

I have traced this to an insert statement that executes as part of a stored procedure.

INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)

VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)

There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.

Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).

View 5 Replies View Related

Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!

Mar 28, 2007

Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC

Here is the page source

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
	FROM dbo.MAKES INNER JOIN
				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
	ORDER BY count(*) DESC
">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!

(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)

View 4 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager

Jun 15, 2007

I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.

Thanks in advance

Daniel Buchanan.

If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.

View 1 Replies View Related

Master Data Services :: Error - Query Processor Could Not Produce A Query Plan

Jul 19, 2015

We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T

• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues

• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"

• We are using the last version of Excel Add-in

• We try to reinstall the MDS feature

• If I backup/restore MDS database to other server it works

• We updated to SQL 2012 SP2 + CU4 but the error persisted ...

Looking at the MDSTraceLog we are routed to the this msg

SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28

At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped

** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)

[code]....

View 3 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results

Sep 22, 2015

-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"

set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo

[code]....

This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?

View 2 Replies View Related