MYSQL :: UNION - Works In 4.0.16, Not In 4.1.8

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

Works
I have this and it works just fine but I cant help but think there is a much better way of accomplishing the same thing in one sweep verses 6
PHP Code:
mysql_query("update residential set bed='1' where bed like 'ONE%'");
mysql_query("update residential set bed='2' where bed like 'TWO%'");
mysql_query("update residential set bed='3' where bed like 'THREE%'");
mysql_query("update residential set bed='4' where bed like 'FOUR%'");
mysql_query("update residential set bed='5' where bed like 'FIVE%'");
mysql_query("update residential set bed='6' where bed like 'SIX%'");

Works In MYSQL 4.1 But Not In 5.0
MySQL
1054 - Unknown COLUMN 'p.manufacturers_id' IN 'on clause'
SELECT count(*) as total FROM products p, categories c, products_to_categories p2c LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE c.categories_status=1 AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND p.products_status = Ƈ' AND p.products_id = pd.products_id AND pd.language_id = Ƈ'

Here is the query in PHP

PHP
"select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = Ƈ' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069.

I want to run a query to return the three items.

My query: Select * from Inventory where Description Like 'w069'

It doesn't seem to work, returning zero row.

Query Only Works Once
I'm using jsp with mysql and am querying the database for data. Everything works fine on the first try. If I click back on my browser and hit submit again on my form, I get no data.

I've tried removing the close() commands but that doesn't seem to work.

SSN Changes In Database But Still Works As Key?
I have a ssn field in a table but somthing happens to the data. I use an update to enter the data and set the value to 123-12-1234 for a test but when I look in the table or I get the record from a record set it come out like -1123.

Why is this? But when I run a query and ask it to return all records which have a ssn of 123-12-1234 it returns the correct record. If this is correct then how can I display the ssn number in a java text field.

How Decimal Works
Say that we have an variable that is

var1 = 3
var2 = 3.65

var3 = var1 * var2 '--- Sum = 10.95

Now my Question!
How do I input this variable into MySQL in a COL whit DECIMAL (10,2)?
I only get the result 10.00 in the COL?
It seams that MySQL round every resout I get to .00 at the end.

If I make an varible hard ex.
var3 = "10.95"
that works but if I convert an numeric variable to string it doesnt?!?!?!
ex.
var4 = cstr(var3)

UPDATE Works In MS SQL
I use the following stament work with MSSql, but not MySql, why ?

Update mytable set mycol = 'my_Text' + mycol

Failed, mycol is varchar

MySQL Delete: Works In Php 5 But Not Php 4?
I've developed a small login system using Xampp on my local machine. Everything is running great except when the admin tries to delete something. Apparently I have the wrong syntax for my deletion in mysql.

Here's my original delete statement (from mysql5):
DELETE at.* FROM access_table AS at WHERE at.client_id=2

And here's the error I get from mysql4 (I ran the command in phpmyadmin console):
#1066 - Not unique table/alias: 'at'

So I removed the aliases and re wrote my command as such:
DELETE * FROM access_table WHERE at.client_id=2

And got the following error from php4:
#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 '* FROM access_table WHERE at.client_id=2' at line 1

SQL Query Works Only In Win32!
I use MySQL from my php page and from phpMyAdmin.

In Windows Server, following query works fine. In Linux Server instead it returns error.

The SQL Query is:

SELECT DISTINCT a.id, a.container, b.container FROM folder AS a JOIN folder AS b ON a.container=b.id

The PHP error is:

You have an error in your SQL syntax near 'ON a.container=b.id' at line 1

So, I ask: If in Windows this query work fine, where is the error in the Linux version?

SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing
I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I
did not convert the privilege tables with mysql_fix_privilege_tables.

Suddenly, the mysql started to show all databases to all users (even
though the user had access denied and could not do show tables on
them). So I started to find help and tried the
mysql_fix_privilege_tables. It didn't work properly (more than just
access denied and errors, that are ok) so I did the changes manually
conforming to source code of mysql_fix_privilege tables.

PhpMyAdmin stopped its warning about privilege tables not conforming
to current mysql version. Everything seemed ok except that SHOW
DATABASES still worked.

The users naturally all have Show_db_priv="N" in the tables. Also
trying "safe_show_database", "skip_show_database" in my.cnf and
"--skip-show-database" witch mysqld didn't help.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.

select sc.name,
Sum(if(student.SNO is not null, 1, 0)) nmbr,
from school sc
left outer join student
on sc.school_id = student.school_id
group by sc.school_id

this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13.
i could not find a solution.But i suspect from NULL threatment.

Parentheses: Query Works With 4.1.7, But Not With 4.1.11
Since we updated to the newest MySQL version (Windows) some of our queries do not work any more.

It's reproducable by this (dummy) query:

SELECT B.ID from ((SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID);

This works with 4.1.7, but not with 4.1.11, unless the parentheses are removed:

SELECT B.ID from (SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID;

Does anybody know why MySQL changed its behaviour that way?

I know the parentheses in the first query are not necessary, but it would be quite difficult to search through all our queries for unnecessary parentheses.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.....

Exporting MS Works For MySQL
I have a MS Works database and I can't find anything useful on the PHP site for working with this type of database. I did a quick search and it seems that it can be exported for OoenOffice.org and other spreadsheet database programs but none of them give a method. Mostly selling books and MS Access.

If there is a way to access the .MWD or .DBF Microsoft works files with PHP that would be great. If that's not possible, preferably not, I'd rther switch to MySQl easily if possible. I'm still looking but it's not looking good.

Mysql.exe Sort Of Works
I have just installed MySQL (not an easy thing to do). I also have installed PHP. I use Dreamweaver8 for 2 static sites, and eventually will figure out how to use that with PHP/MySQL.
Everything seems to work fine, and I'm working my way through Janet Valade's book, "PHP and MySQL for Dummies." Everything I have tried in the book works perfectly except for the mysql.exe command.
I am in the correct folder and type mysql.exe -u root -p and am prompted for my password. When I enter my password the cursor moves to the next line and just sits there. Nothing happens. Fortunately, the window is not frozen and I can just close it.

Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing.
Code:

SELECT Artists.Artist, Albums.Album_Name
FROM Artists, Albums
WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id

GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version.

The query is this:

Query Help. This Works, But I Don't Think It Is Good Practice
comics:
id
date
location

comments:
id
comicid
datetime
header
comment
author

Every comic can have multiple comments. Each comic will have a comment, but there is a period of time where the comic is posted but the first comment is not yet submitted. So for argument's sake we can't assume there is any comment at all for a given comic.

I want the all the comic ids and dates before a certain date along with the most first comment entered relating to that comic.

select comics.date
, comics.id
, comments.header
from comics
left outer
join comments
on comments.comicid = comics.id
where (comics.date>=񟭇-03-01')
group by comics.id
I never defined WHICH header should be returned when many are grouped together. I just know that mysql will return the first one in this circumstance. I seem to recall reading somewhere that different databases handle this sort of thing differently, with some returning a null in that field?

I keep trying to forumate a useful subquery but keep drawing blanks just as I think I have it figured out in my head. Like I forget how the string starts by the time I mentally formulate the end....

Suddenly A Script No Longer Works
I always get the following message when starting a search in my database:

Search for titles in the mylibrary database.

Search for titles beginning with a:

V. 4.1.7 + Php4 Works, + Php5 Problems
I am running:

Mac OS X 10.3.7
apache 2.0.52
MySQL 4.1.7
php 4.9.3 & php 5.0.3

I have an html form that "POST"s data to a db.
I have a "GET" script to repopulate form for editing.
Everything works fine with php 4.9.3.

With php5.0.3 the data is "POST"ed just fine
(I used a different GUI app to check)
When I try to "GET" said data, form is returned empty?

I have all error reporting turned on = nothing.
I have all backwards-compatibility directives in
php.ini config file turned on.

Two Different Websites Connected To One Database But Only One Works...
I have been recently having problems transferring my website from one domain name to another. The first domain name was used whilst at uni, but the second is my own perosnal website. One the original website, the MySQL database allows the user to login and register new users without any problems.

However, the second website does not allow me to do this. I would consider myself a newbie when its comes to MySQL so I was wondering if the community could help me out on this one. Both websites are hosted by the same company, and both websites use the same database but my limited knowledge cannot solve this problem. Are there certain permissions I need to set or something? Can anyone help me out on this one?

Delete Using Multiple Table: Works On 4.1.20, But Not On 4.0.16
I've a simple delete query delete from joingrp as J using joingrp as J,msggrp as M
where J.msgid=M.msgid and M.parent!=0

When I tried it on phpMyadmin at ixweb, having mysql-4.1.20, it works as desired.
But when I tried it on phpMyadmin at iPowerweb, having 4.0.16, it says
#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 'as J using joingrp as J,msggrp as M where J.msgid=M.msgid Can I solve this without using any server-side loops?

My MySQL Back-end Works Very Slow
I have installed my MySQL Server in a Window XP platform running in a Pentium4 machine. When I access the database on the machine, it is working in a normal speed. But when I access the database from a client machine (still running in windows platform), it runs very slow.

SQL Query Works Fine With Phpmyadmin But Gives Me No Results And EOF With ASP
I have a strange problem: when I perform a sql query on my phpmyadmin it works fine and it gives me the right results. If I perform the _same_ query through my asp application I reach immediately the EOF condition without any results!

Can't Update Multiple Rows Via PHP, But Works In PhpMyAdmin
My php script will update 1 row fine, but fails on 2. However, the exact same query works when pasted into phpMyAdmin.

THIS QUERY WORKS BOTH IN MY PHP AND PHPMYADMIN:

$sql = "update table set color='red' where id='1';";
$sql_do = mysql_query($sql);

THIS QUERY WORKS ONLY IN PHPMYADMIN:

$sql = "update table set color='red' where id='1'; update table set color='green' where id='2';";
$sql_do = mysql_query($sql);

I know the second query is correct, because when I copy it from my php code and paste it into phpMyAdmin, it works. But only the first query, with 1 row update, works from my php code.

Quering Works Except Need To Sort Results By Other Table...
I have the following query:

SELECT plugin_id FROM tc_plugin_lists WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC;

This almost works, the problem is the ORDER BY clause. It should order the results by the sort_number field from another table called tc_plugins. Now it throws an error...

SELECT items FROM other table ORDER BY other tables field values where field is same as in 'items' ASC

?

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)