Query Accross Multiple Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Accross Multiple Tables

Author

Topic

NAPS
Starting Member

4 Posts

Posted - 2015-04-21 : 11:16:38

Dear all,

I am trying to write a query which includes the following two tables.

Incidents
Column 1 = SourceContactNo

Contacts
Column 1 = ContactNo
Column 2 = Ref

The first columns in Incidents and Contacts both contain the same type of information (contact numbers, only the column names are different, some contact numbers appear in table 2 (contacts) which do not appear in incidents).

What I really want to do is return the Contact Number & Ref from the table named contacts where the contact number is the same as SourceContactNo (in the first table incidents).

I hope I have explained this well enough! Can anybody help?

Here is what I have so far:

SELECT Incidents.SourceContactNo, Contacts.ContactNo, Contacts.REF
FROM Contacts, Incidents

"There are no failures, only results".

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-21 : 11:43:56

You can do the following, assuming that SourceContactNo is unique in Incidents table - i.e., there is only one row for any given SourceContactNo. If there can be multiple incidents for a given contact number, this would not be true.

SELECT  i.SourceContactNo ,
        c.ContactNo ,
        c.REF
FROM    Contacts c
		INNER JOIN Incidents i ON
			i.SourceContactNo = c.ContactNo;

If you can have multiple incidents for a given SourceContactNo, use this instead:

SELECT  c.ContactNo ,
        c.REF
FROM    Contacts c
WHERE	EXISTS 
		(	SELECT * FROM Incidents i
			WHERE i.SourceContactNo = c.ContactNo
		);

By the way, in your original query, since Incidents.SourceContactNo and Contacts.ContactNo are going to be the same (because that is what you are querying for), you don't need both columns in the select list.

NAPS
Starting Member

4 Posts

Posted - 2015-04-21 : 12:02:41

It worked! Fantastic, thanks for the information James K.

"There are no failures, only results".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources