Join only when table 2 has a value

Question

0.00/5 (No votes)

See more:

Is it possible to compose a piece of SQL to retrive data from two tables where the second table may or may not have a value.

e.g.

 Select t1.name, t2.diplomatype
from studentmaster t1, resultmaster t2
where t1.result = t2.result
or t1.result is null

Desired Ouitput
Name DiplomaType
---- -----------
Tom Pass
Mary Honours
Jack
Liz Pass

... Or do I need to use a stored proocedure?

Posted 4-Jan-11 21:50pm

Ger Hayden

Add a Solution

Comments

Manas Bhardwaj 5-Jan-11 6:12am

Looks like this thread has a bad karma. All the answers have 1 Vote :)

Rajesh Anuhya 5-Jan-11 6:36am

Why all answers are voted 1 ???????????? :)

Ger Hayden 5-Jan-11 9:34am

I've just voted on 4 of the 5 anwsers. I'll have to look at the other later. Nothing less than a 4 awarded. Trouble is all those 1's are weighing down some very specific, accurate and helpfull answers

4 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hiren solanki · Accepted Answer · 2011-01-04T22:00:00

Solution 5

Yes offcourse Use MySQL Outer JOIN.

See Here[^]

Posted 4-Jan-11 22:00pm

Hiren solanki

Updated 4-Jan-11 22:03pm

v2

Comments

Ger Hayden 5-Jan-11 9:33am

As with Ans 3, its what I needed to look up but the Rumsfeld factor got me

Manas Bhardwaj · Accepted Answer · 2011-01-04T21:53:00

Solution 1

SELECT *
FROM
StudentMaster T1
    LEFT JOIN RESULTMASTER T2 ON
       T1.Result = T2.Result

Posted 4-Jan-11 21:53pm

Manas Bhardwaj

Comments

Ger Hayden 5-Jan-11 9:28am

Perfect. Exactly what I want. Looking at it I was sceptical about getting a value up out of T2 but it is a thing of beauty in its simplicity.

adocas · Accepted Answer · 2011-01-04T21:57:00

Solution 4

You may use an Outer Join:

SQL

Select t1.name, t2.diplomatype
from studentmaster t1 LEFT OUTER JOIN resultmaster t2
on  t1.code = t2.code
where 1=1

You must put the join conditions in the ON expression, and it's done.

Posted 4-Jan-11 21:57pm

adocas

Updated 4-Jan-11 21:58pm

v2

Comments

Ger Hayden 5-Jan-11 9:32am

Perfect, as is Ans 1

Rajesh Anuhya · Accepted Answer · 2011-01-04T21:53:00

Solution 3

You are looking for this [^] ????

Posted 4-Jan-11 21:53pm

Rajesh Anuhya

Comments

Ger Hayden 5-Jan-11 9:30am

I am. But I was having a Donald Rumsfeld moment.

Join only when table 2 has a value

4 solutions

Solution 5

Solution 1

Solution 4

Solution 3

Add your solution here

Preview 0

Join only when table 2 has a value

4 solutions

Solution 5

Solution 1

Solution 4

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us