union

a simple way to sum a result from UNION in MySql

I have a union of three tables (t1,t2,t3). Each rerun exactly the same number of records, first column is id, second amount: 1 10 2 20 3 20 1 30 2 30 3 10 1 20 2 40 3 50 Is there a simple in sql way to sum it up to only get: 1 60 2 80 3 80 ...

PHP a href union

Hello. So i have this script: <?php $query = mysql_query(" SELECT meetup AS text, id AS theID, dato FROM member_meetups WHERE username = '$pusername' UNION SELECT navn AS text, id AS theID, dato FROM member_film WHERE username = '$pusername' UNION SELECT title AS text, id AS theID, dato FROM member_tutorials WHERE username = '$puserna...

How to combine two result sets from one table sorted independently using one SQL query?

This is a simplified task which I have to solve in real project. In this project data is stored in HSQLDB. Data is accessed using JDBC. I have one table: name | flag ----------- aa | 1 bb | 0 cc | 1 dd | 0 ee | 1 ff | 0 I need to compose query to get the following table: name | flag ----------- aa | 1 cc | 1 ee | 1...

UNION inside IF EXISTS statement not working

SELECT A, B, C FROM TUser UNION IF EXISTS(SELECT dataUserId FROM TUserData WHERE DataId = @dataId AND UserId = @userId) BEGIN SELECT @dataUserId = dataUserId FROM TUserData WHERE DataId = @dataId AND UserId = @userId SELECT A, B, C FROM TUser WHERE UserId = dataUserId END ...

SQL Server: SELECT rows with MAX(Column A), MAX(Column B), DISTINCT by related columns

Scenario: Table A MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/1/2010, 'Fred', null, null 2, 1/2/2010, 'Barney', 'Mr. Slate', 1/7/2010 3, 1/3/2010, 'Noname', null, null Table B MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/3/2010, 'Wilma', 'The Great Kazoo', 1/5/2010 2, 1/4/2010, 'Betty', 'Dino', 1/...

How to get records from subquery using union in linq

sql = " SELECT * FROM userDetail "; sql += " WHERE userId IN "; sql += " (SELECT friendId FROM userFriends "; sql += " WHERE approvalStatus='True' AND userId=" + userId; sql += " UNION"; sql += " SELECT userId FROM userFriends "; sql += " WHERE approvalStatus='True' AND friendId=" + userId + ")"; ...

Will SQL Server 2000 be able to use indexes in a view over a union of a view across linked servers.

I have the following scenario: 2 DB servers (Linked to each other) DB1 has a (large) table with transaction records DB2 has a (not quite as large yet) table with transaction records (of a similar nature but without some of the data as it is a different system) There are a bunch of reports that pull records out of the transaction table ...

MySQL - optimising selection across two linked tables

I have two MySQL tables, states and trans: states (200,000 entries) looks like: id (INT) - also the primary key energy (DOUBLE) [other stuff] trans (14,000,000 entries) looks like: i (INT) - a foreign key referencing states.id j (INT) - a foreign key referencing states.id A (DOUBLE) I'd like to search for all entries in trans with...

Implementing a c/c++ style union as a column in MySQL

Friends, I have a strange need and cannot think my way through the problem. The great and mighty Google is of little help due to keyword recycling (as you'll see). Can you help? What I want to do is store data of multiple types in a single column in MySQL. This is the database equivalent to a C union (and if you search for MySQL and...

C++ union assignment, is there a good way to do this?

I am working on a project with a library and I must work with unions. Specifically I am working with SDL and the SDL_Event union. I need to make copies of the SDL_Events, and I could find no good information on overloading assignment operators with unions. Provided that I can overload the assignment operator, should I manually sift thro...

python union of 2 nested lists with index

I want to get the union of 2 nested lists plus an index to the common values. I have two lists like A = [[1,2,3],[4,5,6],[7,8,9]] and B = [[1,2,3,4],[3,3,5,7]] but the length of each list is about 100 000. To A belongs an index vector with len(A): I = [2,3,4] What I want is to find all sublists in B where the first 3 elements are equal...

Union Distinct rows but order them by number of occurrences in mysql

Hi I have the following query: SELECT o.id,o.name FROM object o WHERE ( o.description LIKE '%Black%' OR o.name LIKE '%Black%' ) UNION ALL SELECT o2.id,o2.name FROM object o2 WHERE ( o2.description LIKE '%iPhone%' OR o2.name LIKE '%iPhone%' ) Which procude the following: id name 2 ...

count on LINQ union

I'm having this link statement: List<UserGroup> domains = UserRepository.Instance.UserIsAdminOf(currentUser.User_ID); query = (from doc in _db.Repository<Document>() join uug in _db.Repository<User_UserGroup>() on doc.DocumentFrom equals uug.User_ID where domains.Contains(uug.UserGroup) select doc) .Union(fro...

Can't seem to get .Union to work (merging 2 array's together, exclude duplicates)

I want to combine two array's, excluding duplicates. I am using a custom class: public class ArcContact : IEquatable<ArcContact> { public String Text; public Boolean Equals(ArcContact other) { if (Object.ReferenceEquals(other, null)) return false; if (Object.ReferenceEquals(this, other)) return true; ...

Union and If Exists - not working together - Please help

I need to get dummy values if they do no rows returned from table. The If exists works by itself, but gives error with a Union. Can someone please guide me with a solution or a workaround? create table test1 (col1 varchar(10)) create table test2 (col1 varchar(10)) create table test3 (col1 varchar(10)) insert test1 values ('tes...

mySQL experts - need help with 'intersect'

I know that mySQL 5.x does not support INTERSECT, but that seems to be what I need. Table A: Products (p_id) Table B: Prod_cats (cat_id) - category info (name, description, etc) Table C: prod_2cats (p_id, cat_id) - many to many prod_2cats holds the many (1 or more) categories that have been assigned to Products (A). Doing a query/fi...

Union on two tables with a where clause in the one

Currently I have 2 tables, both of the tables have the same structure and are going to be used in a web application. the two tables are production and temp. The temp table contains one additional column called [signed up]. Currently I generate a single list using two columns that are found in each table (recno and name). Using these two ...

Joining two similar tables in MySQL

Hello, I have two tables with similar columns - let's say table A with column LABEL_A and table B with column LABEL_B. The data types of LABEL_A and LABEL_B are same. How can I select LABELs from both tables in a single query? (So the the result of the query contains single column LABEL containing data from LABEL columns of both tables...

MySQL UNION query from one table + ORDER BY

I have one table with two queries and I need to sort it with descending type using ORDER BY. Here is my MySQL query that does not work properly: (SELECT `text` FROM `comments` WHERE user_fr='".$user."' && archive='1' ORDER BY `is_new_fr` DESC) UNION (SELECT `text` FROM `message` WHERE user_to='".$user."' && archive='1' ORDER B...

Union of complex polygons

Given two polygons: POLYGON((1 0, 1 8, 6 4, 1 0)) POLYGON((4 1, 3 5, 4 9, 9 5, 4 1),(4 5, 5 7, 6 7, 4 4, 4 5)) How can I calculate the union (combined polygon)? Dave's example uses SQL server to produce the union, but I need to accomplish the same in code. I'm looking for a mathematical formula or code example in any language that ...