[Solved] Tweaking a Query

Creating tables and queries
Post Reply
bvwputnam
Posts: 6
Joined: Mon Apr 14, 2008 2:31 pm

[Solved] Tweaking a Query

Post by bvwputnam »

Got a db setup w/a table imported from Calc and I've created a query that works...

SELECT DISTINCT "ResidentID", "Last", "ResNumber", "ResAddress", "ResUnitA", "ResUnitB" FROM "Precinct9" WHERE "Election" = 2008 ORDER BY "ResNumber", "ResAddress"

...except that it only returns data where there is a single value for "Election." In fact, the table has duplicate entries which have other values for "Election"; 2005, 2006 and 2007. For example...

01AFK2153000 2007 9 U ALMEIDA
01AFK2153000 2008 9 U ALMEIDA
01AFK2153000 2006 9 U ALMEIDA
01AFK2153000 2005 9 U ALMEIDA

What I need to do is create a query that will extract a single entry (i.e., one ResidentID is listed) if the value of "Election" is equal to both 2007 AND 2008 (or 2006 AND 2008, etc.)

I've tried various ways of structuring it (e.g., WHERE ("Election" = 2008 AND "Election" = 2007)), but these return no data.

Is there any way to write the query to get what I want?
OR
Must I change the composition of the table (i.e., have multiple values in a single field, instead of separate entries)?

Brent
Last edited by bvwputnam on Tue Apr 15, 2008 3:12 pm, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Tweaking a Query

Post by Villeroy »

A value can not be 2007 and 2008 at the same time. It is 2007 OR 2008 or it is BEWEEN 2007 AND 2008. Another issue may be the data type. If the field contains text it is '2007' OR '2008'
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bvwputnam
Posts: 6
Joined: Mon Apr 14, 2008 2:31 pm

Re: Tweaking a Query

Post by bvwputnam »

Villeroy, Thanks, that was useful; this is getting me closer to what I need.

There are 2734 residents with an Election value of 2008. If I use...
WHERE "Election" = '2008'
...then I get 2734 entries.

There are 619 residents with an Election value of 2007. If I use...
WHERE "Election" = '2007'
...then I get 619 entries.

However, if I use this syntax...
WHERE "Election" BETWEEN '2007' AND '2008'
...then I get 2746 entries. This appears to be every distinct ResidentID in the table; it appears to be returning either those with an Election value of 2008, or those with an Election value of 2007 and presenting the list of distinct entries (removing duplicates).

What I need to get are those with an Election value of 2007 AND 2008, not either-or. For example, if the Election value is 2008 and the Election value is 2007, then they would appear in the query results; if the Election value is 2008 but not 2007, or 2007 but not 2008, they would not appear in the query results. The result should be about 600 residents.

How would I form this query?
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Tweaking a Query

Post by Villeroy »

Code: Select all

SELECT "ResidentID", "Last", "ResNumber", "ResAddress", "ResUnitA", "ResUnitB"
FROM "Precinct9"
GROUP BY "ResidentID", "Last", "ResNumber", "ResAddress", "ResUnitA", "ResUnitB"
HAVING ("Election" = 2008 OR "Election" = 2007)
Since every record may represent more than one entity (person), every field has to use an aggregate (group by, sum, count, avg, ...).
For instance you may add another field
COUNT("Election") AS "Counter"
to the SELECT-list.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bvwputnam
Posts: 6
Joined: Mon Apr 14, 2008 2:31 pm

Re: Tweaking a Query

Post by bvwputnam »

Your suggested script returns an error...

The data could not be loaded.
Not in aggregate function or group by clause

If I remove the GROUP BY it works, but it sums the entries (I get every 2008 AND 2007 entry, 3353 in all). No matter how I format the query using GROUP BY, I get the error.

Is GROUP BY supported in Base, or is there a different way to format this?
bvwputnam
Posts: 6
Joined: Mon Apr 14, 2008 2:31 pm

Re: Tweaking a Query

Post by bvwputnam »

Another idea I had...just learned about the INTERSECT command, so I broke data into separate years and tried this...

SELECT *
FROM Pct9-2008
INTERSECT
SELECT *
FROM Pct9-2007

...but it gives a SQL error, "syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE"

Is this a problem with the query, or the way the data in the table is formatted?
bvwputnam
Posts: 6
Joined: Mon Apr 14, 2008 2:31 pm

Re: Tweaking a Query

Post by bvwputnam »

I'm not sure what I did, but this query...

SELECT "ResidentID", "Last", "ResNumber", "ResAddress", "ResUnitA", "ResUnitB"
FROM "Pct9-2008"
INTERSECT
SELECT "ResidentID", "Last", "ResNumber", "ResAddress", "ResUnitA", "ResUnitB"
FROM "Pct9-2007"
ORDER BY "ResAddress", "ResNumber"

...worked. As long as I edit the working query and do a Save As, the other queries work. The only way I can explain this is that there must have been a hidden character somewhere and somehow it was deleted.
Post Reply