Comparing if two columns are equal in Where clauses

I would like to compare if two columns a开发者_高级运维re equal in the where clause. The columns are two columns are from different tables that have been joined

This is the statement that I am using

SELECT authors.state, sum(qty*price)as sales_revenue 
  from authors,titles, stores, sales 
 WHERE authors.state = (SELECT  stores.state from stores)

but I get an error that says this M

sg 8120, Level 16, State 1, Line 1
Column 'authors.state' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Use this:

WHERE authors.state = stores.state

Or better, use the ANSI-92 JOIN syntax.

SELECT authors.state, sum(qty*price) as sales_revenue
FROM authors
JOIN stores ON authors.state = stores.state
JOIN titles ON ...
JOIN sales ON ...
GROUP BY authors.state

This:

SELECT a.state,
       sum(l.qty*l.price)as sales_revenue 
       FROM authors a
       LEFT JOIN stores s on a.state=s.state
       GROUP BY a.state

Specify what columns are available in each table so you can check its relationships

e.g

authors = id,state
store = id,store

Regards