SQLITE- IF statement for querying data

I am trying to query data and I found out the keys to join the table. However the data is only 1 to 1 if its from the same "STATE". This is a sample of the data

Name  | State| ID    <--precinct table            Name | State | ID
BILLY | TN   | 03     Polling location Table-->   stat1| NY    | 01
TRENT | NY   | 03                                 stat2| TN    | 03
TRUDY | NJ   | 01                                 stat3| NJ    | 03
WILL  | KY   | 22                                 stat4| KY    | 22
PAUL  | LA   | 25                                 stat5| LA    | 25

So those are sample data of the two table I want to join but my query looks like:

SELECT precinct.street,
   precinct.apt,
   precinct.city,
   precinct.state,
   precinct.zip,
   polling_location.street AS [Polling Street],
   polling_location.city AS [Polling City],
   polling_location.state AS [Polling State],
   polling_location.zip AS [Polling Zip],
   polling_location.country AS [Polling Country]
FROM precinct
   LEFT JOIN
   polling_location ON precinct.ID = polling_location.ID and polling_location.state = precinct.state

So I joined the tables by the ID and the State so that the data will be matching with the correct location. But by also joining it by state, Trudy and Trent will return Null values, If I dont add state Billy will have duplicate incorrect data.

Is there a If statement that could resolve this?

1 answer

  • answered 2018-04-14 15:49 MikeT

    As per the comments your data does not make sense. You are joining on ID's and States the latter makes sense. However the use of ID doesn't. Typically ID is used for the IDentification of something, so duplicates such as Billy and Trent having the same ID* is contrary to the normal use of **ID as it indicates/points to both not one. The same applies to the use of ID for the polling location.

    It could be that ID is used to relate to something else. If so this hasn't been made at all clear.

    I believe your issues stem from the ID's not really having any relevance as they are.

    If the ID's were used as would generally be accepted, then each name in the precinct table would have a unique ID, likewise each polling_location would have a unique ID.

    Assuming this (and also giving each State an ID in a states table) Then the tables could look like :-

    The States (new table):-

    enter image description here

    • etc....

    The precinct table :-

    enter image description here

    The polling_location table :-

    enter image description here

    You could then, as an example, create a list of precincts and where they could vote, assuming a precinct can only vote at a polling location in the same state, using :-

    SELECT precinct.name||' who lives at '||
         precinct.apt||' '||
         precinct.street||', '||
         precinct.city||', '||
         states.name||' '||
         precinct.zip||' '||
         states.Code||' can vote at:- '||group_concat(polling_location.name||' -'||polling_location.street||', '||polling_location.city,';  ') AS voting_places
    FROM precinct 
        JOIN states ON precinct.state = states.id 
        JOIN polling_location ON precinct.state = polling_location.state
    GROUP BY precinct.id ORDER by states.Name
    

    Which would result in :-

    enter image description here

    Will who lives at 55 The Spade, Diggington, Kentucky 75463 KY can vote at:- stat4 -40 Cord Boulevard, Zetton
    Paul who lives at 1020 Edmonton Circuit, Clarraville, Louisiana 23456 LA can vote at:- stat5 -50 Thread Avenue, Kalagoro
    Judy who lives at 62 Burlington Place, Heston, New Jersey 89766 NJ can vote at:- stat3 -30 Twine Lane, Alerton
    Trent who lives at 7a Wash Alley, Finker, New York 76543 NY can vote at:- stat1 -10 Rope Street, Roppington;  stat11 -60 Cotton Crescent, Luton;  stat12 -70 Wool Circuit, Eddingter
    Billy who lives at 1 Fluster Road, Luxtan, Tennessee 12245 TN can vote at:- stat2 -20 String Way, Blowingstone
    

    Is there a If statement that could resolve this?

    There is no IF keyword, there are some KEYWORDS that can be used for conditional processing.

    For example in the above the GROUP BY precenct.id is saying merge all rows with the same precinct ID into one row.

    • This is used in the above to get all the polling_locations per precinct by utilising the aggregate function group_concat

    SQL As Understood By SQLite - Aggregate Functions (not really an equivalent of IF as such though).

    WHERE can be used to select rows IF they meet the criteria. - For example WHERE precinct.name LIKE '%l' would only select rows where the precinct.name ended in l (or L), so Will and Paul but none of the rest.

    CASE WHEN ... THEN ... ELSE ... END can be used to conditionally add columns to the results. and there's plenty more you can do.