MYSQL :: UNION And Count(DISTINCT)

Can I Get A Total COUNT From A UNION DISTINCT?
I've got a rather large table (18M rows) of customer prospects and I need to count the number of rows that can be returned for different customers. The wrinkle here is that I have over 20 different "scores" for each prospect and they can be combined in virtually any combination which means I cannot index them all in one index as that's too many for one index and the leftmost prefix rule means the indexes would be ignored if one of the scores wasn't used. The fastest query I've been able to construct is using UNION with individual indexes for each score:

SELECT COUNT(memberID) FROM members WHERE score1 > 100 AND areaCode IN (1, 2, 3, 4)
UNION DISTINCT
SELECT COUNT(memberID) FROM members WHERE score2 > 100 AND areaCode IN (1, 2, 3, 4)
UNION DISTINCT
SELECT COUNT(memberID) FROM members WHERE score7 > 100 AND areaCode IN (1, 2, 3, 4)

Now unfortunately this is giving me three counts of memberIDs, one for each SELECT statement, whereas I want a total number of distinct rows for the whole statement. I can't just add the three results together as each count can contain some duplicates which means the total would be inaccurate.

If I remove the COUNT() from the queries (i.e. SELECT memberID FROM members) it returns large recordsets of around 50K rows but I get a list of distinct IDs and doing a countrows on this is 100% accurate. But as you can imagine this is slow and I don't actually need this large recordset it returns. Is this slow because of the large numbers or rows or because it's merging the list to be distinct? If it's the latter then getting a distinct COUNT() will be slow anyway.

But, assuming it's not the merge that is slowing things can anyone suggest a way of getting an overall count of distinct records?

Select Distinct Union?
I have a query script that works with a classifieds script

class_prodimages.pid can have more than one image entry

my client only wants only one image to be displayed

here is what I have now

$sqlm = @mysql_query("
SELECT
class_products.shortDescription,
class_products.id,
class_products.title,
class_products.price,
class_prodimages.image,
class_prodimages.pid
FROM class_products,class_prodimages
WHERE class_products.featured = 'Y' AND class_prodimages.pid = class_products.id
");
I have put DISTINCT all over the place but it doesnt work.

then searche the forum and see union and tried from the examples but its also not working

SELECT shortDescription, id, title, price
FROM
class_prodimages
UNION
SELECT image, pid
FROM class_prodimages
WHERE class_products.featured = 'Y' AND class_prodimages.pid = class_products.id

Union Count
First I'm trying to add together two counts in a union.Code:

select count(id) as count from products where catid=2
union
select count(id) as count from collections where catid=2

How can I get the sum of those two counts?

Distinct Count
I have a script automatically create a new table at the beginning of every month, for the current month. The tables all have the same exact columns, but a different name (i.e. stats_October_2006, stats_November_2006). I did this just to keep the amount of records in one table as minimal as possible.I would like to be able to perform a count query across both tables and have the values merge, so it would be like performing the query on one large table.

This is the query I am able to run on just one of the tables: SELECT DISTINCT domain, count( * ) AS count FROM stats_October_2006 GROUP BY domain ORDER BY count DESC

However, I would like to perform the same query on multiple tables as if they were all one table.

How would I go about doing this? I have looked into LEFT JOIN but don't quite understand it.

COUNT DISTINCT
I have a table that stores information on visits to various business locations. In the table I have the user_id and the office_id. How can I count how many distinct users have visited all locations. I have tried the following but it groups it by office ID and I just want one total figure...

SELECT COUNT(DISTINCT(User_ID)) FROM My_Table GROUP BY Office_ID;

Count (distinct) Alternative
I have recently ported my dbase from MySQL to sqlite and i am having problems porting my sql statements over to sqlite. I cannot use the count (distinct ) feature, and need to workaround this.... what i need is an alternative to this query without the use of count(distinct) .... i need to obtain all the unique x elements that have correlating y elements that exist in table 1 and occur more than once.

table 1 has y
table 2 has x,y

i want to know retrieve all the x's where in table 1 the y values for the same x exceed one element (not the value but the number of y elements in table 1 that have the same x exceeding 1)....i hope this makes my question cleare..thanks again

| SELECT
| | x
| FROM
| | (
| | SELECT DISTINCT
| | | (2.x),
| | | count(DISTINCT(1.y)) AS count
| | FROM 1,
| | | 2
| | WHERE 1.y= 2.y
| | GROUP BY 2.x
| | ) AS collectionOFx
| WHERE count > 1

Using Count From Distinct Table...
select count(*) from tblproducts a,tblsubcategory b where (b.stSubCategoryName like '%".$keyword."%' or b.stSubCategoryDesc like '%".$keyword."%' or a.stProductName like '%".$keyword."%' or a.stProductDesc like '%".$keyword."%') and a.inSubCategoryId=b.inSubCategoryId

in above query i want to count only tblproducts
i used count(a.*) not worked

Getting A Count Of Distinct Elements
If I have a table like this:

+----+---------------+-------------+
| id | A | B |
+----+---------------+-------------+
| 1 | 1 | -1040187392 |
| 2 | 1 | -1040187392 |
| 3 | 2 | 1409286144 |
| 4 | 2 | 1409286144 |
| 5 | 2 | 1409286144 |
| 6 | 2 | 1442840576 |
| 7 | 3 | 989855744 |
| 8 | 3 | 2080374784 |
| 9 | 3 | 989855744 |
| 10 | 4 | -671088640 |
| 11 | 4 | 1107296256 |
| 12 | 4 | 1107296256 |
| 13 | 4 | 1073741824 |
| 14 | 4 | 1073741824 |
| 15 | 4 | 1107296256 |
+----+---------------+-------------+

I want to get a frequency count of the elements. Can I do it with mysql? I mean, I want something like the following:

+---------------+-------------+---------+
| A | B |frequency|
+---------------+-------------+---------+
| 1 | -1040187392 | 2|
| 2 | 1409286144 | 3|
| 2 | 1442840576 | 1|
+---------------+-------------+---------+

Using Count(*) With Distinct To Determine Multiple Copies
I have an sql table 'messages' that stores peoples messages, in the form below: (Cname = conversation name)

User Recipient Cname Subject Message
Fred Bill Weather Rain Levels I think they are low
Fred Harry Traffic Congestion Its getting worse
Fred Bill Traffic Congestion Its getting worse
Fred Harry Money Savings The interest rates dropped
Fred Bill Money Savi

Distinct List Of Parents With A Child Count Result
Parent (id, name)
Child (id, parent_id, session_name)

I'm trying to write a query that will have one output row per Parent. Each row will show:
parent.id, parent.name, count of children for that parent AS children_count

Some parents have no children. Those rows could return null or 0 for the children_count field.

I know multiple methods external to SQL to solve the overall problem such as returning the join and counting where child.ids (for the same parent) is not null.

==>> but is there a way to do it in one SQL query that returns just the info I need?

Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.

This is my query:

select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate

It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....

UNION, Individually Works Fine, But Not In Union
Well, I am using an UNION, and while both of the queries works fine while used individually, they don't work while in the UNION.

Here is the code -

PHP

(
SELECT *
FROM job
WHERE date_fin !=0000-00-00
ORDER BY date_fin ASC
)
UNION (
SELECT *
FROM job
WHERE date_fin =0000-00-00
ORDER BY date_creation ASC
)

What do u think guys?

Note : MySQL version 4.1.21

Note : Not working means, this part is not working => 'ORDER BY date_fin ASC', means, the returned results are not ordered as I want.

MySQL doc. tells,

To apply ORDER BY or LIMIT to an individual SELECT, place
the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have;

SELECT DISTINCT `field1`, `field2`, `field3` FROM table1

This returns what I need. There is also another field (field4) which I also want to select, but not distinctly.

Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1`

The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct)

I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me.

How can I display this not-wanted-distinct field in a distinct query?

Select Distinct And Include Non-distinct Columns
I have a publication table that tracks the products assigned to various publications.

I want to select all of the distinct products, based on product_ID, assigned to a specific publication but I also want to return additional columns that do not need to be distinct.

If I use the following select:

Select distinct publication.product_ID, publication_ID.code, publication.region from publication where publication_ID = '12'

I would get a list showing the three columns that I want to see which are product_ID, publication_ID and region for publication 12 but I get too many items as the distinct function means that all three of these columns must be distinct and I only need the product_ID to be distinct.

So how do I find all of the distinct product_ID but also show other columns such as region?

Do I need to do this with some kind of self-join?

#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows.

I'm using my SQL version: 4.1.19

Any clue on why this isn't working?

INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` )
VALUES (
(
SELECT Project_Code, Grant_Code, FY, SUM( Capital )
FROM Grants_Temp
WHERE Project_Code = 'OSUT'
AND FY = '2006'
AND Claim_Month = '072006'
GROUP BY Grant_Code
ORDER BY Grant_Code
)
)

Column Count Doesn't Match Value Count At Row 1
I am getting the following error when I run my query.
Column count doesn't match value count at row 1

I have looked up this error and have checked and I appear to have the right number and names in my query. In the DB table I have 34 columns, and that is what I have in the query.

The last 4 in the query are for the names of the images being uploaded, but am not sure how this all works, so I don't know if I need them, but have them there until I know for sure. Comment, Purchase and Remarks are not used in this form, but I have added them so everything is being shown in the query. Code:

Count(*) As Count And TotalCount Of Count
data in myTable1

(n) country_id
(1) 3
(2) 1
(41) 1
(5) 2
(6) 3
(7) 4

data in myTable4

(time) param
(10 : 10) c=4
(10 : 12) c=2
(10 : 30) n=41&k=5
(10 : 35) c=1
(10 : 37) n=5
(10 : 50) c=2
(10 : 54) c=2
(10 : 55) n=1&cate=6
(11 : 12) c=2
(11 : 15) n=7
(11 : 20) c=1
I have data in myTables like the above.

I have the following code.

code

(select left(time,2) as hour, count(*) as count
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the code above produces the following result.

result

(day) count
(10) 3
(10) 1
(11) 1
The following would-be code doesn't work correctly, but it will show what I want.

would-be code

(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the following is my target result.

target result

(day) count totalCount
(10) 3 5
(10) 1 5
(11) 1 5

Union Without Union
I am running version 3.23 of MySQL. It does not allow UNION statements.
How can I pull this off without a union. I hear it can be done with a LEFT JOIN

SELECT `id`,`name`,`date_of_event` FROM `events` ORDER BY `date_of_event` ASC
UNION
SELECT `id`,`name`,`date_of_event` FROM `upcoming` ORDER BY `date_of_event` ASC

Using UNION
(SELECT id,name FROM table1)
UNION
(SELECT id,name FROM table2)
UNION
(SELECT id,name FROM table3)
order by name limit 1;

this query is works. I can get the fields values. But i cant get the owner table that i read. for example i get this values 3,george

(which table has this record???)

UNION On Ver. 3.23
Are there any alternatives to UNION? Running mySQL ver. 3.23
Upgrade is not an option!

My query looks like this:

(SELECT sum(t.timer) AS sumtimer FROM emply
WHERE t.tmnr = 1 AND t.tjobnr = 69 AND
t.tfaktim = 2 AND t.tdato BETWEEN '05/7/4' AND '06/2/1')

UNION (

SELECT sum(f2.timer) AS fakbare FROM timer f2 WHERE
f2.tjobnr = 69 AND f2.tmnr = 1 AND f2.tfaktim = 1 AND f2.tdato BETWEEN '05/7/4' AND '06/2/1')

Union
I have X number of identical (structure-wise) tables, with one generated for every day. There is a reporting tool which requires that certain data be extracted for a given time period, which can span any number of these databases. My idea is to use a dynamically generated UNION ALL statement which simply joins identical selects for each table. e.g.

CODESELECT id, blah, bleh FROM t1
UNION ALL
SELECT id, blah, bleh FROM t2
....

UNION HELP
I have written a PHP search App and I want it to search between like 8 or so tables in one Database.

Here is my union mysql code:

$query = "(select * from arcamax where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from drudge where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from google where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from human_events where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from newsmax where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from street where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from townhall where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from weatherbug where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") union (select * from worldnet where promo_code like "%$trimmed%" or start_end_date like "%$trimmed%" or mailedlist like "%$trimmed%" or description like "%$trimmed%" or orders like "%$trimmed%" or totalrevenue like "%$trimmed%") order by mailedlist";

Now if I run a search with the above code it does not work...but if I limit the number of tables to two it works fine.

How To Do A Union
I have a query I need to do that combines two queries The structure for the first table is

Application_Period
CampusID
GroupID
IndivID

This table uses the lookup table Time to get the groupID and IndivID.

TimeID
Application_Period

TimeID matches GroupID and IndivID. How can I combine the two tables into one without the UNION operator?

Using Union
i have two tables, "txns" and "proc". "txns" has 100 records while "proc" has 55 records. All records in "proc" table can also be found in "txns" table. I mean all those 55 records in proc are also in txns.

(txns - proc)
txns=100 records
-
proc=55 records
------------------
45 records that have no match

the question.

What must i do to find those records that dont have a match in txns table and view those unmatched records

UNION
I have a problem with the use of UNION. If I query my database with the following:

Quote: SELECT column1 FROM mytable WHERE title = "mytitle" UNION SELECT column1 FROM mytable WHERE author = "myauthor"

I get a syntax error! (#1064) Why? I can;t find anything wrong in the query above.

Union
I have the following code

SELECT year, count(*) FROM test1 GROUP BY year UNION ALL SELECT year, count(*) FROM test2 GROUP BY year";

This query is fine but I have to be able to add the totals for example if table test1 has the value 2007 and count 5 and table test2 has the value 2007 and count 5. I have to be able to store this in an array as 2007, 10.

Not In + Union
got tables brochure_applied, and brochure_content_protected, and brochure_user_protected. each has the column bid. brochure applied also has uid.
uid is the reference key (as shown in my example query).
i basically wanna get all the bids from brochure_applied that have uid = 3, but remove all the bids that are either in brochure_content_protected and also brochure_user_protected.

my query is: select bid from brochure_applied where uid=3 not in ((select bid from brochure_content_protected) union (select bid from brochure_user_protected))

UNION
it's my command:
select name from users where userid=1 union select name from users where userid=2;
but mysql return a 1064 error: syntax error near union...
Do u know why?

Help With A Query That Has UNION And SUM
I am trying to join 2 tables (union) and calculate the final quantity like below:

PHP

(SELECT SUM(`sales`.`Quantity`)
FROM sales
WHERE
`sales`.`SKU` = 'S0089-060927-S0072-bar-exP^223')

UNION ALL

(SELECT SUM(`reversals`.`Quantity`)
FROM reversals
WHERE
`reversals`.`SKU` = 'S0089-060927-S0072-bar-exP^223')

The result that I am getting when executing this is 2 lines. One is the sales total quantity (let;s say 10) and the other is reversals total quantity (let's say 2). How can I merge so that i have the net quantity (ie 8)?

Union Problem
Can anyone tell me the best way of removing duplicates when using a union on 2 or more Fulltext queries

Conditional Union
My objective is to gather all the type groups for a mailing list. I also would like to add to the list an 'Undefined' item for all the users that are ungrouped:

1. I'm trying to make a conditional union, where a union will occur only if the previous condition is true (if any null typeIDs are found)
2. I would like to make one query only [to use with a php function wich only allows single queries]

So this is a 'sketch' of my query:

SELECT typeID, name FROM pa_users_broadcast_types
UNION
IF((SELECT COUNT(*) FROM pa_users_broadcast WHERE ISNULL(typeID))>0)
SELECT (Ɔ') typeID, ('Undefined') name;

Group By In Union
I need some help with this query, i need to be able to group the combined results by "N_U_R_C". The following query does not work because of the line in red.

Quote:

select i.id
, i.id as substitute_for
, i.fd_Date
, i.catid
, i.Make
, i.Model
, i.modelGroup
, i.Description
, i.N_U_R_C as nurc
, i.Price
, i.pic1
from inventory i, inventory_category c
where i.catid=c.id AND c.parent=47 AND i.Model='$model' AND i.Make='$make' AND i.I_IC_C_S!='S' AND i.N_U_R_C!='C')
union all
(select i_sub.id
, i_real.id as substitute_for
, i_sub.fd_Date
, i_sub.catid
, i_af.make
, i_af.model
, i_sub.modelGroup
, i_sub.Description
, i_sub.N_U_R_C as nurc
, i_sub.Price
, i_sub.pic1
from inventory i_real
join inventory_alsofits i_af
on i_af.itemid = i_real.id
join inventory i_sub
on i_af.itemid = i_sub.id
join inventory_category c_real
on i_real.catid = c_real.id
where c_real.parent=47 AND i_af.model='$model' AND i_af.make='$make' AND i_real.I_IC_C_S!='S' AND i_real.N_U_R_C!='C'
ORDER BY i_real.Make='$make')
GROUP BY nurc
ORDER BY $order $sortorder
LIMIT $limitvalue,$limit

Question About UNION
My first post on this site, was recommended to me by a colleague of mine.

Anyway,
What I have is two tables called table1 and table2. I want to search these tables by a users ID. From the result set I wish to grab the ID of all the files associated with this user as fileID. Then I want to search in another table called file for this fileID and from that result set I wish to grab the productID of that file. I then what to search a table called products for the productype. Then what I want to do is from the whole lot, grab a list of producttypes that have been found and a total of how many of those products are associated to the user.

What I have written works, except for one thing. I get the totals and the producttypes for that specific user, but the problem is that because they are coming from a union between table 1 and table 2, I have duplicates in my result set.

(
[0] => Array
(
[producttype] =>
[total] => 0
)

[1] => Array
(
[producttype] => DVD
[total] => 1
)

[2] => Array
(
[producttype] => BOOK
[total] => 1
)

[3] => Array
(
[producttype] => GAME
[total] => 1
)

[4] => Array
(
[producttype] => CD
[total] => 1
)

[5] => Array
(
[producttype] => CD
[total] => 2
)

[6] => Array
(
[producttype] => MAGAZINE
[total] => 2
)

[7] => Array
(
[producttype] => OTHER
[total] => 1
)

)
As you can see I have two lots of CD in my result set, when really I would want them to be grouped together and give me a total of 3, instead of a total of 2 and a seperate total of 1.

How could I possibly do this? Sorry if I have explained things badly, its pretty hard explaining a system to someone who hasn't seen it.

Below is my current SQL statement, which works fine except for grouping totals for identical producttypes

SELECT
p.producttype, COUNT(p.producttype) as total
FROM
table1 as t
LEFT JOIN
file AS f
ON
t.fileID = f.fileID
LEFT JOIN
products AS p
ON
f.ProductID = p.id
WHERE
t.userID = :userID
GROUP BY
p.producttype

)
UNION ALL
(
SELECT
p.producttype, COUNT(p.producttype) as total
FROM
table2 AS t2
LEFT JOIN
file AS f
ON
t2.stickerpictureID = f.stickerpictureID
LEFT JOIN
products AS p
ON
f.ProductID = p.id
WHERE
t2.userID = :userID
GROUP BY
p.producttype
)
ORDER BY
producttype

UNION Question
I am performing a UNION on 3 tables as follows:

SELECT DISTINCT col1_1, col2_2, col3_3
FROM (
SELECT col1, col2, col3 FROM table1
UNION
SELECT col1, col2, col3 FROM table2
UNION
SELECT col1, col2, col3 FROM table3
)
SORTED_TABLE LIMIT 0, 20

All works well but my question is, is it possible to store the name of the table from which each record in the new table has come from. So I'd therefore be able to see that record x has come from table1 originally.

Union Woes.....
I'm having problems with this slightly more complex union query.

1. Users can be members of groups (via usergroupmap mapping table)
2. Groups can have zero users.
3. Users can be members of zero groups.

I'd like a record set that displays the follwing:

|groupname|username|
|ABCGroup|Mark| /*ABCGroup with 3 members*/
|ABCGroup|John|
|ABCGroup|Ted|
|BCDGroup|NULL| /*BCDGroup with 0 members*/
|CDEGroup|John| /*CDEGroup with 2 members*/
|CDEGroup|Ted|
|DEFGroup|Mark| /*DEFGroup with 1 members*/
|NULL|David| /Users who are not in any groups/
|NULL|Frank|
|NULL|Peter|

NOTE:
-Mark and John and Ted are members of more than 1 group!
-If users are not members of any groups, they are listed at the bottom with a NULL groupname column.
-if groups have no members, they have a NULL username column.

I made the following query, but it returns me ALL users, and ALL groups + members.

(SELECT
group.name as c1,
user.name as c2
FROM
group
LEFT JOIN usergroupmap on (group.id = usergroupmap.groupid)
LEFT JOIN user on (user.id = user.groupmap.userid)

WHERE user.id = 1234)

UNION

(SELECT
NULL as c1,
user.name as c2,

FROM
user

WHERE user.id = 1234)

Union Query
is it possible to sum the results of a union all query?

example:

SELECT col1 FROM table WHERE col3 = 'value 1'
UNION ALL
SELECT col2 FROM table WHERE col4 = 'value 1'

can all the results from col1 and col2 be summed together?

Union ALL Tables
I have a content manager that I wrote and I was looking into a simple solution for a feature I am thinking of...

All the tables in the database that my system uses are the same structurally so I want to UNION them all together to do *site searches*.

Is it possible to indicate *via a SQL statement* a UNION of all tables in a database without actually specifying them all individually?

Using Union In A Subquery?
I'm trying to make a query that fetches messages that were created by one of your friends (friendships are stored in a separate table) and was thinking this query would possibly do the trick:

SELECT *
FROM public_messages
WHERE author_id IN
(
(SELECT friend_from FROM friendships WHERE friend_to=1 AND pending=0)
UNION
(SELECT from_to FROM friendships WHERE friend_from=1 AND pending=0)
)

Where the user's id is 1... Running this query gives an error of:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION ( SELECT from_to FROM friendships WHERE friend_from = 1 AND pending = 0 ) ' at line 1

and of course it works fine without the union... Is such a thing possible in one query?

Union Query
I am using Access 2K as a front end to a MYSQL database.

I am trying to run a Union query on the MYSQL database. The query is (much simplified)

SELECT [faxid] as ID from faxdata UNION
SELECT [letid] as ID from letdata UNION
SELECT [memoid] as ID FROM MEMODATA;

I get an ODBC error. The same query runs when the backend files are MDB files and it runs with MYSQL if I only combine 2 tables. Is there some limit with MYSQL on being only able to use a UNION on 2 tables.

Union Query
I am trying to make a union query to join 4 tables. I have reduced the
tables to just 5 fields and made sure that the field types and names are the
same. I keep getting an error message ODBC call failed.

I have tried various combinations of the tables and find that I can use any
2 of them but as soon as a third is included in the statement the query
fails.

Is there a limit on the number of tables in a union query.
The union query looks like this in its simplest form when I have made sure
that the fields names, types and position match but I have also tried by
specifically naming the fields in the same order for each table

select * from T1 union select * from T2 union select * from T3 UNION select
* from T4;

UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days.

Notes:
Server language is ColdFusion
gamedate is type DATE

SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate >= now()
UNION (
(SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate < now()
ORDER BY gamedate desc LIMIT 1)
ORDER BY gamedate asc LIMIT 3

The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".

Union Statement
I have three tables all holding seprate data but they have two similar columns: event and date. I want to build a mysql query in php that will list all on the rows in the each of the events' columns with no duplicates. The query below does not do this because duplicate rows are shown for rows that have the same event name but different dates. For example, EVENT1 might be an event in table 1 with date 2006-09-20 and also and event in table 2 with date 2006-09-30 - so EVENT1 would show twice in my list.

$query_race ="SELECT event, date FROM table1 WHERE event LIKE '2007'
UNION
SELECT event, date FROM table2 WHERE event LIKE '2007'
UNION
SELECT event, date FROM table3 WHERE event LIKE '2007'
GROUP BY event";

Is there anyway to pick up the row with just the latest date so that I could have a list with no duplicates and the latest date from all tables?

UNION And Optimization?
My question is about a UNION query to deal with an (annoying) JOIN
over two tables. I am joining over a double column primary key (where the
order of the columns can be changed). This is so slow using a
join, but very fast using a union. How come this is? Code:

Union All With Or Without Sub-select?
i'm using a sqlite database and have to query similar fields spread across 3-5 tables, so in other words, the schema of the tables are the same.

now, what you guys think is faster, more performant in general?

SELECT ... FROM table1 WHERE ...
   UNION ALL
SELECT ... FROM table2 WHERE ...
   ...
ORDER BY ...;

or

SELECT ... FROM
   (SELECT * FROM table1 UNION ALL SELECT * FROM table2 ...)
WHERE ...
ORDER BY ...;

UNION Issue
I'm trying to display info from 2 different tables in a table on my site. I have the structure set to 7 columns and a count of 30 rows. I can get it to work fine if I just use one select statement with desc limit from 1 table.

But what I want is for it to display info from 1 table first that has 20 rows then grab the remaining 10 rows from the other table. I've tried using UNION for this but I keep getting the column error for different column sizes. I'm running mysql 5 with php 5.2.5.

Is there a better way of doing this? From what I understood about mysql 5 you could run 2 select statements. But that doesn't seem to work.

These are the 2 individual queries that work on their own:

$sql = "SELECT * FROM tbl_user tb
INNER JOIN tbl_user_2 tbl
ON tb.user_id=tbl.user_id
WHERE tbl.status > 0 AND
tb.user_check='Y'
ORDER BY
tbl.status DESC";

$sql = "SELECT * FROM tbl_user WHERE user_check='Y' ORDER BY logged_today DESC";

Union 2 Table On 2 Pc
how can join 2 table of 2 db on 2 different pc ?

In sqlServer i just do:
selet * from pc1.dbo..db1.table1
union
selet * from pc2.dbo..db2.table2

in MySql?

UNION Problem
I had this problem in 4.1.7, upgraded to 4.1.11, but am still having the problem. If I look at WinXP Task Manager, MySQL is taking 99% of CPU cycles and grabbing more memory every few seconds. The first two of these SQL statements work. The 3rd one, which contains a UNION statement, hangs.

1) select distinct(Metadata.metadataID),Formats.format FROM Metadata
JOIN Formats ON Metadata.MetadataID = Formats.MetadataID
WHERE Metadata.Private='0'
AND Metadata.MetadataID IN
(Select Metadata.MetadataID from metadata
where (title like '%pancreas%'));

This returned about 700 valid rows for me.

2) Select keywords.metadataID FROM keywords where keyword like '%pancreas%';

This also returns about 600 valid rows. But when I combine the two in the SQL below, MySQL never returns. It just sits there consuming 99% of the CPU cycles and reducing the system to a grindingly slow pace:

3) select distinct(Metadata.metadataID),Formats.format FROM Metadata
JOIN Formats ON Metadata.MetadataID = Formats.MetadataID
WHERE Metadata.Private='0'
AND Metadata.MetadataID IN
(Select Metadata.MetadataID from metadata
where (title like '%pancreas%')
UNION Select keywords.metadataID FROM keywords where keyword like
'%pancreas%');

Can You Join A Union
I would like to know if and how it is possible to join the *result* of
a union with another table (without first doing individual joins to
each part of the result that then will be combined into a union) ??
For example, I would like to do such a join, if possible, to avoid
doing something like this:

select tableA.col1 as c1 , tableC.col2, tableC.col3, tableC.col4,
tableC.col5, .....
from tableA inner join tableC
on tableA.col1 = tableC.col1
union
select tableB.col1 as c1, tableC.col2, tableC.col3, tableC.col4,
tableC.col5, .....
from tableB inner join tableC
on tableB.col1 = tableC.col1

The problem with the code above is that I will have a lot of wanted
output fields which in the code will be duplicated from tableC in each
part of the union statement, and if I in the future will add a field
it will then have to be added in two places.

Instead of the code above I would like to do extract the output
columns from tableC to only one place, with code something like this:

select c1 , tableC.col2, tableC.col3, tableC.col4, tableC.col5, .....
from tableC inner join
(
select tableA.col1 as c1 from tableA
union
select tableB.col1 as c1 from tableB
) as VirtualUnionTable
on tableC.col1 = VirtualUnionTable.c1

but this does not work (at least not with mysql 4.0.13) and I don't
know how to refer to the result of the union and the join column of
that union (as you can see above I tried to name it with "as
VirtualUnionTable" and then "on... = VirtualUnionTable.c" but it does
not work).

As I mentioned, the purpose was to eleminate the duplication of all
columns from tableC, which I will have to do if I join tableC
individually to the selects which are then combined into a union, as
in the first example above.

Is it even possible to make a join to the result of a union without
doing the joins separately first, and can someone show how that kind
of code would look like ?