union

How to union queries from the same table in MySQL

Hi all. I have two sets of results: SELECT name, count(appearance) as countA from table where results = '1' SELECT name, count(appearance) as countB from table where results = '2' And I wanted to combine them side by side, like this: +---------+---------+---------+ | col_1 | countA | countB | +---------+---------+---------+ | Jo...

How can I "compress" my tree into aggregate data on up the hierarchy in Oracle?

I have to get a list of limits for a the sub-parts of a level 1part. Limits for some parts are null, and for those null parts, I have to query the level 2 parts that are on that part and give the MIN of the limits of those level 2 parts. For some of the level 2 parts, I have to get the MIN of their sub-parts (the level 3 parts on that ...

Which is faster: Union or Concat? (C# Linq)

I don't care about the order of the elements. http://msdn.microsoft.com/en-us/library/system.linq.enumerable.union.aspx http://msdn.microsoft.com/en-us/library/bb302894.aspx ...

Linq and nullable key relationships affecting UNION operation

Here is an example: Lets say I have 3 tables, Countries, People and Cities. City records have a non-nullable foreign key field identifying a country. People records have a nullable foreign key field identifying a country - they may be from an Eastern European country that no longer exists. I want to create a combined list of countries ...

MySQL Union Query w/ Left Join - Order By Error ?

We have a Union Query. Here's a basic (similar) example: SELECT a.Name, b.Info FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE a.Name LIKE "a%" UNION SELECT a.Name, b.Info FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE a.Name LIKE "b%" ORDER BY a.Name, b.Info; I am receiving an error that says "Unknown column 'b.Info' in 'order clause'". When...

Connecting to Remote Server Database through view or synonym causes all queries to return all rows, but connecting to table does not, why?

So here the scneario. We have a database of read only records that contains about 3 million rows. This database is used by all of our application databases for data lookup. We placed this database on a new server and used a synonym to point to it in the local database. The synonym was pointing to a view on the remote server. This allow...

SQL - Identifying source table from UNION query

Hi, I'm building an RSS feed in PHP which uses data from three separate tables. The tables all refer to pages within different areas of the site. The problem I have is trying to create the link fields within the XML. Without knowing which table each record has come from, I cannot create the correct link to it. Is there a way to solve...

MySQL Combining UNION and LIMIT Operations

Hi, I have a JOBS and a COMPANIES table, and I want to extract 20 jobs that meet the following criteria a) jobs only from 2 named companies b) there can at most be 10 jobs per company I have tried the following SELECT with UNION DISTINCT, but the problem is that the LIMIT 0,10 applies to the whole result set, whereas I want it to apply...

SQL: cascade UNION and JOIN

Hi I have a union opertaion between two tables SELECT ID_1, name_1, surname_1, FROM T_ONE UNION SELECT ID_2, name_2, surname_2 FROM TABLE_2 now I want to join the result of this JOIN operation with another one table or even with all TABLE_1. How can I handle this new table result of the UNION. for ex after the previous UN...

Why only one record after union?

mysql> select count(id) total from applicants; +-------+ | total | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) mysql> select count(id) total from jobs; +-------+ | total | +-------+ | 0 | +-------+ 1 row in set (0.00 sec) mysql> select count(id) total from applicants union select count(id) total from jobs; +-------+ | tot...

How do I select one parent row and additional rows for its children without a UNION?

I have a parent and child table and want to create a select statement that, given a parent id, returns a row for that parent and additional rows for every child. Doing a left join is not giving me a row for the parent by itself when one or more children exist. I know this can be done with a UNION but I'm looking for a solution that does ...

Combining 2 different but fairly similar tables

I have 2 tables that are similar but not the same so a union is not a possibility. I need to combine the tables bearing in mind there's about 40 columns where only 20 are common to both. Any ideas on the best approach? Table1 ActivityCategory ActivityType Nationality Language ----------------------------------------------------...

Getting rid of duplicate results in MySQL query when using UNION

Hi everyone, I have a MySQL query to get items that have had recent activity. Basically users can post a review or add it to their wishlist, and I want to get all items that have either had a new review in the last x days, or was placed on someone's wishlist. The query goes a bit like this (slightly simplified): SELECT items.*, reacti...

SQL query to collect entries from different tables - need an alternate to UNION

Hi guys, I'm running a sql query to get basic details from a number of tables. Sorted by the last update date field. Its terribly tricky and I'm thinking if there is an alternate to using the UNION clause instead...I'm working in PHP MYSQL. Actually I have a few tables containing news, articles, photos, events etc and need to collect al...

What kind of SQL join is this?

Say for some reason I have employees in two separate tables, employee1 and employee2 I just want to add them together, as if they are stacked on top of each other. something like: select all from employee1 and employee2 where name = bubba i know im generalizing, this will be in postgres eventually so if there are any specifics there...

Index on UNION query?

i've got this union query: (SELECT INSTALLER, INSTALLTIME, RESULT, JOBNUMBER, HONAME, ADDRESS, CITY, STATE, ZIP, NOTES, SMNOTES, '' as priority, PAFS, upsell, TERM, MMRUPGRADE, WARRANTY, EFT FROM ACCOUNTS WHERE INSTALLDATE = '$date' && FUNDINGSTATUS !='DEAD') UNION (SELECT technician, servicetime, result, ID, Customername, address, c...

Portability concerns on C struct/union

Supposing I have the following type from an external library: union foreign_t { struct { enum enum_t an_enum; int an_int; } header; struct { double x, y; } point; }; is it safe to assume the following code fragment will work as expected on different platforms and with different compilers? struc...

rails union hack, how to pull two different queries together

I have a query which searches two separate fields in the same table... looking for locations which are most likely a specific city, but could also be a country... ie the need for two fields. Table looks like: Country City Germany Aachen USA Amarillo USA Austin Result: Keyword Sideinfo Aachen Germany USA ...

What am I doing wrong with this use of StructLayout( LayoutKind.Explicit ) when calling a PInvoke struct with union?

The following is a complete program. It works fine as long as you don't uncomment the '#define BROKEN' at the top. The break is due to a PInvoke failing to marshal a union correctly. The INPUT_RECORD structure in question has a number of substructures that might be used depending on the value in EventType. What I don't understand i...

What is the difference between structures containing bool vs uint when using PInvoke?

Ok, I'm now very confused. After my last question had several people comment about changing bool to uint I verified they are the same size by: Console.WriteLine("sizeof bool = {0}", Marshal.SizeOf(typeof(bool))); Console.WriteLine("sizeof uint = {0}", Marshal.SizeOf(typeof(uint))); Which of course prints: sizeof bool = 4 siz...