|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Simple Query QuestionHi,
From Invoices table I want to select those rows where Doc_no in those rows is same. What will be the query for this? Thanks -- pmud >> From Invoices table I want to select those rows where Doc_no in those Same as what? Are you looking for duplicates? Which invoice table are you >> rows is same. What will be the query for this? referring to? See www.aspfaq.com/5006 -- Anith Hi Anith,
I dont want to find duplicate rows... I want to find all those rows where the same doc_no has appeared in more than one row.... -- Show quotepmud "Anith Sen" wrote: > >> From Invoices table I want to select those rows where Doc_no in those > >> rows is same. What will be the query for this? > > Same as what? Are you looking for duplicates? Which invoice table are you > referring to? See www.aspfaq.com/5006 > > -- > Anith > > > Please refer to the link in my previous post & provide sufficient
information for others to better understand the problem. Based on guesswork: SELECT * FROM tbl t1 WHERE t1.doc_no IN ( SELECT t2.doc_no FROM tbl t2 GROUP BY t2.doc_no HAVING COUNT( * ) > 1 ); -- Anith Hi Anith,
I read the article whose link you had sent. The DDl for my table is as follows but i didnt understnad ho to put in sample data there. Do i have to copy the code thats there and run it in query analyzer? The DDL is of table inventor. I didnt give Invoices table 'coz I think i am not sure I should give that table. From th Inventor table,I want to find all those rows where the same DOC_NO has eared in more than one row....but the TYPE is different. DDL is as follows CREATE TABLE [INVENTOR] ( [SERIAL_NO] [T_INTEGER] NOT NULL , [UNIQUE_ID] [T_INTEGER] NULL , [ITEM_NO] [T_VARCHAR_15] NULL , [ENTRY_DATE] [T_DATE_TIME] NULL , [PONUM] [T_VARCHAR_15] NULL , [CMNUM] [T_VARCHAR_15] NULL , [REF_NO] [T_VARCHAR_15] NULL , [VENDOR] [T_VARCHAR_15] NULL , [AREA_TYPE] [T_STOCK_AREA_TYPES] NULL , [COST_IN] [T_MONEY] NULL , [AREA_CODE] [T_VARCHAR_15] NULL , [MFG_SER] [T_VARCHAR_50] NULL , [LIST_PRICE] [T_MONEY] NULL , [OTHER] [T_MEMO] NULL , [DOCNUM] [T_VARCHAR_15] NULL , [TYPE] [T_PROCESS_DOC_TYPES] NULL , [CUSTCHAR1] [T_VARCHAR_40] NULL , [CUSTCHAR2] [T_VARCHAR_40] NULL , [CUSTCHAR3] [T_VARCHAR_40] NULL , [CUSTCHAR4] [T_VARCHAR_40] NULL , [CUSTDATE1] [T_DATE_TIME] NULL , [CUSTDATE2] [T_DATE_TIME] NULL , [CUSTDATE3] [T_DATE_TIME] NULL , [CUSTDATE4] [T_DATE_TIME] NULL , [CUSTLOG1] [T_BOOLEAN_F] NULL , [CUSTLOG2] [T_BOOLEAN_F] NULL , [CUSTLOG3] [T_BOOLEAN_F] NULL , [CUSTLOG4] [T_BOOLEAN_F] NULL , [CUSTNUM1] [T_DECIMAL_19_4] NULL , [CUSTNUM2] [T_DECIMAL_19_4] NULL , [CUSTNUM3] [T_DECIMAL_19_4] NULL , [CUSTNUM4] [T_DECIMAL_19_4] NULL , [PD_TYPE] [T_STOCK_DOC_TYPES] NULL , [PO_ID] [T_INTEGER] NULL , [LOT_NO] [T_VARCHAR_15] NULL , [CUSTMEMO1] [T_MEMO] NULL , [CUSTMEMO2] [T_MEMO] NULL , [CUSTMEMO3] [T_MEMO] NULL , [CUSTMEMO4] [T_MEMO] NULL , [CUSTCHAR5] [T_VARCHAR_40] NULL , CONSTRAINT [PK_INVENTOR] PRIMARY KEY CLUSTERED ( [SERIAL_NO] ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO Thanks -- Show quotepmud "Anith Sen" wrote: > Please refer to the link in my previous post & provide sufficient > information for others to better understand the problem. > > Based on guesswork: > > SELECT * > FROM tbl t1 > WHERE t1.doc_no IN ( SELECT t2.doc_no > FROM tbl t2 > GROUP BY t2.doc_no > HAVING COUNT( * ) > 1 ); > > -- > Anith > > > This should help (based on Anith's):
select * from INVENTOR where DOCNUM in (select DOCNUM from INVENTOR group by DOCNUM having count(distinct TYPE)>1) Btw, you can download free tool, ObjectScripter/QALite from site (http://rac4sql.net) to generate DDL+Inserts. -- Show quote-oj "pmud" <p***@discussions.microsoft.com> wrote in message news:A2AC1ECA-8F23-4879-9728-11F7A1344E67@microsoft.com... > Hi Anith, > > I read the article whose link you had sent. The DDl for my table is as > follows but i didnt understnad ho to put in sample data there. Do i have > to > copy the code thats there and run it in query analyzer? > > The DDL is of table inventor. I didnt give Invoices table 'coz I think i > am > not sure I should give that table. From th Inventor table,I want to find > all > those rows where > the same DOC_NO has eared in more than one row....but the TYPE is > different. > > DDL is as follows > > CREATE TABLE [INVENTOR] ( > [SERIAL_NO] [T_INTEGER] NOT NULL , > [UNIQUE_ID] [T_INTEGER] NULL , > [ITEM_NO] [T_VARCHAR_15] NULL , > [ENTRY_DATE] [T_DATE_TIME] NULL , > [PONUM] [T_VARCHAR_15] NULL , > [CMNUM] [T_VARCHAR_15] NULL , > [REF_NO] [T_VARCHAR_15] NULL , > [VENDOR] [T_VARCHAR_15] NULL , > [AREA_TYPE] [T_STOCK_AREA_TYPES] NULL , > [COST_IN] [T_MONEY] NULL , > [AREA_CODE] [T_VARCHAR_15] NULL , > [MFG_SER] [T_VARCHAR_50] NULL , > [LIST_PRICE] [T_MONEY] NULL , > [OTHER] [T_MEMO] NULL , > [DOCNUM] [T_VARCHAR_15] NULL , > [TYPE] [T_PROCESS_DOC_TYPES] NULL , > [CUSTCHAR1] [T_VARCHAR_40] NULL , > [CUSTCHAR2] [T_VARCHAR_40] NULL , > [CUSTCHAR3] [T_VARCHAR_40] NULL , > [CUSTCHAR4] [T_VARCHAR_40] NULL , > [CUSTDATE1] [T_DATE_TIME] NULL , > [CUSTDATE2] [T_DATE_TIME] NULL , > [CUSTDATE3] [T_DATE_TIME] NULL , > [CUSTDATE4] [T_DATE_TIME] NULL , > [CUSTLOG1] [T_BOOLEAN_F] NULL , > [CUSTLOG2] [T_BOOLEAN_F] NULL , > [CUSTLOG3] [T_BOOLEAN_F] NULL , > [CUSTLOG4] [T_BOOLEAN_F] NULL , > [CUSTNUM1] [T_DECIMAL_19_4] NULL , > [CUSTNUM2] [T_DECIMAL_19_4] NULL , > [CUSTNUM3] [T_DECIMAL_19_4] NULL , > [CUSTNUM4] [T_DECIMAL_19_4] NULL , > [PD_TYPE] [T_STOCK_DOC_TYPES] NULL , > [PO_ID] [T_INTEGER] NULL , > [LOT_NO] [T_VARCHAR_15] NULL , > [CUSTMEMO1] [T_MEMO] NULL , > [CUSTMEMO2] [T_MEMO] NULL , > [CUSTMEMO3] [T_MEMO] NULL , > [CUSTMEMO4] [T_MEMO] NULL , > [CUSTCHAR5] [T_VARCHAR_40] NULL , > CONSTRAINT [PK_INVENTOR] PRIMARY KEY CLUSTERED > ( > [SERIAL_NO] > ) ON [PRIMARY] > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] > GO > > Thanks > -- > pmud > > > "Anith Sen" wrote: > >> Please refer to the link in my previous post & provide sufficient >> information for others to better understand the problem. >> >> Based on guesswork: >> >> SELECT * >> FROM tbl t1 >> WHERE t1.doc_no IN ( SELECT t2.doc_no >> FROM tbl t2 >> GROUP BY t2.doc_no >> HAVING COUNT( * ) > 1 ); >> >> -- >> Anith >> >> >> Please provide DDL, sample data, and expected results so we have *some* hope
of answering your question at all (). OK - here is a total guess, based on information you did not provide: /**************************/ CREATE TABLE Documents ( Doc_no INT NOT NULL, Doc_Name VARCHAR(35) NOT NULL ); INSERT INTO Documents (Doc_no, Doc_Name) VALUES (1, 'The Importance of Primary Keys'); INSERT INTO Documents (Doc_no, Doc_Name) VALUES (1, 'Data Modeling for Newbies'); INSERT INTO Documents (Doc_no, Doc_Name) VALUES (2, 'Principle of SQL Development'); INSERT INTO Documents (Doc_no, Doc_Name) VALUES (3, 'Learning the SQL Standard'); SELECT Doc_no, Doc_Name FROM Documents WHERE Doc_no IN (SELECT Doc_no FROM Documents GROUP BY Doc_no HAVING COUNT(*) > 1); DROP TABLE Documents; /**************************/ This returns: /**************************/ Doc_no Doc_Name ----------- ----------------------------------- 1 The Importance of Primary Keys 1 Data Modeling for Newbies /**************************/ If this is a recurring issue, make the Doc_no column the primary key for the table, or at least add a unique constraint to the Doc_no to ensure duplicates cannot be added. Show quote "pmud" <p***@discussions.microsoft.com> wrote in message news:C821AB10-E930-4895-A616-D910AA12B8BC@microsoft.com... > Hi, > > From Invoices table I want to select those rows where Doc_no in those rows > is same. What will be the query for this? > > Thanks > -- > pmud  |
|||||||||||||||||||||||
Other interesting topics