hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..
now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...
so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..
so now every user must have a list of other userid's.. as contact list..
Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
THANK YOU !
if it helps..iam using sql server express edition..and iam accessing using asp.net/C#
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
was hoping someone couild provide some insight into a problem I'm trying to solve.
I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:
CRITERIAID CRITERIA CRITERIAVALUE 1 AGE 2 2 SEX 4 3 GRADE 8 4 LOCALE 16 5 REGION 32
A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.
Taking the Northwind database as an example,I have an order table: 'Orders'a order details table: 'Order Details'and a products table: 'Products'For reasons best ignored, I want to produce a query which has columns:Orders.OrderID, Productswhich has results like:10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella diGiovanni'10249, 'Tofu, Manjimup Dried Apples'________so for those who don't really know what I'm on about and haven't gotaccess to northwind, I want the second cell to contain the returnedcolumn of a second query (but in text format)could anyone suggest a way this could be done? am I entering the landof cursors?Many thanks,Neil
I have a SQL query that goes like this "select * from Product where ProductID in (1,2,3)" How can i create a stored procedure where a single input parameter can take multiple values? Can anyone help me with this?
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames 887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID, dbo.tblAttributes.AttributeName FROM dbo.tblProgramAttributes INNER JOIN dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%') ORDER BY dbo.tblProgramAttributes.ProgramID DESC
ALTER PROC [dbo].[sp_GetInvitationStatusTest] ( @invited_by NVARCHAR (50) ) AS BEGIN -- SELECT * FROM dbo.Merck_Acronym_Invitations WHERE invited_by=@invited_by select distinct t1.invited_isid as 'ISID', t1.invited_name as 'NAME',t1.invitation_status as 'STATUS',
[Code] ....
If you look at the where clause i have invited by , i get the desired output if i just provide 1 name in the execution such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj' But my requirement is to make the procedure work with more than one input variable such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj','sujith' now i should get the output for 2 people but if i run this i receive the following error Procedure or function sp_GetInvitationStatusTest has too many arguments specified.
how to make my procedure work with more than 1 input variable?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888
And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
This provides a good example for my situation. In this example, you will see a Movie dimension with four attributes; Genre, Language, Movie, and Theme. I have a similar setup except mine is Top Level Hierarchy>Categories>Values which are all under the one hierarchy.
My Question: I have the dimension setup as a multi-value parameter in one of my reports. When I filter on a value in Genre and in Language, it provides all values from that genre and all values from that language. I really only want the values that include both.
If I filter on Western and English, I get Movie1-5 when all I really want is Movie2 only. Is there any way to have this do an Intersect within the same dimension or do I have to build each one out into its own dimension?
I have a field called "Owners", and it's a child to an "Activities" table.
An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners.
I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height.
Let's say you had a User table and one of the fields was called Deceased. It's a simple closed-ended question, so a bit value could be used to satisfy the field, if the person is dead or alive. Let's say another field is called EyeColor. A person can have only one eye color and thus one answer should be stored in this value, so this is easy as well. Now, let's say I want to store all the languages that a specific user can speak. This isn't as easy as the previous examples since it's not a yes or no or a single-value answer. I haven't had much experience with working with databases so I've come up with two possible ways with my crude knowledge hehe. In terms of inputting the multi-answer values, I suppose I could use a multiple-selection listbox, cascading dropdowns, etc. Now, here are the 2 solutions that came to mind..... 1) Make a field called LanguagesSpoken in the User table. When I process the selections the user makes on the languages he knows, I can then insert into the LanguagesSpoken field a string "English, Spanish, Czech" or IDs corresponding to the languages like "1, 5, 12" (these IDs would be referenced from a separate table I guess). I would use commas so that later on, when I need to display a user's profile and show the user's languages, I can retrieve that long string from the LanguagesSpoken field, and parse the languages with the commas I've used. Using commas would just be a convention I use so I would know how to parse (I could have used "." or "|" or anything else I guess) the data. 2) Forget about the LanguagesSpoken field in the User table altogether, and just make a LanguagesSpoken table. A simple implementation would have 3 fields (primary key, userId, languageId). A row would associate a user with a language. So I would issue a query like "SELECT * FROM LanguagesSpoken WHERE userId=5" (where userId=5 is some user). Using this method would free me from having to store a string with delimited values into the User table and then to parse data when I need them. However, I'm not sure how efficient this method would be if the LanguagesSpoken table grows really large since the userIds would NOT be contiguous, the search might take a long time. I guess I would index the userId field in the LanguagesSpoken table for quicker access? OR, I may be going about this the wrong way and I'm way out on left field with these 2 solutions. Is there a better way other than those 2 methods? I haven't work extensively with databases and I'm just familiar with the basics. I'm just trying to find out the best-practice implementation for this type of situation. I'm sure in the real world, situations like this is very common and I wonder how the professionals code this. Thanks in advance.
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |... Orders -> id | idCustomer | date | ... Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total FROM Products INNER JOIN Orders ON Orders.id = Products.idOrder INNER JOIN Customers ON Customers.id = Orders.idCustomer WHERE year(date)=2014 GROUP BY customers.name ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015) -------- customer A | 1000$ | 2000$ | 3000$ customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
If I have a Select statement like this in my C# code: Select * From foods Where foodgroup In (@foodgroup) And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter? I tried meat, dairy, fruit 'meat', 'dairy', 'fruit' but neither worked. Is this possible?
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
numbers 2 4 2Above is an example of my table with a single column. My problem is how to add all the numbers in that column to make it 8? The rows are also dynamic. Your help is highly appreciated.
I have a table that has multiple postal codes in one of the columns. Those have to be split up one per line and stored in another table. The zip codes are comma seperated. Is there a function that can do this...?
Example data in ZipCodeTable. (Name and ZipCode are 2 columns in a table)
I have a query set that returns values as part of a data set, I need a new one to return values from two queries to a single row.
select '1' as thekey, 'Total Picks' as Tot,sum(prod_qty) as picks from exceed.aseld, exceed.csymh where luis_id in ('I','E') and aseld.whse_id = 1 and ( (aseld.batch_id between goal_beg_batch and goal_end_batch and monitor_group = 'YK')
[code]....
is it possible to get the numbers from keys 1 & 2 on the same row in a new query?
Or if it is easier a query that with give me (completed picks/total picks) = a decimal I can feed to the display as a percentage.
Can somebody please tell me whether the following syntax is supportedor whether it's a "feature" that will someday stop working. It works inboth SQL Server 2000 and 2005 at the moment.declare @var varchar(1000)set @var = ''select @var = @var + colx from some_table where col1 = some_valuecolx is a varchar or at least is cast to one as part of the selectstatement. If the where clause would normally return more than one row,all returned values for colx are concatenated into @var.I've not seen this syntax before but that doesn't make it wrong ;-)Malc.
greetings,i was wondering is it better to have multiple small stored procedures or one large store procedure ???? example : 100 parameters that needs to be inserted or delete ..into/from a table.............is it better to break it up into multiple small store proc or have 1 large store proc....thanks...............
I'm doing a BCP of a large table 37 million rows. On a single CPU server, SQL 7, sp 3, with 512 meg of RAM, this job runs in about 3 hours. On a 8 way server with 4 Gig of RAM, SQL 7 Enterprise, this job runs 12 hours and is only a third done. The single CPU machine is running one RAID 5 set while the 8 way server is running 4 RAID 5 sets with the database spread out over two of them.
Is there something obvious that a single CPU box would run this much faster?
I'm a real novice user and I we use SQL Server. I have a table called TableLog that has the 4 columns. The first column is repeated x number of times for each value in the second column. I'd like to see this information put into a matrix where I could tell if there are any missing intersections. In addition, I need to insert a WHERE clause that says "BETWEEN 20090000 AND 20100000" for the first and second columns.
