Using Union In A Subquery?

Which One? JOIN, Subquery, UNION?
I am having trouble with a select statement. Here's what I want to do:

tbl1 is a list of photo albums, each with an 'album_id'.
tbl2 is a list of photos, with a column specifying the 'album_id' of the photo album it belongs to.

I want to SELECT a list of albums, and append to that list a column containing the number of photos in that particular album (determined by counting the number of photos that have an 'album_id' matching each album). How should I do this?

View Replies !

---

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);

View Replies !

---

SUBQUERY Vs DEPENDENT SUBQUERY
I have a table called `accounts`, that contains columns:

account_name VARCHAR (128)
account_no VARCHAR (16)
account_hash VARCHAR (32)
balance DECIMAL(10,2)
There is an INDEX on account_hash and there's currently 128 rows in the table.

I have a table called `activity` that contains columns:....

View Replies !

---

Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query.

First Query
select

ACCOUNT_ID,
ACCOUNT_TYPE_C,
PAT_ID

from PAT_ACCT_CVG

where ACCOUNT_TYPE_C in (120103,120104,120101)

Second Query

SELECT

PAT_CVG_FILE_ORDER.PAT_ID,
PAT_CVG_FILE_ORDER.LINE,
COVERAGE.COVERAGE_ID,
COVERAGE.CVG_EFF_DT,
COVERAGE.CVG_TERM_DT


FROMPAT_CVG_FILE_ORDER
LEFT OUTER JOIN COVERAGE
ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID


Where coverage.payor_id = ?'


Third Query

select

TRAN.ORIG_SERVICE_DATE
TRAN.TRAN_TYPE,
TRAN.INSURANCE_AMOUNT

from Tran

where TRAN.TRAN_TYPE = 1
and TRAN.INSURANCE_AMOUNT > 0
and TRAN.proc_ID in 1008,1009

(now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)

View Replies !

---

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))

View Replies !

---

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?

View Replies !

---

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.

View Replies !

---

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???)

View Replies !

---

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')

View Replies !

---

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

View Replies !

---

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.

View Replies !

---

Whats A Union?
I know how to create a join - correct me if I am wrong, but its
something like the following:

SELECT contacts.firstname,contacts.lastname,address.line_ 1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

I have seen mixed references in my books about unions and joins that
make me think they are in some way related. My Core MySQL book, which I
think is great, is unfortunately not clear enough for my head to grasp.

I'd appreciate if someone would could give me a real and an imaginery
world example (thus sample code and perhaps compare apples and oranges
in a shopping list or whatever)...

View Replies !

---

UNION ALL GROUP BY
I have:

SELECT x, count(x)
FROM table1
WHERE ...

UNION ALL

SELECT x, count(x)
FROM table2
WHERE...

UNION ALL

SELECT x, count(x)
FROM table3
WHERE ...

UNION ALL
....
....

and I need to do GROUP BY with all the sentences.

View Replies !

---

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 ?

View Replies !

---

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:

View Replies !

---

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?

View Replies !

---

Using Union On 2 Tables
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

View Replies !

---

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.

View Replies !

---

UNION & SQL_CALC_FOUND_ROWS
I have the following query:

SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=0 OR kind=1 ORDER BY rank LIMIT 40,10

it would run slowly because 'OR' breaks usage of indexes. So I rewrote it using the UNION:

(SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=0 ORDER BY rank LIMIT 50) UNION ALL (SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE kind=1 ORDER BY rank LIMIT 50) ORDER BY rank LIMIT 40,10

The problem is that FOUND_ROWS() returns just 100 which is the number of rows in a temporal table created by UNION ALL, but I'd like to get the entire number of rows that have kind of 0 or 1

View Replies !

---

Mysql UNION
I have 2 tables with data i need to loop out to a webpage.

Table1
-Field1_ok
-Field2

Table2
-Field1
-Field2-er

I've tried using...

SELECT Field1 AS f1 FROM Table1 UNION SELECT Field1 AS f1

However, not every value in each table is outputted.



View Replies !

---

ORDER BY With UNION ALL
We have a problem using ORDER BY with UNION ALL.

The problem occurs when using UNION ALL between many complex select's, but I'll try to make things more simple, posting smaller select's.

When I run the select below, which I call select1, query results are ok, and ORDER BY clause is respected, sorting my query. Code:

View Replies !

---

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%');

View Replies !

---

Union Statements
I am trying to create a union statement to join two queries together.
one of the fields on one of the queries is a constant, ie because it is from the table i want it to output the letter 'B'. When i try to union this with a variable in another query, i get the error: "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'"

View Replies !

---

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".

View Replies !

---

Union In Column Name
I created a database, then when I tried to create a table, one of my column names was supposed to be UNION (as in labor union). I kept getting a sytax error until I removed it. Then it worked fine. Anyone know why?

View Replies !

---

Union In Mysql 3.23
Does mysql 3.23 supports "union" It doesn't accept the syntax saying i have an error near 'UNION' i took the most simple example and still it doesn't work. Example:

SELECT Custnum
FROM Custnotes
UNION
SELECT Custnum
FROM Note_NoTipul

View Replies !

---

Union Repeat
which I want to be able to insert into a different database table:

The results are in these three variables which ar being looped:

myString / typenumber / codenumber

How can I insert these loop results into another database table? row by row?

Like this:

auto_id column1 column2 column3
1 value1(myString) value1(typenumber) value1(codenr
2 value2(mystring) value2(typenumber) value2(codenr)

etc....

If I use this: one row is being inserted. Someone said I have to use this but then use the UNION REPEAT.....

<CFQUERY NAME="update" DATASOURCE="">
INSERT INTO dev (dev_volgnr,dev_typenr, dev_nr)
SELECT '#myString#','#typenumber#','#codenumber#'

</CFQUERY>

View Replies !

---

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";

View Replies !

---

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)?


View Replies !

---

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

View Replies !

---

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 (&#390;') typeID, ('Undefined') name;

View Replies !

---

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

View Replies !

---

ORDER BY And UNION
i'm looking at showing both the latest articles, and latest news within a "featured news and articles" section; so wish to combine two seperate tables of data, into one query.

This below union seems to do the trick perfectally, the only issue is;

The typical "order by" doesn't seem to work (i'm trying to order the data by date)

Anyone have any experience with this? Here is my statement.

MySQL Code:
SELECT
    news.nid AS id,
    news.title,
    news.body,
    news.preview,
    news.fn,
    DATE_FORMAT(news.DATE, '%M %D, %Y %H:%i %p') AS fdate,
    DATE_FORMAT(news.DATE, '%m/%Y') AS ldate,
    'news' AS source
FROM
    news
WHERE
    news.live=1
UNION
SELECT
    articles.id AS id,
    articles.title,
    articles.body,
    articles.preview,
    articles.fn,
    DATE_FORMAT(articles.DATE, '%M %D, %Y %H:%i %p') AS fdate,
    DATE_FORMAT(articles.DATE, '%m/%Y') AS ldate,
    'article' AS source
FROM
    articles
WHERE
    articles.live = 1

View Replies !

---

Table Join Or Union
I have two tables.

Table 1 (Date, Total)
Table 2 (Date, Total)

I'm trying to find a select statement which will give me all the dates
from Table 1 and Table 2 in a column along with Table1.Total and
Table2.Total.
ie.
Dates, Table1.Total, Table2.Total

I'm told that I would need a FULL OUTER JOIN to be able to do this but
according to the MySQL reference, FULL OUTER JOIN is not supported? How
would I be able to achieve the required result?

View Replies !

---

UNION, LIMIT And ORDER BY
I've trying to write a query that seems like it should be simple but
for some reason my attempts are not working. This is really a general
SQL quesion and doesn't pertain to MySQL but I couldn't find a generic
database discussion group except on on advancement and theory and this
is really a basic query construction question.

Just say I have a table with three columns, name, date, score and
these represent the test scores for various people. Each person can
take the test as many times as they like but I only want to get back
the last result.

My first thought didn't seem right but I figured I'd try it anyway:
SELECT name, date, score FROM testresults
GROUP BY name
ORDER BY date DESC
And yes this is wrong and does not return anything useful since the
score returned seems to be based on where the grouping started in
someway.

My second thought was that the list of people is small and known so I
was going to just hardcode their names into the query:
SELECT name, date, score FROM testresults WHERE name = 'bob' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = 'mary' ORDER BY
date DESC LIMIT 1
UNION ALL
SELECT name, date, score FROM testresults WHERE name = 'jim' ORDER BY
date DESC LIMIT 1
UNION ALL
This is syntactically incorrect.

View Replies !

---

Column Width In Union
Column width in a series of UNIONs is determined by the width of the
first SELECT. Here is an example:

mysql> ( SELECT "foo" ) UNION ( SELECT "fnord" );
+-----+
| foo |
+-----+
| foo |
| fno |
+-----+
2 rows in set (0.00 sec)

This is in MySQL 4.0.21.

In my real queries, I cannot know ahead of time the maximum length of
any column as returned by any of the SELECT blocks. My workaround is
to start my UNION chain with a

SELECT "xxxxxxxxxxxxxxxx",
"xxxxxxxxxxxxxxxx",
...

And then in the code that uses the results, ignore any rows that have
"xxxxxxxxxxxxxxxx" in them. This is extremely kludgy.

There was as thread from 2003-08-24 with the subject "Union and Order
By give strange results in Mysql 4.0.13" which describes this problem.
It was unresolved (only workarounded) over a year ago.

View Replies !

---

Mysql Problem With UNION
I have a strange problem with a UNION of 3 queries. The 3 separate
select-queries return (at this point) respectively 4, 3 an zero rows,
but the UNION returns no rows at all.

When I take the query that returns 0 rows, and put it in front of the
other two, everything to go fine (I get 7 rows), but this is of course
no real solution, next time another query returns zero rows.

View Replies !

---

Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....

View Replies !

---

GROUP, UNION And COUNT...
writing a query to create a tree menu, it pulls from a table of music "tracks". In this database there are four "sub_genre" fields for each track, and I need to create a list of all used sub_genres and how many tracks are in each.

The list will look like this:
Expand|Select|Wrap|Line Numbers

View Replies !

---

Mysql Union Queries
I have been developing a mysql application for a month on one machine, time to move to another, much to my surprise it has 3.23.58 and there is no way to change it. Now all of my union queries are broken.
I found one solution:

CREATE TEMPORARY TABLE temp_union TYPE=HEAP select * from asdf;
INSERT INTO temp_union select * from qwer;
SELECT * FROM temp_union;
DROP TABLE temp_union;

Now I ask, how efficient is this? I mostly use the union in two places: a search query where I aggregate a search from three tables, and a favorites list where I aggregrate favorites from three tables. So using the above methods I would need to create 2 temporary tables for each query.

I am using php as a frontend, would it be better to do 3 queries and aggregrate everything in php? the only reason I do it on mysql is because I also need a sorting.

View Replies !

---

Duplicate Rows In UNION
I have 2 joined query:

(select * from TABLE1 where data like '%A%' and data like '%B%')
UNION
(select * from TABLE1 where data like '%A%' OR data like '%B%')

I want to show result of the first half of query come before the result of the second one. How could I achieve this?

View Replies !

---

Union And Count With Same Column
How do you make a another column and count the column with the same ClientID and refSite. For Example as Follows:
345 , Site , Router , 2
712 , Site , Router , 1

mysql Code:

Original
- mysql Code

SELECT refClientID , refSite , 'Router' as type ,
FROM tblConnRouter
UNION
SELECT refClientID , refSite , '3G' as type FROM tblConn3G
UNION
SELECT refClientID , refSite , 'ADSL' as type FROM tblConnADSL
UNION
SELECT refClientID , refSite , 'Black Berry' FROM tblConnBlackBerry

SELECT refClientID , refSite , 'Router' as type ,
FROM tblConnRouter
UNION
SELECT refClientID , refSite , '3G' as type FROM tblConn3G
UNION
SELECT refClientID , refSite , 'ADSL' as type  FROM tblConnADSL
UNION
SELECT refClientID , refSite , 'Black Berry'  FROM tblConnBlackBerry

View Replies !

---

UNION To Join Problem
I'm having a problem with using UNION to join my query on MYSQL 4.1.22, it work correctly on 5.0.45 but that is not installed on my host so im stuck with 4.1.22.

The problem is that it seems not to be joining the second part of the query. Is there something I can do to make it work on 4.1.22?


Code:


SELECT gameID, round, date, year, hteam, ateam, played, SUM((aFinalGoals *6) + ( aFinalPoints )) AS highest, a.teamName AS team
FROM games g
LEFT JOIN venues v ON ( g.venueID = v.venueID )
LEFT JOIN rounds r ON ( g.roundID = r.roundID )
LEFT JOIN teams a ON ( g.aTeam = a.teamID )
WHERE g.venueID =7
AND played =1
GROUP BY gameID

UNION

SELECT gameID, round, date, year, hteam, ateam, played, SUM((hFinalGoals *6) + ( hFinalPoints ) ) AS highest, h.teamName AS team
FROM games g
LEFT JOIN venues v ON ( g.venueID = v.venueID )
LEFT JOIN rounds r ON ( g.roundID = r.roundID )
LEFT JOIN teams h ON ( g.hTeam = h.teamID )
WHERE g.venueID =7
AND played =1
GROUP BY gameID

ORDER BY highest DESC
LIMIT 5

View Replies !

---

UNION And Count(DISTINCT)
I have a bunch of tables that store messages sent by users of my system. All of the tables are identical in structure and include a SentDate and a UserID.

I have been asked to create a query that gives a count of active users within a specific time period, an active user being a user who sent a message in the given time period.

If it were one table, then easy;

SELECT count(DISTINCT(UserID)) FROM Messages1 WHERE SentDate>=FROM and SentDate<=TO

Is it possible to get a count of distinct users across many (identical) tables? I can't see how to do it with UNION...

View Replies !

---

Alias On Union Result
How to assign alias to result of union operation, and perform select query on that alias

The query that I wrote doesn't work

SELECT
*
FROM
(
SELECT
table1.col1AS uni_col1,
table1.col2 AS uni_col2,
FROM
table1
UNION
SELECT
table1.col2AS uni_col1,
table1.col1 AS uni_col2,
FROM
table1
) AS uni,
table2

WHERE
uni.uni_col1 = table2.ID;

My be problem with assign alias to result of union operation.

View Replies !

---

UNION Of Table That Doesn't Exist
SQL Statement A:
select fileid from tbl_MetaData_200809

SQL Statement B:
select fileid from tbl_MetaData_200810

Result of Statement A results in:
+--------+
| fileid |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)

Result of Statement B results in:
ERROR 1146 (42S02): Table 'archive.tbl_MetaData_200810' doesn't exist

But, (SQL Statement A) UNION (SQL Statement B) also results in:
ERROR 1146 (42S02): Table 'archive.tbl_MetaData_200810' doesn't exist

What are my options here, execute the UNION statement and test the warning message and exclude table that doesn't exist and re-run command?

View Replies !

---