I have two tables, product and sold_record. product contains my list of products, while sold_record contains all of my transactions. How can I select a distinct list of products that both customer A and customer B bought? ...
I have a table in Postgresql, I run a query on it with several conditions that returns multiple rows, ordered by one of the columns. In general it's: SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date DESC Now I'm only interested in getting the first and the last row from this query. I ...
I'm trying to insert data into my SQlite3 database (this works via the command line). I gave the data1.db file 777 permissions and still it says it's unable to open the file. I get this error: Warning: SQLite3::exec() [sqlite3.exec]: unable to open database file in /var/www/test.php on line 3 Using this code: $db = new SQLite3('./da...
On a project I work I have sometimes have to deal with changes on the table structure, like adding fields. Is there a script that can I use for generate a migration SQL file with only the changes? PHP, bash or Perl would be nice. The database is postgresql. ...
I have a table which contains records which are related in the following way: parent, child, grandchild. I would like to be able to run queries to do the following: retrieve all records that are descended from a particular record retrieve all records that are direct children of a particular record (i.e. only direct children) retrieve...
Hi, I'm writing an sql query on three tables which finds and displays categories and all the entries within each category For example Category 1 post 1 post 2 post 3 post 4 Category 2 post 5 post 6 Category 3 post 7 post 8 etc I have the categories displaying but can only get one item from each. Can anyone suggest a better approac...
Hi, i have an SQL table with extended properties on each column. Is there a way to access these from LINQ in c# using Linq2SQL? ...
Hi, I have two tables, 1 indexing the details of an email campaign. The second table details the recipients of this campaign, and whether they have responded to the email etc. I need to create a stored procedure that can update the status of the 'master record' in TBL1 when all the references(recipients) in TBL2 have a status >1. The ...
Is there a T-SQL statement to auto fill an empty column in a table with incremented values starting at one specific value? E.g. UPDATE A SET A.NO = ROW_NUMBER() OVER (ORDER BY A.NO) + @max WHERE A.NO is NULL This statement doen't work and I don't know how to make it run... Any help is appreciated! ...
Hi, This could easily be done using code, but I wondered if it could be done at the database level using SQL Server (2008). I have a table similar to below: CROP_ID YEAR_ PRODUCTION 1 1 0 1 2 300 1 3 500 2 1 100 2 2 700 I want to be able to run a que...
I want to find the largest sale for each of my employees (and display the name of the employee). In MySQL, it's pretty straightforward: select * from employee, sale where employee.id = sale.employee_id group by employee_id order by sale.total desc This does pretty much what one would expect, it would return a list of emplo...
I have a problem creating a database schema for the following scenario: (I’m not creating a dating site but just using this as an example) A user logs on to a dating site and is given a multiple selection for the hair colour they’d like their date to have: This is easy enough to model with the three tables below: Tables: User {key}...
I'm trying to fetch some data from a database. I want to select an employee, and if available, all appointments and other data related to that employee. This is the query: SELECT TA.id, TEI.displayname, TA.threatment_id, TTS.appointment_date, TEI.displayname FROM tblemployee AS TE LEFT OUTER Join tblappointment AS T...
I've got an sql query that selects data from several tables, but I only want to match a single(randomly selected) row from another table. Easier to show some code, I guess ;) Table K is (k_id, selected) Table C is (c_id, image) Table S is (c_id, date) Table M is (c_id, k_id, score) All ID-columns are primary keys, with appropriate FK ...
UPDATE Evidently I didn't include enough data, sorry! What I need to do is set 'campaign_Status' = 6 when 'campaign_Date' is more than 90 days old. Hi, I have a column (campaign_Date) which stores a DATETIME. Using a Stored Procedure I need to check if the stored date is 90 days old (or more). Any help would be great. Thanks. ...
I have a bit of a puzzle (at least for me) which I am hoping is mostly because I am not yet an SQL master of the universe. Basically I have three tables: Table A, Table B, and Table C. Table C has a FK (Foriegn Key) to Table B, which has FK to Table A. (Each of these is many to one) I need to remove an entry from Table A and of cours...
I'm creating a table with 30-50 columns. There are about 200K of these rows. Is it recommended to store this data in separate tables? Are there performance issues when you have this many columns. I'll explain a bit about the table. I have to store all sports games over the past 10 years (basketball, baseball, football, hockey). Fo...
I have a trigger on INSERT in MySQL 5.1. I want to know, how many times per second is it called. How can I do this? ...
Not sure if that makes sense, but say I have this code... $updateSql = oci_parse($conn, 'update "table" SET "column"=:column where "Unique_Record_Id" = :Unique_Record_Id'); OCIBindByName($updateSql, ":Unique_Record_Id", $absenceData['Unique_Record_Id']); OCIBindByName($updateSql, ":column", $column); if(oci_execute($updateSql)){ // np...
I need to generate a SQL update command in a *.sdf file. The *.sdf file is deployed using Visual studio. I am looking to generate a script which fills in a table the maximum number of records which is about 2MBytes. any ideas for how I can do this i.e. connect remotely to the database and script filling in the database with a dummy rec...