Joining these two queries

[Robert J Egan]

Can anyone help me to join Query 1 to Query 2

cheers
rob

Query 1
-------------

SELECT * FROM

(SELECT COUNT(DISTINCT RLID) AS NoBooked, SUM(PlacesNo) AS NoPlacesTaken,
RL.EID, V.EventPlaces, CAST (V.EventPlaces-SUM(PlacesNo) AS Int) AS
PlacesAvailable
FROM tbl_ep_Returns_List RL, tbl_ep_Venue V
WHERE RL.Status <= 2 AND RL.EID = 4 AND RL.EID = V.EID
GROUP BY RL.EID, V.PlacesMax, V.PlacesMin, V.EventPlaces)

tbl_tmp1

WHERE EID = 4

Query 2
---------------

SELECT EID, Status AS TheStatus, RLID, PlacesNo, FName, LName, Organisation,
RecievedDate, JobRole
FROM dbo.tbl_ep_Returns_List
WHERE RLID = 22

[Bob Barrows]

Assuming this is SQL Server(what version, please?)
Select * FROM
(Query1) q1
INNER JOIN
(Query2) q2
ON q1.<joining column> = q2.<joining column>

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From header is
my spam trap, so I don't check it very often. You will get a quicker
response by posting to the newsgroup.
"Robert J Egan" <rob_j_egan@hotmail.com> wrote in message
news:epRgTPs2DHA.2544@TK2MSFTNGP10.phx.gbl...

> Can anyone help me to join Query 1 to Query 2
>
> cheers
> rob
>
> Query 1
> -------------
>
> SELECT * FROM
>
> (SELECT COUNT(DISTINCT RLID) AS NoBooked, SUM(PlacesNo) AS NoPlacesTaken,
> RL.EID, V.EventPlaces, CAST (V.EventPlaces-SUM(PlacesNo) AS Int) AS
> PlacesAvailable
> FROM tbl_ep_Returns_List RL, tbl_ep_Venue V
> WHERE RL.Status <= 2 AND RL.EID = 4 AND RL.EID = V.EID
> GROUP BY RL.EID, V.PlacesMax, V.PlacesMin, V.EventPlaces)
>
> tbl_tmp1
>
> WHERE EID = 4
>
> Query 2
> ---------------
>
> SELECT EID, Status AS TheStatus, RLID, PlacesNo, FName, LName,

Organisation,

> RecievedDate, JobRole
> FROM dbo.tbl_ep_Returns_List
> WHERE RLID = 22
>
>

[Robert J Egan]

Hi thanks for replying Bob you assumed right. I'm using Version 7.

Using the below i got the following "Incorrect syntax near the keyword
'ON'."


I used the following

SELECT * FROM

(SELECT COUNT(DISTINCT RLID) AS NoBooked, SUM(PlacesNo) AS NoPlacesTaken,
RL.EID, V.EventPlaces, CAST (V.EventPlaces-SUM(PlacesNo) AS Int) AS
PlacesAvailable
FROM tbl_ep_Returns_List RL, tbl_ep_Venue V
WHERE RL.Status <= 2 AND RL.EID = 4 AND RL.EID = V.EID
GROUP BY RL.EID, V.PlacesMax, V.PlacesMin, V.EventPlaces)

q1

INNER JOIN

(SELECT EID, Status AS TheStatus, RLID, PlacesNo, FName, LName,
Organisation, RecievedDate, JobRole
FROM dbo.tbl_ep_Returns_List
WHERE RLID = 22)

ON q1.EID = q2.EID




"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ur3XJzs2DHA.2556@TK2MSFTNGP10.phx.gbl...

> Assuming this is SQL Server(what version, please?)
> Select * FROM
> (Query1) q1
> INNER JOIN
> (Query2) q2
> ON q1.<joining column> = q2.<joining column>
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From header

is

> my spam trap, so I don't check it very often. You will get a quicker
> response by posting to the newsgroup.
> "Robert J Egan" <rob_j_egan@hotmail.com> wrote in message
> news:epRgTPs2DHA.2544@TK2MSFTNGP10.phx.gbl...

> > Can anyone help me to join Query 1 to Query 2
> >
> > cheers
> > rob
> >
> > Query 1
> > -------------
> >
> > SELECT * FROM
> >
> > (SELECT COUNT(DISTINCT RLID) AS NoBooked, SUM(PlacesNo) AS

NoPlacesTaken,

> > RL.EID, V.EventPlaces, CAST (V.EventPlaces-SUM(PlacesNo) AS Int) AS
> > PlacesAvailable
> > FROM tbl_ep_Returns_List RL, tbl_ep_Venue V
> > WHERE RL.Status <= 2 AND RL.EID = 4 AND RL.EID = V.EID
> > GROUP BY RL.EID, V.PlacesMax, V.PlacesMin, V.EventPlaces)
> >
> > tbl_tmp1
> >
> > WHERE EID = 4
> >
> > Query 2
> > ---------------
> >
> > SELECT EID, Status AS TheStatus, RLID, PlacesNo, FName, LName,

> Organisation,

> > RecievedDate, JobRole
> > FROM dbo.tbl_ep_Returns_List
> > WHERE RLID = 22
> >
> >

>
>

[Bob Barrows]

Robert J Egan wrote:

> Hi thanks for replying Bob you assumed right. I'm using Version 7.
>
> Using the below i got the following "Incorrect syntax near the keyword
> 'ON'."
>
>
> I used the following
>
> SELECT * FROM
>
> (SELECT COUNT(DISTINCT RLID) AS NoBooked, SUM(PlacesNo) AS
> NoPlacesTaken, RL.EID, V.EventPlaces, CAST
> (V.EventPlaces-SUM(PlacesNo) AS Int) AS PlacesAvailable
> FROM tbl_ep_Returns_List RL, tbl_ep_Venue V
> WHERE RL.Status <= 2 AND RL.EID = 4 AND RL.EID = V.EID
> GROUP BY RL.EID, V.PlacesMax, V.PlacesMin, V.EventPlaces)
>
> q1
>
> INNER JOIN
>
> (SELECT EID, Status AS TheStatus, RLID, PlacesNo, FName, LName,
> Organisation, RecievedDate, JobRole
> FROM dbo.tbl_ep_Returns_List
> WHERE RLID = 22)
>
> ON q1.EID = q2.EID
>
>

You left out the q2 alias:

WHERE RLID = 22) q2


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Robert J Egan]

It works!

Thanks Bob, you've been a great help for me.

rob :)


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:ew$2pX12DHA.1908@TK2MSFTNGP10.phx.gbl...

> Robert J Egan wrote:

> > Hi thanks for replying Bob you assumed right. I'm using Version 7.
> >
> > Using the below i got the following "Incorrect syntax near the keyword
> > 'ON'."
> >
> >
> > I used the following
> >
> > SELECT * FROM
> >
> > (SELECT COUNT(DISTINCT RLID) AS NoBooked, SUM(PlacesNo) AS
> > NoPlacesTaken, RL.EID, V.EventPlaces, CAST
> > (V.EventPlaces-SUM(PlacesNo) AS Int) AS PlacesAvailable
> > FROM tbl_ep_Returns_List RL, tbl_ep_Venue V
> > WHERE RL.Status <= 2 AND RL.EID = 4 AND RL.EID = V.EID
> > GROUP BY RL.EID, V.PlacesMax, V.PlacesMin, V.EventPlaces)
> >
> > q1
> >
> > INNER JOIN
> >
> > (SELECT EID, Status AS TheStatus, RLID, PlacesNo, FName, LName,
> > Organisation, RecievedDate, JobRole
> > FROM dbo.tbl_ep_Returns_List
> > WHERE RLID = 22)
> >
> > ON q1.EID = q2.EID
> >
> >

> You left out the q2 alias:
>
> WHERE RLID = 22) q2
>
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>