JOIN without using JOIN

[thompom]

Hi - strange Q this one on a database that isnt mySQL but here goes:
I have an ODBC driver that doesnt support JOIN
so am trying to write this query

SELECT a.username, b.post
FROM GB_00_userdetails AS a
LEFT JOIN(
SELECT lastuseredit, count(postcode) AS post
FROM mk_01_customerrecords
WHERE datelastedited BETWEEN 2010-03-01 AND 2010-03-31
AND postcode = ''
GROUP BY lastuseridedit
) AS b ON b.lastuseredit = a.userID

but without using JOIN - is there an 'old school' way of accomplishing this.

If I have to reference one table from another I have to query like

SELECT a.surname, b.username
FROM mk_01_customerrecords AS a, GB_00_userdetails AS b
WHERE a.lastuseredit = b.userID

 

[jaxtell]

What kind of database is it? Is there some reason you can't upgrade to an ODBC driver that does support join?

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[thompom]

...the driver is the only one available for this automotive system called Autoline by Kerridge.
(and it cost over 1000GBP! per licence)
am getting round it by running separate queries and merging the results using my scripting language

 

[r937]

did you try the obvious...

SELECT b.lastuseredit
     , a.username
     , COUNT(b.postcode) AS post
  FROM GB_00_userdetails a
     , mk_01_customerrecords b
 WHERE b.datelastedited BETWEEN 2010-03-01 AND 2010-03-31
   AND b.postcode = ''
   AND b.lastuseredit = a.userID
GROUP 
    BY b.lastuseridedit
     , a.username

i would normally put quotes around the date constants, but since you didn't, i didn't

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thompom]

hi rudy - thanks for your reply

that query works great - but wanted to list all username from GB_00_userdetails even if there are no results for
b.postcode = ''

following on from the above - is there a way to this:

SELECT a.username, b.post, c.email
FROM GB_00_userdetails AS a
LEFT JOIN(
SELECT lastuseridedit, count(postcode) AS post
FROM mk_01_customerrecords
WHERE datelastedited BETWEEN 2010-03-01 AND 2010-03-31
AND postcode = ''
GROUP BY lastuseridedit
) AS b ON b.lastuseridedit = a.userID
LEFT JOIN(
SELECT lastuseridedit, count(emailaddress) AS email
FROM mk_01_customerrecords
WHERE datelastedited BETWEEN 2010-03-01 AND 2010-03-31
AND emailaddress = ''
GROUP BY lastuseridedit
) AS c ON c.lastuseridedit = a.userID