SQL query question

Question

So...I'm new to sql and loving what I've learned, but here is a question I'm stumped on.

I've created a website form. It's a timecard type form. A user logs on and clicks a pull down menu (which is being selected from the DB) It's called jobsite, then he scrolls down and clicks Jobsite#2. (both fields are selecting from the same table..) When they submit, it submits into the DB just fine. Showing both fields as the ID number they picked.. Not the name..

SELECT  jobsite.Jobsite as 'Customer',
FROM    timeCard
LEFT JOIN jobsite ON timeCard.jobsite = jobsite.id

That query works great. Pulling timeCard.jobsite information. If I change it to

LEFT JOIN jobsite ON timeCard.jobsite2 = jobsite.id

That works great too pulling all the inputted data from Jobsite2...What query will work to get them to show as 'Customer 1, and Customer2' on the same query? Does my question make sense? I'm trying to get it to export into Excel so that it would show up with all the information on one line basically. Employee 1, worked at jobsite 1 and jobsite 4 (but instead show: Employee (me) worked at (this place) and (that place)

Where both jobsites come from the same table in the DB

Thanks for any help you guys/gals can give

Dan J · Accepted Answer · 2011-01-31 21:56:48Z

4

You're going to need to join to the jobsite table twice, because one timecard record is associated with two different jobsite records (assuming some things about your schema and DBMS...):

SELECT t.EmployeeName, j1.jobsite AS 'Customer 1', j2.jobsite AS 'Customer 2'
FROM timecard t
LEFT JOIN jobsite j1 ON t.jobsite = j1.id
LEFT JOIN jobsite j2 ON t.jobsite2 = j2.id

answered Jan 31, 2011 at 21:56

Dan J

16.8k7 gold badges53 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quassnoi · Accepted Answer · 2011-01-31 23:04:00Z

1

SELECT  js1.jobsite AS customer1, js2.jobsite AS customer2
FROM    timecard tc
LEFT JOIN
        jobsite js1
ON      js1.id = tc.jobsite
LEFT JOIN
        jobsite js2
ON      js2.id = tc.jobsite2

edited Jan 31, 2011 at 23:04

answered Jan 31, 2011 at 21:56

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Comments

BlueRaja - Danny Pflughoeft · Accepted Answer · 2011-01-31 21:56:45Z

0

Are you asking for something like this?

SELECT jobsite.Jobsite as 'Customer',
FROM   timeCard, jobSite
WHERE  timeCard.jobsite = jobsite.id
 OR timeCard.jobsite2 = jobsite.id

(PS. Since you mentioned you are new to SQL, I'd like to take this opportunity to make sure you know about parameterized queries (articles on ASP.Net, PHP), which you should always be using instead of concatenating strings)

answered Jan 31, 2011 at 21:56

BlueRaja - Danny Pflughoeft

86.2k36 gold badges208 silver badges297 bronze badges

Collectives™ on Stack Overflow

SQL query question

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related