How to write the exact query using joins. Please guide me

Question

0.00/5 (No votes)

See more:

--Scenario

--first table
select * from main

--here the addressid of A IS 10...addressid of B IS 20....addressid of C IS 30
--two hub id is there in the table 20 and 136
address_id hub_id location distance
10 20 A NULL
20 20 B NULL
30 20 C NULL
40 20 D NULL
10 136 A NULL
20 136 B NULL
30 136 C NULL
40 136 D NULL

---------------------------------------------------------------------------------------------

--second table
select * from distance

--HERE THE ADDRESSID OF A That is 10 is 50 km from hub 136
--HERE the addressid OF B That is 20 is 50 km from hub 136

address_id hub_id location distance
10 136 A 50
20 136 B 75
30 136 C 85
40 136 D 95

=================Desired output which i want ============================================
--now i have to select in main table(first table) with HUB_ID 136 .
--desired output as

ADDRESS_ID HUB_ID LOCATION DISTANCE
10 136 A 50
20 136 B 75
30 136 C 85
40 136 D 95

--and if i try to select the hub as 20 distance should be null as distance is not there for hub_id 20 in second table(distance table)

ADDRESS_ID HUB_ID LOCATION DISTANCE
10 136 A NULL
20 136 B NULL
30 136 C NULL
40 136 D NULL

============================================================================================

-- i was trying to write in this way
--ok this is working
select m.address_id,m.hub_id,m.location,d.distance
from main m
join distance d on m.address_id=d.address_id
where m.hub_id=136

address_id hub_id location distance
10 136 A 50
20 136 B 75
30 136 C 85
40 136 D 95

-- but if i select for 20--this is not desired output as distance should be null
select m.address_id,m.hub_id,m.location,d.distance
from main m
join distance d on m.address_id=d.address_id
where m.hub_id=20

address_id hub_id location distance
10 20 A 50
20 20 B 75
30 20 C 85
40 20 D 95

-- i was trying this ...but no result
select m.address_id,m.hub_id,m.location,d.distance
from main m
left join distance d on m.address_id=d.address_id
where m.hub_id=20
and d.hub_id=20

please help this is urgent...

Posted 19-Sep-13 9:30am

anurag19289

Add a Solution

2 solutions

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)

chaau · Accepted Answer · 2013-09-19T13:18:00

When you join the tables make sure you include all columns that need to be linked in your ON clause. Your WHERE clause should contain only the filter for the main table:

SQL

select m.address_id,m.hub_id,m.location,d.distance
from main m
left join distance d on m.address_id=d.address_id AND m.hub_id=d.hub_id AND m.location = d.location
where m.hub_id=20

And please, use the tags when you writing the questions

VeeraAnnapureddy · Accepted Answer · 2013-09-19T18:31:00

SQL

;with cte(address_Id,hub_id,location,distance)
as
(
select  m.address_id
        ,   m.hub_id
        ,   m.location
        ,   d.distance
            from main as m
            left join Distance as d
            on m.address_id = d.address_id
            and m.hub_id = d.hub_id
            and m.location = d.location
)

select * from cte
where hub_id = 20

How to write the exact query using joins. Please guide me

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0