Query two tables with related data


i have this query

select distinct drawer, packagetype, doctype
from document where drawer = 'abul'

returns

drawer-packtype-doctype
ABUL 1000 0
ABUL 1015 0
ABUL 1017 0
ABUL 9999 NOTE
ABUL 1000 RECO
ABUL 1000 MISC
ABUL 1000 EMAI
ABUL 1000 TEST
ABUL 1000 FX


i have another table named packtype. it has columns packagetype & description. Instead of showing the packtype in the original query, i want to show the description from the packtype table.
Posted On: Sunday 11th of November 2012 09:33:56 PM Total Views:  214
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




a query : this one stops me

Hi again, I was practicing queries and this one I tried to find how to write it but I can't find how to do it. I think a single table is enough. The table : CREATE TABLE COURSE ( NUMCOURSE NUMERIC(4) PRIMARY KEY, DATECOURSE DATETIME, NUMSAL NUMERIC(2) FOREIGN KEY REFERENCES SALARIE (NUMSAL), NUMCLI CHAR(6) FOREIGN KEY REFERENCES CLIENT (NUMCLIENT), LIEUDEPART CHAR(15), LIEUARRIVE CHAR(3)); Here is how it is filled : INSERT INTO COURSE VALUES ('1612','08-16-2002','1','PAR002','LYON','LYON') INSERT INTO COURSE VALUES ('1613','08-17-2002','5','ADM002','LYON','PARIS') INSERT INTO COURSE VALUES ('1614','08-18-2002','3','ENT004','VALENCE','LYON') INSERT INTO COURSE VALUES ('1615','08-19-2002','2','ENT006','LYON','DIE') INSERT INTO COURSE VALUES ('1616','08-20-2002','1','ADM005','LYON','LYON') INSERT INTO COURSE VALUES ('1617','08-21-2002','4','PAR007','LYON','LYON') INSERT INTO COURSE VALUES ('1618','08-22-2002','4','ADM006','LYON','MARSEILLE') INSERT INTO COURSE VALUES ('1619','08-23-2002','2','ENT008','MONTELIMAR','LYON') INSERT INTO COURSE VALUES ('1620','08-24-2002','4','ADM008','MARSEILLE','LYON') The QUERY is : count the number of courses performed by the employe number 2 (the number being NUMSAL). I don't know how to write as it has to filter who performed the courses before counting. And, according to what I was told the COUNT must be written in the SELECT. Thank you for your help.
VIEWS ON THIS POST

137

Posted on:

Sunday 11th November 2012
View Replies!

Which query would be more efficient?

I've got a query that needs to join about 10 tables, and is taking too long to run. So in the interest of speeding things up, I would like to know which of the following queries would be more efficient when implemented on a much larger scale. Code: --Query1 SELECT Table1.Field1, Table1.Field2, Table2.Field3, Table2.Field4 FROM Table1 INNER JOIN Table2 ON Table1.JoinField = Table2.JoinField AND Table2.Field3 = 'Value' Code: --Query2 SELECT Table1.Field1, Table1.Field2, Table2.Field3, Table2.Field4 FROM Table1 INNER JOIN Table2 ON Table1.JoinField = Table2.JoinField WHERE Table2.Field3 = 'Value'
VIEWS ON THIS POST

255

Posted on:

Sunday 11th November 2012
View Replies!

A quick way to build this query

i need to build a query..i know the long way...need a quick way itemid Routeto 1 NB 1 IQ 1 IP 4 NB 4 IP 4 IQ 5 NB 5 JY 6 AB 6 NB 2 NB 3 IS I want only those itemid which had routeto = NB and never had routeto=IQ or IP i.e 5 ,6 , 2 so i dont want 1,4 to ever come in the resultset! Thks in advance
VIEWS ON THIS POST

121

Posted on:

Sunday 11th November 2012
View Replies!

I need help with a complex query and subqueries

On this page, http://www.specialopspaintball.com/b...ber_finder.asp, when doing a search by RANK, I am having a hard time writing a SQL query that will work. My problem is that RANK is based on a members total POINTS. I need a query that can SUM the points, lookup what RANK the points fall betweeen (based on min and max column values) My test query: SELECT TOP 100 *, (SELECT COUNT(LogID) FROM PlayLog WHERE MemberID = Members.MemberID) AS Logs, (SELECT SUM(Points) FROM Points WHERE MemberID = Members.MemberID) AS Points, (SELECT Rank FROM Ranks WHERE MIN = (SELECT SUM(Points) FROM Points WHERE MemberID = Members.MemberID)) AS Rank FROM Members WHERE Rank = 'Private' The part that is not working: WHERE Rank = 'Private' Why can I not use where on a column that I defined with a subquery, and what's the workaround
VIEWS ON THIS POST

144

Posted on:

Sunday 11th November 2012
View Replies!

Redirecting query results in UNIX to a text file.

How can I save the large outputs of a query to a text file from the command window
VIEWS ON THIS POST

148

Posted on:

Sunday 11th November 2012
View Replies!

How can i refine this query?

Hi all, hope you can help. I posted a thread a while back and thought i had solved the problem i originally facing. However it seems i was mistaken. I believe i now understand the issues i had, but don't know how to work it into the sql query. The sql query needs to extract data from tables that will list all current tasks (jobs to do) that are still outstanding for a specific store/shop. eg: store 1, task to do number 7943 task to do number 7944 task to do number 7945 total = 3 store 2, task to do number 7943 task to do number 7944 task to do number 7945 total = 3 etc.. Only when a task has been completed by the store/shop, will it be sent to another table(pgm_action_responses ). So I have to cross ref the stores from the table that holds a complete list of stores(stores). Sounds easy enough, and i thought this would be ok: Code: select pss_store_code, store_name, st_id, st_title from pgm_store_stories as ss full join stores as s on s.store_code = ss.pss_store_code full join pgm_stories as st on ss.pss_story_id = st.st_id where storestatus = 'on' and st_id > '7900' and pss_store_code not in ( select ar_store_code from pgm_action_responses where ar_story_id > '7944' ) order by pss_store_code, st_id but this doesnt bring back the correct information. This is because the query searches for a store_code not in the pgm_actions_responses that is above '7944', and due to many tasks' being sent to many stores some task will be completed by stores and and their store number will then be sent to this pgm_action_responses table. So the query will pick up the stores that are in the database and not return the correct result. When the, where ar_story_id > '7944' is taken out, nothiing is returned as the search is done through the whole table, and because of previous task the store code will already be in there. So ideally i need to select all store_codes that are not present in the action_responses table where a specific task is unique to a specific store. Really cant work out how to do it Hope this all makes sense if not please ask, and i shall try to answer your question. Kind regards MG
VIEWS ON THIS POST

157

Posted on:

Monday 12th November 2012
View Replies!

Bring sql query to another line

This query is correct but anybody knows how do i concatenate this if i want to bring this long query to the next line instead of it in one line if this is in .CS file Code: select * from t3 c FULL Join t2 e on c.ID=e.ID where c.Status='APP2' and c.App = 'NA' or c.Status='APP2' and c.App = 'Away' or c.Status='APP1' and c.App2 = 'NA' or c.Status='APP1' and c.App2 = 'Away' or c.Status ='APP1' and c.App1 = 'test@email.com' or c.Status='APP2' and c.App2 = 'test@email.com'
VIEWS ON THIS POST

103

Posted on:

Monday 12th November 2012
View Replies!

Help with periodically query

I have a table based on daily data, such as stock price. (Date and Price - usually it's 5 days a week) I want to create a query that will return the first data of each month, but I couldn't find the way... For example: 1/5/2009 2.5$ 2/1/2009 2.8$ 3/3/2009 3.0$ 4/2/2009 1.8$
VIEWS ON THIS POST

161

Posted on:

Monday 12th November 2012
View Replies!

Need Hierarchical query

Hi all, i want to know how to write a hierarchical query with two table. Here im givig two sample tables which i have taken. Asset table1 AssetId AstType ParentAssetId 1 1 NULL 2 2 1 3 3 2 4 4 3 5 4 3 6 4 3 7 4 3 8 5 4 9 7 4 10 5 4 11 6 8 12 6 8 13 6 8 14 6 8 15 8 9 16 8 9 17 9 15 18 9 15 19 7 5 20 7 5 21 7 5 22 7 5 23 5 6 24 5 6 25 5 6 26 5 6 27 7 7 28 5 7 29 5 7 30 10 19 31 10 19 32 10 19 33 10 19 34 10 19 35 10 19 36 10 19 37 10 20 38 10 20 39 10 20 40 10 20 41 10 20 42 10 20 43 10 20 44 10 20 45 10 20 46 10 20 47 10 20 48 10 22 49 10 22 50 10 22 51 10 22 52 10 22 53 8 22 54 10 22 55 10 22 56 8 22 57 10 22 58 10 22 59 10 22 60 10 22 61 8 22 62 10 22 63 10 22 64 10 22 65 6 24 66 6 24 67 6 24 68 6 24 69 6 24 70 6 24 71 6 24 72 6 24 73 6 24 74 6 24 75 10 27 76 10 27 77 10 27 78 8 27 79 10 27 80 10 27 81 10 27 82 10 27 83 10 27 84 10 27 85 10 27 86 10 27 87 10 27 88 10 27 89 10 27 90 10 27 91 10 27 92 10 27 93 6 28 94 6 28 95 6 28 96 6 28 97 6 28 98 6 28 99 6 28 100 6 28 101 6 28 102 6 28 103 6 29 104 6 29 105 6 29 106 9 78 107 9 78 108 9 78 109 3 2 122 3 2 124 4 122 126 4 109 127 5 126 130 4 122 131 4 109 133 3 2 135 4 133 138 4 133 139 3 2 140 4 139 141 4 139 143 16 11 144 16 11 145 16 11 146 16 11 147 12 143 148 12 143 149 13 152 150 13 144 151 16 13 152 16 13 153 16 13 154 16 13 155 16 13 156 13 144 157 13 152 158 4 139 159 4 139 Issue tbale is: IssueId AssetId 5 2 7 122 12 2 13 2 14 2 15 2 17 138 18 2 19 135 20 2 22 2 25 4 26 8 27 149 31 10 33 140 Now i want to Count of IssueId in the tree of AssetId=2 From Asset table and Issue table. I will explain how 1, 2 and 3 are having, 1 is parent for all AssetId comes under 1, 2 is child for 1 and parent for remaining all, 2 already having 8 IssueIds directly in Issue table and their childs are 4, 8, 10, 122, 135, 138, 140 and 149. 4 is child for 2 and 1, its having directly one issue, and also parent for 8, 10, 149. like we need to count all issues from IssueTable and which has to map with asset table. Expected Output is: AssetId CountOfIssueId 1 16 2 16 3 4 4 4 5 0 6 0 7 0 8 2 9 0 10 1 11 0 12 0 13 1 14 0 15 0 16 0 17 0 18 0 19 0 20 0 21 0 22 0 23 0 24 0 25 0 26 0 27 0 28 0 29 0 30 0 31 0 32 0 33 0 34 0 35 0 36 0 37 0 38 0 39 0 40 0 41 0 42 0 43 0 44 0 45 0 46 0 47 0 48 0 49 0 50 0 51 0 52 0 53 0 54 0 55 0 56 0 57 0 58 0 59 0 60 0 61 0 62 0 63 0 64 0 65 0 66 0 67 0 68 0 69 0 70 0 71 0 72 0 73 0 74 0 75 0 76 0 77 0 78 0 79 0 80 0 81 0 82 0 83 0 84 0 85 0 86 0 87 0 88 0 89 0 90 0 91 0 92 0 93 0 94 0 95 0 96 0 97 0 98 0 99 0 100 0 101 0 102 0 103 0 104 0 105 0 106 0 107 0 108 0 109 0 122 1 124 0 126 0 127 0 130 0 131 0 133 2 135 1 138 1 139 1 140 1 141 0 143 0 144 0 145 0 146 0 147 0 148 0 149 1 150 0 151 0 152 1 153 0 154 0 155 0 156 0 157 0 158 0 159 0 Please anyone can help me..
VIEWS ON THIS POST

111

Posted on:

Monday 12th November 2012
View Replies!

Update query

Hi , I am busy merging data from 3 columns in table 'A' to table 'B', but having difficulty with a bulk update query. The 2 tables are joined by OrgID. The columns that are added from table A to table B have OrgIDs in both tables, and I would like to add the data from table B to table A when the ID corresponds. I was thinking in the following direction, but no success. Please help. [highlight="SQL"]Update A Set A.Col1 = B.Col1, A.Col2 = B.Col2, A.Col3 = B.Col3 Where A.OrgID = B.OrgID[/highlight] This is urgent, please help.
VIEWS ON THIS POST

113

Posted on:

Thursday 15th November 2012
View Replies!

Why this query is not returning any rows

all, I have 2 tables in sql (2000), Code: 1. Category for which fields are CategoryID(int),CategoryName(nvarchar(50)) 2. Usercategory, for which the fields are UserID(int),CategoryID(nvarchar(500)),SubcategoryID(nvarchar(500)). In first table values are stored like Code: categoryID...............categoryName 1 Cat1 2 cat2 3 Cat3 In 2nd table, I am storing values like Code: Userid............... categoryID ...............SubcategoryID 1 ............... 2,4,5,7,9 ............... 8,11,12,14 2 ............... 1 ............... 5,11,8,9 3 ............... 10,12,13 ............... 20,21,22 That means, I am storing using comma seperator. If I create a query Code: select categoryname,category.categoryid from category inner join usercategory on category.categoryid = usercategory.categoryid where usercategory.userid = 1 then it will not return anything, but if userid is 2 then it's returning 1. Why this problem is coming and how could I sort this out
VIEWS ON THIS POST

126

Posted on:

Thursday 15th November 2012
View Replies!

String function query

i have a table of following structure Names Raj Kumar Pradeep Kumar Syam Sundhra Javagal Srinath Pandit Ravishankar I need a query with output as follows Names RK PK SS JS PR
VIEWS ON THIS POST

404

Posted on:

Thursday 15th November 2012
View Replies!

Want distinct row in query

Hi... I can't get distinct row from query..i tried a lot but couldnt succeded. Let me explain in brief. I am having a table name usermaster. Having column..RID(autogen), Name, Date, Miniute. Now when i tried to fetch rows between two dates, at that time it gives all rows between those dates..What i want is uniq name with max date...for example There are two rows... id, name, date, min 1, Dev, 10/26/04, 20 2, Dev, 10/27/04, 30... now i want the last rows to be fetched as its max date...when I tried query with date between 10/26/04 to 10/28/04..both row comes.. I used distinct, groupby, order by ...but it wont work... Pls. pls. help me to sort it out.. Thanx. Dev
VIEWS ON THIS POST

165

Posted on:

Thursday 15th November 2012
View Replies!

SQL server creates 2 records for a single insert SQL query

The problem is SQL Server produced two records each time a signle insert statement fired up. After the user clicked the submit button in the asp Request form, it will call the do_insert.asp and I have the source code below. For an example, I assigned the data to all variables for testing purposes. The user submitted his personal info with 2 days that he worked overtime. General speaking, one record goes to the table tbl_request and 2 records to tbl_workTime with carrying the request_ID from tbl_request. When do_insert.asp executed it generated 2 records for table tbl_request and 4 records for table tbl_worktime. It didn't happen when I ran the queries from the SQL Query Analyzer. My environment is Win 2K, SQL Server2000, IIS 5.1. The following text is the code and the stored procedure scripts. TIA ============== do_insert.asp begins ===================
VIEWS ON THIS POST

376

Posted on:

Thursday 15th November 2012
View Replies!

sql query string help?

Dear friends... I need a sql query string,.... I have database with names as follows.. ABC ABC DEF FRG FRG FRG FRG DEF ABC Now i need a sql query string to show the data by following way. FRG 4 ABC 3 DEF 2 please help regards Ananda
VIEWS ON THIS POST

259

Posted on:

Thursday 15th November 2012
View Replies!

count clause in sql query

I use following query. SELECT employees.empno, employees.name, approvals.contractno, assignments.acontract,assignments.aposit, assignments.astrdate, assignments.aenddate FROM (employees INNER JOIN approvals ON employees.empno=approvals.empno) INNER JOIN assignments ON employees.empno=assignments.empno data retrieve from the sql server tables like this. empno name aposit contractno acontract --------------------------------------------------------- 1234 Martin Engineer 2016 229 7890 Jones *** Engr 2002 221 1234 Martin Engineer 2016 222 1590 Peter Sr.Engr 894 982 1234 Martin Engineer 2010 985 1590 Peter Sr.Engr 229 2016 1234 Martin Engineer 2016 3010 1590 Peter Sr.Engr 229 2016 7890 Jones *** Engr 2002 229 4567 George Engineer 2015 222 7800 Mathew Sureyor 2017 225 ........... and so on....... contractno means approvals.contractno acontract means assignments.acontract some employee are working four contracts, some are three and some are two and remaining employees working only one contract. How can data can retreive from the above query that it count the employee that it working four contract, three contracts, two contract and one contract etc. data retrieve like this. empno..name....aposit...contractno..acontract count (working acontract) ---------------------------------------------------------- 1234...Martin...Engineer....2016....229........4 1234...Martin...Engineer....2016....222 1234...Martin...Engineer....2010....985 1234...Martin...Engineer....2016....3010 1590...Peter....Sr.Engr.....894.....982........3 1590...Peter....Sr.Engr.....229.....2016 1590...Peter....Sr.Engr.....229.....2016 7890...Jones....*** Engr....2002....221........2 7890...Jones....*** Engr....2002....229 4567...George...Engineer....2015....222........1 7800...Mathew...Sureyor.....2017....225........1 ........... and so on........ How can use count clause in above query and display data like as above order first display which employee working highest number of contracts ie four and so on.. regards. Mateen
VIEWS ON THIS POST

97

Posted on:

Thursday 15th November 2012
View Replies!

difficulty in creatin a query

hi friends... I have a table which contains ---------------------------- prodmanufac prodname 1 brand1 2 brand2 1 brand3 3 brand4 ---------------------------- How can check that a "Prodmanufac" contains multiple "prodname"
VIEWS ON THIS POST

245

Posted on:

Thursday 15th November 2012
View Replies!

syntax error in id query HELP!!!!!!

i have 1 page which gives the results of a data base search. the title of each result is hypertext so clicking on it will open a second page which will contain the other fields of the same database record.I have used the id field for this purpose but it dosent work: I get this error message:Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Errore di sintassi (operatore mancante) nell'espressione della query 'id ='. -------------------------------------------------------------------i couldn't upload the script her it wouldn't let me so the page can be found at this address:http://web.zonalingue.com/asp.htm
VIEWS ON THIS POST

233

Posted on:

Thursday 15th November 2012
View Replies!

Sql query problem

hi, i am having a problem in a SQLquery. actually i am developing a project in asp.net with C# i. e. "MESSAGE BOARD". in this all the topics are displayed in gridview. but here sorting should be as per the last modified topic as well as if any user has replied more than 10 posts , then these messages should be displayed first. i mean sticky posts should have highest priority to display first in the gridview as well as last modified topic. the two tables from which i am accessing datas are: Table1: "create_topic" : Table Fields : Description topic_id# : Topic id group_id# : Group id cat_id# : subgroup id/category id username# : user name topic_title : title of topic topic_msg : original message topic_create_date : date when topic is created no_of_views : total no. of views no_of_reply : total no. of replies to this topic Is_sticky : If more than 10 posts by a single user then it is set to Y else N Is_closed : ban the reply if satisfied. Table 2: "reply_topic" : group_id# : group id cat_id# : category id reply_id # :reply id topic_id # :topic id Username # :username reply_msg : reply rply_date : date when reply can anyone tell me the query which can fulfill the purpose. I have tried the query: "SELECT a.topic_id, a.group_id, a.cat_id, a.username, a.topic_title, a.topic_msg, a.topic_create_date, a.no_of_views, a.no_of_reply, a.is_sticky, a.is_closed, CASE WHEN a.is_sticky = 'Y' THEN getdate() ELSE COALESCE (b.rply_date, a.topic_create_date) END AS SortDateFROM create_topic AS a FULL OUTER JOIN reply_topic AS b ON a.group_id = b.group_id AND a.cat_id = b.cat_id AND a.topic_create_date = b.rply_dateWHERE (a.cat_id = @cat_id)ORDER BY SortDate DESC" Although the query is resulting the sticky post to be displayed first and rest others to be displayed next.Only as per the date of creating post sorting is maintained ,but when any body replied then this does not reflect the order. Also when there are more than one sticky posts then as per the last modified (either made post or replied ) sorting should be maintained and even same should be applied for the non sticky posts. This is not actually happening. What changes should i make ,so that it may give the result what actually i want. Please help me.
VIEWS ON THIS POST

130

Posted on:

Monday 19th November 2012
View Replies!

1.7Million rows. How to speed up query?

Hi , I am asking this question on behalf of a friend. I have little knowledge of SQL but my friend is quite knowledgeable, although this is the first time he is dealing with large database for a client. So here's the story. His client has a database containing 1.5 million books. Now he is setting up a website which will enable users to search books. Searching by ISBN is no problem as it only takes 1 second. The problem is, searching by Title takes more than 20seconds, which is unacceptable. My friend has only done smaller database and he just recently thought of implementing indexing and now looking for other ideas. Each row contains book details such as Title, Author1, Author2, Author3, Publisher, Publication Date, ISBN, etc. Can anyone who are more experienced in doing large database share with me some design ideas His client is aiming for 8seconds or less.
VIEWS ON THIS POST

212

Posted on:

Monday 19th November 2012
View Replies!