In my new job I have to administer an existing SQL-database with approx. 50 tables. In this database are no joins :confused: defined between the tables. We use a Visual Basic 6 application to create a GUI and within this VB6 app. there are several SELECT statements to retrieve the required data. In these SELECT statements are all the INNER and OUTER JOINS between the tables defined.
My question: is this a correct way to work with or is it better to create all the JOINs between the tables on the database itself? Or should I create different views and define the JOINs in there? My main concern is the speed to retrieve data and second the required time to administer this database.
My question : How Do I bring all rows from TblB and matching single row from TblA (I have more than one sessionID in TblB and only one Unique SessionID in TblA).
The Select query I was using is
SELECT PageViews.ID AS ID, PageDetailView.VisitID, PageViews.PageAccessed, PageDetailView.PageAccessed AS Expr1, PageViews.QueryString, PageDetailView.QueryString AS Expr2, PageViews.Referer, PageViews.SessionID, PageDetailView.SessionID AS Expr3, PageDetailView.PdtID, PageDetailView.Pcode, PageDetailView.CustID, PageDetailView.OrdTot, PageViews.[Date] FROM PageViews RIGHT OUTER JOIN PageDetailView ON PageViews.SessionID = PageDetailView.SessionID ORDER BY PageViews.ID DESC
I have a basic sql statement, where I have a usersID, and I want to joing that usersID to another table in another database to get the users first and last names. How do I join across databases... each with a different connection string? Here's what I want.. Select usersID from tableA in databaseA, and usersFirstName, usersLastName from table B in database B where the usersID from tableA = the usersID in tableb.
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr, FROM plan2008 GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr FROM fsp_auftrag GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database. The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records. I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered). I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these). I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes. I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem. So my question is: Is it normal for such a type of read query to take 90 seconds to complete? Is there anything I could do to speed it up. Any other thoughts? Thanks
2222 22 22 15 90 1 and this is what I want to retrieve from these 3 tables
stud_num pk2 pk3 age
5432 55 44 23
9876 99 77 23
2222 22 22 15 this my query.... SELECT student.stud_num, student.pk2, student.pk3, student1.age, student2.age AS Expr1FROM student INNER JOIN student1 ON student.stud_num = student1.stud_num AND student.pk2 = student1.pk2 AND student.pk3 = student1.pk3 INNER JOIN student2 ON student.stud_num = student2.stud_num AND student.pk2 = student2.pk2 AND student.pk3 = student2.pk3WHERE (student1.grade = '90') AND (student1.pass = '1') AND (student2.grade = '90') AND (student2.pass = '1') however...it doesnt return any results... can anyone correct my code?
I have two tables in sql server database. Tables are Authors AuthorName varchar (primary key), AuthorImage varchar Threads ThreadID int Primary Key, ThreadAuthor varchar, ForumID int (Foreign Key), ThreadReplyID int, ThreadPostedDate int I have this query: "SELECT ThreadAuthor, ThreadSubject, ThreadPost, ThreadPostedDate , ForumID FROM Threads WHERE ThreadID = " + threadId + " OR ThreadReplyID = " + threadId + " ORDER BY ThreadPostedDate ASC" but i want to display AuthorImage on my webpage. How to modify the existing query to get the Author's image also. Plz help me out anyone there. Thanks
i've two tables. such as tableA and table B table A has a column named CreatedBY and table B the same column.. now i need to get only the rows which match with table A's createdby column.. suppose tableA has only 2 values. when i make the join with tableB it shows as 4 values
I need to perform a join on six different tables to produce a report. The biggest join I have ever done has been four tables. The way I did that query was to join three tables and then to perform the final select on the three table join. Can anyone give me some advice. Should I use the same approach? Or can I just continue to use the keyword INNER JOIN to perform the joins?
select left(orders.orderdate,11) as date, orderdetails.partnumber, SUM(orderdetails.qty) as total from orderdetails, orders where orderdetails.partnumber is not null and orderdetails.order_id = orders.order_id and orders.orderdate >= {ts'2001-05-01 00:00:00'} and orders.orderdate <= {ts'2001-05-31 23:59:59'} group by orders.orderdate, orderdetails.partnumber order by orders.orderdate, total
But I do not have part that have null orders. My goal is to get
1) Get all part numbers, qty in the month of May. 2) at the end attach all partnumbers that have null ordered values to see which one have no orders.
I have 2 tables. One with col1,col2 and second one with col1,col2,col3. My requirement is to compare col1,col2 of t1 with t2 and update only changed records in t2. How to implement? Please advise.
I've tried an outer left join but cannot get the right data. Basically everything from the timecard table and the leftovers from the default table with descriptions for all.
I've got two tables, one called clientsharedeals and the clientorderdeals. In the first table, I have four fields (Rundate, Accno, Dealid, Nominal) that I need to sum(Nominal), grouping by dealid.
Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).
My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.
Could somebody please explain to me how do we join a table onto itself as that is what I was advised to do but I can't quite get where I want to go.
What I want to do is list values from a table,but those values can be just a quote (what would cost if they decided to go for that option) or those values can represent what was spent and invoiced, what is confusing me is that all of that gets saved in the same table and in same columns, so what was quoted for example for AirFares and what was spent gets saved in the same record but when it is "quoted amount" then ID = 1 but when it is invoiced ID = -1 and that is how we know what was quoted and what was invoiced.
But I need to split that one field into two columns one showing AirFareQuoted and one AirFareInvoiced and i have no idea how to achieve this.
hi, I knew how to join 2 tables but i have a process to select 3 tables. I have a sample table and field below. I want to join Parts & Orders using field Prt_no and Supplier & Parts using field Sup _code
Hey Folks, I am stuck at one place in data migration........ I have the following source tables having no PK / INDEX / Constraint defined on any of them........
T1 ( ID1 INT, STYLE VARCHAR(34) )
T2 ( COL_ID VARCHAR(34), OBJ_ID VARCHAR(34) )
T3 ( OUT_ID VARCHAR(34), ITEM VARCHAR(34), )
And here is the relation..........
Quote: T1.STYLE = T2.COL_ID
T2.OBJ_ID = T3.OUT_ID
T3.ITEM = T2.COL_ID
Here is what I have tried with.........
Quote: SELECT * FROM T3 WHERE ITEM IN ( SELECT COL_ID FROM T1 INNER JOIN T2 ON T1.STYLE = T2.COL_ID INNER JOIN T3 ON T2.OBJ_ID = T3.OUT_ID )
But this query is not helping me out. I mean the number of output records are 0.
Now can any one suggest me a join that includes all the 3 tables, or finetune my query?
hi everybody I have a question and need answer as soon as possible. so i hope i get help. How can i join two tables have no common fields? I tried outer join but i get redundant data(repeated values).
Give me a script to solve the following problem . There are no real wrong answers, but the more efficient and bulletproof the query is the better. Assign rooms to classes based on capacity using the Classes and Rooms tables. Rules: each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.
And this is the tables query:
CREATE TABLE Classes (
ClassID int NOT NULL,
Class varchar (50),
Students int NOT NULL, CONSTRAINT PK_Classes PRIMARY KEY CLUSTERED (ClassID
) )
GO
CREATE TABLE Rooms (
Room int NOT NULL,
Capacity int NOT NULL,
CONSTRAINT PK_Rooms PRIMARY KEY CLUSTERED (Room)
)
GO
INSERT INTO Classes (ClassID, Class, Students) VALUES (1, 'Whats New for SQL Server 2008', 65)
INSERT INTO Classes (ClassID, Class, Students) VALUES (2, 'Introduction to Silverlight 1.1', 52)
INSERT INTO Classes (ClassID, Class, Students) VALUES (3, 'XQuery Deep Dive', 35)
INSERT INTO Classes (ClassID, Class, Students) VALUES (4, 'Ranking and Windowning', 35)
i have 2 tables with a common ID in both tables. one table has more number of rows than the other i want to a query which gives the list of ids or rows which are missing in the other table.
left,right,inner jopins only give me common or common + right/left i want only the missing once.
hi, i am facing an issue. i have got two tables say table1 and table2. table1 has column1,2,3 ( has values a,b,c) where as table 2 has different columns 4,5,6(j,k,l). column4 in table 2 has value which contains %+column1+% and it has two entires for each row in table1.the column4 value in table2 is different for all rows.When i am trying to join the two tables, i am getting values like
1 2 3 4 5 6 a b c j k l a b c j1 k l a1 b c j k l a1 b c j1 k l
but i want to display a b c k l a1 b c k l
is there a way to do it.
My queries were select distinct pc_assign_worklist.pxRefObjectInsName AS [pxRefObjectInsName], pc_assign_worklist.pxUrgencyAssign AS [pxUrgencyAssign], pc_assign_worklist.pyLabel AS [pyLabel], pc_assign_worklist.pyAssignmentStatus AS [pyAssignmentStatus], pc_assign_worklist.pxAssignedOperatorID AS [pxAssignedOperatorID], CONVERT(char (11),pc_assign_worklist.pxCreateDateTime) AS [pxCreateDateTime], pc_assign_worklist.pxCreateOpName AS [pxCreateOpName], pc_index_workparty.MemberIdentifier AS [MemberIdentifier], pc_index_workparty.LastName AS [Last Name], pc_index_workparty.FirstName AS [First Name], pc_index_workparty.pxInsName AS [pxInsName], pc_index_workparty.pzInsKey AS [pzInsKey], pc_index_workparty.pxpartySubscript as "Workparty" from dbo.pc_assign_worklist LEFT OUTER JOIN dbo.pc_index_workparty ON pc_index_workparty.pzInsKey LIKE '%' + pc_assign_worklist.pxRefObjectInsName + '%' where pxAssignedOperatorID = 'dasxkx1'
i also tried left inner join but it always returns the same result seta. I even created a view by inner join of two tables and then did left outer join on the second table . The result is same.
Can somebody tell whether this is achivable or not.
Hi everybody I know how to join two tables with [INNER JOIN] or [LEFT/RIGHT OUTER JOIN] Keywords ,But when i have more than two tables , always i mixed up and have difficulty to build the query specially when i have to use LEFT or RIGHT OUTER JOIN Keywords!!! and that's why i always use SQL Server Query Builder and let it to make the query string for me. i couldn't understand how SQL SERVER build the [FROM] section of the query ,really what method (Algorithm) does it uses to Build the query String??? could anyone point me or give me some advices? is there any book to this topic?
