join two selects

Tim Golden mail at timgolden.me.uk
Thu May 21 16:54:22 CEST 2009


gert wrote:
> I am trying to figure out how to join two selects ?
> 
> SELECT * FROM search
> SELECT eid, SUM(pnt) AS total_votes FROM vote
> 
> CREATE TABLE votes (
>     eid  INTEGER PRIMARY KEY,
>     uid  VARCHAR(64),
>     pnt  INETEGER DEFAULT 0,
> );
> 
> CREATE TABLE search (
>     eid  INTEGER PRIMARY KEY,
>     txt  VARCHAR(64),
>     end  DATETIME
> );
> 
> so the result would be a table that looks like this
> 
> ["eid", "txt", "end", "total_votes"]

That's what's known technically as a join:

SELECT
  sea.eid,
  sea.txt,
  sea.end,
  SUM (vot.pnt) AS total_votes
FROM
  search AS sea
JOIN votes AS vot ON
  vot.eid = sea.eid
GROUP BY
  sea.eid,
  sea.txt,
  sea.end,


(Guessing the join condition from the column names)

TJG



More information about the Python-list mailing list