Consider a table like this: | Name | Version | Other | | ---------------------|-------| | Foo | 1 | 'a' | | Foo | 2 | 'b' | | Bar | 5 | 'c' | | Baz | 3 | 'd' | | Baz | 4 | 'e' | | Baz | 5 | 'f' | -------------------------------- I would lik...
How do you combine multiple select count(*) from different table into one return? I have a similar sitiuation as this post but I want one return. I tried Union all but it spit back 3 separate rows of count. How do you combine them into one? select count(*) from foo1 where ID = '00123244552000258' union all select count(*) from foo2 ...
We have an email service that hosts close to 10000 domains such that we store the headers of messages in a SQL Server database. I need to implement an application that will search the message body for keywords. The messages are stored as files on a NAS storage system. As a proof of concept, I had implemented a SQL server based searc...
I am designing a new laboratory database with MANY types of my main entities. The table for each entity will hold fields common to ALL types of that entity (entity_id, created_on, created_by, etc). I will then use concrete inheritance (separate table for each unique set of attributes) to store all remaining fields. I believe that this...
Hi guys. Need your suggestion on php/mysql software. I develop PHP websites using Dreamweaver, I also have to use phpmyadmin, sql query editor (to work with database) and WinSCP (to upload/backup files over SFTP). Could you suggest me please some software which contains all in one (PHP syntax highlighting, db connections and running sql...
I have the following tables in SQL Server 2005 ReceiptPoint: ID (PK), Name GasIndexLocation: Location (PK) ReceiptPointIndexLocation: ReceiptPointId (FK), IndexLocation (FK), StartDate, EndDate On any given day, a ReceiptPoint can be mapped to 1 or more IndexLocations. I need to be able to, for a given day, see what IndexLocatio...
Table – Employee.Status.XREF Client Employee Date 198 Jon Doe 3/1/2009 198 Jon Doe 1/1/2009 Table – Employee.Status.History Client Employee Date Status 198 Jon Doe 5/21/2009 T 198 Jon Doe 3/1/2009 A 198 Jon Doe 1/1/2009 P Only query records in both Employee.Status.History and Employee.Status.XREF where Client, Employe...
Hi, I have 3 tables - venues, users, and updates (which have a integer for rating) - and I want to write a query that will return a list of all my venues as well as their average ratings using only the most recent update for each person, venue pair. For example, if user 1 rates venue A once at 9 am with a 4, and then rates it again at 5...
Is it possible to suppress warnings generated by T-SQL scripts? If so, how? I know I can turn of the 'records affected' messages with SET NOCOUNT ON but is there an equivalent for warnings? Eg: Warning: Null value is eliminated by an aggregate or other SET operation. If I'm expecting these errors, it helps to sift the real errors ...
How can you have IN -clause in ON -clause with LEFT JOIN? I am trying to select the title of a question, tag(s) for the question and question_id for the question from my database such that question_id is 14. I expect to the tags and title of the question with question_id 14. The output should look like title | question_i...
I have a table with records that look like this: CREATE TABLE sample ( ix int unsigned auto_increment primary key, start_active datetime, last_active datetime ); I need to know how many records were active on each of the last 30 days. The days should also be sorted incrementing so they are returned oldest to newest. I'm using ...
Hello everyone, I am wondering what ports are used by SQL Server database engine? I need such port number to write configuration scripts to grant access to specific port of the machine installed with SQL Server to make it safe. A related question is whether SQL Server database engine will use one static port number to serve all client r...
I saw couple of tools but all are paid, I have only one database in MySQL that we want to convert in MSSQL. Suppose i create the schema by myself then is it possible to use OPENROWSET to open connection to mysql and import all data into sql with following statement? INSERT INTO Customers SELECT * FROM ( OPENROWSET ( 'm...
I want to sort my result form mysql by date. I use the query like this: <?php $date = $db->get_query("select distinct created_date from comments"); $condate = ''; for($i=0; $i < count($date); $i++) { $condate = $date[$i]['created_date']; $data = $db->get_query("select id,created_date from comments where created_date='$condate' order...
I have a table of pages in my database, each page can have a parent as below: id parent_id title 1 0 Home 2 0 Sitemap 3 0 Products 4 3 Product 1 5 3 Product 2 6 ...
I'm trying to use a view to create an ADO.NET entity using a view. However, that view does not have a single column that is NOT NULL. One thing that occurred to me was to create a NOT NULL column on the view to use as a 'primary key' for the view. This worked, but the field is still reported as NULL. Is there a way to force or trick SQ...
Hi guys, I wonder if it would be possible to query two tables as pictured bellow, and achieve the following result. The two tables are (note that the page_id in the pages_contents table corresponds to the id column in the pages table.): Wanted result (the two tables are joined by the biggest version number): Thanks in advance for...
What is the advantages of only one null for unique constraint is SQL Server? ...
Hello, I'm terrible at SQL. I do not know if what I am trying to do is possible. But, because of our data structure, I need to solve this problem this way or do a massive architectural change. I am trying to count the number of 'Provinces' (a.k.a States) for a Country. However, there are just a few Provinces that need to be ignored fro...
I am in the preparation stages of writing my first real program. I've designed a set of tables to store my financial data. I know 1) what data goes where in the tables 2) what relationships exist between tables and fields 3) what queries are needed 4) what the forms should look like to write data to the tables 5) what output I need an...