Im creating a project tasklist application. I have project, section, task, issue classes, and would like to use one class to be able to add simple notes to any object instance of those classes.
The task, issue tables both use a standard identity field as a primary key.
The section table has a two field primary key.
The project table has a single int primary key defined by the user.
Is there a way to associate the note class with each of these without using a seperate lookup table for each class?
I'm not so sure my original idea is a decent way to implement this. I considered the following (each variable mapping to a field n the notes table.
Private _NoteId As Integer
Private _ProjectId As Integer
Private _SectionId As Integer
Private _SectionId2 As Integer
Private _TaskId As Integer
Private _IssueId As Integer
Private _Note As String
Private _UserId As Guid
Then I would be able to write seperate methods (getProjectNotes, getTaskNotes) to get notes attached to each class.
I started writing this a few weeks ago but got pulled away before i could finish. When revisiting this code today my first thought "this is retarded". Thoughts on drawbacks to this design?
i have a database that already has a users table
COLUMNS:
userID - int
loginName - string
First - string
Last - string
i just installed the asp.net membership table. Right now all of my tables are joined into my users table foreign keyed into the "userId" field
How do i integrate asp.net_users table into my schema? here are the ideas i thought of:
Add a membership_id field to my users table and on new inserts, include that new field in my users table. This seems like the cleanest way as i dont need to break any existing relationships.
break all existing relationship and move all of the fields in my user table into the asp.net_users table. This seems like a pain but ultimately will lead to the most simple, normalized solution
any thoughts?
One day, wordpress suddenly jumped from pots id 9110 to 890000000 post.
Days later I'd like to move back new posts to continue from id 9111.
I'm sure that id will never reach id 890000000, no problem here, but id is an autoincrement field and "ALTER TABLE wp8_posts AUTO_INCREMENT =9111" is not working.
Can I force id to continue from 9111 ?
Hi All:
I want know is there good way to detect Column DataType for Date field (NOT DateTime)?
This what currently I do:
switch (dt.Columns[col].DataType.FullName)
{
case "System.DateTime":
formatedVal = Formatter.GetDateTime(val);
break;
// which is NOT possible, but something equivalent am looking for
case "System.Date":
formatedVal = Formatter.GetDate(val);
break;
default:
formatedVal = val.ToString();
break;
}
Thanks a bunch. :-)
I am building a database similar to the one described here where I have products of different type, each type with its own attributes.
I report a short version for convenience
product_type
============
product_type_id INT
product_type_name VARCHAR
product
=======
product_id INT
product_name VARCHAR
product_type_id INT -> Foreign key to product_type.product_type_id
... (common attributes to all product)
magazine
========
magazine_id INT
title VARCHAR
product_id INT -> Foreign key to product.product_id
... (magazine-specific attributes)
web_site
========
web_site_id INT
name VARCHAR
product_id INT -> Foreign key to product.product_id
... (web-site specific attributes)
This way I do not need to make a huge table with a column for each attribute of different product types (most of which will then be NULL)
How do I SELECT a product by product.product_id and see all its attributes?
Do I have to make a query first to know what type of product I am dealing with and then, through some logic, make another query to JOIN the right tables? Or is there a way to join everything together? (if, when I retrieve the information about a product_id there are a lot of NULL, it would be fine at this point).
Thank you
Hello there
I've a table with about 130 000 records with telephonenumbers. The numbers are all formated like this +4311234567. The numbers always include international country code, local area code and then the phonenumber and sometimes an extension.
There is a webservice which checks for the caller's number in the table. That service works already. But now the client wants that also if someone calls from a company which number is already in the database but not his extension, that the service will return some result.
Example for table.
**id** | **telephonenumber** | **name**
| 1 | +431234567 | company A
| 2 | +431234567890 | employee in company A
| 3 | +4398765432 | company b
now if somebody from company A calls with a different extension for example +43123456777, than it should return id1. But the problem is, that I don't know how many digits the extensions have. It could have 3,4 or more digits.
Are there any patterns for string kind of matchings?
The data is stored in a sql2005 database.
Thanks
Setup:
mysql> create table t(a integer unsigned,b integer unsigned);
mysql> insert into t(a,b) values (1,2),(1,3),(2,4);
mysql> create index i_t_a on t(a);
mysql> create index i_t_b on t(b);
mysql> explain select * from t where a=1 or b=4;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
Is there something I'm missing?
Update
mysql> explain select * from t where a=1 or b=4;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | i_t_a,i_t_b | NULL | NULL | NULL | 1863 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
Version:
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.36-community-log |
+----------------------+
Has anyone ever successfully make index merge work for MySQL?
I'll be glad to see successful stories here:)
I asked a question yesterday about password safety...
I am new at security...
I am using a mysql db, and need to store users passwords there. I have been told in answers that hashing and THEN saving the HASHED value of the password is the correct way of doing this.
So basically I want to verify with you guys this is correct now.
It is a classifieds website, and for each classified the user puts, he has to enter a password so that he/she can remove the classified using that password later on (when product is sold for example).
In a file called "put_ad.php" I use the $_POST method to fetch the pass from a form.
Then I hash it and put it into a mysql table.
Then whenever the users wants to delete the ad, I check the entered password by hashing it and comparing the hashed value of the entered passw against the hashed value in the mysql db, right?
BUT, what if I as an admin want to delete a classified, is there a method to "Unhash" the password easily?
sha1 is used currently btw.
some code is very much appreciated.
Thanks
I have a table of 2 tables in a one to many relationship. I want to run an update script that will update the table with the FK of the related table only if there is one result (because if there is multiple then we need to decide which one to use, in another method)
Here is what I have so far:
UPDATE import_hourly_event_reports i
SET i.banner_id = b.banner_id
FROM banner b
JOIN plan p ON b.plan_id = p.id
WHERE b.campain_id = i.campaign_id
AND b.size_id = i.size_id
AND p.site_id = i.site_id
HAVING COUNT(b.banner_id) = 1
As you can see, the HAVING clause doesn't quite work as I'd expect it. I only want to update the row in the import table with the id of the banner from the banner table if the count is equal to 1.
Hey all
i have 3 tables each as follow
cash_credit
Bank_Name-------in_date-------Com_Id---Amount
America Bank 15/05/2010 1 200
HSBC 17/05/2010 3 500
Cheque_credit
Bank_Name-----Cheque_Number-----in_date-------Com_Id---Amount
America Bank 74835435-5435 15/05/2010 2 600
HSBC 41415454-2851 17/05/2010 5 100
Companies
com_id----Com_Name
1 Ebay
2 Google
3 Facebook
4 Amazon
Companies table is a linked table when i tried to create an query as follow
SELECT cash_credit.Amount, Companies.Com_Name, cheque_credit.Amount
FROM cheque_credit INNER JOIN (cash_credit INNER JOIN Companies ON cash_credit.com_id = Companies.com_id) ON cheque_credit.com_id = Companies.com_id;
I get an error saying that my inner Join is incorrectly, this query was created using Access 2007 query design
the error is
Type mismatch in expression
then i thought it might be the inner join so i tried Left Join and i get an error that this method is not used
JOIN expression is not supported
I am confused on where is the problem that is causing all this
How can I do to get the next row in a table?
`image_id` int(11) NOT NULL auto_increment
`image_title` varchar(255) NOT NULL
`image_text` mediumtext NOT NULL
`image_date` datetime NOT NULL
`image_filename` varchar(255) NOT NULL
If the current image is 3 for example and the next one is 7 etc. this won’t work:
$query = mysql_query("SELECT * FROM images WHERE image_id = ".intval($_GET['id']));
echo $_GET['id']+1;
How should I do?
thanks
Why is my delimiter not appearing in the final output? It's initialized to be a comma, but I only get ~5 white spaces between each attribute using:
SELECT [article_id]
, dbo.GROUP_CONCAT(0, t.tag_name, ',') AS col
FROM [AdventureWorks].[dbo].[ARTICLE_TAG_XREF] atx
JOIN [AdventureWorks].[dbo].[TAGS] t ON t.tag_id = atx.tag_id
GROUP BY article_id
The bit for DISTINCT works fine, but it operates within the Accumulate scope...
Output:
article_id | col
-------------------------------------------------
1 | a a b c
I only have rudimentary C# API knowledge...
C# Code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml.Serialization;
using System.Xml;
using System.IO;
using System.Collections;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
public struct GROUP_CONCAT : IBinarySerialize
{
ArrayList list;
string delimiter;
public void Init()
{
list = new ArrayList();
delimiter = ",";
}
public void Accumulate(SqlBoolean isDistinct, SqlString Value, SqlString separator)
{
delimiter = (separator.IsNull) ? "," : separator.Value ;
if (!Value.IsNull)
{
if (isDistinct)
{
if (!list.Contains(Value.Value))
{
list.Add(Value.Value);
}
}
else
{
list.Add(Value.Value);
}
}
}
public void Merge(GROUP_CONCAT Group)
{
list.AddRange(Group.list);
}
public SqlString Terminate()
{
string[] strings = new string[list.Count];
for (int i = 0; i < list.Count; i++)
{
strings[i] = list[i].ToString();
}
return new SqlString(string.Join(delimiter, strings));
}
#region IBinarySerialize Members
public void Read(BinaryReader r)
{
int itemCount = r.ReadInt32();
list = new ArrayList(itemCount);
for (int i = 0; i < itemCount; i++)
{
this.list.Add(r.ReadString());
}
}
public void Write(BinaryWriter w)
{
w.Write(list.Count);
foreach (string s in list)
{
w.Write(s);
}
}
#endregion
}
I have a table recording the amount of data transferred by a given service on a given date. One record is entered daily for a given service.
I'd like to be able to retrieve the most recent amount for a set of services.
Example data set:
serviceId | amount | date
-------------------------------
1 | 8 | 2010-04-12
2 | 11 | 2010-04-12
2 | 14 | 2010-04-11
3 | 9 | 2010-04-11
1 | 6 | 2010-04-10
2 | 5 | 2010-04-10
3 | 22 | 2010-04-10
4 | 17 | 2010-04-19
Desired response (service ids 1,2,3):
serviceId | amount | date
-------------------------------
1 | 8 | 2010-04-12
2 | 11 | 2010-04-12
3 | 9 | 2010-04-11
Desired response (service ids 2, 4):
serviceId | amount | date
-------------------------------
2 | 11 | 2010-04-12
4 | 17 | 2010-04-19
This retrieves the equivalent as running the following once per serviceId:
SELECT serviceId, amount, date
FROM table
WHERE serviceId = <given serviceId>
ORDER BY date DESC
LIMIT 0,1
I understand how I can retrieve the data I want in X queries. I'm interested to see how I can retrieve the same data using either a single query or at the very least less than X queries.
I'm very interested to see what might be the most efficient approach. The table currently contains 28809 records.
I appreciate that there are other questions that cover selecting the most recent set of records. I have examined three such questions but have been unable to apply the solutions to my problem.
Hi,
I have a db with two tables like these below,
page table
pg_id title
1 a
2 b
3 c
4 d
tagged table
tagged_id pg_id
1 1
2 4
I want to select the pages which are tagged, I tried with this query below but doesn't work,
SELECT *
FROM root_pages
LEFT JOIN root_tagged ON ( root_tagged.pg_id = root_pages.pg_id )
WHERE root_pages.pg_id != root_tagged.pg_id
It returns zero - Showing rows 0 - 1 (2 total, Query took 0.0021 sec)
But I want it to return
pg_id title
2 b
3 c
My query must have been wrong?? How can I return the pages which are not tagged correctly?
Thanks.
My table looks like this with duplicates in col1
col1, col2, col3, col4
1, 1, 0, a
1, 2, 1, a
1, 3, 1, a
2, 4, 1, b
3, 5, 0, c
I want to select distinct col1 with max (col3) and min(col2);
so result set will be:
col1, col2, col3, col4
1, 2, 1, a
2, 4, 1, b
3, 5, 0, c
I have a solution but looking for best ideas?
If you do a search for:
http://www.google.co.uk/search?q=0x57414954464F522044454C4159202730303A30303A313527&hl=en&start=30&sa=N
you will see a lot of examples of an attempted hack along the lines of:
1) declare @q varchar(8000) select @q = 0x57414954464F522044454C4159202730303A30303A313527 exec(@q) --
What is exactly is it trying to do? Which db is it trying to work on?
Do you know of any advisories about this?
I need a way to store an int for N columns. Basically what I have is this:
Armies:
ArmyID - UINT
UnitCount1 - UINT
UnitCount2 - UINT
UnitCount3 - UINT
UnitCount4 - UINT
...
I can't possible add a column for each and every unit, so I need a fast way to store the number of each units in an army (you might have guesses it's for a game by now). Using XML is not an option as it will be dead slow.
Does anybody else have this same problem, when you import data from Excel file to MSSQL Server 2005, if some column contains mostly numeric data, but even if you set the column type to varchar, the wizard fails to import those fields that fail to parse as numbers?
I want to use a database which name is stored in a variable. How do I do this?
I first thought this would work but it doesn't:
exec('use '+@db)
That will not change database context
Suggestions anyone?
Hi guys,
I was wondering if anyone ever had a change to measure how a would 100 joined tables perform?
Each table would have an ID column with primary index and all table are 1:1 related.
It is a common problem within many data entry applications where we need to collect 1000+ data points. One solution would be to have one big table with 1000+ columns and the alternative would be to split them into multiple tables and join them when it is necessary.
So perhaps more real question would be how 30 tables (30 columns each) would behave with multitable join.
500K-1M rows should be the expected size of the tables.
Cheers
have a table like this
empid questionid options
1 1 A
2 1 A
3 1 B
4 1 C
now i need result like this
questionid responseA responseB responseC
1 50% 10% 10%
Is there faster alternative to this:
Take almost 1 minute in our server.
SELECT
tc.constraint_name, tc.table_name, kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';
Maybe using pg_class metadata?, thanks.
i have a good, working valid non-corrupted database in mssql that i want to revert to a point in time
how is that done?
the standard RESTORE command requires a full backup as a starting point, and then log backups thereafter.
i cant understand why this must be done from a backup. if my db is good and the logs are OK, why cant i just revert with a STOPAT from the live logs in the db?
one dba suggested that whenever i want to restore i should THEN make a log backup and then RESTORE with STOPAT. i believe it would work but sounds a little backwards
any better ideas?
thank you very much
Hi I have a table test as below
NAME
---------
abc1234
XYZ12789
a12X8b78Y9c5Z
I try to find out the count of number of numbers and characters in the string as
select name,length(replace(translate(lower(name),'abcdefghijklmnopqrstuvwxyz',' '),' ','')) as char_count,
length(replace(translate(name,'1234567890',' '),' ','')) as num_count
from test6;
Its executing fine giving the output
NAME CHAR_COUNT NUM_COUNT
abc1234 4 3
XYZ12789 5 3
a12X8b78Y9c5Z 7 6
But my question is there any option by not giving the abcdefghijklmnopqrstuvwxyz
and 1234567890 manually