Hi.
Maybe I'm just being dim, but I'm struggling to get my head around
optimising a query with regard to indexes. If I make a select query, such
as a pseudo-example 'select * from bigtable where foo='bar' and
(barney>rubble and fred<flintoff)', and the table is indexed on 'foo', how
could I make that any better? What indexes could I add, or what could I
change in the query?
I know it looks simple, but so am I.
Cheers
Chris Weston
Hi 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 ,
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,
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 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,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 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?
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedHi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
Regards,
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
View 4 Replies View Related how can i execute for example
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code
View 3 Replies View RelatedHi All,
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Regards,
Rich
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
View 1 Replies View RelatedCould anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please
View 1 Replies View RelatedI just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
Thank you all very much in advance.
Hi!
Where to write MDX Queries and how to call the query in Analysis Services.
Please help me on this.
I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...
Any ideas? Thanks.
Hi and thanks for reading.
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk,
eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate,
l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription
FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk
LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk
LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID
LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows.
But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question).
Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (".
But, I hate using two queries and just wonder if there's a better way around this.
Appreciate the help, and thanks for reading.
-colin
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
Thanks.
It is given the following schema of relations:
Donators(Id <<PK>>, Name, Surname, IdCity <<FK(City)>>, Group, Sex, Age);
Files(IdFile <<PK>>, Donator <<FK(Donators)>>, Date, Type);
City(IdCity <<PK>>, Name, State);
I need to find a query for each of the following:
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution:
SELECT d.Name, d.Surname
FROM Donators d, Filles f
WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age)
FROM Donators d, Files f
WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id
FROM Donators d, Files f, City c
WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity
FROM City c
WHERE NOT IN (SELECT c.idcity
FROM City c, Donators d, Files f
WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Thanks
Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:
cust_id int
dateon DATETIME
dateoff DATETIME
There are two possibilites
1) Its no longer on hold and so a dateon and dateoff value will be in the table.
2) Its still on hold and so dateoff will be NULL.
I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802
SELECT Hon,Hoff FROM
(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate()))
FROM dbo.tblonenet_sdt_proj_hold
WHERE cust_id='802' AND dateoff IS NULL) AS HON
[code]...
The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'
I'm confused.
I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.
I have inputted data to my database. The user should be able to do searches such as:
rides (and shops or all) available in a certain area.
rides in a certain area above a certain height rescrictions.
etc
I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.
I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...
and what about triggers and user defined statements??
PLEASE help, I have a deadline in a week : (
THANK YOU
how do i make a querie that finds relations to a "part number" for models, and assemblies, but when it finds a relation of part number with a Product, it gives additional attributes for information?
this code that i have so far doesn't do waht i want,, showing
part_number
2 2 1 1 Kilimanjaro
2 2 2 2 Dolomiti
2 2 11 11 Mud Zinger 1
and jumbling up information
select part_inventory.part_number, assemble.assemble_number, model.model_number, product.serial_number, model.model_name
from part_inventory inner join model_configuration on part_inventory.part_number = model_configuration.part_number
inner join model on model.model_number = model_configuration.model_number
inner join assembly_configuration on part_inventory.part_number = assembly_configuration.part_number
inner join assemble on assemble.assemble_number = assembly_configuration.assembly_number
inner join product on product.serial_number = model.serial_number
where part_inventory.part_number = 2;
Label(LabelID,Label) labelid=pk
-------------------------------
Shelf(ShelfID,Shelf) shelfid=pk
-------------------------------
Country(CountryID,Country) countryid=pk
---------------------------------------
Track(TrackID,Track) trackid=pk
-------------------------------
Artist(ArtistID,Artist,ArtistPhoto,CountryID) artistid=pk,countryid=fk country(countryid)
-----------------------------------------------------------------------------------------
CD(CDID,CDTitle,CDDate,CDCover,LabelID,ShelfID) cdid=pk, labelid=fk label(labelid),shelfid=fk shelf(shelfid)
------------------------------------------------------------------------------------------------------------
CDTrack(TrackID,CDID) trackid,cdid=pk, trackid=fk track(trackid), cdid=fk CD(cdid)
----------------------------------------------------------------------------------
ArtistTrack(ArtistID,TrackID) artistid,trackid=pk, artistid=fk artist(artistid),trackid=fk track(trackid)
---------------------------------------------------------------------------------------------------------
I've already created these tables but i need some aid writing these queries coz i am still a beginner in sql
Search Queries
------------------------------------------------------------
Input Artist
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 1
Group ByCDSection
------------------------------------------------------------
InputCDTitle
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 2
Group ByCDSection
------------------------------------------------------------
InputTrack
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 3
Group ByCDSection
------------------------------------------------------------
InputLabel
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 4
Group ByCDSection
------------------------------------------------------------
Ashish Amodia writes "how to create tables in ms-sql?
how different opration can be perform in database table, like insert, update, delete with tables.
how to create store procedure?"
Iam developing a randomizing program .which selects random rows frm the database.iam using sql as backend and asp.net as front end.
i allow user to give a number say 1000, so 1000 random rows will be slected frm the database.iam using NEWID() for random selection.
But how do i put the user value into query. so as to select that many random rows from the databse.
please help me out on this
i hav this problem .i hav one column in my table which contains records which can be integers or string.
i allow user in my .aspx page to enter integers in three text boxes.
and depending on those integers say 1,2,3 ... i hav to select those rows from my table.
where the problem is that i hav to selct the row which is say row no.1 which can contain string or integer and iam not getting the slected result instead some integers .
the query iam using is
SELECT " & range1 & " UserID FROM TAble
where range1 is the integer that user puts into textbox .i hav only one cloumn(UserID) in my table which can contain integer or string so i hav to select the row which is what the user has entered but contains string or integer(which is not what user has entered).