Select Multiple Tables Without A Join

[tomtimms]

I need to select columns from 2 tables however not user a join.  It doesn't seem to want to work, is my second select correct where I have the 2 columns equaling each other?

 

SELECT a.date AS date, a.source AS source, SUM(a.total) AS total, (SELECT SUM(b.search) FROM table2 b WHERE a.date = b.date AND a.source = b.source) AS search FROM table1 a WHERE a.date BETWEEN '2010-08-01' AND '2010-08-31' GROUP BY date, source

[kickstart]

Hi

 

Why do you not want to use a JOIN?

 

I think you probably need a GROUP BY clause on the subselect.

 

Best I can make out that you want (but using a join) is as follows

 

SELECT a.date AS date, a.source AS source, SUM(a.total) AS total, 
FROM table1 a
INNER JOIN (SELECT date, source, SUM(b.search) AS search FROM table2 b GROUP BY date, source) b
ON a.date = b.date AND a.source = b.source
WHERE a.date BETWEEN '2010-08-01' AND '2010-08-31' 

 

All the best

 

Keith

[mikosiko]

I need to select columns from 2 tables however not user a join.  It doesn't seem to want to work, is my second select correct where I have the 2 columns equaling each other?

 

SELECT a.date AS date, a.source AS source, SUM(a.total) AS total, (SELECT SUM(b.search) FROM table2 b WHERE a.date = b.date AND a.source = b.source) AS search FROM table1 a WHERE a.date BETWEEN '2010-08-01' AND '2010-08-31' GROUP BY date, source

 

what part is not working?.... works for me....  what datatype is your column 'date'  is a DATE or DATETIME?