Hello, I'm making a query to grab the latest B record for each A record that is before the A record date. (I hope that makes sense) And I have no idea how to do it! Please help! My Final view will be AID, AData, ADate, BData Rough table schema table A ( AID, ADate, AData ) table B ( BID, AID, BDate, BData ) Things to note are:...
I am using MySQL. Here is my schema: Suppliers(sid: integer, sname: string, address string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) (primary keys are bolded) I am trying to write a query to select all parts that are made by at least two suppliers: -- Find the pids of parts s...
For some reason, this MySQL fails: CREATE SCHEMA IF NOT EXISTS `partB` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `partB`; CREATE TABLE Employees ( ssn CHAR(11), Name CHAR(30), mlot INTEGER, PRIMARY KEY(ssn)) ENGINE = InnoDB; CREATE TABLE Dept_Mgr ( did INTEGER, dname CHAR(20), ssn CHAR(11) NOT NULL, PRIMARY KEY (did...
how do you prevent the entry of duplicate records in php mysql? validating input before insertion to the table. i am currently building a user registration section for my website but want to present them with an error if a duplicate username or email address is found in the database. any ideas would be appreciated... thanks! ...
I have a database full of band names. Many of these start with "The" (eg "The Strokes"). Should I split out the "The" portion into its own DB field or keep it all as one name and handle "The" sorting/searching with SQL? Originally I had split out "The" from the beginning of the band names and put it in a boolean flag field. I did thi...
I'd like to find a replacement for Crystal Reports. Oh how I dislike CR. Ideally I'd like to find a PDF generation tool that has the following: Solid API in .NET Speed: we'd like to generate 1000-2500 pdfs per day WYSIWYG editor hotlinking of image files (logos) good widow/orphan control reasonably good typography control (kernin...
I am in the beginning stages of developing a solution to decrypt the hard drives of a few thousand laptops. I have a thought that it would be useful to create a parent application that would handle all of the database calls, client updates, client commands and status updates. This application would either run as a service or as a .EXE f...
I sort of understand the concept of database normalization but always have a hard time explaining it in plain English especially for a job interview. I have read the wikipedia post, but still find it hard to explain the concept to none developers. "Design a database in a way not to get duplicated data" is the first thing that comes to ...
how to create the schema, AND USING SCHEMA HOW TO create tables under the schemas ...
SELECT column FROM table WHERE column is largest? ...
so here's what I accomplished right now: given email1 and email2, get all emails that are not equal to email1 create a list of them check if email2 exists in the list if it does return false, if it does not return true Is there a way to accomplish this via a query instead of creating a list and searching if email2 exists in it? (beca...
From the MSDN docs for create function: User-defined functions cannot be used to perform actions that modify the database state. My question is simply - why? Yes, a UDF that modifies data may have potentially unwanted side-effects. Yes, there is overhead involved if a UDF is called thousands of times. But that is the whole point of d...
i have a table that has id,fromdate and todate as columns. i need to select the ids that is between 2dates specified by the user. for example: ID FromDate ToDate 1 2010-01-10 2010-01-13 2 2009-03-20 2010-01-09 so if the user entered datefrom=2000-00-00 and dateto=2009-03-21 i return : ID 2 if the user entered from=2009...
I have the following code: As you can see, i need to pass a parameter to Field2, but i also need that parameter to be ablo to handle the ""all values" option, for example, if i assign "foo" to the parameter, the query will return every record where Field2 = "foo"... but i also want to be able to pass a wildcard, or something to tell tha...
I'm writing an R script to get some database data and then do stuff with it, using the RODBC package. Currently all my sqlQuery commands are one long string; stsample<-sqlQuery(odcon, paste"select * from bob.DESIGNSAMPLE T1, bob.DESIGNSUBJECTGROUP T2, bob.DESIGNEVENT T3, bob.CONFIGSAMPLETYPES T4 WHERE T1.SUBJECTGROUPID = T2.SUBJECTGROU...
Hi, I'm trying to implement something similar to Ruby on Rails' polymorphic relationships. I have the following three tables : Events Users Organisations An event can be owner by either a user or an organisation, so my Events table includes the columns: owner_type and owner_id. I can easily list all events that belong to either users...
I have a WPF question. I have 2 textboxes and an image control in a WPF Form. The image control has an image in it. I want to insert the contents of each of the text boxes and the image in the 3 separate columns in an SQL database. The text boxes feed into varchar columns while the image itself is loaded into a column with datatype ima...
In a .Net web application I use the public DataRow[] Select(string filterExpression) method in many places. Due to a last minute change characters such as ' and " are now valid input. What options do I have, is there a way to change the filterExpression in an adequate way and still preserve the Select functionality on the datatable, can...
I need to write a bit of SQL which parses the second word of any given string, or if there is no space, the entire string. The logic is 'XXX YYYYY ZZZZZ' would return YYYY, 'XXX YYY' would return YYY and 'XXXXXX' would return XXXXXX. Any suggestions? Is there a way of using SUBSTR and the 2 positions within the string? Rather an u...
Is it more performant to use a Prepared Statement with one question mark in it fifty times, or to use a Prepared Statement with fifty question marks in it once? Essentially is Where Person = ? or Where Person IN (?, ?, ?, ...) better? Example Say you have a table with a column, country, and then a few relational tables away you have t...