Help, two queries JOIN or UNION

[Begbie]

Hello,
I have two queries

SELECT s.datum, s.schicht, s.bargeld, au.stundenlohn, a.von, a.bis
FROM arbeitszeiten a, authuser au, schichtumsatz s
WHERE s.datum BETWEEN ".$vonDatum." AND ".$bisDatum."
AND au.id=".$check["id"]."
AND a.mitarbeiterid = ".$check["id"]."
AND a.schicht=s.id
ORDER BY s.datum ASC


SELECT s.datum, s.schicht, sum(a.bis - a.von) As zeit
FROM arbeitszeiten a, schichtumsatz s
WHERE a.schicht = s.id
AND s.datum BETWEEN ".$vonDatum." AND ".$bisDatum."
GROUP BY s.datum, s.schicht
ORDER BY s.datum ASC

From the first one I need all that I have selected, from the second one I need only this sum(a.bis - a.von) As zeit column.
How can I get this column inside this first querie. Do I need JOIN or UNION?

These are the tables:

CREATE TABLE authuser (
id int(11) NOT NULL auto_increment,
uname varchar(25) NOT NULL default '',
stundenlohn FLOAT NOT NULL default 0.,
passwd varchar(32) NOT NULL default '',
team varchar(25) NOT NULL default '',
level int(4) NOT NULL default '0',
status varchar(10) NOT NULL default '',
lastlogin datetime default NULL,
logincount int(11) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;

CREATE TABLE arbeitszeiten (
id int NOT NULL auto_increment PRIMARY KEY,
von float NOT NULL,
bis float NOT NULL,
mitarbeiterid int(11) NOT NULL,
schicht int NOT NULL,
FOREIGN KEY (schicht) REFERENCES schichtumsatz (id),
FOREIGN KEY (mitarbeiterid) REFERENCES authuser (id)
);

CREATE TABLE schichtumsatz (
id int NOT NULL auto_increment PRIMARY KEY,
datum int NOT NULL,
schicht CHAR NOT NULL,
bargeld FLOAT NOT NULL DEFAULT 0.0,
belege FLOAT NOT NULL DEFAULT 0.0,
gutschein FLOAT NOT NULL DEFAULT 0.0,
rechnung FLOAT NOT NULL DEFAULT 0.0,
sonstiges FLOAT NOT NULL DEFAULT 0.0,
kassiert FLOAT NOT NULL DEFAULT 0.0,
stand FLOAT DEFAULT 0.0,
putzgeld FLOAT DEFAULT 0.0,
fehlend FLOAT DEFAULT 0.0,
dateofchange int NOT NULL,
useridofchange int NOT NULL
);

Hope someone can help me. Thanks in advance ;)

P.S. This $check["id"] is like someone's name

[Old Pedant]

Try this:

SELECT s.datum, s.schicht, s.bargeld, au.stundenlohn, a.von, a.bis, x.zeit
FROM arbeitszeiten a, authuser au, schichtumsatz s,
( SELECT s.datum, s.schicht, sum(a.bis - a.von) As zeit
FROM arbeitszeiten a, schichtumsatz s
WHERE a.schicht = s.id
AND s.datum BETWEEN ".$vonDatum." AND ".$bisDatum."
GROUP BY s.datum, s.schicht ) AS x
WHERE s.datum BETWEEN ".$vonDatum." AND ".$bisDatum."
AND au.id=".$check["id"]."
AND a.mitarbeiterid = ".$check["id"]."
AND a.schicht=s.id
AND s.datum = x.datum
AND s.schicht = x.schicht
ORDER BY s.datum ASC


It's a join on the second SELECT used as an inner query.

I'm not sure that the
AND s.schicht = x.schicht
is needed or wanted, so try it both with and without that.

(I could have figured it out with time, but faster to just try it.)