Re: join two selects

2009-05-21 Thread gert 
On May 21, 4:54 pm, Tim Golden  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
-- 
http://mail.python.org/mailman/listinfo/python-list

Re: join two selects

2009-05-21 Thread Tim Golden 

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
--
http://mail.python.org/mailman/listinfo/python-list

join two selects

2009-05-21 Thread gert 
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"]
-- 
http://mail.python.org/mailman/listinfo/python-list