I have a problem querying multiple tables in MySQL and am currently at my wits end. I have unique IDs in each table, and am using an INNER JOIN to combine them; I am quite new to SQL and this may be the wrong method, hence me posting here: Query: SELECT res.UserID, res.QuizID, res.QuizResult, u.UserID, u.UserLogin, q.QuizID, q.QuizNam...
Hello, I have been looking for a while now but I can not find an easy solution for my problem. I would like to duplicate a record in a table, but of course, the unique primary key needs to be updated. I have this query: INSERT INTO invoices SELECT * FROM invoices AS iv WHERE iv.ID=XXXXX ON DUPLICATE KEY UPDATE ID = (SELECT MAX(ID)+1 F...
I'd like to create DetachedCriteria which would perform something similar to the following SQL code: select * FROM PRICELIST pl LEFT OUTER JOIN PRICELISTDURATIONSHIFT sh ON sh.PRICELISTID = pl.ID WHERE sh.CUSTOMERID = :cust AND nvl(sh.DATEFROM, pl.DATEFROM) <= :dt ORDER BY nvl(sh.DATEFROM, pl.DATEFROM) DESC I'm able to left-outer-jo...
Is there any way to get all tables that have foreign keys to another table in oracle with a query? ...
I thought full-text search would let me do exact phrase searching in a more optimized way than a LIKE predicate, but I'm reading that it doesn't do that exactly. Is "LIKE" the most efficient way to search thousands of rows of TEXT fields in a table for a literal string? It's got to be exact matching... ...
Give the following simple table structure: Departments PK - DeptID DeptName -------------------------- 1 Department 1 2 Department 2 3 Department 3 4 Department 4 Groups PK - GroupdID DeptID -------------------------- 1 1 2 1 3 3 4 ...
Standard EAV schema : One column for Entity ID, one for Attribute ID, one for Value ID. Historical EAV schema : Add an additional column(s) for times/date-ranges At run time, certain rows will be excluded. There may be 0, 1, or many rows returned per entity, per attribute. We only want the most recent value for each attribute remainin...
lets say i have 2 tables: products (just product ID and name) and sales (sale ID, product ID, amount, date) now, given a start date and end date, i want to sum for every product its total sales amount in the given time frame notice that naturally some products will just have zero sales how should i write this query? ...
Hello is there any chance to create temporary stored procedure or function on MS SQL 2005? I would like to use this stored procedure only in my query so after execution it will be gone. I have a query I would like to EXEC against some data. But for every table I will process this command I need to change some parts of it. So i thought I...
I'm using jquery for modal dialogs. I want to open model dialog from one page and to send some additional query string to modal dialog page. something like this: <asp:HyperLink ID="hypClientSearch" runat="server" NavigateUrl="~/SomePage.aspx?KeepThis=true&additionalQS='<%= txtBox.Text %>'&TB_iframe=true&height=650&width=800&modal=true"...
I have a table with primary key in my MS SQL Server 2005 table. I would like to disable it. Now i get error: Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'dbo.Table'. I would like this error not to occur and to work with PRIMARY KEY like with normal column without constraint and than restore this...
I have a table in MS SQL 2005. And would like to do: update Table set ID = ID + 1 where ID > 5 And the problem is that ID is primary key and when I do this I have an error, because when this query comes to row with ID 8 it tries to change the value to 9, but there is old row in this table with value 9 and there is constraint violation...
In Postgre, why does select abc from (select 1) as abc produces: (1) and select * from (select 1) as abc produces: 1 That's really strange to me. Is that the case with MySQL, Oracle, etc? I spent hours figuring out why my conditions were failing... ...
Using PHP and MySQL, I want to query a table of postings my users have made to find the person who has posted the most entries. What would be the correct query for this? Sample table structure: [id] [UserID] 1 johnnietheblack 2 johnnietheblack 3 dannyrottenegg 4 marywhite 5 marywhite 6 johnnietheblack I woul...
We are building an application which requires a daily insertion of approximately 1.5 million rows of data per table. We have 16 tables. We keep track of 3-day historical data including the current day's data. The application is done using C#; on the server side, we run an exe that fills the data tables during market hours (4.5 hours), a...
Say if @news_writers is an array of records. I then want to use @news_writers to find all news items that are written by all the news writers contained in @news_writers. So I want something like this (but this is syntactically incorrect): @news = News.find_all_by_role_id(@news_writers.id) Note that class Role < ActiveRecord::Base ...
I am trying to come up with a query to report revenue. It will require 2 tables: clicks and offers. Revenue is calculated by the number of conversions * commission for the offer. Conversions are stored in the clicks table in a field called "conversionDate", and the commission for each offer is stored in the offers table. There needs ...
I am currently working on building a baseball website. On one side of the page I want to display information on the next upcoming game. I am using mysql ver. 5.0.45. My table is built as so: opposingTeam, date, time, score Date is in the mysql date format (yyyy-mm-dd) I have found the datediff(date1,date2) command and tried experiment...
Hi, I'm trying to run a query, but I get an "unknown column 'MyField' in 'where clause'" error. This is my query: SELECT id, sum(lineValue * quantity) as TotalLine FROM myTable WHERE (TotalLine BETWEEN 10 and 500) group by id How can I perform a similar query? Thanks ...
Dim db2 As New NewsDataContext Dim filter As String = "System" Dim Msg = From m In db2.Blog_Messages _ **From c In db2.Blog_Comments _** Join u In db2.users On m.userID Equals u.userid _ Where m.userID.Equals(filter) _ **Where c.MessageID.Equals(myRec) _** Group c By c.MessageID I...