I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.
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
I have database with three tables Accounts, Results, and ClosedOrders. All are connected through AccountID PK/FK.
I got a wonderful select statement that gives me the latest Results for each Account.
WITH cte AS ( SELECT Accounts.AccountID, Accounts.AccountName, Results.ResultTime AS LastUpdated,
[Code] ....
I've been struggling to extend this with two more columns from the ClosedOrders table. How to add columns to the this view? Basically what I need is this:
SELECT SUM([Lots]) AS Longs FROM [DEV].[dbo].[ClosedOrders] WHERE OrderTypeID = 0;
SELECT SUM([Lots]) AS Shorts FROM [DEV].[dbo].[ClosedOrders] WHERE OrderTypeID = 1;
But it has to "join" the CTE somehow so that I get the correct answer for each Account row.
I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my output ok but when I try to receive that output from write another select statement then its show error.
Msg 102, Level 15, State 1, Line 276 Incorrect syntax near ';'. Msg 102, Level 15, State 1, Line 315 Incorrect syntax near ')'. Msg 156, Level 15, State 1, Line 351 Incorrect syntax near the keyword 'as'.
Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
I have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2
1.) @Tab1 have multiple records for each ID
2.) @Tab2 also have multiple records for each ID
3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date
Note: @Tab1 always have lesser dates than @tab2 for each ID
Tables looks like as follows
declare @tab1 table (id varchar(3), effDt Date, rate int) insert into @tab1 values ('101','2013-12-01',5) insert into @tab1 values ('101','2013-12-02',2) insert into @tab1 values ('101','2013-12-03',52)
[code]....
In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.
Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
1 2015 ba1 137 HL EL Eco 2 2015 ba1 138 EL SL HS 3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS 2 15120002 2015 ba1 HL EL Eco 3 15120003 2015 ba1 SL EL His 4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL Drop table Bezeichnungen; GO create table Bezeichnungen ( Artikelnummer nvarchar(18), Artikelbezeichnung nvarchar(80), Artikelgruppe nvarchar(13),
i have 3 tables names parent, child1, child2 parent has 1 record, child1 has 2 record and child 3 has 3 records the script
select Parent.*,child1.f1,child2.f2 from child1 inner join Parent on parent.id =child1.id inner join child2 on child1.id =child2.id
running above query gives me sixes rows but i want only all rows of childs but not their Cartesian products
Object: Table [dbo].[Parent] Script Date: 06/18/2015 17:33:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Parent]( [id] [int] NOT NULL,
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
Hi there,I want to select records from 3 tables. In SQL Server 2005, I'm using of "For XML" like this:Select *, (Select * From dbo.PageModules Where (PageId = 1) For Xml Auto) As Modules, (Select * From dbo.PageRoles Where (PageId = 1) For Xml Auto) As Roles From dbo.PagesThat works fine in SQL 2005 but not in SQL 2000, Because SQL 2000 does not support nested "FOR XML".Is there any way for selecting records from multiple tables by a query?Thanks in advance
Table USERS Contains columns User_id and UserName Table DOMAIN Contains columns Domain_id and DomainName Table USER_DOMAIN Contains columns User_id, Domain_id, count, day, month, year
I am looking to run a report that pulls its information from USER_DOMAIN but instead of displaying User_id, Domain_id, it returns the UserName and DomainName associated.
The query to pull the info i need is very simple, where i am having problems is linking the user_id to the UserName and the Domain_id to the DomainName.
Hi all! I just registred (very nice site) and have problem with getting some data from multiple tables, I would like to get result in one result set and best would be in one sql query.
I have DB for miniMessenger proggy, what i try to do is retrieve list of contacts.
Table containing user account information.
CREATE TABLE `account` ( `id_account` mediumint(8) unsigned NOT NULL auto_increment, `userdata_id` mediumint(8) unsigned NOT NULL default '0', `login` varchar(15) NOT NULL default '', `pwd` varchar(15) NOT NULL default '', `messenger_id` mediumint(8) unsigned NOT NULL default '0', `logged` tinyint(1) NOT NULL default '0', `ost_login` varchar(11) default NULL, PRIMARY KEY (`id_account`), UNIQUE KEY `messenger_UN` (`messenger_id`), UNIQUE KEY `userdata_UN` (`userdata_id`) )
Contact list, first field is contact number (like 4356789 - MESSENGER id) next to this number is its contact number, auth - if contact was authorised, ban selfexplained :) I just take every row with number 4356789 and get contact numbers next to it. CREATE TABLE `contacts` ( `contact_id` mediumint(8) unsigned NOT NULL default '0', `contacts` mediumint(8) unsigned NOT NULL default '0', `auth` tinyint(1) unsigned NOT NULL default '0', `ban` tinyint(1) unsigned NOT NULL default '0', KEY `Contacts ID` (`contact_id`) ) INSERT INTO `contacts` VALUES (4118394, 3333333, 1, 0); INSERT INTO `contacts` VALUES (4118394, 1234567, 0, 1);
Its table for messenger data, ID, status of contact (offline,online,ect), description, chat archiwum, CREATE TABLE `messenger` ( `id_messenger` mediumint(8) unsigned NOT NULL default '0', `status_id` tinyint(3) unsigned NOT NULL default '0', `description` varchar(255) NOT NULL default '', `archiwum` mediumtext NOT NULL, PRIMARY KEY (`id_messenger`) ) INSERT INTO `messenger` VALUES (1234567, 0, '', ''); INSERT INTO `messenger` VALUES (3333333, 1, '', ''); INSERT INTO `messenger` VALUES (4118394, 2, '', '');
Status is enumeration of status states(off,on,brb ect). CREATE TABLE `status` ( `id_status` tinyint(3) unsigned NOT NULL default '0', `stat` varchar(15) default NULL, PRIMARY KEY (`id_status`) ) INSERT INTO `status` VALUES (0, 'offline'); INSERT INTO `status` VALUES (1, 'Online'); INSERT INTO `status` VALUES (2, 'brb');
What i want to get is contact list + additional info of specific user by its messenger id. Like: id_messenger,contacts,auth,ban,stat
which is userID, contact ID, authorisation, ban, status
My query looks like this: SELECT id_messenger,contacts,auth,ban,status_id FROM account,messenger,contacts WHERE account.login = 'User' AND messenger.id_messenger = account.messenger_id AND contacts.contact_id = messenger.id_messenger
And it shows in stat only status of user of which i retrieve contact list. Please help me, im tired of working on this, im sure it is trivial :(
Can someone explain to me why this would be considered "bad"? One thing that pops in my mind is that I really don't need all the columns from all these tables, only specific columns. Would this cause a performance issue when used in a stored proc for a transactional app?
SELECT * FROM CASE_XREF CX, CASE_RENEWAL_XREF CRX, RENEWAL_BATCH RB, PROPOSAL P WHERERB.MKT_SEG = @MKT_SEG AND RB.CORP_ENT_CD = 'oh' AND RB.RENEWAL_DT = '01/01/2008' AND CRX.TRIGGER_TYPE_CD = 'P' AND RB.BATCH_ID = CRX.BATCH_ID AND CRX.CASE_ID = CX.CASE_ID AND CRX.REN_PROSPECT_ID = P.PROSPECT_ID AND CRX.REN_PROP_NUM = P.PROP_NUM AND P.PROP_STATUS <> 'C' AND CX.ACCT_NBR = 123152
So my aim is to display ALL DATA for each of these tables.
Tried the below but doesn't return any rows...
Code SnippetSELECT gd.Quantity, c.Comments, gc.GPositionID, cc.CPPositionID, cd.PositionDate FROM ReconComments AS c INNER JOIN RGCrossRef AS gc INNER JOIN RGData AS gd ON gc.GPositionID = gd.PositionID ON c.GPositionID = gc.GPositionID INNER JOIN RCPData AS cd INNER JOIN RCPCrossRef AS cc ON cd.UniquePositionID = cc.CPPositionID ON c.CPPositionID = cc.CPPositionID WHERE gc.ForcedMatch = 'yes' AND cc.ForcedMatch = 'yes'
I have a 'charges' table that records charges for an invoice. There are several different types of charges, each with its own unique set of additional data fields that need to be recorded.
I maintain separate tables for each charge type and these tables participate in an "ISA" relationship with the main charges table.
Here is a simplified version of my schema. Hourly charges are one type of charge:
charges table ============= id int (autoincremented primary key) date datetime amount money
hourly_charges table ==================== charge_id int (primary key, also a foreign key to charges table) start_time datetime end_time datetime
I need to write a query that will duplicate all charges meeting a certain criteria by inserting new records into both the charges table and the hourly_charges table.
Here is some non-working pseudo-code that hopefully will get across what I would like to accomplish:
INSERT INTO charges JOIN hourly_charges ( charges.date, charges.amount, hourly_charges.charge_id, hourly_charges.start_time, hourly_charges.end_time ) SELECT date, amount, SCOPE_IDENTITY(), start_time, end_time FROM charges JOIN hourly_charges ON charges.id = hourly_charges.charge_id WHERE some condition is true
Now I realize this code is invalid and I'll have to go about this an entirely different way but I'm wondering if someone can tell me what the proper way is.
I'm trying to get the number of records from one table where a column matches another column in a 2nd table. I then need the total values of another column that it has selected.
SELECT HOLIDAY_REF].holiday_id, COUNT([BOOKING].booking_status_id) AS record_count COUNT([BOOKING].total_value) AS total_value FROM [HOLIDAY_REF] LEFT OUTER JOIN [BOOKING] ON [HOLIDAY_REF].holiday_id = [BOOKING].booking_status_id WHERE [BOOKING].holiday_id=[HOLIDAY_REF].holiday_id && booking_status_id = '330'
Hi! I want to get some fields from more than one table. How can I use select command to do this? Please help me! The results should be in one table only! Thanks in advance!
Hi,I have two tables: Code and Color.The create command for them is :create table Color(Partnum varchar(10),Eng_Color char(10),Span_Color char(20),Frch_Color char(20),CONSTRAINT pkPartnum PRIMARY KEY(Partnum))create table Code(Partnum varchar(10),Barcode varchar(11),I2of5s varchar(13),I2of5m varchar(13),UPC varchar(11),BigboxBCode varchar(11),DrumBCode varchar(11),TrayBCode varchar(11),QtyBCode varchar(11),CONSTRAINT fkPartnum FOREIGN KEY(Partnum) references Color(Partnum))Now my question is,how can i give a select statement such that I can get all the fields asoutput.Also plz note that the above is a sample. I have another 9 tables and Ineed a solutionsuch that on being refered by Partnum, I can get all the attributes.Thanks
Hi, I have a number of related tables: RGData is related to RGCrossReference RCPPositionData is related to RCPCrossReference RGCrossReference is also related to RCPCrossReference. The data is returned correctly from these tables. However, I also want to return data from another table - RComments. How do I do this? RComments is related to either RGData or RCPPositionData only. Thanks.
Code Snippet SELECT cm.CommentImage AS ViewComment, gd.PositionID AS GPositionID, cd.UniquePositionID AS CPPositionID FROM RGData gd INNER JOIN RGCrossReference g ON g.GPositionID = gd.PositionID INNER JOIN RCPCrossReference c ON c.GMatchID = g.GMatchID INNER JOIN RCPPositionData cd ON cd.UniquePositionID = c.CPPositionID left outer JOIN RComments cm ON ((cm.CPPositionID = cd.UniquePositionID) or (cm.GPositionID = gd.PositionID)) AND cm.CommentsDate = (SELECT MAX(CommentsDate) AS Expr1 FROM RComments WHERE (GPositionID = g.GPositionID)) WHERE (cd.Quantity != gd.Quantity OR cd.Currency != gd.Currency) AND g.ForcedMatch = 'no';
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
What's the best way to go about inserting data from several tables that all contain the same type of data I want to store (employeeID, employerID, date.. etc) into a temp table based on a select query that filters each table's data?
In a Library Management database we have these tables
1) Document ( DocNo , Doc_type , permalink,inDate) 2)Title(id, DocNo,Main_Title, Other_Title) 3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....) 4)Publisher(id,DocNo , Name,Publisedate,address) 5)Subject(id,DocNo,Subject) 6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc
In document table I have 500,000 records.
I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?
I have a MS Access database (mdb) containing the following tables:
Crime
Criminal
CrimeCommitted
Hideout
CriminalType
The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:
Code Snippet
SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;
This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).
The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
