Select query for Multiple tables with one Common Column

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

a select query for Multiple tables with one Common Column

i have tried something below but not working

SQL

Select *  from DM_Audit da,  DM_APPLICANTS dap , DM_POLICY dp, DM_Names dn,DM_Address ad 
where da.fk_applicationID=dap.fk_applicationID and
dap.fk_applicationID=dp.fk_applicationID and 
dp.fk_applicationID=dn.fk_applicationID and
dn.fk_applicationID=ad.fk_applicationID

Here fk_applicationID is Common column in all the table

New to the Sql Please Help

Posted 21-Aug-14 21:08pm

Omkaara

Add a Solution

Comments

Sanchayeeta 22-Aug-14 3:32am

Can you please explain your question little more, like if you can give an example what output you want.

Ashi0891 22-Aug-14 3:40am

this code works for me, may be you are not getting your required result. Please Use improve question option to explain more and also add your desired output.

_Asif_ 22-Aug-14 4:25am

The problem seems to be with your data. You turn off each join and see where data starts missing.

Jörgen Andersson 22-Aug-14 6:03am

What's not working?
Update your question with more info.

4 solutions

Solution 4

SQL

Select *  from DM_Audit as da,  DM_APPLICANTS as dap , DM_POLICY as dp, DM_Names as dn,DM_Address as ad
where da.fk_applicationID=dap.fk_applicationID and
da.fk_applicationID=dp.fk_applicationID and
da.fk_applicationID=dn.fk_applicationID and
da.fk_applicationID=ad.fk_applicationID

Posted 4-Nov-14 0:25am

kskumaran

Solution 2

Try to Create a View then its very much clear and after creating view your query will automatically created

Posted 21-Aug-14 21:26pm

Awadhendra Tripathi

Solution 5

Seems your data is wrong or either your join is wrong, i am not sure what you are try to do any way here is my suggesstion

SQL

Select *
from DM_Audit da
Left join  DM_APPLICANTS dap  on da.fk_applicationID=dap.fk_applicationID
Left join  DM_POLICY dp　on　dap.fk_applicationID=dp.fk_applicationID
Left join  DM_Names dn　on dp.fk_applicationID=dn.fk_applicationID
Left join  DM_Address ad  on dn.fk_applicationID=ad.fk_applicationID

use the above query with a where condition and Not NULL check for your each table one by one, then you are able to find the wrong data easily
hope you got it

Posted 4-Nov-14 0:30am

Thava Rajan

Updated 4-Nov-14 0:32am

v2

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)

Omkaara · Accepted Answer · 2014-11-04T00:12:00

The column name may be the same, but does it reference data for the intended records in each table?

If it definitely does, then re-writing the query using joins would be an easier way of checking the links:

SQL

Select *  
from DM_Audit da  
INNER JOIN DM_APPLICANTS dap ON dap.fk_applicationID=da.fk_applicationID

etc........
I would usually have a 'key' table in this situation (i.e. with the most data in it, or one which links to the most other tables which need to be joined) Then it is much easier to join the other tables.

N.B. if not all tables join to the key table, join as many as you can first, then join to the 'joined' tables further down the statement.