How To Get Unmatched Rows In The Query

Apr 2, 2004

The following stored procedure is returning the correct row(s). But I am not sure it is correct.
In the stored procedure I need the variable passed in (@lotnum) in the dbo.tblBag_data compare with dbo.tblBag_results and return unmatch rows from dbo.tblBag_data. Basically I need the unmatched records in dbo.tblBag_data between two tables. For example there are five records. In dbo.tblBag_data 1st record contains lotnum(1000), bag1. next record contains lotnum(1000), bag2. .. last record contains lotnum(1000), bag5. In dbo.tblBag_results there are two bags. I need the three records that do not match up. Thanks!


CREATE PROCEDURE sp1
AS

declare @lotnum int

set @lotnum = 4056;
SELECT dbo.tblBag_data.work_ord_num, dbo.tblBag_data.work_ord_line_num, dbo.tblBag_data.bag_num, dbo.tblBag_data.lotnum, dbo.tblBag_data.bag_status
FROM dbo.tblBag_data
WHERE dbo.tblBag_data.lotnum = @lotnum AND dbo.tblBag_data.bag_num not in (select dbo.tblBag_results.bag_num from dbo.tblBag_results Where dbo.tblBag_results.lotnum = @lotnum)
GO

View 1 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Loop Unmatched Rows Data From Two Tables

Jan 12, 2015

We are having folder table and team table as like below structure.

Folderlist (F)Table: (
==============

id ,folder_name, parent_id
1, c, 101
2,b,202
3,c,203

Teamlist table (T)
============

team_id, Team_name, Parent_folderid
101 , mobile,101
202 ,Tab,200
200, Phone,200
203,apple,205
205,nokia,208
208,samsung,208

If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101)
then output should come as 'Mobile/c' (this is for f.parent_id=101)

If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id
then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id

Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache

like phone/tab/b (this is for F.parent_id=202)

View 1 Replies View Related

Parameter Query For Unmatched Records

Jul 15, 2013

I have to build a query and I am not sure how to build it.

Our data has providers with different products. We have an “DGH” product and a “PRM”. I need to find the provider that don’t have both. Its sounds very simple, but I am having an awful time figuring it out. Sometimes I get a record that has both products, it is probably how my where clause is working.

Anyway, here is an example of what I don’t want in my records because this is good. It has a DGH and PRM.

pro_pcs pro_lname pro_fname prop_prd pro_tax1 pro_addr100018791 Smith John DGH 12345868 Kiem ST
00018791 Smith John PRM 12345868 Keim ST

I would want to see this one because there only a DGH and it need to have a PRM product.

pro_pcspro_lname pro_fname prop_prd pro_tax1 pro_addr1
00018791 Rubin SamDGH2345868 Kiem ST

I came up with this below and it seems to be working. The only thing is, I now have to make this so people can add a parameter on the pro_tax1 to see the different records. Is there a better way to do this or how can I use what I have below and add a parameter too it.

Select pro_pcs, pro_lname, pro_fname, pro_addr1
from #ALLProducts
Where pro_tax1 = '12345868'
and prop_prd = 'DGH'

except

Select pro_pcs, pro_lname, pro_fname, pro_addr1
from #ALLProducts
Where pro_tax1 = '12345868'and prop_prd = 'PRM'

View 3 Replies View Related

Unmatched Records.

Jan 19, 2005

Hi everyone and belated happy new year...

I think someone has shown me how to do this before but I can't find it.... anyway....

I have three tables Person, Color and PersonColor.

Person Table
IdName
-------
P1Barry
P2Jenny

Color Table
IdColor
-------
C1Black
C2Green
C3Yellow

PersonColor Table
PersonIdColorId
------
P1C2
P2C1
P2C3

I need a query to find what colors people have not selected. So the result set would look something like

P1BarryC1Black
P1BarryC3Yellow
P2JennyC2Green

I can't use temp tables and I can't use cursors. Anyone got a solution for me?

Cheers,
Rokslide
edit: corrected sample data

View 4 Replies View Related

Extracting Unmatched Records

Aug 31, 2005

Hey guys

Im pretty new to SQL so i was wondering if you might be able to give me a hand with something.

I have 2 tables, clients + orders. Each table contains a column called custID which contains the client code, each order creates a new record in the orders table so the orders table contains many more records than the clients table.

I am trying to find out what clients have not placed orders at all, but i can't wrap my head around it, i can obviously work out who HAS, but i need to find out who HASN'T.

Any help would be really appreciated.

Mark

View 4 Replies View Related

How To Display Unmatched Records In Two Tables

Feb 28, 2008

I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.

SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
-------------------------------------------
Result: Record count 3000.

Only field3 is populated everything else is null.

select field3 from tbl1 group by field2 - record count = 48



SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL2

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

Result Record count = 0

------------------------------------------------------
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
minus
SELECT
nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM tbl2
---------------------------
Result: Record Count = 48

I used the left join and it didn't work.


I would like to build a query that will display all 3000 records.


Any help will be appreciated.

thank you!

View 4 Replies View Related

Unmatched Schema - Transactional Replication

Mar 18, 2008

Can you use Transactional replication for exchange data between tables where schema is not matching?



------------------------
I think, therefore I am - Rene Descartes

View 6 Replies View Related

Left Join Leaving Out Unmatched Records?

Oct 4, 2007

Hi folks. Thanks for the opportunity to get a bit of help here.

I've got two tables. Say, table "foo" and table "bar".
I LEFT JOIN them on a key, say, foo.id and bar.fooid.

For some reason, when I do this join, I get only records from "foo" which have a mate in "bar".

What I really need is all records from "foo", but the columns from bar when there is a match. I don't want to exclude records from "foo" just because they don't have a match from "bar".

Am I just misusing LEFT JOIN?

Any guidance would be greatly appreciated.

View 5 Replies View Related

Selecting Unmatched Records Based On Multiple Fields

Jul 21, 2004

I need to list all the records in Table2 which don't have matching field values in Table1.

This the the exact opposite of what I need:
SELECT DISTINCT
Field1,
Field2,
Field3,
Field4,
Field5
FROM
[Table1]
WHERE EXISTS(
SELECT DISTINCT
FieldA,
FieldB,
FieldC,
FieldD,
FieldE
FROM
[Table2]
)

The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.

What I need, however, is the exact opposite.

I tried the above using NOT EXISTS but I get no records at all.

How do do this?

View 6 Replies View Related

T-SQL (SS2K8) :: Convert Column In Query1 And Return Unmatched?

Apr 14, 2014

I have cpt codes in qry1 that need to be converted then match against qry2 and return the unmatched.

Example:

Cpt 88305 in qry1 can be either g4164 or g4165 in qry2
Cpt 88174 in qry1 can be either g3564 or g3565 or g3566 in qry2

select
Invoice_number
,cpt
From qry1
Select
Invoice_number
,cpt
From qry2

What is the easiest way to get the unmatched from qry1 against qry2 joining on invoice_number and converted cpt?

View 1 Replies View Related

SQL Server 2012 :: Get Unmatched And Matched Values From Two Tables

Apr 10, 2015

I have two tables category and lu_category.

The category table has columns [CategoryId], [CategoryName], [TotalCategoryRiskScore] and the lu_category has columns [CategoryId], [CategoryName].

I want a sql query that will list all values from lu_category table and category table and if a categoryid is not available in lu_category table but available in category table,
i need that too in the result.

Below is the screenshot of the data and my desired output

I have attached the data as spreadsheet.

View 6 Replies View Related

Using Password Ending In Unmatched Double Quote Mark With OleDbConnection

Mar 14, 2008


Issue



I need to write VS2005 C# code using SQL OLE DB to access SQL Server 2005. I have no choice in that matter. I can create a database user with a password like COMPANY", which is a string of uppercase characters ending in an unmatched double-quote mark.

Using Microsoft SQL Server Server Management Studio I login using Windows Authentication, create an account with the password, COMPANY", check the "Enforce password policy", click "OK", and then exit.


Setup


I launch Microsoft SQL Server Server Management Studio again, select "SQL Server Authentication", type in the account name and the COMPANY" password, click the Connect button, and I'm in.

Problem


Now, I need to connect programmatically and run a stored procedure. The password is stored in clear text in hte Registrynot my choice, it's a legacy application, and changing that is not an option open to me. (We have probably all seen company safes where the combination is scribbled on the wall in case you forget it!)

Here is the code I use to run the sproc:


DBCONNINFOLib.DBConnectionInfoClass DBConnInfo1 = new DBCONNINFOLib.DBConnectionInfoClass();
String strConnString = DBConnInfo1.GetConnectionString( "" );
OleDbConnection con1 = new OleDbConnection( strConnString );
OleDbCommand cmd1 = con1.CreateCommand();
cmd1.CommandType = CommandType.StoredProcedure;
String sCmdText = "sp_SomeStoredProcedure";
cmd1.CommandText = sCmdText;

If I set the connection string in the registry to COMPANY", I get an error like this:


Server Error in '/' Application.


Response is not available in this context.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Response is not available in this context.

Source Error:





Line 60: catch (System.Exception e)
Line 61: {
Line 62: Response.Write(e.Message.ToString());
Line 63: }
Line 64:
Source File: C:sourceProductNameDataCenterAdm&ReportsWebPagesProductNameWebCommonCodeCommon.cs Line: 62

Stack Trace:





[HttpException (0x80004005): Response is not available in this context.]
System.Web.UI.Page.get_Response() +2077605
SiteIQWeb.CommonCode.Common..ctor() in C:ProductNameDataCenterAdm&ReportsWebPagesProductNameWebCommonCodeCommon.cs:62
SiteIQWeb.MasterPage..ctor() in C:ProductNameDataCenterAdm&ReportsWebPagesProductNameWebMasterPage.master.cs:21
ASP.masterpage_master..ctor() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot255b545c8a400c7App_Web_hfj8popy.0.cs:0
__ASP.FastObjectFactory_app_web_hfj8popy.Create_ASP_masterpage_master() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot255b545c8a400c7App_Web_hfj8popy.3.cs:0
System.Web.Compilation.BuildResultCompiledType.CreateInstance() +49
System.Web.UI.MasterPage.CreateMaster(TemplateControl owner, HttpContext context, VirtualPath masterPageFile, IDictionary contentTemplateCollection) +250
System.Web.UI.Page.get_Master() +48
System.Web.UI.Page.ApplyMasterPage() +18
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +685




Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433


The closes to success I have been able to come is to set the password to "COMPANYNAME""" or 'COMPANYNAME"', which results in a System.Data.OleDb.OleDbConection Exception with a HResult of 0x80040e4d, and the message Login failed for user 'username'.


Questions


Can the password be formatted differently in the registry, or somehow processed after retrieving it, so that SQL Server 2005 will accept it?

Is this a bug in .NET Framework or SQL OleDb?

Is this simply a case of "Is it hurts, don't do it"?

Thanks in advance. As with all postings, my job, promotion, product success, company future, or some combination thereof is on the line.

View 2 Replies View Related

Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate

Nov 8, 2007

Hi All,

I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.

But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.

There are indexes in the tables.

Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.


But nothing is improving

View 7 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).

Jan 28, 2008

Hi, 
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. 
I have three tables namely – Forums,Topics and Threads  in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC 
Whose result set is as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  
On modifying the query to:- 
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC 
I get the result set as below:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
4
Swings
p@q.com
2008-01-27 15:12:51.000

I want the result set as follows:- 




forumid
topicid
name
author
lastpostdate
NoOfThreads

1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2

1
2
JSP
NULL
NULL
0

1
3
EJB
NULL
NULL
0

1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1

1
5
AWT
NULL
NULL
0

1
6
Web Services
NULL
NULL
0

1
7
JMS
NULL
NULL
0

1
8
XML,HTML
NULL
NULL
0

1
9
Javascript
NULL
NULL
0

2
10
Oracle
NULL
NULL
0

2
11
Sql Server
NULL
NULL
0

2
12
MySQL
NULL
NULL
0

3
13
CSS
NULL
NULL
0

3
14
FLASH/DHTLML
NULL
NULL
0

4
15
Best Practices
NULL
NULL
0

4
16
Longue
NULL
NULL
0

5
17
General
NULL
NULL
0  I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. 
The scripts for creating the tables and inserting test data is as follows in an already created database:- 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO 
CREATE TABLE [dbo].[Forums] (
            [forumid] [int] IDENTITY (1, 1) NOT NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Threads] (
            [threadid] [int] IDENTITY (1, 1) NOT NULL ,
            [topicid] [int] NOT NULL ,
            [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [replies] [int] NOT NULL ,
            [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO 
CREATE TABLE [dbo].[Topics] (
            [topicid] [int] IDENTITY (1, 1) NOT NULL ,
            [forumid] [int] NULL ,
            [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Forums] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [forumid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Threads] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [threadid]
            )  ON [PRIMARY]
GO 
ALTER TABLE [dbo].[Topics] ADD
             PRIMARY KEY  CLUSTERED
            (
                        [topicid]
            )  ON [PRIMARY]
GO  
ALTER TABLE [dbo].[Threads] ADD
             FOREIGN KEY
            (
                        [topicid]
            ) REFERENCES [dbo].[Topics] (
                        [topicid]
            )
GO 
ALTER TABLE [dbo].[Topics] ADD
             FOREIGN KEY
            (
                        [forumid]
            ) REFERENCES [dbo].[Forums] (
                        [forumid]
            )
GO  
------------------------------------------------------ 
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum'); 
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion'); 
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
 

View 7 Replies View Related

SQL Query Repeating Rows.

Dec 12, 2007

i have a query when i run it shows data rows repeating like 5 times. I am not sure what am i doing wrong. hope someone can help me out.
 this is my query.
SELECT "INVOICEDETAILS"."SeviceAmount", "RptSalesUser"."SalesPersonId", "RptSalesUser"."SalesPersonName", "RptSalesUser"."TemplateStatus", "INVOICE"."InvoiceDate"
FROM   ("imacstest"."dbo"."INVOICE" "INVOICE" INNER JOIN "imacstest"."dbo"."INVOICEDETAILS" "INVOICEDETAILS" ON "INVOICE"."InvoiceNo"="INVOICEDETAILS"."InvoiceNumber") INNER JOIN "imacstest"."dbo"."RptSalesUser" "RptSalesUser" ON ("INVOICE"."InvoiceNo"="RptSalesUser"."invoiceNumber") AND ("INVOICEDETAILS"."InvoiceNumber"="RptSalesUser"."invoiceNumber")
WHERE  "RptSalesUser"."TemplateStatus"='A' AND ("INVOICE"."InvoiceDate">={ts '2007-01-01 00:00:00'} AND "INVOICE"."InvoiceDate"<{ts '2007-12-13 00:00:00'}) AND "RptSalesUser"."SalesPersonId"=5 and "Invoice"."InvoiceNo"='1004447' ORDER BY "RptSalesUser"."SalesPersonId","Invoice"."InvoiceDate"
when i execute this i get about 12 data rows which are fine but then the same 12 data lines repeats 4 more times. hope someone be able to help me quick.
 

View 17 Replies View Related

How To Count Rows In This Query...

Dec 20, 2007

SELECT ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHERE EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita
 
hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)

View 8 Replies View Related

SQL Query - Delete All Rows Bar The Last 10? Help Please!

Jan 6, 2008

Hi,
I'm really not very good at SQL sadly, so would really appreciate any help.
I'm basically working on a website that has a chatroom. I want it so the chatroom table only holds, say 15 lines of chat, then once a new line of chat is entered the last line in the table is deleted, so the table always has a maximum of just 15 rows.
The fields are:
MessageID
Poster
Message
DateTime
I've tried my best, but just don't know how to do it.
I suppose it's something like:
SELECT TOP 15 * FROM Chatroom ORDER BY MessageID DESC - this gives me the last 15 rows.
Then I need a delete statement to delete the rest?! Sorry, I am very bad at SQL, so any help would be great. This is written in a stored procedure.
Can the stored procedure pick up whether there are 15 or more rows in the table, and if so then delete all bar the newest 15 rows of chat?
To summarise: I want a stored procedure that checks if 15 or more rows exist, if they do then delete all bar the newest 15 rows.
Thanks,
Ricky

View 5 Replies View Related

Returning Rows From A Query

Oct 22, 2001

Greetings!

I need to run a select statement that only returns 50 rows. How do I limit the amount of rows returned? Normally the query will return hundreds of rows but all I need is the first 50 it retrieves. I have looked in the BOL and can only find help with a block cursor not just a query.

Thanks a million

zachary

View 1 Replies View Related

How Many Rows Will A Query Return?

Apr 8, 2006

Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?

The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.

View 3 Replies View Related

Need To Get Rid Of Duplicate Rows In A Query

Dec 12, 2007

Hello I am fairly new to SQL and having spent much time over the manual I decided to ask for help. So here's my deal.

I've got a query with 5 tables that I join together


Code:

SELECT * FROM Map
INNER JOIN ThreatCategory
INNER JOIN Threat ON
ThreatCategory.threatCategoryID = Threat.threatCategoryID
INNER JOIN Threat_Map
ON Threat.threatID = Threat_Map.threatID
ON Map.mapID = Threat_Map.mapID
LEFT JOIN person on map.contentPersonID = person.personID
WHERE (((DATEDIFF(dd, Map.dataAcquisitionDate, GETDATE()) > map.goodForDays) and (map.expired = '1'))
or (map.expired = '3'))



The problem is the table Threat_Map is a many to many mapping between the Map table and the Threat table. Eg) A map can have more than one threat and a threat can have more than one map. I know this is not the best way to have a database set up but its out of my hands as to changing the database. What I need help with is this.

My application checks as to whether a certain field in the Map table is expired or out of date (as in the query). If so it gets some required information from the other tables using those joins. However, I don't want to get information for the same Map.mapID that's expired twice. I don't really care which ThreatID I get from the Threat_Map table I just need to get one of them to meet the objects standards. However, so far this seemingly simple task has eluded me. I'd like to do this in SQL. Is there perhaps a way to do this. If not I guess I'll just take care of it in the application.

-Alex

View 1 Replies View Related

Query To Get Top 100 Rows From The Table X

May 6, 2008

what is query to get top 100 rows from the table x

View 5 Replies View Related

Need Help - SQL Query To Join Two Rows

May 22, 2008

Current ResultSet
------------------
ColA ColB
TEST1 111
TEST1 222
TEST2 333
TEST3 444
TEST3 555

I am currently outer-joiing two tables to retrive some data in the above format. My intent is to modify the query so I can retrive the data in below fashion.

Intended ResultSet
------------------
ColA ColB
TEST1 111,222
TEST2 333
TEST3 444,555

Can someone please assist with this? I am not sure if it is possible in a direct query or not... Any expert advice is appreciated.

View 1 Replies View Related

SQL Query: Rows To Columns

Feb 6, 2008


Hi All,

I have a sample table as below:
(All columns do not have to have unique values)
Column n is int, the rest are varchar
[First row is the column Names]

n | t1 | t2 | t3 | t4
1 | a | b | c | d
3 | e | w | a | t
3 | w | q | u | k
2 | g | w | q | n
2 | a | b | s | a
5 | n | a | w | h


I need to create a query or some how create a way to get the data in the form of the below table:

n | t1 | t2 | t3 | t4 | t11 | t21 | t31 | t41
1 | a | b | c | d |
3 | e | w | a | t | w | q | u | k
2 | g | w | q | n | a | b | s | a
5 | n | a | w | h


What that means is that I have a huge table (~6000 rows) that has the column 'n' above (int type) which can have rows that start with the same number (such as number 3 & 2 above). I need these duplicate rows of numbers to be converted/transformed to columns alongside each other. This would create 1 entry for each type of number but have the 4 columns of t1, t2, t3, t4 'added' on as extra columns in the new table with a different column name ofcourse.
So what that would mean is that if there were 3 rows of number n=2 then would have 3x4=12 columns, plus the 'n' column and so forth.

I am not sure on how to start the query for this.
What I have thought is maybe using a cursor and going through the table. But not sure how I could do that really.
The other is using a Pivot in a SELECT statement. But that would not work since the columns can grow indefinetely depending on how many of the same 'number' row there is!

Any ideas on this? Thanks a bunch !!!


(running SQL 2005)

View 4 Replies View Related

Unique Rows Of Data Query

Sep 21, 2006

How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID             LastName        Email997249            MCCO-49       S.MCCO-49@SampleISD.org997462            BATE-62         A.BATE-62@SampleISD.org997605            DENS-05        B.DENS-05@SampleISD.org  997622            KAIS-22         A.KAIS-22@SampleISD.org997623            KAIS-22         A.KAIS-22@SampleISD.org997624            KAIS-22         A.KAIS-22@SampleISD.org997625            KAIS-22         A.ZKAIS-22@SampleISD.org997626            KAIS-22         AX.ZKAIS-22@SampleISD.org997627            KAIS-22         AX.KAIS-22@SampleISD.org   Result UserID             LastName        Email997249            MCCO-49       S.MCCO-49@SampleISD.org997462            BATE-62         A.BATE-62@SampleISD.org997605            DENS-05        B.DENS-05@SampleISD.org  997622            KAIS-22         A.KAIS-22@SampleISD.org997625            KAIS-22         A.ZKAIS-22@SampleISD.org997626            KAIS-22         AX.KAIS-22@SampleISD.org Thanks

View 13 Replies View Related

Getting The Number Pf Rows In A Query Result

Sep 1, 2004

My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:

"In the forums there is X results for the word X
In the articles there is X results...."

And when the user click one of those lines, the list shows the specific results in that section.

My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…

Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)

Great thanks

View 1 Replies View Related

Query - Return All Rows Is Failing

Apr 4, 2001

When querying any of our database tables (returning all rows from within the enterprise manager) we are getting the following error message:
"The query cannot be executed because some files are either missing or not registered" Can anyone help me with this....

View 4 Replies View Related

Returning A Subset Of Rows From A Query

Apr 19, 2001

This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.

The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...

Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id

Thanks,

Jim

View 1 Replies View Related

MS SQL Query : Group By Returing Too Many Rows.

May 30, 2004

Hi,

I have posted this in the Experts Forum so I have put replies to questions in here as well (Hence the long post!!). Hope someone here can help!!

I am having trouble with a T-SQL query. I have three tables in a join and I need to limit the results returned by query using a group by.

All of the fields in the result group are the same except for two fields, a date field and a varchar field. What I want to achieve is to return the row that has the latest date, and I need the varchar field as that is the information I am after. The problem is if I include the varchar field in the group by, it is returned as a separate group, if I do not included it in the group by, Server: Msg 8120 (aggregate fn / group by) error occurs!

Any ideas how to get around this?

Sample code and results below:

Query:

select distinct s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'
from [coded] c join sec s on s.id = c.id
join [mkt-security] m on m.id = s.id
where ([market-id] = [scheme] or [market-id]+'CODE' = [scheme])
Group by s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'


Sample results:

id : [active-from] : code : desc : [scheme] : [market-id]
--------------------------------------------------------------------------------------------------------------------------
10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :1986-10-24 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE

The result I want to achieve is to only return these two rows:

10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE

i.e. those with the latest [active-from] date, but I must have the corresponding code value.

I have tried a correlated sub query on the [active-from] field, but it is possible to have different ids with the same [active-from] date so it did not work.

The primary key for the [coded] table is the combination of [active-from], code and [scheme]. The tables are truncated and then imported back into the SQL database from a Progress database daily, so restructuring the table(s) is not a possibility.


I have rewritten the query to "AND [ACTIVE_FROM] in (SELECT MAX([ACTIVE_FROM]) FROM CODED AND MAX([CODE]) = S.[CODE] AND MAX([SCHEME]) = S.[SCHEME]", (Which I think is the T-SQL for you query), but it returned the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Also I think the query need to use a Group by as the results I need returned are those with the latest [ACTIVE_FROM] date WITHIN the group of [ID]and [MARKET-ID]. I can't think of any conditions where I can filter the results in the where clause.

The [ ] in the T-SQL is to identify table names as some of the table names (from the Progress db) use characters (the "-") that are unsupported for table names in T-SQL.

I am using the group by as I want to return specific groups of results ( [ID] and [MARKET-ID] ), that have the latest active-from date. I have made a change to that posted before to illustrate:

All results:

id : [active-from] : code : desc : [scheme] : [market-id] : [market-id]+CODE

10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE
10769 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE

The result I want to achieve is to only return these three rows:

10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE


The reason the last row needs to be returned is that it has a different market id to the other row, despite its active-from date being earlier.

I have tried the self join earlier, but it would then only return 1 of the rows that have an id of 10769!!

Hope this makes sense!!

Cheers,
Guytz

View 2 Replies View Related

Query From Multiple Tables And Rows, HELP!

Feb 27, 2007

I'm trying to run a sql query using the core dotnetnuke database. I am tying to pull from two separate tables in the same database and return the results below:
------------------------------------

LastName | FirstName | City | PhoneNumber

Smith Joe New York (555)555-5555

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

Last Name and FirstName are two separate columns in the DB_Users table, while City and PhoneNumber are both values of the same column DB_UserProfile.PropertyValue located in separate rows defined by an ID in the DB_UserProfile.PropertyDefinitionID column.

The UserProfile table looks something like this...

ID | PropertyDefinitionID | PropertyValue

1 27 New York
2 31 (555)555-5555

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

This is what I have so far but it is not working out to well...

SELECT DB_Users.FirstName, DB_Users.LastName, DB_UserProfile.PropertyValue AS City, DB_UserProfile.PropertyValue AS PhoneNumber
FROM DB_Users, DB_UserProfile WHERE DB_Users.UserID = DB_UserProfile.UserID AND DB_UserProfile.PropertyDefinitionID = 27 AND DB_UserProfile.PropertyDefinitionID = 31
ORDER BY DB_Users.LastName

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

THANKS!!

View 5 Replies View Related

Query Returning Duplicate Rows

Oct 6, 2004

I'm trying to write a query that will return rows within a specified range and print to a Crystal Report. When I run the query, it produces 2 row of everything. I would use the SELECT DISTINCT clause, but Crystal Reports will not let me edit the Select statement. But I can edit the FROM, WHERE and ORDER BY clauses. I think the problem is in my INNER JOINS but I'm having a problem figuring it out. Can someone please guide me in the right direction.

SELECT
DrawingVouchers."PlayerID",
DrawingVoucherNumbers."PromoID", DrawingVoucherNumbers."VoucherNumber", DrawingVoucherNumbers."IssueDate", DrawingVoucherNumbers."UserID",
CDS_PLAYER."LastName", CDS_PLAYER."FirstName",
CDS_ACCOUNT."Address1A", CDS_ACCOUNT."City1", CDS_ACCOUNT."State1", CDS_ACCOUNT."Zip1"
FROM
{ oj (("WinOasis"."dbo"."DrawingVouchers" DrawingVouchers INNER JOIN "WinOasis"."dbo"."DrawingVoucherNumbers" DrawingVoucherNumbers ON
DrawingVouchers."PlayerID" = DrawingVoucherNumbers."PlayerID")
INNER JOIN "WinOasis"."dbo"."CDS_PLAYER" CDS_PLAYER ON
DrawingVoucherNumbers."PlayerID" = CDS_PLAYER."Player_ID")
INNER JOIN "WinOasis"."dbo"."CDS_ACCOUNT" CDS_ACCOUNT ON
CDS_PLAYER."Player_ID" = CDS_ACCOUNT."Primary_ID"}
WHERE
DrawingVoucherNumbers."VoucherNumber" >= 37806 AND
DrawingVoucherNumbers."VoucherNumber" <= 37813

View 2 Replies View Related

SQL Query To Convert Rows Into Column

Jan 16, 2008

I have detail table like

date item_id grade in out
------ ------- ------- ----- -----
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8


Result View
=======

Item_id A B O T Total
------- --- ---- --- --- -------
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 -8 20 -10 2

I want result group by Item_id and sum of grade in column .
where grade could be any Alphabet, the column of result query could varies.
Each grade contains sum of (in-out) of item_id of detail table.

Smartsys99

View 1 Replies View Related

Counting Rows By Date (was Help On Query)

Apr 21, 2004

I hate to ask such silly helps..but I'm missing something here..need help.
I have a table having columns for createddate and deleteddate. The data gets created and deleted periodically and I need to find out the number of created,deleted and remaining number of records on each day. This query works, but takes a lot of time...not sure if there is a more better way to do this.. Please help
SELECT
CAST(createddate AS DATETIME) AS createdDate,
Created,
Deleted,
Remaining
FROM(
SELECT
CONVERT(VARCHAR,createdon,102) AS CreatedDate,
COUNT(1) created,
(SELECT COUNT(1) FROM table ta2
WHERE CONVERT(VARCHAR,ta2.deletedon,102) =
CONVERT(VARCHAR,ta.createdon,102)) Deleted,
((SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.createdon,102) <=
CONVERT(VARCHAR,ta.createdon,102)) -
(SELECT COUNT(1) FROM table ta1
WHERE CONVERT(VARCHAR,ta1.deletedon,102) <=
CONVERT(VARCHAR,ta.createdon,102))) Remaining
FROM table ta
WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90)
GROUP BY CONVERT(VARCHAR,createdon,102)
ORDER BY CONVERT(VARCHAR,createdon,102) DESC)
AS tmp

View 13 Replies View Related







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