audit tables - Page 39 - Developer IT

Self-referencing tables in Linq2Sql

- by J-Man

Hi, I've seen a lot of questions on self-referencing tables in Linq2Sql and how to eagerly load all child records for a particular root object. I've implemented a temporary solution by accessing all underlying properties, but you can see that this doesn't do the performance any good. The thing is though, that all records are correlated with each-other using a correlation GUID. Example below: RootElement - Id: 1 - ParentId: null - CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD ChildElement1 - Id: 2 - ParentId: 1 - CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD ChildElement2 - Id: 3 - ParentId: 2 - CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD ChildElement1 - Id: 4 - ParentId: 2 - CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD In my case, I do have access to the correlationId, so I can retrieve all of my records by performing the following query: from element in db.Elements where element.CorrelationId == '4D68E512-4B55-44f4-BA5A-174B630A03DD' select element; But, of course, I want these elements associated with each other by executing this query: from element in db.Elements where element.CorrelationId == '4D68E512-4B55-44f4-BA5A-174B630A03DD' && element.ParentId == null select element; My question is: is it possible to combine the results the first query as some sort of 'caching mechanism' for the query where I get the root element? Thanks for the input. J.

Read the article

how to 'scale' these three tables?

- by iddqd

I have the following Tables: Players id playerName Weapons id type otherData Weapons2Player id playersID_reference weaponsID_reference That was nice and simple. Now I need to SELECT items from the Weapons table, according to some of their characteristics that i previously just packed into the otherData column (since it was only needed on the client side). The problem is, that the types have varying characteristics - but also a lot of similar data. So I'm trying to decide on the following possibilities, all of which have their pros and cons. Solution A Kill the Weapons table, and create a new table for each Weapon-Type: Weapons_Swords id bladeType damage otherData Weapons_Guns id accuracy damage ammoType otherData But how will i Link these to the Players ? create Weapons_Swords2Players, Weapons_Guns2Players for each weapon-type? (Will result in a lot more JOINS when loading the player with all his weapons...and it's also more complicated to insert a new player) or add another column to Weapons2Players called WeaponsTypeTable, then do sub-selects to the correct Weapons sub-table (seems easier, but not really right, slightly easier insert i guess) Solution B Keep the Weapons table, and add all the fields i need to it. The Problem is that then there will be NULL fields, since not all Weapon-Types use all fields (can't be right) Weapons id type accuracy damage ammoType bladeType otherData This seems to be pretty basic stuff, but i just can't decide what's best. Or is there a correct Solution C? many thanks.

Read the article

SQL query using information from 4 tables (not all directly linked)

- by Yvonne

I'm developing a simple classroom system, where teachers manage classes and their subjects. I have 2 levels of access in my teachers table, assigned by an integer (1 = admin, 2 = user)... Meaning that the headteacher is the admin :) A teacher (of level 1) can have have many classes and a class can have many teachers (so I have 'TeachersClasses' table). A class can have many subjects, and a teacher can have many subjects. Basically, I'm attempting a query to display the admin teacher's (level 1) subjects. However, only teachers with a level of 2, are directly related to a subject, which is set by the admin user. The headteacher can view all of their subjects via the classroom, but I cannot get all of the subjects to be displayed on one page, instead I can only get the subjects to appear under a specific classroom right now... This is what I have so far, which is returning nothing. (I'm guessing this may require an SQL clause more advanced that 'INNER JOIN' which is the only join type I am familiar with, and thought it would be enough! $query = "SELECT subjects.subjectid, subjects.subjectname, subjects.subjectdetails, classroom.classid, classroom.classname FROM subjects INNER JOIN classroom ON subjects.subjectid = classroom.classid INNER JOIN teacherclasses ON classroom.classid = teacherclasses.classid INNER JOIN teachers ON teacherclasses.teacherid = teachers.teacherid WHERE teachers.teacherid = '".intval( $_SESSION['SESS_TEACHERID'] )."'"; In order for all subjects related to the headteachers class to be displayed, I'm gathering that all of my tables will need to be called up here? Thanks for any help! Example output: subject name: maths // teacher: mr smith // classroom: DG99 x10 for all the subjects associated with the headteachers classrooms :)

Read the article

Eclipselink update existing tables

- by javydreamercsw

Maybe I got it wrong but i though that JPA was able to update an existing table (model changed adding a column) but is not working in my case. I can see in the logs eclipselink attempting to create it but failing because it already exists. Instead of trying an update to add the column it keeps going. (Had to remove some < so it displays) property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jwrestling"/ property name="javax.persistence.jdbc.password" value="password"/ property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/ property name="javax.persistence.jdbc.user" value="user"/ property name="eclipselink.ddl-generation" value="create-tables"/ property name="eclipselink.logging.logger" value="org.eclipse.persistence.logging.DefaultSessionLog"/ property name="eclipselink.logging.level" value="INFO"/ And here's the table with the change (online column added) [EL Warning]: 2010-05-31 14:39:06.044--ServerSession(16053322)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100517-r7246): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'account' already exists Error Code: 1050 Call: CREATE TABLE account (ID INTEGER NOT NULL, USERNAME VARCHAR(32) NOT NULL, SECURITY_KEY VARCHAR(255) NOT NULL, EMAIL VARCHAR(64) NOT NULL, STATUS VARCHAR(8) NOT NULL, TIMEDATE DATETIME NOT NULL, PASSWORD VARCHAR(255) NOT NULL, ONLINE TINYINT(1) default 0 NOT NULL, PRIMARY KEY (ID)) Query: DataModifyQuery(sql="CREATE TABLE account (ID INTEGER NOT NULL, USERNAME VARCHAR(32) NOT NULL, SECURITY_KEY VARCHAR(255) NOT NULL, EMAIL VARCHAR(64) NOT NULL, STATUS VARCHAR(8) NOT NULL, TIMEDATE DATETIME NOT NULL, PASSWORD VARCHAR(255) NOT NULL, ONLINE TINYINT(1) default 0 NOT NULL, PRIMARY KEY (ID))") [EL Warning]: 2010-05-31 14:39:06.074--ServerSession(16053322)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.0.v20100517-r7246): org.eclipse.persistence.exceptions.DatabaseException After this it continues with the following. Am I doing something wrong or is a bug?

Read the article

Using Linq2Sql to insert data into multiple tables using an auto incremented primary key

- by Thomas

I have a Customer table with a Primary key (int auto increment) and an Address table with a foreign key to the Customer table. I am trying to insert both rows into the database in one nice transaction. using (DatabaseDataContext db = new DatabaseDataContext()) { Customer newCustomer = new Customer() { Email = customer.Email }; Address b = new Address() { CustomerID = newCustomer.CustomerID, Address1 = billingAddress.Address1 }; db.Customers.InsertOnSubmit(newCustomer); db.Addresses.InsertOnSubmit(b); db.SubmitChanges(); } When I run this I was hoping that the Customer and Address table automatically had the correct keys in the database since the context knows this is an auto incremented key and will do two inserts with the right key in both tables. The only way I can get this to work would be to do SubmitChanges() on the Customer object first then create the address and do SubmitChanges() on that as well. This would create two roundtrips to the database and I would like to see if I can do this in one transaction. Is it possible? Thanks

Read the article

SQL: script to create country, state tables

- by pcampbell

Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity: USA = states Australia = states + territories Canada = provinces + territories Mexico = states Brazil = states Sweden = provinces UK = counties, principalities, and perhaps more! Most times when approaching this problem, I have to scratch together a list of good countries, and the states/prov/counties of each. The app may be concerned with a few countries and not others. The process is full of pain. It typically involves one of two approaches: opening up some previous DB and creating a CREATE script based on those tables. Run that script in the context of the new system. creating a DTS package from database1 to database2, with all the DDL and data included in the transfer. My goal now is to script the creation and insert of the countries that I'd be concerned with in the app of the day. When I want to roll out Countries X/Y/Z, I'll open CountryX.sql, and load its states into the ProvState table. Question: do you have a set of scripts in your toolset to create schema and data for countries and state/province/county? If so, would you share your scripts here? (U.K. citizens, please feel free to correct me by way of a comment in the use of counties.)

Read the article

Creating self-referential tables with polymorphism in SQLALchemy

- by Jace

I'm trying to create a db structure in which I have many types of content entities, of which one, a Comment, can be attached to any other. Consider the following: from datetime import datetime from sqlalchemy import create_engine from sqlalchemy import Column, ForeignKey from sqlalchemy import Unicode, Integer, DateTime from sqlalchemy.orm import relation, backref from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Entity(Base): __tablename__ = 'entities' id = Column(Integer, primary_key=True) created_at = Column(DateTime, default=datetime.utcnow, nullable=False) edited_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False) type = Column(Unicode(20), nullable=False) __mapper_args__ = {'polymorphic_on': type} # <...insert some models based on Entity...> class Comment(Entity): __tablename__ = 'comments' __mapper_args__ = {'polymorphic_identity': u'comment'} id = Column(None, ForeignKey('entities.id'), primary_key=True) _idref = relation(Entity, foreign_keys=id, primaryjoin=id == Entity.id) attached_to_id = Column(Integer, ForeignKey('entities.id'), nullable=False) #attached_to = relation(Entity, remote_side=[Entity.id]) attached_to = relation(Entity, foreign_keys=attached_to_id, primaryjoin=attached_to_id == Entity.id, backref=backref('comments', cascade="all, delete-orphan")) text = Column(Unicode(255), nullable=False) engine = create_engine('sqlite://', echo=True) Base.metadata.bind = engine Base.metadata.create_all(engine) This seems about right, except SQLAlchemy doesn't like having two foreign keys pointing to the same parent. It says ArgumentError: Can't determine join between 'entities' and 'comments'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly. How do I specify onclause?

Read the article

Submitting changes to 2 tables with C# linq only one table is changing

- by Laurence Burke

SO I am changing the values in 2 different tables and the only table changing is the address table any one know why? protected void btnSubmit_Click(object sender, EventArgs e) { TestDataClassDataContext dc = new TestDataClassDataContext(); var addr = (from a in dc.Addresses where a.AddressID == Convert.ToInt32(ddlAddList.SelectedValue) select a).FirstOrDefault(); var caddr = (from ca in dc.CustomerAddresses where addr.AddressID == ca.AddressID select ca).FirstOrDefault(); if (txtZip.Text != "" && txtAdd1.Text != "" && txtCity.Text != "") { addr.AddressLine1 = txtAdd1.Text; addr.AddressLine2 = txtAdd2.Text; addr.City = txtCity.Text; addr.PostalCode = txtZip.Text; addr.StateProvinceID = Convert.ToInt32(ddlState.SelectedValue); caddr.AddressTypeID = Convert.ToInt32(ddlAddrType.SelectedValue); dc.SubmitChanges(); lblErrMsg.Visible = false; lblSuccess.Visible = true; } else { lblErrMsg.Text = "Invalid Input"; lblErrMsg.Visible = true; } }

Read the article

Need a SQL statement focus on combination of tables but entries always with uinque ID

- by Registered User KC

Hi All, I need SQL code to solve the tables combination problem, described on below: Table old data: name version status lastupdate ID A 0.1 on 6/8/2010 1 B 0.1 on 6/8/2010 2 C 0.1 on 6/8/2010 3 D 0.1 on 6/8/2010 4 E 0.1 on 6/8/2010 5 F 0.1 on 6/8/2010 6 G 0.1 on 6/8/2010 7 Table new data: name version status lastupdate ID A 0.1 on 6/18/2010 #B entry deleted C 0.3 on 6/18/2010 #version_updated C1 0.1 on 6/18/2010 D 0.1 on 6/18/2010 E 0.1 off 6/18/2010 #status_updated F 0.1 on 6/18/2010 G 0.1 on 6/18/2010 H 0.1 on 6/18/2010 #new_added H1 0.1 on 6/18/2010 #new_added the difference of new data and old date: B entry deleted C entry version updated E entry status updated C1/H/H1 entry new added What I want is always keeping the ID - name mapping relationship in old data table no matter how data changed later, a.k.a the name always has a unique ID number bind with it. If entry has update, then update the data, if entry is new added, insert to the table then give a new assigned unique ID. However, I can only use SQL with simple select or update statement then it may too hard for me to write such code, then I hope someone with expertise can give direction, no details needed on the different of SQL variant, a standard sql code as sample is enough. Thanks in advance! Rgs KC

Read the article

How do I join three tables with SQLalchemy and keeping all of the columns in one of the tables?

- by jimka

So, I have three tables: The class defenitions: engine = create_engine('sqlite://test.db', echo=False) SQLSession = sessionmaker(bind=engine) Base = declarative_base() class Channel(Base): __tablename__ = 'channel' id = Column(Integer, primary_key = True) title = Column(String) description = Column(String) link = Column(String) pubDate = Column(DateTime) class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key = True) username = Column(String) password = Column(String) sessionId = Column(String) class Subscription(Base): __tablename__ = 'subscription' userId = Column(Integer, ForeignKey('user.id'), primary_key=True) channelId = Column(Integer, ForeignKey('channel.id'), primary_key=True) And the SQL commands that are executed to create them: CREATE TABLE subscription ( "userId" INTEGER NOT NULL, "channelId" INTEGER NOT NULL, PRIMARY KEY ("userId", "channelId"), FOREIGN KEY("userId") REFERENCES user (id), FOREIGN KEY("channelId") REFERENCES channel (id) ); CREATE TABLE user ( id INTEGER NOT NULL, username VARCHAR, password VARCHAR, "sessionId" VARCHAR, PRIMARY KEY (id) ); CREATE TABLE channel ( id INTEGER NOT NULL, title VARCHAR, description VARCHAR, link VARCHAR, "pubDate" TIMESTAMP, PRIMARY KEY (id) ); NOTE: I know user.username should be unique, need to fix that, and I'm not sure why SQLalchemy creates some row names with the double-quotes. And I'm trying to come up with a way to retrieve all of the channels, as well as an indication on what channels one particular user (identified by user.sessionId together with user.id) has a subscription on. For example, say we have four channels: channel1, channel2, channel3, channel4; a user: user1; who has a subscription on channel1 and channel4. The query for user1 would return something like: channel.id | channel.title | subscribed --------------------------------------- 1 channel1 True 2 channel2 False 3 channel3 False 4 channel4 True This is a best-case result, but since I have absolutely no clue as how to accomplish the subscribed column, I've been instead trying to get the particular users id in the rows where the user has a subscription and where a subscription is missing, just leave it blank. The database engine that I'm using together with SQLalchemy atm. is sqlite3 I've been scratching my head over this for two days now, I've no problem joining together all three by way of the subscription table but then all of the channels where the user does not have a subscription gets omitted. I hope I've managed to describe my problem sufficiently, thanks in advance.

Read the article

MySQL: Complex Join Statement involving two tables and a third correlation table

- by Stephen

I have two tables that were built for two disparate systems. I have records in one table (called "leads") that represent customers, and records in another table (called "manager") that are the exact same customers but "manager" uses different fields (For example, "leads" contains an email address, and "manager" contains two fields for two different emails--either of which might be the email from "leads"). So, I've created a correlation table that contains the lead_id and manager_id. currently this correlation table is empty. I'm trying to query the "leads" table to give me records that match either "manager" email field with the single "leads" email field, while at the same time ignoring fields that have already been added to the "correlated" table. (this way I can see how many leads that match have not yet been correlated.) Here's my current, invalid SQL attempt: SELECT leads.id, manager.id FROM leads, manager LEFT OUTER JOIN correlation ON correlation.lead_id = leads.id WHERE correlation.id IS NULL AND leads.project != "someproject" AND (manager.orig_email = leads.email OR manager.dest_email = leads.email) AND leads.created BETWEEN '1999-01-01 00:00:00' AND '2010-05-10 23:59:59' ORDER BY leads.created ASC; I get the error: Unknown column 'leads.id' in 'on clause' Before you wonder: there are records in the "leads" table where leads.project != "someproject" and leads.created falls between those dates. I've included those additional parameters for completeness.

Read the article

How to map a Dictionary<string, string> spanning several tables

- by Kim Johansson

I have four tables: CREATE TABLE [Languages] ( [Id] INTEGER IDENTITY(1,1) NOT NULL, [Code] NVARCHAR(10) NOT NULL, PRIMARY KEY ([Id]), UNIQUE INDEX ([Code]) ); CREATE TABLE [Words] ( [Id] INTEGER IDENTITY(1,1) NOT NULL, PRIMARY KEY ([Id]) ); CREATE TABLE [WordTranslations] ( [Id] INTEGER IDENTITY(1,1) NOT NULL, [Value] NVARCHAR(100) NOT NULL, [Word] INTEGER NOT NULL, [Language] INTEGER NOT NULL, PRIMARY KEY ([Id]), FOREIGN KEY ([Word]) REFERENCES [Words] ([Id]), FOREIGN KEY ([Language]) REFERENCES [Languages] ([Id]) ); CREATE TABLE [Categories] ( [Id] INTEGER IDENTITY(1,1) NOT NULL, [Word] INTEGER NOT NULL, PRIMARY KEY ([Id]), FOREIGN KEY ([Word]) REFERENCES [Words] ([Id]) ); So you get the name of a Category via the Word - WordTranslation - Language relations. Like this: SELECT TOP 1 wt.Value FROM [Categories] AS c LEFT JOIN [WordTranslations] AS wt ON c.Word = wt.Word WHERE wt.Language = ( SELECT TOP 1 l.Id FROM [Languages] WHERE l.[Code] = N'en-US' ) AND c.Id = 1; That would return the en-US translation of the Category with Id = 1. My question is how to map this using the following class: public class Category { public virtual int Id { get; set; } public virtual IDictionary<string, string> Translations { get; set; } } Getting the same as the SQL query above would be: Category category = session.Get<Category>(1); string name = category.Translations["en-US"]; And "name" would now contain the Category's name in en-US. Category is mapped against the Categories table. How would you do this and is it even possible?

Read the article

Which SQL statements to execute with intersection / junction tables

- by user1455103

Here a simplified database layout One condo can hold multiple properties (flats, garage boxes, etc) - 1->n relationship One owner can have multiple properties in the same condo and properties can have more than one owner (m->n changed to 1->n with the junction table) One condo can have multiple owners - 1->n Some additional clarification: A owner is a member of a condo. A condo is made of properties belonging to owners BUT a owner is not linked to a property directly (there can be no relation between a property and a owner for a certain time BUT there will ALWAYS be a relation between a owner and a condo). Reason for this: the agent managing the condo will first create a list of owners and a list of properties. It is only later thet he will "link" each property to one or multiple owners (or inversely) I'm quite new to SQL. What SQL statements should I execute to: SELECT, for a specific condo (WHERE condition), the properties and their respective owners (all properties should be listed even if owners are null) SELECT, for a specific condo (WHERE condition), the owners along with their properties (all owners should be listed even if properties are null) UPDATE / DELETE existing owners (I'm uncertain about how to handle the operation for the junction tables. Should I first check if there is an entry in the junction table or not ?) UPDATE / DELETE existing properties (same concern) INSERT new owners (should I use two different SQL statements depending if the owner should be linked to a property or NOT - IF condition ?) INSERT new properties (same question as above) Could you be as clear and generic as possible so that it can be reused ? :-)

Read the article

Can't combine "LINQ Join" with other tables

- by FullmetalBoy

The main problem is that I recieve the following message: "base {System.SystemException} = {"Unable to create a constant value of type 'BokButik1.Models.Book-Author'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."}" based on this LinQ code: IBookRepository myIBookRepository = new BookRepository(); var allBooks = myIBookRepository.HamtaAllaBocker(); IBok_ForfattareRepository myIBok_ForfattareRepository = new Bok_ForfattareRepository(); var Book-Authors = myIBok_ForfattareRepository.HamtaAllaBok_ForfattareNummer(); var q = from booknn in allBooks join Book-Authornn in Book-Authors on booknn.BookID equals Book-Authornn.BookID select new { booknn.title, Book-AuthorID }; How shall I solve this problem to get a class instance that contain with property title and Book-AuthorID? // Fullmetalboy I also have tried making some dummy by using "allbooks" relation with Code Samples from the address http://www.hookedonlinq.com/JoinOperator.ashx. Unfortunately, still same problem. I also have taken account to Int32 due to entity framework http://msdn.microsoft.com/en-us/library/bb896317.aspx. Unfortunatley, still same problem. Using database with 3 tables and one of them is a many to many relationship. This database is used in relation with entity framework Book-Author Book-Author (int) BookID (int) Forfattare (int) Book BookID (int) title (string) etc etc etc

Read the article

Get count of rows in each table while having more than 1 tables

- by sneha khan

I have more then one tables on same page and want to add a line show count of each table as below. I tried something but it gives sum of count of all table rows. <table> <tr> <td>Some data</td> <td>More data</td> </tr> <tr> <td>Some data</td> <td>More data</td> </tr> </table> <table> <tr> <td>Some data</td> <td>More data</td> </tr> <tr> <td>Some data</td> <td>More data</td> </tr> </table> <script type="text/javascript" src="jquery.js"></script> <script type="text/javascript"> $().ready(function(){ //I want to add a line after each table showing each table row count $("table").after(??? + " rows found."); }); </script>

Read the article

Best way to dynamically get column names from oracle tables

- by MNC

Hi, We are using an extractor application that will export data from the database to csv files. Based on some condition variable it extracts data from different tables, and for some conditions we have to use UNION ALL as the data has to be extracted from more than one table. So to satisfy the UNION ALL condition we are using nulls to match the number of columns. Right now all the queries in the system are pre-built based on the condition variable. The problem is whenever there is change in the table projection (i.e new column added, existing column modified, column dropped) we have to manually change the code in the application. Can you please give some suggestions how to extract the column names dynamically so that any changes in the table structure do not require change in the code? My concern is the condition that decides which table to query. The variable condition is like if the condition is A, then load from TableX if the condition is B then load from TableA and TableY. We must know from which table we need to get data. Once we know the table it is straightforward to query the column names from the data dictionary. But there is one more condition, which is that some columns need to be excluded, and these columns are different for each table. I am trying to solve the problem only for dynamically generating the list columns. But my manager told me to make solution on the conceptual level rather than just fixing. This is a very big system with providers and consumers constantly loading and consuming data. So he wanted solution that can be general. So what is the best way for storing condition, tablename, excluded columns? One way is storing in database. Are there any other ways? If yes what is the best? As I have to give at least a couple of ideas before finalizing. Thanks,

Read the article

temporary tables within stored procedures on slave servers with readonly set

- by lau

Hi, We have set up a replication scheme master/slave and we've had problems lately because some users wrote directly on the slave instead of the master, making the whole setup inconsistent. To prevent these problems from happening again, we've decided to remove the insert, delete, update, etc... rights from the users accessing the slave. Problems is that some stored procedure (for reading) require temporary tables. I read that changing the global variable read_only to true would do what I want and allow the stored procedures to work correctly ( http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_only ) but I keep getting the error : The MySQL server is running with the --read-only option so it cannot execute this statement (1290) The stored procedure that I used (for testing purpose) is this one : DELIMITER $$ DROP PROCEDURE IF EXISTS test_readonly $$ CREATE DEFINER=dbuser@% PROCEDURE test_readonly() BEGIN CREATE TEMPORARY TABLE IF NOT EXISTS temp ( BT_INDEX int(11), BT_DESC VARCHAR(10) ); INSERT INTO temp (BT_INDEX, BT_DESC) VALUES (222,'walou'), (111,'bidouille'); DROP TABLE temp; END $$ DELIMITER ; The create temporary table and the drop table work fine with the readonly flag - if I comment the INSERT line, it runs fine- but whenever I want to insert or delete from that temporary table, I get the error message. I use Mysql 5.1.29-rc. My default storage engine is InnoDB. Thanks in advance, this problem is really driving me crazy.

Read the article

Non standard interaction among two tables to avoid very large merge

- by riko

Suppose I have two tables A and B. Table A has a multi-level index (a, b) and one column (ts). b determines univocally ts. A = pd.DataFrame( [('a', 'x', 4), ('a', 'y', 6), ('a', 'z', 5), ('b', 'x', 4), ('b', 'z', 5), ('c', 'y', 6)], columns=['a', 'b', 'ts']).set_index(['a', 'b']) AA = A.reset_index() Table B is another one-column (ts) table with non-unique index (a). The ts's are sorted "inside" each group, i.e., B.ix[x] is sorted for each x. Moreover, there is always a value in B.ix[x] that is greater than or equal to the values in A. B = pd.DataFrame( dict(a=list('aaaaabbcccccc'), ts=[1, 2, 4, 5, 7, 7, 8, 1, 2, 4, 5, 8, 9])).set_index('a') The semantics in this is that B contains observations of occurrences of an event of type indicated by the index. I would like to find from B the timestamp of the first occurrence of each event type after the timestamp specified in A for each value of b. In other words, I would like to get a table with the same shape of A, that instead of ts contains the "minimum value occurring after ts" as specified by table B. So, my goal would be: C: ('a', 'x') 4 ('a', 'y') 7 ('a', 'z') 5 ('b', 'x') 7 ('b', 'z') 7 ('c', 'y') 8 I have some working code, but is terribly slow. C = AA.apply(lambda row: ( row[0], row[1], B.ix[row[0]].irow(np.searchsorted(B.ts[row[0]], row[2]))), axis=1).set_index(['a', 'b']) Profiling shows the culprit is obviously B.ix[row[0]].irow(np.searchsorted(B.ts[row[0]], row[2]))). However, standard solutions using merge/join would take too much RAM in the long run. Consider that now I have 1000 a's, assume constant the average number of b's per a (probably 100-200), and consider that the number of observations per a is probably in the order of 300. In production I will have 1000 more a's. 1,000,000 x 200 x 300 = 60,000,000,000 rows may be a bit too much to keep in RAM, especially considering that the data I need is perfectly described by a C like the one I discussed above. How would I improve the performance?

Read the article

Excessive use of Inner Join for more than 3 tables

- by Archangel08

Good Day, I have 4 tables on my DB (not the actual name but almost similar) which are the ff: employee,education,employment_history,referrence employee_id is the name of the foreign key from employee table. Here's the example (not actual) data: **Employee** ID Name Birthday Gender Email 1 John Smith 08-15-2014 Male [email protected] 2 Jane Doe 00-00-0000 Female [email protected] 3 John Doe 00-00-0000 Male [email protected] **Education** Employee_ID Primary Secondary Vocation 1 Westside School Westshore H.S SouthernBay College 2 Eastside School Eastshore H.S NorthernBay College 3 Northern School SouthernShore H.S WesternBay College **Employment_History** Employee_ID WorkOne StartDate Enddate 1 StarBean Cafe 12-31-2012 01-01-2013 2 Coffebucks Cafe 11-01-2012 11-02-2012 3 Latte Cafe 01-02-2013 04-05-2013 Referrence Employee_ID ReferrenceOne Address Contact 1 Abraham Lincoln Lincoln Memorial 0000000000 2 Frankie N. Stein Thunder St. 0000000000 3 Peter D. Pan Neverland Ave. 0000000000 NOTE: I've only included few columns though the rest are part of the query. And below are the codes I've been working on for 3 consecutive days: $sql=mysql_query("SELECT emp.id,emp.name,emp.birthday,emp.pob,emp.gender,emp.civil,emp.email,emp.contact,emp.address,emp.paddress,emp.citizenship,educ.employee_id,educ.elementary,educ.egrad,educ.highschool,educ.hgrad,educ.vocational,educ.vgrad,ems.employee_id,ems.workOne,ems.estartDate,ems.eendDate,ems.workTwo,ems.wstartDate,ems.wendDate,ems.workThree,ems.hstartDate,ems.hendDate FROM employee AS emp INNER JOIN education AS educ ON educ.employee_id='emp.id' INNER JOIN employment_history AS ems ON ems.employee_id='emp.id' INNER JOIN referrence AS ref ON ref.employee_id='emp.id' WHERE emp.id='$id'"); Is it okay to use INNER JOIN this way? Or should I modify my query to get the results that I wanted? I've also tried to use LEFT JOIN but still it doesn't return anything .I didn't know where did I go wrong. You see, as I have thought, I've been using the INNER JOIN in correct manner, (since it was placed before the WHILE CLAUSE). So I couldn't think of what could've possible went wrong. Do you guys have a suggestion? Thanks in advance.

Read the article

Creating a Mysql view to SELECT coloumns from different tables

- by user330429

Read the article

Azure batch operations delete several blobs and tables

- by reft

I have a function that deletes every table & blob that belongs to the affected user. CloudTable uploadTable = CloudStorageServices.GetCloudUploadsTable(); TableQuery<UploadEntity> uploadQuery = uploadTable.CreateQuery<UploadEntity>(); List<UploadEntity> uploadEntity = (from e in uploadTable.ExecuteQuery(uploadQuery) where e.PartitionKey == "uploads" && e.UserName == User.Idendity.Name select e).ToList(); foreach (UploadEntity uploadTableItem in uploadEntity) { //Delete table TableOperation retrieveOperationUploads = TableOperation.Retrieve<UploadEntity>("uploads", uploadTableItem.RowKey); TableResult retrievedResultUploads = uploadTable.Execute(retrieveOperationUploads); UploadEntity deleteEntityUploads = (UploadEntity)retrievedResultUploads.Result; TableOperation deleteOperationUploads = TableOperation.Delete(deleteEntityUploads); uploadTable.Execute(deleteOperationUploads); //Delete blob CloudBlobContainer blobContainer = CloudStorageServices.GetCloudBlobsContainer(); CloudBlockBlob blob = blobContainer.GetBlockBlobReference(uploadTableItem.BlobName); blob.Delete(); } Each table got its own blob, so if the list contains 3 uploadentities, the 3 table and the 3 blobs will be deleted. I heard you can use table batch operations for reduce cost and load. I tried it, but failed miserable. Anyone intrested in helping me:)? Im guessing tablebatch operations are for tables only, so its a no go for blobs, right? How would you add tablebatchoperations for this code? Do you see any other improvements that can be done? Thanks!

Read the article

MySQL: How to pull information from multiple tables based on information in other tables?

- by Greg

Ok, I have 5 tables which I need to pull information from based on one variable. gameinfo id | name | platforminfoid gamerinfo id | name | contact | tag platforminfo id | name | abbreviation rosterinfo id | name | gameinfoid rosters id | gamerinfoid | rosterinfoid The 1 variable would be gamerinfo.id, which would then pull all relevant data from gamerinfo, which would pull all relevant data from rosters, which would pull all relevant data from rosterinfo, which would pull all relevant data from gameinfo, which would then pull all relevant data from platforminfo. Basically it breaks down like this: gamerinfo contains the gamers basic information. rosterinfo contains basic information about the rosters (ie name and the game the roster is aimed towards) rosters contains the actual link from the gamer to the different rosters (gamers can be on multiple rosters) gameinfo contains basic information about the games (ie name and platform) platform info contains information about the different platforms the games are played on (it is possible for a game to be played on multiple platforms) I am pretty new to SQL queries involving JOINs and UNIONs and such, usually I would just break it up into multiple queries but I thought there has to be a better way, so after looking around the net, I couldn't find (or maybe I just couldn't understand what I was looking at) what I was looking for. If anyone can point me in the right direction I would be most grateful.

Read the article

Mapping one column in a table to multiple tables

- by user1721814

I am working on a new product development creating a WMS system. I have done it in past using ASP, VB, and other techniques where we did not hard code the mapping. But now i am working on it using MVC and entity frame work and i am stumped. How can i map one column in transaction table to a column in multiple tables. I have transaction table trans Transid orderref TType productid qty ....(More Columns) now the orderref will hold either Receiptkey, orderkey , movementkey, adjustmentkey and the TType column will tell me which type of transaction i am dealing with and based on that i would know which table to link further. Now how can i achieve this in Entity Frame work. This is the most important step. I have done this many times in my other languages but now using EF i am stuck. Please help. I have checked a lot online but i have not found it. I am new to MVC and entity frame work architecture. Any guidance would be highly appreciated. Ranjit

Read the article

Join with Three Tables

- by John

Hello, In MySQL, I am using the following three tables (their fields are listed after their titles): comment: commentid loginid submissionid comment datecommented login: loginid username password email actcode disabled activated created points submission: submissionid loginid title url displayurl datesubmitted I would like to display "datecommented" and "comment" for a given "username," where "username" equals a variable called $profile. I would also like to make the "comment" a hyperlink to http://www...com/.../comments/index.php?submission='.$rowc["title"].'&submissionid='.$rowc["submissionid"].'&url='.$rowc["url"].'&countcomments='.$rowc["countComments"].'&submittor='.$rowc["username"].'&submissiondate='.$rowc["datesubmitted"].'&dispurl='.$rowc["displayurl"].' where countComments equals COUNT(c.commentid) and $rowc is part of the query listed below. I tried using the code below to do what I want, but it didn't work. How could I change it to make it do what I want? Thanks in advance, John $sqlStrc = "SELECT s.loginid ,s.title ,s.url ,s.displayurl ,s.datesubmitted ,l.username ,l.loginid ,s.title ,s.submissionid ,c.comment ,c.datecommented ,COUNT(c.commentid) countComments FROM submission s INNER JOIN login l ON s.loginid = l.loginid LEFT OUTER JOIN comment c ON c.loginid = l.loginid WHERE l.username = '$profile' GROUP BY c.loginid ORDER BY s.datecommented DESC LIMIT 10"; $resultc = mysql_query($sqlStrc); $arrc = array(); echo "<table class=\"samplesrec1c\">"; while ($rowc = mysql_fetch_array($resultc)) { $dtc = new DateTime($rowc["datecommented"], $tzFromc); $dtc->setTimezone($tzToc); echo '<tr>'; echo '<td class="sitename3c">'.$dtc->format('F j, Y &\nb\sp &\nb\sp g:i a').'</a></td>'; echo '<td class="sitename1c"><a href="http://www...com/.../comments/index.php?submission='.$rowc["title"].'&submissionid='.$rowc["submissionid"].'&url='.$rowc["url"].'&countcomments='.$rowc["countComments"].'&submittor='.$rowc["username"].'&submissiondate='.$rowc["datesubmitted"].'&dispurl='.$rowc["displayurl"].'">'.stripslashes($rowc["comment"]).'</a></td>'; echo '</tr>'; } echo "</table>";

Read the article

How to write a JOIN statement to combine data from disparate tables

- by Amarundo

I have the following 2 procedures that I use as my source for a report. As of now, I'm presenting 2 different tables in my SQL Server Reporting Services 2008 R2 report, because it doesn't let me put them together as they belong to 2 different data sets. I want to present them in a single table, but I have not been successful trying to use JOIN here. How do I do that? NOTE: cName in IAgentQueueStats corresponds to UserId in AgentActivityLog. /*** Aggregate values for Call Center Agents for calls, talk and hold time ***/ /*** The detail/row values is per 30-minute interval ***/ ALTER PROCEDURE [dbo].[sp_IAgentQueueStats_OnlyCalls_Grouped] @p_StartDate datetime, @p_EndDate datetime, @p_Agents varchar(8000) AS SELECT [cName] ,sum([nAnswered]) SumNAnswered ,sum([nAnsweredAcd]) SumNAnsweredAcd ,sum([tTalkAcd]) SumTTalkAcd ,sum([nHoldAcd]) SumNHoldAcd ,sum([tHoldAcd]) SumTHoldAcd ,sum([tAcw]) SumTAcw FROM [I3_IC].[dbo].[IAgentQueueStats] WHERE dIntervalStart between @p_StartDate and DATEADD(s, 86400-1, @p_EndDate) AND CHARINDEX ( cName ,@p_Agents)> 0 AND cReportGroup <> '*' AND cHKey3 = '*' and cHKey4 ='*' AND nEnteredAcd > 0 AND cReportGroup <> 'CCFax Email' GROUP BY cName And here is the second one: /*** Aggregate values for Call Center Agents for status/activity time ***/ /*** The detail/row values is per start-time/end-time ***/ ALTER PROCEDURE [dbo].[sp_AgentActivity_Grouped] @p_StartDate datetime, @p_EndDate datetime, @p_Agents varchar(8000) AS SELECT [UserId],[StatusCategory],SUM([StateDuration]) [StatusDuration] FROM ( SELECT [UserId] ,[StatusGroup] ,[StatusKey] , CASE [StatusKey] WHEN 'Available' THEN 'Productive' WHEN 'Follow Up' THEN 'Productive' WHEN 'Campaign Call' THEN 'Productive' WHEN 'Awaiting Callback' THEN 'Productive' WHEN 'In a Meeting' THEN 'Not Your Fault' WHEN 'Project Work' THEN 'Not Your Fault' WHEN 'At a Training Session'THEN 'Not Your Fault' WHEN 'System Issues' THEN 'Not Your Fault' WHEN 'Test' THEN 'Not Your Fault' WHEN 'At Lunch' THEN 'Non Productive' WHEN 'Available, Forward' THEN 'Non Productive' WHEN 'Available, Follow-Me' THEN 'Non Productive' WHEN 'At Play' THEN 'Non Productive' WHEN 'AcdAgentNotAnswering' THEN 'Non Productive' WHEN 'Do Not Disturb' THEN 'Non Productive' WHEN 'Available, No ACD' THEN 'Non Productive' WHEN 'Away from desk' THEN 'Non Productive' ELSE [StatusKey] END StatusCategory ,stateduration FROM [I3_IC].[dbo].[AgentActivityLog] WHERE [StatusDateTime] between @p_StartDate and DATEADD(s, 86400-1, @p_EndDate) AND CHARINDEX ( [UserId] ,@p_Agents)> 0 AND [StatusKey] not in ('Gone Home','Out of the Office','On Vacation','Out of Town') ) a GROUP BY [UserId],[StatusCategory] ORDER BY [UserId], [StatusCategory] desc BTW, if I take some time to comment/reply on your posts, it's not lack of interest, but of understanding...

Search Results

Search found 8943 results on 358 pages for 'audit tables'.

Page 39/358 | < Previous Page | 35 36 37 38 39 40 41 42 43 44 45 46 | Next Page >

- by J-Man

- by iddqd

- by Yvonne

- by javydreamercsw

- by Thomas

- by pcampbell

- by Jace

- by Laurence Burke

- by Registered User KC

- by jimka

- by Stephen

- by Kim Johansson

- by user1455103

- by FullmetalBoy

- by sneha khan

- by MNC

- by lau

- by riko

- by Archangel08

- by user330429

- by reft

- by Greg

- by user1721814

- by John

- by Amarundo

< Previous Page | 35 36 37 38 39 40 41 42 43 44 45 46 | Next Page >