CODEWITHSUNDEEP
javapostgresql
Mathomatic
Mathomatic

Reputation: 929

Combining multiple SELECT query results from ONE table

My table coordinates is:

id (PK) SERIAL, address (VARCHAR), city (VARCHAR), latitude (VARCHAR), longitude (VARCHAR)

I have a list of 10,000 addresses which I'm looping through to retrieve the corresponding latitude/longitude if the address exists in the table.

The SELECT query looks like:

SELECT ADDRESS, CITY, LATITUDE, LONGITUDE FROM coordinates WHERE address = ? AND city = ?

I then check the result set to see if there was a match. The other 9,999 addresses are then looped through the query above which takes far too long.

Is there a way to create ONE query that contains all 10,000 addresses and returns a result set with 4 columns: |address|city|latitude|longitude|

Any address not found should have an empty (0 or null) lat/lon value in that result set column. I'm assuming I could then loop the result set to identify anything missed, much faster than 10,000 statements.

What's the best approach?

Upvotes: 1

Views: 455

Answers (2)

Teddy
Teddy

Reputation: 4243

The query length might be an issue for 10,000 pairs. Also, if you absolutely need to do it in one hit, you can try this.

Insert the values into a temp table. Then:

SELECT tbl.val1, tbl.val2
FROM tbl
WHERE (tbl.val1, tbl.val2) in (select tmp_table.val1, tmp_table.val2 from tmp_table);

Also alternatively a JOIN, instead of IN.

Update:

(1) Inserts would have to be one per line. Probably create a script file with some COMMIT; statements once in few hundred rows. If it is done from Java you could use JDBC addBatch to commit once in 500/1000 rows.

--Create temp table before this with two columns address (VARCHAR), city (VARCHAR)
INSERT INTO TMP_TABLE VALUES ( ?, ?); //Prepare statement and addBatch

(2, 3, 4) 

SELECT ADDRESS, CITY, LATITUDE, LONGITUDE
FROM coordinates
WHERE (ADDRESS, CITY) in (select tmp_table.address, tmp_table.city from tmp_table);

Upvotes: 1

Andreas
Andreas

Reputation: 159135

UPDATE: Changed to include unmatched "query" addresses in the result, and added QUERY_ID to help identify "query" addresses, which could be just an index in the list supplying the query parameters.

Since it is PostgreSQL, you can use the VALUES clause, e.g.

SELECT q.QUERY_ID
     , c.ADDRESS
     , c.CITY
     , c.LATITUDE
     , c.LONGITUDE
  FROM (VALUES (1, ?, ?)
             , (2, ?, ?) // repeat as many times as needed
             , (3, ?, ?)
       ) AS q (QUERY_ID, ADDRESS, CITY)
  LEFT JOIN coordinates AS c
       ON  c.ADDRESS LIKE q.ADDRESS
       AND c.CITY LIKE q.CITY

Now you need to loop through your 10000 addresses and set all the values for that PreparedStatement.

Upvotes: 1

Related Questions