join two selects

[gert]

On May 21, 4:54 pm, Tim Golden <m... at timgolden.me.uk> wrote:
> 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

Thanks works great :-)
just needed to at LEFT JOIN and remove sea.txt sea.end from the GROUP
BY