Joining the same table twice in one query

[TheDust]

I have a table I want to join twice in one SELECT statement and I can do it just fine. However, the column I want that gets pulled in has the same name in both instances and I don't know how to name them uniquely. Here is a stripped down version of what I'm trying to do:

SELECT * FROM jobs 
LEFT JOIN alerts AS jobStatusAlerts ON jobStatuses.alertID=jobStatusAlerts.alertID 
LEFT JOIN alerts AS priorityAlerts ON priorities.alertID=priorityAlerts.alertID

The table 'alerts' has only 2 columns - 'alertID' and 'alert'. I want to know how I can rename the 'alert' column to have a unique name for both instances of it. Right now the way the query runs it returns 2 columns named simply 'alert'.

Help would be much appreciated on this issue... I've looked around for advice to no avail. Anyone?

 

[ericbrunson]

Alias the table:

select a.field, b.field
from jobs a, jobs b
where ...

 

[sleipnir214]

Use your alias names in your column references.

For example, to refer to a column from the first time in your query that you join alerts, use jobStatusAlerts.{columnname}


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[TheDust]

I was under the impression that I already am aliasing the tables:

SELECT * 
FROM jobs
LEFT JOIN jobStatuses ON jobs.jobStatusID = jobStatuses.jobStatusID
LEFT JOIN priorities ON jobs.priorityID = priorities.priorityID
LEFT JOIN alerts AS jobStatusAlerts ON jobStatuses.alertID = jobStatusAlerts.alertID
LEFT JOIN alerts AS priorityAlerts ON priorities.alertID = priorityAlerts.alertID

I'm aliasing 'alerts' the first time as 'jobStatusAlerts' and 'alerts' the second time as 'priorityAlerts'. When I run this query it is not giving me 'jobStatusAlerts.alert' and 'priorityAlert.alert' as columns like I would expect. Instead I get two instances of columns named 'alert'. So obviously I'm not understanding and not aliasing my tables correctly...

eric, in your response I'm having a tough time determining what 'a' and 'b' are supposed to represent. Are they tables or columns?

 

[ericbrunson]

You're using column aliases, we're telling you to a) alias your table and b) reference the table twice.

What you're doing obviously doesn't work.

 

[ericbrunson]

a and b are the aliases of your tables. Imagine you had two copies of the same table, named a and b. Then simply operate on them as they were two different tables, mysql doesn't the work.

 

[TheDust]

OK... I've been trying everything out, but to no avail just yet. Am I on the right track with this?

SELECT p.priorityID
, p.priority
, p.priorityOrder
, p.alertID AS priorityAlert
, js.jobStatusID
, js.jobStatus
, js.jobStatusOrder
, js.alertID AS jobStatusAlert 
FROM priorities p, jobStatuses js 
LEFT INNER JOIN alerts a1 
ON p=a1.alertID 
LEFT INNER JOIN alerts a2 
ON js.alertID=a2.alertID

 

[ericbrunson]

I see the problem with my examples, I was looking at your first post and I thought the table was "jobs".

It should look like

SELECT p.priorityID
, p.priority
, p.priorityOrder
, p.alertID
, js.jobStatusID
, js.jobStatus
, js.jobStatusOrder
, js.alertID
FROM alerts p, alerts js
WHERE p.alertID = js.alertID

I've never been comfortable with the "LEFT INNER JOIN" syntax. The WHERE clause makes much more sense to me.

 

[r937]

maybe the reason you've never been comfortable with LEFT INNER JOIN is because there is no such thing

you probably meant LEFT OUTER JOIN

and there is no way to achieve the results of a LEFT OUTER JOIN without using LEFT OUTER JOIN syntax

once you get used to writing INNER JOIN you won't go back to the table list method, especially when there are more than two tables being joined, simply because it isolates the join conditions away from all the other cruft in the WHERE clause

When I run this query it is not giving me 'jobStatusAlerts.alert' and 'priorityAlert.alert' as columns like I would expect.

that's right, you're getting two columns with the same name, "alert"


what you want to do in that case is use column aliases as well

e.g.

SELECT p.alertID as P_alertID
, js.alertID as JS_alertID
, ...

rudy

http://r937.com/

SQL Consulting

 

[ericbrunson]

r937, did you read the entire thread or just jump in at the very end?

In his code he has:

FROM priorities p, jobStatuses js

from what I can tell from his posts, there are no tables priorities or jobstatuses, he's trying to join the table alerts to itself.

I've never had to use the LEFT OUTER JOIN syntax because Oracle has their own syntax (broken though it is) for doing outer joins.

 

[r937]

eric, i did read the whole thread

the example i gave him was poorly chosen -- i was working off his last query, and should have referred to the one before that

thedust, do you see that you should be using both column aliases and table aliases?

rudy

http://r937.com/

SQL Consulting

 

[ericbrunson]

Yeah, I did the same thing when I made my comment on "LEFT INNER JOIN" since that's what he used in his query.

I've since read the manual on LEFT (OUTER) JOIN and I understand where it's coming from. No more or less intuitive than Oracles nomenclature (I always thought the (+) should go on the other column)

 

[r937]

yeah, that plus sign is confusing

hey, do you know where the asterisk goes in microsoft's old outer join syntax -- is it the same side as oracle's plus sign or isn't it?

as soon as you deal with more than one "proprietary" version of sql, you see the immediate benefit of the JOIN syntax



rudy

http://r937.com/

SQL Consulting

 

[TheDust]

Sorry I didn't get a chance to check on this job today... very busy around my office.

Anyways, I am trying to link to alerts on two different occasions because the table 'priorities' and 'jobStatuses' both have alertID columns to correlate to the table 'alerts'. The table 'priorities' and 'jobStatuses' are part of a much larger query involving lots of left joins. I'll try your suggestions out tomorrow... if you think the query you gave me isn't what I'm looking for I'll welcome any other advice you might have... thanks so much for all of your help!

 

[ericbrunson]

Alright, your being totally confusing. I though you were trying to join one table to itself. Now you're saying priorities and jobstatuses are *also* tables?

Never mind, I've given up until you figure out how to express your problem clearly.

 

[TheDust]

OK... here is the table layout:

priorities
----------
priorityID
priority
alertID

jobStatuses
-----------
jobStatusID
jobStatus
alertID

alerts
------
alertID
alert

'priorities' and 'jobStatuses' are part of a larger select query with many left joins and I just need to figure out how to LEFT JOIN on 'alerts' twice and get a different column name returned for the column 'alert' so that I can differentiate which value belongs to which table.

I hope this makes more sense. I'm really sorry I wasn't clear enough on the problem earlier...

 

[TheDust]

Basically, this query is working for me, but I'm getting back 2 columns named 'alert'. I need to know how and where to place column aliases to get back 2 different column names instead:

SELECT 
* FROM jobs j
LEFT JOIN jobStatuses js ON j.jobStatusID=js.jobStatusID 
LEFT JOIN priorities p ON p.priorityID=j.priorityID 
LEFT JOIN alerts a ON p.alertID=a.alertID 
LEFT JOIN alerts a2 ON js.alertID=a2.alertID 
LIMIT 0, 30

 

[TheDust]

It looks like I've got it thanks to your guys' help! Here is what worked for me:

SELECT 
j.*, a.alert AS priorityAlert, a2.alert AS jobStatusAlert FROM jobs j
LEFT JOIN jobStatuses js ON j.jobStatusID=js.jobStatusID 
LEFT JOIN priorities p ON p.priorityID=j.priorityID 
LEFT JOIN alerts a ON p.alertID=a.alertID 
LEFT JOIN alerts a2 ON js.alertID=a2.alertID

Sorry for the confusion, but still without your guys' guidance into the right direction I wouldn't have figured this out on my own. Thank you so much!