Inner join without optional columns

[Drongo_III]

Hello

 

Very much stuck on this and it's held me up for a good few hours so help is much appreciated - i'll try and keep this brief.

 

Essentially I have two tables - a 'users' table with cols for user id, email, name etc. and a 'jobs' table containing a lot of columns that I won't list but there are two that are relevant to this.

 

The two columns are 'user_id' and 'request_other' - both of these are relational to the users table user id. There are two cols because the application stores data on the person adding the job (in user_id) and the person who requested it (in request_other). This latter field get set to '0' if the person adding the job is also the requester.

 

So here's where I'm stuck. When I query the jobs table to populate some data in a page I need to get the email address, based on the user_id, from the users table. So I query the jobs table and perform an inner join as follows and all works well(stripped out most of the fields for the sake of clarity):

 

SELECT jobs.job_number, jobs.brand, jobs.description, users.email FROM jobs INNER JOIN users on jobs.user_id = users.id WHERE jobs.status !='live' ORDER BY jobs.brand ASC, jobs.date_created DESC

 

Here the join is done on the basis of a match between user_id in both tables. BUT what I really want is to conditionally alter the '...INNER JOIN users on' part of the query to say:

 

"If request_other !=0 then use request_other = users.id ELSE use jobs.user_id = users.id"

 

I dont know if that's possible or not? I've tried adding in cases and If statements but nothing seems to work as I am probably doing it wrong - despite a lot of trying :/

 

If anyone could suggest a means of achieving what I am after I would be eternally thankful!

[Jessica]

I believe you'd have to make them LEFT joins, then use an IF in your selected columns list to get the right info. 

[shlumph]

This is an interesting problem. It sounds like you're going to have to join on the users table twice. Once doing users.id = jobs.request_other and a second time doing users.id = jobs.user_id. Select only distinct records, and order request_other appropriately.

 

At second thought, I think you could do this: SELECT ... INNER JOIN jobs ON (users.id = jobs.request_other) OR (users.id = jobs.user_id) ... and then order appropriately.

Edited February 13, 2013 by shlumph

[Barand]

try

 

SELECT jobs.job_number, jobs.brand, jobs.description,
CASE WHEN jobs.request_other <> 0 THEN req.email ELSE users.email END as email
FROM jobs
INNER JOIN users on jobs.user_id = users.id
LEFT JOIN users as req ON jobs.request_other = req.id
WHERE jobs.status !='live'
ORDER BY jobs.brand ASC, jobs.date_created DESC

 

or you could replace the CASE...END with

 

IFNULL(req.email, users.email) as email

Edited February 13, 2013 by Barand

[kicken]

 This latter field get set to '0' if the person adding the job is also the requester.

 

Don't do that, just set them both to the same ID.  The your joins become simple:

SELECT jobs.job_number, jobs.brand, jobs.description, users.email 
FROM jobs 
INNER JOIN users addedBy on jobs.user_id = addedBy.id 
INNER JOIN users requestedBy ON jobs.request_other = requestedBy.id
WHERE 
 jobs.status !='live' 
ORDER BY jobs.brand ASC, jobs.date_created DESC

 

If you need details on both who added it and who requested it.  

 

If you only need details on the requester then only join for that field.

SELECT jobs.job_number, jobs.brand, jobs.description, users.email 
FROM jobs 
INNER JOIN users requestedBy ON jobs.request_other = requestedBy.id
WHERE 
 jobs.status !='live' 
ORDER BY jobs.brand ASC, jobs.date_created DESC

Edited February 13, 2013 by kicken

[Drongo_III]

Thanks Guys

 

Lots of very helpful suggestions there. I will give these a whirl tomorrow in work and see what fits best. I shall report back!

 

Many thanks!

 

Drongo

[fenway]

You can use COALESCE(), too.

[Drongo_III]

I went with Barand's suggestion as it seemed more inline with the way I envisoned it working and it worked a treat.

 

I confess I've only used very simple joins and I would like to understand mutilple joins better - I am a bit weak on my sql. So I'm hoping someone can answer a few questions on this...

 

Firstly this line:

 

CASE WHEN jobs.request_other <> 0 THEN req.email ELSE users.email END as email

 

The only thing confusing me is the 'as email' bit at the end. Why do you give it the alias 'email'? It doesn't appear to be used anywhere else in the query so what's the purpose here?

 

Also the req.email part. 'req' at this point in the query has no meaning so am I right in saying that it becomes meaningful when you alias it as 'uses as req' later in the query?

 

 

And my last question is about how the query gets executed with mutliple joines like this. Is it the case that the Inner join statement will run first and retrieve all email results where and ID matches in both tables. Then the query gets re-run with the Left join statement and overwrites the email results whereever there is an id match resulting from 'jobs.request_other <> 0' in boht tables? Is that right?

 

Many thanks for your patience,

 

Drongo

 

 

 

 

try

 

SELECT jobs.job_number, jobs.brand, jobs.description,
CASE WHEN jobs.request_other <> 0 THEN req.email ELSE users.email END as email
FROM jobs
INNER JOIN users on jobs.user_id = users.id
LEFT JOIN users as req ON jobs.request_other = req.id
WHERE jobs.status !='live'
ORDER BY jobs.brand ASC, jobs.date_created DESC

 

or you could replace the CASE...END with

 

IFNULL(req.email, users.email) as email

[Barand]

Firstly this line:

 

CASE WHEN jobs.request_other <> 0 THEN req.email ELSE users.email END as email

 

The only thing confusing me is the 'as email' bit at the end. Why do you give it the alias 'email'? It doesn't appear to be used anywhere else in the query so what's the purpose here?

 

I gave it the alias "email" as it's easier, when processing the query results, to refer to

$row['email']

instead of

$row['CASE WHEN jobs.request_other <> 0 THEN req.email ELSE users.email END']

 

Also the req.email part. 'req' at this point in the query has no meaning so am I right in saying that it becomes meaningful when you alias it as 'uses as req' later in the query?

 

Yes. The second users table is given the alias "req" so we can distinguish between columns from the two users tables. I used a LEFT JOIN as there may not always be a match (the zero id case) with that table. An INNER JOIN in this case would give no resulting row at all.