Python Forum
Off topic SQL question
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Off topic SQL question
#1
This is a Query I am trying to get right with sqlite3 before I code it into my python program. My SQL is extremely rusty, so It's not obvious to me what I am doing wrong
I have two tables with the same schema, but different data. the schemas are:
Output:
sqlite> .schema CREATE TABLE ElectionResults2012 (VTDDescription, CandidateLName, CandidateFName, CandidateMInit, CandidateSuffix, PartyID, PartyDescription, Town, PoliticalOffice, MachineVotes, AbsenteeVotesTotal, TotalVotes); CREATE TABLE ElectionResults2014 (VTDDescription, CandidateLName, CandidateFName, CandidateMInit, CandidateSuffix, PartyID, PartyDescription, Town, PoliticalOffice, MachineVotes, AbsenteeVotesTotal, TotalVotes);
my test query is:
Output:
sqlite> SELECT ...> A.VTDDescription, B.VTDDescription, A.CandidateLName, B.CandidateLName, A.CandidateFName, B.CandidateFName, ...> A.CandidateMInit, B.CandidateMInit, A.CandidateSuffix, B.CandidateSuffix, A.PartyID, B.PartyID, A.PartyDescription, ...> A.Town, B.Town, A.PoliticalOffice, B.PoliticalOffice, A.MachineVotes, B.MachineVotes, A.AbsenteeVotesTotal, ...> B.AbsenteeVotesTotal, A.TotalVotes, B.TotalVotes ...> FROM ...> ElectionResults2012 A, ElectionResults2014 B ...> INNER JOIN B ON ...> B.VTDDescription = A.ElectionResults2012.VTDDescription ...> and ...> A.ElectionResults2014.PoliticalOffice = B.ElectionResults2012.PoliticalOffice ...> ORDER BY ...> ElectionResults2012.Town;
I get the following error:
Error: no such table: B

So I guess i don't remember how to do this. Di I not need to define all the columns of both tables and only mention the B table after the 'INNER JOIN B ON'?
Reply
#2
This is what I wanted, don't even know why I thought I needed a join:
Output:
SELECT A.VTDDescription, B.VTDDescription, A.CandidateLName, B.CandidateLName, A.CandidateFName, B.CandidateFName, A.CandidateMInit, B.CandidateMInit, A.CandidateSuffix, B.CandidateSuffix, A.PartyID, B.PartyID, A.PartyDescription, A.Town, B.Town, A.PoliticalOffice, B.PoliticalOffice, A.MachineVotes, B.MachineVotes, A.AbsenteeVotesTotal, B.AbsenteeVotesTotal, A.TotalVotes, B.TotalVotes FROM ElectionResults2012 A, ElectionResults2014 B WHERE A.VTDDescription = B.VTDDescription AND A.Town = B.Town ORDER BY A.Town;
Reply
#3
Without a join specified, it'd be a full outer join, which is sort of a worst case scenario (every row of one table matched with every row of the other table).

I've actually never seen the syntax from table_1, table_2 before, and I didn't know that'd work. I would have written it like this:
FROM ElectionResults2012 A
    JOIN ElectionResults2014 B ON
        B.VTDDescription = A.VTDDescription
        AND A.PoliticalOffice = B.PoliticalOffice
Also, I don't see why these are two different tables. They're the same, separated only by year, so I'd just have one table, with an extra column for which ElectionPeriod the results represent. But that's just a nitpick :p
Reply
#4
The entire database is being reorganized and normalized, but I still need to be able to access whats there, so your response is both helpful and appreciated.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  I can't send topic AndrzejB 1 1,773 Nov-23-2019, 06:50 PM
Last Post: buran
  Totally off topic! Larz60+ 3 4,203 Dec-07-2016, 09:46 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020