Search Results

Search found 10101 results on 405 pages for 'temporary tables'.

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

  • How to join the results of two tables in django python

    - by user1787524
    I have two models class Weather(model.model): region = models.ForeignKey(Region) district = models.ForeignKey(District) temp_max = models.IntegerField(blank=True, null=True, verbose_name='Max temperature (C)') temp_min = models.IntegerField(blank=True, null=True, verbose_name='Min temperature (C)') and class Plan(model.model): name = tinymce_models.HTMLField(blank=True, null=True) region = models.ForeignKey(Region) district = models.ForeignKey(District) Provided for every region and district have unique row. I want to combine the result so that i can get all the columns of both tables These two Models are not related to each other. ' I need to make the join like join weather w on w.region = A.region and w.distric = A.district so that result contains all the columns in everyobject like obj.temp_max etc

    Read the article

  • SUM a pair of COUNTs from two tables based on a time variable

    - by Kevin O.
    Been searching for an answer to this for the better part of an hour without much luck. I have two regional tables laid out with the same column names and I can put out a result list for either table based on the following query (swap Table2 for Table1): SELECT Table1.YEAR, FORMAT(COUNT(Table1.id),0) AS Total FROM Table1 WHERE Table1.variable='Y' GROUP BY Table1.YEAR Ideally I'd like to get a result that gives me a total sum of the counts by year, so instead of: | REGION 1 | | REGION 2 | | YEAR | Total | | YEAR | Total | | 2010 | 5 | | 2010 | 1 | | 2009 | 2 | | 2009 | 3 | | | | | 2008 | 4 | I'd have: | MERGED | | YEAR | Total | | 2010 | 6 | | 2009 | 5 | | 2008 | 4 | I've tried a variety of JOINs and other ideas but I think I'm caught up on the SUM and COUNT issue. Any help would be appreciated, thanks!

    Read the article

  • display data from tables that contain a null value

    - by user2631662
    I need the code below to say - if myReader reads a null entry a new method is called. At the moment it will not display data from tables that contain a null value conDataBase.Open(); myReader = cmdDataBase.ExecuteReader(); while (myReader.Read()) { if (myReader["Code_CodeID"] != DBNull.Value) { string sFirst = myReader["First"].ToString(); string sLast = myReader["Last"].ToString(); string sAdd1 = myReader["Address1"].ToString(); string sCode = myReader["Code"].ToString(); txtFirst.Text = sFirst; txtSecond.Text = sLast; txtadd1.Text = sAdd1; txtDeviceIMEI.Text = sCode; } } } else { //go to a new method } }

    Read the article

  • In yii how to access other tables fields

    - by user1636115
    I am creating project in yii framework. I am having table as- Qbquestion QbquestionOption -questionId -optionId -question -questionId -userId -option -isPublished -isAnswer In QbquestionOption controller i want to access Qbquestion tables fields. I had written quesry as- $Question=Qbquestion::model()->findAllByAttributes(array("questionId"=>$number)); where $number is some random number. When i am using its fields as= $Question-isPublished then its giving error as "trying to get access to property of non-object" Statement var_dump($Question) is showing all record and all values of Qbquestion table. So how can i access records? Please help me

    Read the article

  • How to deal with 2 almost identical tables

    - by jgritty
    I have a table of baseball stats, something like this: CREATE TABLE batting_stats( ab INTEGER, pa INTEGER, r INTEGER, h INTEGER, hr INTEGER, rbi INTEGER, playerID INTEGER, FOREIGN KEY(playerID) REFERENCES player(playerID) ); But then I have a table of stats that are basically exactly the same, but for a team: CREATE TABLE team_batting_stats( ab INTEGER, pa INTEGER, r INTEGER, h INTEGER, hr INTEGER, rbi INTEGER, teamID INTEGER, FOREIGN KEY(teamID) REFERENCES team(teamID) ); My first instinct is to scrap the Foreign key and generalize the ID, but I still have a problem, I have these 2 tables, and they can't have overlapping IDs: CREATE TABLE player( playerID INTEGER PRIMARY KEY, firstname TEXT, lastname TEXT, number INTEGER, teamID INTEGER, FOREIGN KEY(teamID) REFERENCES team(teamID) ); CREATE TABLE team( teamID INTEGER PRIMARY KEY, name TEXT, city TEXT, ); I feel like I'm overlooking something obvious that could solve this problem and reduce stats to a single table.

    Read the article

  • Query to select from two different tables

    - by ryan
    I would like to select from two tables and display my result using this query: CREATE TABLE Buy_Table ( buy_id int identity primary key, user_id int, amount decimal (18,2) ); go INSERT INTO Buy_Table (user_id, amount) VALUES ('1', 10), ('1', 8), ('1', 20), ('3', 1), ('2', 2); go CREATE TABLE Sell_Table ( sell_id int identity primary key, user_id int, amount decimal (18,2) ); go INSERT INTO Sell_Table (user_id, amount) VALUES ('1', 10), ('1', 8), ('1', 20), ('3', 3), ('2', 3); go select [user_id], 'Buy' as [Type], buy_id as [ID], amount from Buy_Table union all select [user_id], 'Sell', sell_id, amount from Sell_Table order by [user_id], [ID], [Type] However the above query will return each row of the user_id like this I want to display my result to something like this in a grid: Can this be done in query itself rather manipulating the grid? Thx

    Read the article

  • SQL Server 2005 - query with case statement

    - by user329266
    Trying to put a single query together to be used eventually in a SQL Server 2005 report. I need to: Pull in all distinct records for values in the "eventid" column for a time frame - this seems to work. For each eventid referenced above, I need to search for all instances of the same eventid to see if there is another record with TaskName like 'review1%'. Again, this seems to work. This is where things get complicated: For each record where TaskName is like review1, I need to see if another record exists with the same eventid and where TaskName='End'. Utimately, I need a count of how many records have TaskName like 'review1%', and then how many have TaskName like 'review1%' AND TaskName='End'. I would think this could be accomplished by setting a new value for each record, and for the eventid, if a record exists with TaskName='End', set to 1, and if not, set to 0. The query below seems to accomplish item #1 above: SELECT eventid, TimeStamp, TaskName, filepath FROM (SELECT eventid, TimeStamp, filepath, TaskName, ROW_NUMBER() OVER(PARTITION BY eventid ORDER BY TimeStamp DESC) AS seq FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') and (TimeStamp <= '2010-4-21 00:00:00.000'))) AS T WHERE seq = 1 order by eventid And the query below seems to accomplish #2: SELECT eventid, TimeStamp, TaskName, filepath FROM (SELECT eventid, TimeStamp, filepath, TaskName, ROW_NUMBER() OVER(PARTITION BY eventid ORDER BY TimeStamp DESC) AS seq FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T WHERE seq = 1 order by eventid This will bring back the eventid's that also have a TaskName='End': SELECT eventid, TimeStamp, TaskName, filepath FROM (SELECT eventid, TimeStamp, filepath, TaskName, ROW_NUMBER() OVER(PARTITION BY eventid ORDER BY TimeStamp DESC) AS seq FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T WHERE seq = 1 and eventid in (Select eventid from eventrecords where TaskName = 'End') order by eventid So I've tried the following to TRY to accomplish #3: SELECT eventid, TimeStamp, TaskName, filepath FROM (SELECT eventid, TimeStamp, filepath, TaskName, ROW_NUMBER() OVER(PARTITION BY eventid ORDER BY TimeStamp DESC) AS seq FROM eventrecords where ((TimeStamp >= '2010-4-1 00:00:00.000') and (TimeStamp <= '2010-4-21 00:00:00.000')) and TaskName like 'Review1%') AS T WHERE seq = 1 and case when (eventid in (Select eventid from eventrecords where TaskName = 'End') then 1 else 0) as bit end order by eventid When I try to run this, I get: "Incorrect syntax near the keyword 'then'." Not sure what I'm doing wrong. Haven't seen any examples anywhere quite like this. I should mention that eventrecords has a primary key, but it doesn't seem to help anything when I include it, and I am not permitted to change the table. (ugh) I've received one suggestion to use a cursor and temporary table, but am not sure how badley that would bog down performance when the report is running. Thanks in advance.

    Read the article

  • Use a single freemarker template to display tables of arbitrary pojos

    - by Kevin Pauli
    Attention advanced Freemarker gurus: I want to use a single freemarker template to be able to output tables of arbitrary pojos, with the columns to display defined separately than the data. The problem is that I can't figure out how to get a handle to a function on a pojo at runtime, and then have freemarker invoke that function (lambda style). From skimming the docs it seems that Freemarker supports functional programming, but I can't seem to forumulate the proper incantation. I whipped up a simplistic concrete example. Let's say I have two lists: a list of people with a firstName and lastName, and a list of cars with a make and model. would like to output these two tables: <table> <tr> <th>firstName</th> <th>lastName</th> </tr> <tr> <td>Joe</td> <td>Blow</d> </tr> <tr> <td>Mary</td> <td>Jane</d> </tr> </table> and <table> <tr> <th>make</th> <th>model</th> </tr> <tr> <td>Toyota</td> <td>Tundra</d> </tr> <tr> <td>Honda</td> <td>Odyssey</d> </tr> </table> But I want to use the same template, since this is part of a framework that has to deal with dozens of different pojo types. Given the following code: public class FreemarkerTest { public static class Table { private final List<Column> columns = new ArrayList<Column>(); public Table(Column[] columns) { this.columns.addAll(Arrays.asList(columns)); } public List<Column> getColumns() { return columns; } } public static class Column { private final String name; public Column(String name) { this.name = name; } public String getName() { return name; } } public static class Person { private final String firstName; private final String lastName; public Person(String firstName, String lastName) { this.firstName = firstName; this.lastName = lastName; } public String getFirstName() { return firstName; } public String getLastName() { return lastName; } } public static class Car { String make; String model; public Car(String make, String model) { this.make = make; this.model = model; } public String getMake() { return make; } public String getModel() { return model; } } public static void main(String[] args) throws Exception { final Table personTableDefinition = new Table(new Column[] { new Column("firstName"), new Column("lastName") }); final List<Person> people = Arrays.asList(new Person[] { new Person("Joe", "Blow"), new Person("Mary", "Jane") }); final Table carTable = new Table(new Column[] { new Column("make"), new Column("model") }); final List<Car> cars = Arrays.asList(new Car[] { new Car("Toyota", "Tundra"), new Car("Honda", "Odyssey") }); final Configuration cfg = new Configuration(); cfg.setClassForTemplateLoading(FreemarkerTest.class, ""); cfg.setObjectWrapper(new DefaultObjectWrapper()); final Template template = cfg.getTemplate("test.ftl"); process(template, personTableDefinition, people); process(template, carTable, cars); } private static void process(Template template, Table tableDefinition, List<? extends Object> data) throws Exception { final Map<String, Object> dataMap = new HashMap<String, Object>(); dataMap.put("tableDefinition", tableDefinition); dataMap.put("data", data); final Writer out = new OutputStreamWriter(System.out); template.process(dataMap, out); out.flush(); } } All the above is a given for this problem. So here is the template I have been hacking on. Note the comment where I am having trouble. <table> <tr> <#list tableDefinition.columns as col> <th>${col.name}</th> </#list> </tr> <#list data as pojo> <tr> <#list tableDefinition.columns as col> <td><#-- what goes here? --></td> </#list> </tr> </#list> </table> So col.name has the name of the property I want to access from the pojo. I have tried a few things, such as pojo.col.name and <#assign property = col.name/> ${pojo.property} but of course these don't work, I just included them to help convey my intent. I am looking for a way to get a handle to a function and have freemarker invoke it, or perhaps some kind of "evaluate" feature that can take an arbitrary expression as a string and evaluate it at runtime.

    Read the article

  • Is it Considered Good SQL practice to use GUID to link multiple tables to same Id field?

    - by Mallow
    I want to link several tables to a many-to-many(m2m) table. One table would be called location and this table would always be on one side of the m2m table. But I will have a list of several tables for example: Cards Photographs Illustrations Vectors Would using GUID's between these tables to link it to a single column in another table be considered 'Good Practice'? Will Mysql let me to have it automatically cascade updates and delete? If so, would multiple cascades lead to an issues? UPDATE I've read that GUID (a hex number) Generally takes up more space in a database and slows queries down. However I could still generate 'unique' ids by just having the table initial's as part of the id so that the table card's id would be c0001, and then Illustrations be I001. Regardless of this change, the questions still stands.

    Read the article

  • Difference between DataTable.Load() and DataTable = dataSet.Tables[];

    - by subbu
    Hi All , I have a doubt i use the following piece of code get data from a SQLlite data base and Load it into a data table "SQLiteConnection cnn = new SQLiteConnection("Data Source=" + path); cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); string sql = "select Company,Phone,Email,Address,City,State,Zip,Country,Fax,Web from RecordsTable"; mycommand.CommandText = sql; SQLiteDataReader reader = mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); cnn.Close();" In some cases when I try to load it Gives me "Failed to enable constraints exception" But when I tried this below given piece of code for same table and same set of records it worked "SQLiteConnection ObjConnection = new SQLiteConnection("Data Source=" + path); SQLiteCommand ObjCommand = new SQLiteCommand("select Company,Phone,Email,Address,City,State,Zip,Country,Fax,Web from RecordsTable", ObjConnection); ObjCommand.CommandType = CommandType.Text; SQLiteDataAdapter ObjDataAdapter = new SQLiteDataAdapter(ObjCommand); DataSet dataSet = new DataSet(); ObjDataAdapter.Fill(dataSet, "RecordsTable"); dt = dataSet.Tables["RecordsTable"]; " Can any one tell me what is the difference between two

    Read the article

  • Map NHibernate entity to multiple tables based on parent

    - by Programming Hero
    I'm creating a domain model where entities often (but not always) have a member of type ActionLog. ActionLog is a simple class which allows for an audit trail of actions being performed on an instance. Each action is recorded as an ActionLogEntry instance. ActionLog is implemented (approximately) as follows: public class ActionLog { public IEnumerable<ActionLogEntry> Entries { get { return EntriesCollection; } } protected ICollection<ActionLogEntry> EntriesCollection { get; set; } public void AddAction(string action) { // Append to entries collection. } } What I would like is to re-use this class amongst my entities and have the entries map to different tables based on which class they are logged against. For example: public class Customer { public ActionLog Actions { get; protected set; } } public class Order { public ActionLog Actions { get; protected set; } } This design is suitable for me in the application, however I can't see a clear way to map this scenario to a database with NHibernate. I typically use Fluent NHibernate for my configuration, but I'm happy to accept answers in more general HBM xml.

    Read the article

  • Broken tables in RichTextBox control (word wrap)

    - by Ben Robbins
    We are having problems with the Windows.Forms.RichTextBox control in Visual Studio 2008. We are trying to display text supplied as an RTF file by a 3rd party in a windows forms application (.NET 3.5). In this RTF text file there are tables, which contain text that spans multiple lines. The RTF file displays correctly when opened with either WordPad or Word 2003. However, when we load the RTF file into the RichTextBox control, or copy & paste the whole text (including the table) into the control, the table does not display correctly - the cells are only single line, without wrapping. Here are links to images showing the exact problem: Correctly displayed in WordPad Incorrectly displayed in RichTextBox control I have googled for solutions and 3rd party .net RTF controls without success. I have found this exact problem asked on another forum without an answer (in fact that's where the link to the images come from) so I'm hoping stack overflow does better ;-) My preferred solution would be to use code or a 3rd party control that can correctly render the RTF. However, I suspect the problem is that the RichTextBox control only supports a subset of the full RTF spec, so another option would be to modify the RTF directly to remove the unsupported control codes or otherwise fix the RTF file itself (in which case any information as to what control codes need to be removed or modified would be a huge help).

    Read the article

  • Reading XML outer tables

    - by Sathish
    I have an Xml file as shown below in which if i convert this to Dataset, the dataset will contain 3 tables with talble names Table Name1, Table Name2 and Table Name3 but i want to get this information without converting this to dataset Basically i want to get all the outer table names out of my excel. Please help me with the piece of code <Main Table> <Table Name1> <Something> <Something> <Something> <Something> </Table Name1> <Table Name2> <Something> <Something> <Something> <Something> </Table Name2> <Table Name3> <Something> <Something> <Something> </Table Name3> </Main Table>

    Read the article

  • Updating multiple related tables in SQLite

    - by PerryJ
    Just some background, sorry so long winded. I'm using the System.Data.SQLite ADO.net adapter to create a local sqlite database and this will be the only process hitting the database, so I don't need to worry about concurrency. I'm building the database from various sources and don't want to build this all in memory using datasets or dataadapters or anything like that. I want to do this using SQL (DdCommands). I'm not very good with SQL and complete noob in sqlite. I'm basically using sqlite as a local database / save file structure. The database has a lot of related tables and the data has nothing to do with People or Regions or Districts, but to use a simple analogy, imagine: Region table with auto increment RegionID, RegionName column and various optional columns. District table with auto increment DistrictID, DistrictName, RegionId, and various optional columns Person table with auto increment PersonID, PersonName, DistrictID, and various optional columns So I get some data representing RegionName, DistrictName,PersonName, and other Person related data. The Region, District and/or Person may or may not be created at this point. Once again, not being the greatest with this, my thoughts would be something like: Check to see if Region exists and if so get the RegionID else create it and get RegionID Check to see if District exists and if so get the DistrictID else create it adding in RegionID from above and get DistrictID Check to see if Person exists and if so get the PersonID else create it adding in DistrictID from above and get PersonID Update Person with rest of data. In MS SQL Server I would create a stored procedure to handle all this. Only way I can see to do this with sqlite is a lot of commands. So I'm sure I'm not getting this. I've spent hours looking around on various sites but just don't feel like I'm going down the right road. Any suggestions would be greatly appreciated.

    Read the article

  • MVC 2.0 - JqGrid Sorting with Mulitple Tables

    - by Billy Logan
    I am in the process of implementing the jqGrid and would like to be able to use the sorting functionality. I have run into some issues with sorting columns that are related to the base table. Here is the script to load the grid: public JsonResult GetData(GridSettings grid) { try { using (IWE dataContext = new IWE()) { var query = dataContext.LKTYPE.Include("VWEPICORCATEGORY").AsQueryable(); ////sorting query = query.OrderBy<LKTYPE>(grid.SortColumn, grid.SortOrder); //count var count = query.Count(); //paging var data = query.Skip((grid.PageIndex - 1) * grid.PageSize).Take(grid.PageSize).ToArray(); //converting in grid format var result = new { total = (int)Math.Ceiling((double)count / grid.PageSize), page = grid.PageIndex, records = count, rows = (from host in data select new { TYPE_ID = host.TYPE_ID, TYPE = host.TYPE, CR_ACTIVE = host.CR_ACTIVE, description = host.VWEPICORCATEGORY.description }).ToArray() }; return Json(result, JsonRequestBehavior.AllowGet); } } catch (Exception ex) { //send the error email ExceptionPolicy.HandleException(ex, "Exception Policy"); } //have to return something if there is an issue return Json(""); } As you can see the description field is a part of the related table("VWEPICORCATEGORY") and the order by is targeted at LKTYPE. I am trying to figure out how exactly one goes about sorting that particular field or maybe even a better way to implement this grid using multiple tables and it's sorting functionality. Thanks in advance, Billy

    Read the article

  • Updating multiple related tables in SQLite with C#

    - by PerryJ
    Just some background, sorry so long winded. I'm using the System.Data.SQLite ADO.net adapter to create a local sqlite database and this will be the only process hitting the database, so I don't need to worry about concurrency. I'm building the database from various sources and don't want to build this all in memory using datasets or dataadapters or anything like that. I want to do this using SQL (DdCommands). I'm not very good with SQL and complete noob in sqlite. I'm basically using sqlite as a local database / save file structure. The database has a lot of related tables and the data has nothing to do with People or Regions or Districts, but to use a simple analogy, imagine: Region table with auto increment RegionID, RegionName column and various optional columns. District table with auto increment DistrictID, DistrictName, RegionId, and various optional columns Person table with auto increment PersonID, PersonName, DistrictID, and various optional columns So I get some data representing RegionName, DistrictName,PersonName, and other Person related data. The Region, District and/or Person may or may not be created at this point. Once again, not being the greatest with this, my thoughts would be something like: Check to see if Region exists and if so get the RegionID else create it and get RegionID Check to see if District exists and if so get the DistrictID else create it adding in RegionID from above and get DistrictID Check to see if Person exists and if so get the PersonID else create it adding in DistrictID from above and get PersonID Update Person with rest of data. In MS SQL Server I would create a stored procedure to handle all this. Only way I can see to do this with sqlite is a lot of commands. So I'm sure I'm not getting this. I've spent hours looking around on various sites but just don't feel like I'm going down the right road. Any suggestions would be greatly appreciated.

    Read the article

  • Nhibernate/Hibernate, lookup tables and object design

    - by Simon G
    Hi, I've got two tables. Invoice with columns CustomerID, InvoiceDate, Value, InvoiceTypeID (CustomerID and InvoiceDate make up a composite key) and InvoiceType with InvoiceTypeID and InvoiceTypeName columns. I know I can create my objects like: public class Invoice { public virtual int CustomerID { get; set; } public virtual DateTime InvoiceDate { get; set; } public virtual decimal Value { get; set; } public virtual InvoiceType InvoiceType { get; set; } } public class InvoiceType { public virtual InvoiceTypeID { get; set; } public virtual InvoiceTypeName { get; set; } } So the generated sql would look something like: SELECT CustomerID, InvoiceDate, Value, InvoiceTypeID FROM Invoice WHERE CustomerID = x AND InvoiceDate = y SELECT InvoiceTypeID, InvoiceTypeName FROM InvoiceType WHERE InvoiceTypeID = z But rather that having two select queries executed to retrieve the data I would rather have one. I would also like to avoid using child object for simple lookup lists. So my object would look something like: public class Invoice { public virtual int CustomerID { get; set; } public virtual DateTime InvoiceDate { get; set; } public virtual decimal Value { get; set; } public virtual InvoiceTypeID { get; set; } public virtual InvoiceTypeName { get; set; } } And my sql would look something like: SELECT CustomerID, InvoiceDate, Value, InvoiceTypeID FROM Invoice INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.InvoiceTypeID WHERE CustomerID = x AND InvoiceDate = y My question is how do I create the mapping for this? I've tried using join but this tried to join using CustomerID and InvoiceDate, am I missing something obvious? Thanks

    Read the article

  • CSS Tables & min-width container?

    - by neezer
    <div id="wrapper"> <div id="header">...</div> <div id="main"> <div id="content">...</div> <div id="sidebar">...</div> </div> </div> #wrapper { min-width: 900px; } #main { display: table-row; } #content { display: table-cell; } #sidebar { display: table-cell; width: 250px; } The problem is that the sidebar isn't always at the right-most part of the page (depending on the width of #content). As #content's width is variable (depending on the width of the window), how to I make it so that the sidebar is always at the right-most part of its parent? Ex. Here's what I have now: <--- variable window width ----> --------------------------------- | (header) | --------------------------------- [content] | [sidebar] | | | | | | | | | | | | | And here's what I want: <--- variable window width ----> --------------------------------- | (header) | --------------------------------- [content] | [sidebar] | | | | | | | | | | | | | Please let me know if you need anymore information to help me with this issue. Thanks! PS - I know I can accomplish this easily with floats. I'm looking for a solution that uses CSS tables.

    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

  • 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

  • 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

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