How to select one column with different join condition in 2 tables

Question

1.00/5 (4 votes)

See more:

Posted 16-Jul-17 21:11pm

Member 12530125

Updated 23-Jul-17 23:28pm

v2

Add a Solution

Comments

BinnyVishwant 17-Jul-17 4:32am

Could you please explain clearly! what exactly you need to do/required?

Ramesh Vishvas 17-Jul-17 6:29am

can you describe more about your requirement?

Tomas Takac 17-Jul-17 6:45am

You are missing the FROM clause. The query must look like this: SELECT ... FROM category LEFT JOIN file ON ...

3 solutions

Solution 1

You can use following query

Select id,(Select path from file where A.bigimageid =id ) as bannerPath,
(Select path from file where A.thumbimageid =id ) as ThumbPath FROM Bannerimages A

Posted 17-Jul-17 20:15pm

anarayane

Updated 21-Jul-17 7:44am

v3

Solution 4

Suppose if banner image table if somewhere in bannerid or thumbid contain null value

then how will write query

Can you tell me how to deal with null in join

Posted 23-Jul-17 23:28pm

Member 12530125

Comments

Atlapure Ambrish 24-Jul-17 6:07am

The LEFT join is made for that. In Thomas' solution he has already suggested to use LEFT join, this should solve your problem. What is the issue?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tomas Takac · Accepted Answer · 2017-07-17T20:18:00

You need to join to the file table twice. For that reason you need to assign the table two different aliases so there is no ambiguity when referencing it.

SQL

select
	category.id,
	bannerfile.[path] as bannerimagepath,
	thumbfile.[path] as thumbimagepath
from 
	[category]
	left join [file] as bannerfile on category.bannerid = bannerfile.id
	left join [file] as thumbfile on category.thumbid = thumbfile.id