join two selects

G

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"]
 
T

Tim Golden

gert said:
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
 
G

gert

gert said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top