Clever Geek Handbook

Need help joining a table

I have a function that exports a table to CSV and in a query I am setting which fields will be exported.

Here is the request:

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

The place_id field is the identifier of the place referred to in another table (venue)

So volunteers_2009.venue_id = venues.id

When I open the CSV file, it displays the event_id, which I understand, but I need help modifying the request to indicate the place name (venues.venue_name) in the CSV file.

Any help is appreciated.

+1


source to share


3 answers 


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


+1


source


A standard SQL query for this (assuming you want both an ID and a 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
0


source


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

use the correct alias if both tables have columns with the same name.

--- EDIT so that all lines from meeting places use outer join as

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

0


source






More articles:


All Articles