CODEWITHSUNDEEP
mysqljoin
Brad
Brad

Reputation: 12272

Need help joining table

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.

Upvotes: 1

Views: 142

Answers (3)

Nrj
Nrj

Reputation: 6841

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

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

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 882746

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

Upvotes: 0

Related Questions