Hi,
I'm looking for tips, advice, best practice etc. on optimising a DB with over 300,000 user records to be accessed rapidly via a web interface. Any help would be greatly appreciated - specifically i'm looking at the different methods of DB optimisation indexing, clustering etc.
Hi ,
We are using a stored procedure which processes more than 11 million records .
The Time that the Stored Procedure takes to execute is around 15 to 20 days .
This is bad . We are not using any cursors , But Delete , Insert & Update Statements . There is some complex where clause also while performing deletes and updates .
Our job is to fine tune the SP . We run into problems like transaction log fillups , Tempdb full etc... U can imagine the problems when u look at the record count ..
Indexes donot help .
Can anybody recomend ways to fine tune the proc.
One More thing we do cross database updates ,inserts ,& deletes (I mean 2 databases in same server).
Bye
NAvin
Hi ,
We are using a stored procedure which processes more than 11 million records .
The Time that the Stored Procedure takes to execute is around 15 to 20 days .
This is bad . We are not using any cursors , But Delete , Insert & Update Statements . There is some complex where clause also while performing deletes and updates .
Our job is to fine tune the SP . We run into problems like transaction log fillups , Tempdb full etc... U can imagine the problems when u look at the record count ..
Indexes donot help .
Can anybody recomend ways to fine tune the proc.
One More thing we do cross database updates ,inserts ,& deletes (I mean 2 databases in same server).
Bye
NAvin
Hi all,
I have 20 SQL jobs thst are scheduled to run from say every 5mins to others that run every hour.
Does anyone know the best way to optimise these jobs to run.
At the moment once these jobs are running I cannot browse any tables in teh DB. I get a locked timeout rquest exceeded..
Do I need to stagger when the jobs run.
Or make one big job where they all run one after another ?
Any help ?
Ray..
hi all,
View 3 Replies View RelatedHi there
Recently our company purchased a product from ip2location.com; a database containing 2.9million IP address ranges, and their approximate cities/countries of registration.
Naturally, I thought - "Hey, wouldn't it be great if we could cross reference this with our IIS logs so we could see where our visitors are from?".
So, I set about doing just that. Our IIS logs are already in SQL.
The trouble is, the ip2location database is so large that executing a query against it to find which range a particular IP address is within takes me 1 second. Multiply that by 1,000,000 log rows, and Houston - we have a problem.
One of the issues is that each record in the ip2location database comprises a FROM_IP and TO_IP range to describe a range of IPs. So to find which IP range a particular IP resides in, I have to join using a BETWEEN statement (or so, I think anyway!).
Does anyone have any suggestions on how to improve this process, or has anyone done anything similar before?
Ideally, I'd like to write a trigger to grab the IP region data (i.e. City/Country) and update the IISLog with that value when the new row is inserted, saving me having to do it later.
I tried this, and the batch import of IIS logs into SQL took so long I got bored and gave up :)
Any help anyone can offer would be appreciated.
Many thanks
Richard.
P.S. Somebody is bound to ask - "Why couldn't you just use Google Analytics?"; my answer is because we want to slice up our log data into chunks, and give it to our customers in semi-real time. Plus the logs report on other services - not just HTTP. ;)
Hi,I have a problem I would really appreciate help with. I am generatingdynamic SQL and need to optimise it. The specific example I am trying tooptimise looks like this:SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2WHERE T1.DataHeaderID = CT2.DataHeaderID AND (EntityFieldID IN ( 34)AND (Data LIKE 'SIDE BY SIDE%' )) ))AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CCT3WHERE T1.DataHeaderID = CCT3.DataHeaderID AND (( Data LIKE 'church%' ))))I was OK optimising it with just 2 criteria and changed:SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2WHERE T1.DataHeaderID = CT2.DataHeaderID AND (( Data LIKE 'church%' ))))which took 26 seconds to using a derived tableSELECT distinct T1.DataHeaderID FROM TB_DataDetailText as T1inner join (SELECT distinct DataHeaderID, Data FROM TB_DataDetailText )CT2on T1.DataHeaderID = CT2.DataHeaderIDWHERE(T1.EntityFieldID IN ( 31) AND (T1.Data LIKE '12BORE%' ))and (( CT2.Data LIKE 'church%' )) which took 0.03 seconds on the same data.My problem is I need to write code to generate the SQL for 1 to n criteriaand am struggling to write the query for more than 2Best regards,Andrew
View 3 Replies View RelatedHi.Maybe I'm just being dim, but I'm struggling to get my head aroundoptimising a query with regard to indexes. If I make a select query, suchas a pseudo-example 'select * from bigtable where foo='bar' and(barney>rubble and fred<flintoff)', and the table is indexed on 'foo', howcould I make that any better? What indexes could I add, or what could Ichange in the query?I know it looks simple, but so am I.CheersChris Weston
View 5 Replies View RelatedDear All, Plz help me in optimising the following query,Reduce repeatable reads from the table via select ,ythe table sare nothaving referntial integrity constarints ,relationsCREATE proc Rolex136SyncasDECLARE @date varchar(50),@ydate varchar(50)print CONVERT(char(11),(GETDATE()-1),100)SET @date =substring(CONVERT(char(11),(GETDATE()),100),5,2)+' -'+substring(CONVERT(char(11),(GETDATE()),100),1,3) +'-'+substring(CONVERT(char(11),(GETDATE()),100),8,4) SET @ydate =substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),8,4)Print @datePrint @ydateinsert intobiiod.dbo.data_trans_currentday_test(MobileNo,UA,M essageID,ContentID,Description,MusicLabel,CPID,CPN ame,ContentType,Category,SubCategory,TransactionDa te,Units,Unitprice,Shortcode,Servicecode,OperatorI D,CatID,SubCatID,SpecialPackage,Royalties,Operator,Circle,OPGPName)(select mobileno,(SELECT CASE uawhen 'unknown' then nullelse uaend) as ua,(select case remarkswhen 'unknown' then nullelse remarksend) as remarks,contentid,(select case descriptionwhen 'unknown' then nullelse descriptionend) as description,(select musiclabel from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as musiclable,(select cpid from datalogs.dbo.contentprovider where cpname =datalogs.dbo.translogs.cpname) as cpid,cpname,contenttype,(select catname from datalogs.dbo.cont_Catg where catid in (selectcatid from cont_master where contentid =datalogs.dbo.translogs.contentid)) as category,(select subcatname from datalogs.dbo.cont_subCatg where subcatid in(select subcatid from cont_master where contentid =datalogs.dbo.translogs.contentid)) as subcategory,transactiondate,1 as Units, price,(select case servicenamewhen 'AIRTELIVE' then remarkswhen 'ALCOMBOPACKREG' then remarkswhen 'HINDI' then remarkswhen 'NOKIAGAL' then remarkswhen 'SUDOKU' then remarkswhen 'SUDOKU_APP' then remarkselse NULLend) as SHORTCODE,servicename,(select case servicenamewhen 'TSTTNEWS' THEN 600when 'TSTTWAP' THEN 600when 'TSTT_MMS' THEN 600when 'AKTEL' THEN 300when 'TELEMOVIL' THEN 700when 'COMCEL' THEN 701when 'QATAR2900' THEN 1ELSE(select operatorid from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as operatorid,(select catid from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as catid,(select subcatid from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as subcatid,(select specialpackage from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as specialpackage,(select Royalties from datalogs.dbo.cont_master where contentid =datalogs.dbo.translogs.contentid) as Royalties,(select case servicenamewhen 'AKTEL' then 'Aktel'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'TeleMovil'when 'COMCEL' THEN 'COMCEL'when 'TSTTNEWS' then 'TSTT'when 'TSTTWAP' then 'TSTT'when 'TSTT_MMS' then 'TSTT'when 'ALCLICKWIN6464' then 'Airtel'when 'ALMMSPORTAL' then 'Airtel'when 'ALMMSSMSDWN' then 'Airtel'when 'ALMYALBUM646' then 'Airtel'when 'HINDU6397' thensubstring(remarks,1,PATINDEX('%.6397.%',remarks)-1)else(select OPname from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as Operator,(select case servicenamewhen 'AKTEL' then 'Bangladesh'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'El Salvador'when 'COMCEL' THEN 'Gautemala'when 'TSTTNEWS' then 'Trinidad'when 'TSTTWAP' then 'Trinidad'when 'TSTT_MMS' then 'Trinidad'when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +6,len(remarks)-PATINDEX('%-%',remarks))else(select Circlename from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as Circle,(select case servicenamewhen 'AKTEL' then 'Aktel'when 'QATAR2900' then 'STAR MULTIMEDIA 2900'when 'TELEMOVIL' then 'TeleMovil'when 'COMCEL' THEN 'COMCEL'when 'TSTTNEWS' then 'TSTT'when 'TSTTWAP' then 'TSTT'when 'TSTT_MMS' then 'TSTT MMS'when 'ALCLICKWIN6464' then 'Airtel Click Win 646'when 'ALMMSPORTAL' then 'Airtel MMS'when 'ALMMSSMSDWN' then 'Airtel MMS SMS'when 'ALMYALBUM646' then 'Airtel My Album'when 'HINDU6397' then 'Hindu 6397'else(select OPname from datalogs.dbo.operator where phoneseries =substring(datalogs.dbo.translogs.mobileno,1,len(ph oneseries)))end) as OPGPNamefrom datalogs.dbo.translogs where transactiondate >= @ydate andtransactiondate < @date and servicename in('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV', 'QATAR2900','SIFY'))go
View 2 Replies View RelatedHi all
I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address.
Simple change to the stored procedure (this is just an example not the real stored proc):
From:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’
To:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’
Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock.
Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on:
strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode.
So I created an index just for the strPostCode (non clustered).
This had no change to the ‘Like select’ what so ever. So I am now stuck.
1)Is there another way to search for part of a text field in SQL.
2)Does ‘Like’ comparison use the index in any way? If so how do I set this index up?
3)Can I stop a ‘Shared Lock’ being created when I do a ‘like select’?
4)Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me).
Any advice truly appreciated.
I have an application that reads a monitoring devices that produces 200 digital outputs every second and I would like to store them in a table. This table would get quite big fairly quickly as ultimately I would like to monitor over a hundred of these devices.
I would like to construct queries against each of the individual digital channels or combinations of them.
M first thought is to set up a table with 200 separate columns (plus others for date stamp, device ID etc) however, I am concerned that a table with 200 boolean (1-bit) fields would be an enormous waste of space if each field takes maybe one to four bytes on the hard disk to store a single bit. However, this would have the advantage of make the SQL queries more natural.
The other alternative is to create a single 200 bit field and use lots of ANDing and ORing to isolate bits to do my queries. This would make my SQL code less readable and may also cause nore hassle in the future if the inputs changed, but it would make the file size smaller.
In essence I am asking (hoping) the following : If I create a table with 200 boolean fields, does SQL server express automatically optimise the storage to make it more compact? This means that the server can mess around at the bit level and leave my higher level SQL code looking cleaner and more logical.
I need to merge replicate data to two different types of subscribers:
Clients subscribers which will have a very small percentage of the data from the central database. The data on these machines will be managed using dynamic filtering on host_name()
Server subscribers which will manage a copy of all the data from the central database
There will be far fewer server subscribers than client subscribers.
As I see it I have two options for the configuration
1) Use two separate merge publications €“ one which is filtered and one which isn€™t
2) Use a single merge publication and setup the filtering so that the server subscribers receive all the rows
Which option is likely to lead to better performance?
With option 1) there would be 2 complete sets of replication metadata which need to be maintained €“ so I am tending towards option 2. Are there any disadvantages in using a dynamic filter to return a very large number of rows?
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
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
View 2 Replies View RelatedI have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.
;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
Thanks in advance.
I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.
View 1 Replies View RelatedHi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.
then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.
The table name is Adm_disc_Match_tbl
Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)
example of data
Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17
If anyone could help it would be much appreciated.
Hi,
I have to search the records after the records populated.
I mean to say, i have displayed records in report, if i enter some strings in the textbox and clicked find, then it will highlight the particular records, instead of highlighting the values, is it possible to display only those particular records.
For example, say i have 50 records in a page,i entered some strings in the textbox and clicked find, then it will highlight the particular 5 records one by one which match the criteria i have entered in the texbox, instead of that i have to display only those 5 records.
Please tell me how to implement in this report,
Thanks and Regards
Altaf Nizamuddin
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies View RelatedTable1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).Â
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
I'm at a bit of a loss here. My T-SQL skills are not up to the task at hand here :(
I've got company records in one table and SIC codes that correlate to the companies linked by the company ID. So, I can run the query, but the output I get is multiple records for each company, because some companies have multiple SIC codes associated with them.
I understand how to get only one record, but what I want to do is create a result set that has all the SIC codes associated with one company. Possibly in a comma seperated list, that would count as one field.
Anyone have any idea how to bring back all the SIC codes for one company as one variable (or multiple variables, but in one record)?
Thank you very much for any help,
Mischa
I cannot figure out how to write a sql statement to display the multiple records of multiple records. I know that doesn't make much sense so let me explain:
This problem involves two tables. They both reference (FK) data in a third table (tblDepartment).
Here are the fields and some test data in each of the two important tables:
=============
tblDepartment =============================
departmentIDuserID
-----------------------------
1user1
2user1
1user2
2user3
3user3
3user4
===========
tblRequest ========================================
requestIDdepartmentIDuserID
----------------------------------------
11user1
21user2
32user3
43user3
53user4
62user1
What I am trying to do is display all requests associated with the multiple departments of "user1". In the above example, I would want to see (after a select statement):
requestIDdepartmentIDuserID
----------------------------------------
11user1
21user2
32user3
52user1
We don't see the requests of user3 or user4 when the given request's departmentID is not a department associated with user1.
Hopefully someone out there understands this. I've never had to write a select statement like this before, so I'm having trouble wrapping my mind around it. Thanks for any help!
Hi,
I have one table which holds an ID, a name, and a parentID (which can either the same as the main ID or it's one of the other ID in that tbl)
At the moment I do a select on the records whos main ID is the same as the ParentID. This gives me an initial list of records who are the "Main" parent records (i.e they are not the child of any other record..
so far so good.
However, what I need to do is for each record work out if they themselves have any Child records. (only some have).
so at the mo I end up with a results set as this:
1 Boats
2 Jumpers
3 Trousers
4 Ships
What I want to end up is something like
1 Boats True
2 Jumpers False
3 Trousers True
4 Ships True
Where the true or false is if the record has any child records..
Any ideas?
A basic select statement to be used.
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
View 2 Replies View RelatedHi,
I already submitted this type of question before and i receive reply. But unfortunately i found out errors when performing on my system.
My problem regarding to this one:
Suppose i have two databases with same tables with different records and I would like to copy the records from one database to another data and vice-versa. So that both the tables contains same number of records inside the tables.
Example:
Database1 (EmployeeTable) contains 6 records.
Database2 (EmployeeTable) contains 10 records.
It should copy only those records which is not present in each other database. No duplicate records.
Before i was recommend to use Primary key, if it is not present use index.
Hope this time i could solve my problem.
Thanks.
Kashif Chotu