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
ADVERTISEMENT
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
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
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
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
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
Jun 11, 2006
hi all,
I need to select the no of records on the basis of specified range of records.
In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..
I have used temporary table,map the table primary key to the next table with identity
but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...
Is there some other best way to extract the specified data
here is the type of query.
select * from customers where rownum between 1000 and 10000
this is in oracle
i am in need to do this in the sql server
waiting for the response...............................
View 5 Replies
View Related
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
View Related
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
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
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
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
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
oot 255b545c8a400c7App_Web_hfj8popy.0.cs:0
__ASP.FastObjectFactory_app_web_hfj8popy.Create_ASP_masterpage_master() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot 255b545c8a400c7App_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
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Mar 20, 2006
Have a column with the following dataa
fname.lname
fname1.lname1
Now I need to seperate the fname & lastname into 2 different columns how to do this
please help
thanks
View 2 Replies
View Related
Oct 29, 2002
hello,
I'm running my user database on a SQL Server 2000. I would like to extract all the indexes from the database and I was wondering, how can i do that. I dont see an option for doing this.
Appreciated If someone out there help me please.
Thanks in advance.
View 6 Replies
View Related
Jun 16, 2005
Hi,
does anyone know how to extract the date (dd.mm.yyyy) from a date expression with format (dd.mm.yyyy hh:mm:ss) ?
Thx in advance
jolli
View 3 Replies
View Related
Jun 17, 2004
hi
I want to select all the dates between 2 dates.ie if i'm passing 17th June and 19th june then it shud give 17,18 and 19.
Like wise if i'm giving 28th june and 3rd july it shud return 28,29,30,1,2,3.
Pls help me.
Thnx in advance
Nebu
View 1 Replies
View Related
Sep 17, 2004
I really apprciate any idea's
On extracting ddl's for each object on a separate file in SQL Server?
Something like ddl's for:
procedure1.sql, procedure2.sql.....so on procedure100.sql
onto a file server.
Does SQL Server has any API's like Oracle's dbms_metadata?
Or can we use SQL enterprise manager to do this job?
Thanks, Madhavi.
View 3 Replies
View Related
Apr 26, 2007
I am trying to extract just the session id from some iislogs, and was curious the best way to go about doing this. The session ids will all be like "ASP.NET_SessionId=1wtdqn241f0ews55n5hsipib" with 42 chars in length, however the sessionid can be anywhere in the column, sometimes by itself, sometimes at the beginning and sometimes near the end. I guess the best way to do this is search for %ASP.NET_% but im not sure how to pull just the sessionid out.
does anyone know how I should approach this or where this topic has been brought up in the forums so I could get an idea. I tried searching by instr and sessionid and few others.
Thanks
SAMPLES WHAT THE DATA MIGHT LOOK LIKE
ASP.NET_SessionId=3mhnrf45wwiypi45polih5ey
ASP.NET_SessionId=ejsfrueqay4tuo45iuszsy45;+.RELYFORMSAUTH=C81CBCADBC5DEB801A3D4EBBC187A063CFCB70D678C1419BBFBE49B06CEE0039EBC85A9592882C15E47B69BD2FBE82A570B3F93011EA95B905301FC547DE67E68D3B5EB0E53CA824EC271A0B4EF8092E
.OASIS7FORMSAUTH=34720D24F742723CD786480BC252E8ABE392B2EC146FC1474501C3BC2A8126FF0539AD35BB14D0B259F366289611B1C696724932DE0C792AAD60B3867430EB8D3540F7B36991A851924F465A5E00CC2B;+ASP.NET_SessionId=3mhnrf45wwiypi45polih5ey
View 8 Replies
View Related
Jul 10, 2000
I have a field in sqlserver 7.0 defined as type datetime. It has the following value - 2000-07-01 00:44:01.700. What is the best way to strip only the 2000-07-01 out of the field into another field as a date?
View 2 Replies
View Related
Feb 6, 2001
hi all,
i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?
thanx.
:-)
hemant
View 2 Replies
View Related
Dec 2, 2005
Is there a way to extract the time from a field like this
2005-11-14-14:30:00:000
I just want to use the 14:30 out of that field but cant seem to find the right way to do it. I can use datename and get the hour but i need it to the min.
thanx
View 2 Replies
View Related
Sep 11, 2004
hi all
i want to extract only the duplicate values from a table
can any one know the sql syntax for that
thnking u
jag
View 2 Replies
View Related
May 22, 2006
The situation is I need info from a certain SharePoint site that is in a sql database. The problem is The SharePoint site currently contains data from 9 different projects, all using the same database. The database is a complex setup and it would not be an easy operation to extract the special information alone. The database is run with Microsoft SQL and is comprised of 4 separate databases. 3 databases organize the data (500+mb of data total) and 1 stores the data for all the sites (12.5+GB and increasing at a rate of 100-300mb per day).
How can I get the special data I need from this setup???
Please help
View 3 Replies
View Related
Jul 9, 2007
Hi everybody i need help on on a query on how i can extract this... with the following table below..
id pub
1 a
1 b
2 c
2 c
I need to extract only the id and pub where pub has more than one with the same id... in the case of the above the result would be
id pub
2 c
2 c
thanks
View 2 Replies
View Related
Mar 27, 2008
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View 1 Replies
View Related
May 19, 2004
Cheers
I would really apprecite if anyone could make a comment on this...
what i wanna know is....
there are two tables, called 'A' and 'B' with identical structures...
is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2
* Records of table 'A' which not in table 'B' and
* Records of table 'B' which not in table 'A'
Thanks
View 5 Replies
View Related
May 21, 2008
Hey all
Is it possible to extract length(size) from an image?
Many thanks,
Rupa
View 13 Replies
View Related
Nov 8, 2013
I have to extract only the filename from the below filepath.
The first part prodserverexport always remains the same. A new folder is created prodserverexport 20071001, prodserverexport 200710012 etc each time the file is dropped . I want to extract only the part after that namely 20071001-200001-e430f8fd-PREC272.wma and like wise and dump it into a table.
prodserverexport2007100120071001-200001-e430f8fd-PREC272.wma
prodserverexport2007100220071002-013203-d00b2007-PREC120.wma
prodserverexport2007100220071002-032038-18381b92-PREC207.wma
prodserverexport2007100820071008-144758-16359a7b-PREC224.wma
prodserverexport2007100920071009-170426-4ed3535c-PREC97.wma
View 7 Replies
View Related
Jan 31, 2014
I am trying to write a query that selects multiple rows of a table and puts those into a single string with a comma delimiter.
I want it to select all rows after a specific word is found in the row above and then stop selecting when another specific word below the last row is found.
Example below: >>>
The data i am using is very unorganised and has not headers, so I have to try and specific lines in order to allocate them to a specific user.
Sample Data:
IDFullContent
459Authorized Privileges:
460ACNT ALLSPOOL ALTPRI AUDIT BUGCHK BYPASS
461CMEXEC CMKRNL DIAGNOSE DOWNGRADE EXQUOTA GROUP
462GRPNAM GRPPRV IMPERSONATE IMPORT LOG_IO MOUNT
463NETMBX OPER PFNMAP PHY_IO PRMCEB PRMGBL
464PRMMBX PSWAPM READALL SECURITY SETPRV SHARE
465SHMEM SYSGBL SYSLCK SYSNAM SYSPRV TMPMBX
466UPGRADE VOLPRO WORLD
467Default Privileges:
Output Expected:
"
ACNT,ALLSPOOL,ALTPRI,AUDIT,BUGCHK,BYPASS,CMEXEC,CMKRNL,DIAGNOSE,DOWNGRADE,EXQUOTA,
GROUP, GRPNAM,GRPPRV,IMPERSONATE,IMPORT,LOG_IO,MOUNT,NETMBX,OPER…etcc"
The delimiter can be anything.
View 1 Replies
View Related
Aug 22, 2006
Hello,
I have a field in my table that includes free form text. Within this text are two five digit numbers seperated by a forward slash (/). I need to be able to locate the "/" and then return and display the numbers before and after the "/" seperately. For example:
"the text looks like this but has two numbers 55555/66666 within the string"
I need to get the "55555" and the "66666" in oprder to then display them. can anyone help? I am using ASP/SQL. Appreciated in advance!
View 5 Replies
View Related
Mar 22, 2007
This is my sample table Data
table1
TagID|TagName
--------------
1|xyz
2|123abc
3|a3bc
4|arsdew
5|xyc324
..............
..............
TagID--Primary Key
table2
TopicID|Topic
----------------
1|jkkj
2|kjgg
3|jhkj
4|refc
5|huhf
8|fyhj
9|jjk
...............
.............
..........
TopicID---Primary Key
table3
DetID|TagID|TopicID
-----------------------
1|1|1
2|1|2
3|1|3
4|2|4
5|2|5
8|3|8
9|3|9
....................
...................
.................
DetID---Primary key
TagID and TopicID----Foreign key
i need Topic based on TagName.
This xyz(TagName) have a 3 Topic. i need the 3 Topic
Once i select 123abc(TagName) that particular Topic I need..
any Query in Single Line.......................
View 2 Replies
View Related
