I have a table connecting principals to their roles. I have come upon a situation where I need to add a role for each user. I have a statement SELECT id FROM principals which grabs a list of all the principals. What I want to create is something like the following: INSERT INTO role_principal(principal_id,role_id) VALUES(SELECT id FROM ...
I'm trying to put together a simple system for a user to generate a list of users to whom surveys will be sent. The list generation may depend on various constraints. For example, "we only want people from the U.S. and Canada" or "we only want people who have a level 2 or level 3 membership." This part is pretty easy and I've set up the...
I have a stored procedure that takes a product_id and returns some data about the product. I would like to make a query or procedure that maps this stored proc over a "select * from products" query. Is there a way to do this? ...
I need a query that returns subtotals by MemberName(PersonID) but broke out into the 2 different ContactTypes(11 & 12 under IsFaceToFace). The below query gets me the base data I need without any subtotals. I tried messing around with WITH ROLLUP and PARTITION BY but those are new to me and never worked completely right. I am sure I...
does it slow down the query time to use a lot of unique indexes? i dont have that many im just curious, i think i have heard this some where id (primary auto_increment) username (unique) password salt (unique) email (unique) ...
I have this query and if it returns successful, I want another function to process, if not, do not process that function. Here is the code for running the query global $DB; $DB->query("UPDATE exp_members SET group_id = '$group_id' WHERE member_id = '$member_id'"); I imagine it is something like... if($DB) { //success } else { ...
i am using this to collect comments made abt a particular user. i want to display 7 comments on each page and want to enable pagination. what would be the steps of implementation. sorry. n00b question. $query = "SELECT msg, user_id, comment_time FROM comments WHERE aid = '$aid' ORDER BY comment_time DESC"; $result = mysql_query...
Via this link, I know that a GUID is not good as a clustered index, but it can be uniquely created anywhere. It is required for some advanced SQL Server features like replication, etc. Is it considered bad design if I want to have a GUID column as a typical Primary Key ? Also this assumes a separate int identity column for my clustering...
So I am trying to write something like this: SELECT s.CompanyID, s.ShareDate, s.OutstandingShares, s.ControlBlock FROM ( SELECT MAX(ShareDate) AS Sharedate, CompanyID FROM ShareInfo WHERE (ShareDate <= @filter_date) GROUP BY CompanyID ) AS si INNER JOIN tblShareInfo AS s ON s....
I have three tables, books, tags, and taggings (books-xref-tags): books id | title | author 1 | Blink | Malcolm Gladwell 2 | 1984 | George Orwell taggings book_id | tag_id 1 | 1 1 | 2 2 | 1 2 | 3 tags id | name 1 | interesting 2 | nonfiction 3 | fiction I'd like to ...
Hi, I would like to know what's the best way to get the last entry of a table with JPA. In Sql, what I'm looking for would be like: select id from table order by id desc limit 1 I was thinking about model.count() but that sounds more like a hack than a good solution ;) ...
I have a SQL Server database with these pages: +------------+--------------+-------------------------------+ | pageid | parentid | title | +------------+--------------+-------------------------------+ | 1 | null | Home | +------------+--------------+----------...
This is probably something very simple but I'm looking for the optimal way of retrieving all Products by Tag, so to speak. This is with Spree, so I should stick to the way they have modeled their data. It's actually Product and Taxon (like category, brand, etc.) So if Product has_and_belongs_to_many :taxons and Taxon has_and_belongs_t...
I have looked all over the internet for an answer to my question to why my sql statement returns false. I checked it out on the sql validator over at mimer and all I got was that I used the reserved word name. There should be something in my database that matches this so here it is: Here is how I create the sql statement: $title = 'S...
Having dutifully normalised all my data, I'm having a problem combining 3NF rows into a single row for output. Up until now I've been doing this with server-side coding, but for various reasons I now need to select all rows related to another row, and combine them in a single row, all in MySQL... So to try and explain: I have three tab...
I'm not sure if one can do this but I need to have foreign key reference 2 tables. Table1 has 2 columns (A PK, B) Table2 has 2 columns (C PK, D) Table3 has 3 columns (A PK, B PK, E) and is made up of the first two table. What I am hoping to do is something like the following: create table Table3 ( A Varchar2 (4), C Varch...
Hello, I have a table which called people, and I have there 2 columns, name and age How can i select the names with age above 15 for example ? Thanks ...
I have a table where I want one value to be set to the same as another when inserting. Here's the table in question; CREATE TABLE categories ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(30) NOT NULL, root INT NULL DEFAULT id, PRIMARY KEY(id) ); I want the column ´root´ to get the same value as the ´id´ column gets when i...
Hi, I want to check if all the triggers are on or not on the SQL Db I am working on. How can I achieve that? Thanks, ...
We've got a view that's defined like this CREATE VIEW aView as SELECT * from aTable Where <bunch of conditions>; The "value" of the view is in the where-condition, so it is okay to use a Select * in this case. When a new column is added to the underlying table, we have to redefine the view with a CREATE OR REPLACE FORCE VIEW aView...