Need help joining table

Question

I have a function that exports a table to CSV and in the query I set which fields will export.

Here is the query:

SELECT lname, fname, email, address1, address2, city, 
state, zip, venue_id, dtelephone, etelephone, tshirt FROM volunteers_2009

The field venue_id is the the id of the venue which is referred to in another table (venues)

So volunteers_2009.venue_id = venues.id

When I open the CSV file it displays the venue_id which I understand, but I need help modifying the query to put in the name of the venue (venues.venue_name) within the CSV file.

Any help is appreciated.

Answer 1

Standard SQL query for this is (assuming you want both ID and name for the venue):

SELECT a.lname as lname, a.fname as fname, a.email as email,
    a.address1 as address1, a.address2 as address2, a.city as city, 
    a.state as state, a.zip as zip, a.venue_id as venue_id,
    b.venue_name as venue_name, a.dtelephone as dtelephone,
    a.etelephone as etelephone, a.tshirt as tshirt
FROM volunteers_2009 a, venues b
WHERE a.venue_id = b.id
AND a.venue_id IS NOT NULL
UNION ALL
SELECT a.lname as lname, a.fname as fname, a.email as email,
    a.address1 as address1, a.address2 as address2, a.city as city, 
    a.state as state, a.zip as zip, a.venue_id as venue_id,
    '' as venue_name, a.dtelephone as dtelephone,
    a.etelephone as etelephone, a.tshirt as tshirt
FROM volunteers_2009 a
WHERE a.venue_id IS NULL

Answer 2

SELECT lname, fname, email, address1, address2, city, state, zip, b.venue_name, dtelephone, etelephone, tshirt FROM volunteers_2009 a, venues b where a.venue_id = b. venue_id

use proper alias in case both tables have any columns with the same name.

--- EDIT to have all the rows from venues use outer join as

SELECT lname, fname, email, address1, address2, city, state, zip, b.venue_name, dtelephone, etelephone, tshirt FROM volunteers_2009 a(+), venues b where a.venue_id = b. venue_id

Answer 3

SELECT a.lname, a.fname,a. email, a.address1,a. address2, a.city, 
    a.state, a.zip, a.venue_id, a.dtelephone, a.etelephone, a.tshirt,
    COALESCE(b.venue_name,'') AS VenueName
FROM volunteers_2009 a
LEFT JOIN venues b ON b.id=a.venue_id