join table in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

i have to join table below table:
join this 4 table

schd_mst:sch_id,date
schd_detail:sch_id,cust_id
cust_mst:cust_id,cust_name,city_id
city_mst:city_id,city_name

join this 3 table:

tour_mst:date,cust_id
cust_mst:cust_id,cust_name,city_id
city_mst:city_id,city_name

my 2 query as below:

SQL

select sm.date,cm.cust_name,cim.city_name   from schd_detail inner join schd_mst sm on sm.Sch_id=sd.Sch_id inner join cust_mst cm on sd.cust_id =cm.cust_id inner join city_mst cim on cm.city_name=cim.city_id  where sm.date='2013-11-01'

SQL

select tm.date,cm.cust_name,cim.city_name from tour_mst tm inner join cust_mst cm on tm.cust_id =cm.cust_id inner join city_mst cim on cm.city_name=cim.city_id  where tm.date='2013-11-01'

i want to join in single query with above tables

Posted 8-Dec-13 6:42am

priyanshbhaliya

Updated 8-Dec-13 11:02am

Maciej Los

v2

Add a Solution

Comments

Peter Leow 8-Dec-13 22:52pm

Some error in your original sql statements:

1. there is a missing 'sd' after 'from schd_detail' in the first sql statement, and

2 It should be 'cm.city_id = cim.city_id' and not 'cm.city_name=cim.city_id' in both statements.

5 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)

subbuksr123 · Answer 1 · 2013-12-08T17:44:00

Hi,

select sm.date,cm.cust_name,cim.city_name from schd_detail inner join schd_mst sm on sm.Sch_id=sd.Sch_id inner join cust_mst cm on sd.cust_id =cm.cust_id inner join city_mst cim on cm.city_name=cim.city_id where sm.date='2013-11-01'

Union ALL

select tm.date,cm.cust_name,cim.city_name from tour_mst tm inner join cust_mst cm on tm.cust_id =cm.cust_id inner join city_mst cim on cm.city_name=cim.city_id where tm.date='2013-11-01'

Check this Once it will Work for u........

Maciej Los · Answer 2 · 2013-12-08T20:27:00

Solution 3

You did not specify what kind of problem do you have...

Have a look here: Visual Representation of SQL Joins[^]
Above article should help you understand how JOIN's works ;)

Posted 8-Dec-13 20:27pm

Maciej Los

Comments

priyanshbhaliya 9-Dec-13 8:49am

i also use union but it wont work as i need:
below data:
------------------------------------------------
schd_mst:
sch_id date
1 2013-11-01
-------------------------------------------------------
schd_detail:
sch_id cust_id
1 1
1 2
----------------------------------------------------------
cust_mst:
cust_id cust_name,city_id
1 xyz 1
2 zzz 1
3 zz 1
4 qqq 1
5 qq 1
---------------------------------------------
city_mst:
city_id,city_name
1 aaa

-----------------------------------
tour_mst:
date cust_id
2013-11-01 3
2013-11-01 4
2013-11-01 5

city and cust table is from above:
output i need is below
date cust_name city cust_name city
2013-11-01 xyz aaa qqq aaa
2013-11-01 zzz aaa zzz aaa
2013-11-01 zz aaa

first cust_name and city from :: schd_mst,schd_detail,cust_mst,city_mst
other cust_name and city from :: tour_mst,cust_mst,city_mst

this my query:
select sm.date,cm.cust_name ,cim.city_name,'' customer,'' city from Schd_Deatils sd inner join Schd_mst sm on sm.Sch_id=sd.Sch_id inner join Cust_mst cm on sd.cust_id =cm.cust_id inner join Citymst cim on cm.city_name=cim.city_id where sm.date='2013-11-01'
union all
select tm.date,'' MTPcust,'' as MTPCity,cm.cust_name cust_name,cim.city_name city_name from Tour_mst tm inner join Cust_mst cm on tm.cust_id =cm.cust_id inner join City_mst cim on cm.city_name=cim.city_id where tm.date ='2013-11-01'

Peter Leow · Answer 3 · 2013-12-09T05:39:00

SQL

SELECT sm.date, cm.cust_name, cim.city_name FROM
schd_detail AS sd INNER JOIN  schd_mst AS sm ON sm.sch_id=sd.sch_id
INNER JOIN cust_mst AS cm ON sd.cust_id =cm.cust_id
INNER JOIN city_mst AS cim ON cm.city_id=cim.city_id where sm.date='2013-11-01'
UNION
SELECT tm.date, cm.cust_name, cim.city_name FROM
tour_mst AS tm INNER JOIN cust_mst AS cm ON tm.cust_id =cm.cust_id
INNER JOIN  city_mst AS cim ON cm.city_id=cim.city_id where tm.date='2013-11-01'

Jain Nishant · Answer 4 · 2013-12-09T19:17:00

Solution 5

U can go for CTE(common table expression) as well for that. It's easy to understand...

Posted 9-Dec-13 19:17pm

Jain Nishant

BK 4 code · Answer 5 · 2013-12-10T18:43:00

<br />
select sm.date,cm.cust_name,cim.city_name   <br />
	from schd_detail inner join schd_mst sm on sm.Sch_id=sd.Sch_id <br />
			inner join cust_mst cm on sd.cust_id =cm.cust_id <br />
			inner join tour_mst tm on tm.cust_id =cm.cust_id<br />
			inner join city_mst cim on cm.city_name=cim.city_id  <br />
where sm.date='2013-11-01'<br />

Hope this will help you.

Thanks,
Bhvaesh

join table in sql server

5 solutions

Solution 1

Solution 3

Solution 4

Solution 5

Solution 6

Add your solution here

Preview 0