noob join question

Question

Sorry if this is a an overly simplistic question; I'm still very new to SQL.

I have two tables in my db. The first table is a list of all contacts. Each contact has a unique identifying key in stored in a column labeled idkey. There are roughly 8,000 contacts. I have a second table that contains all the current projects for my contacts. There are roughly 1,500 current projects. Some contacts do not have any current projects. Each project has the identifying key that corresponds to one of the contacts. I want a list of contacts that have current projects. I think I need a JOIN query but, assuming I'm correct, I'm not sure what type of JOIN I need. I've tried the following:

SELECT * FROM contacts
INNER JOIN projects
ON contacts.idkey = projects.idkey;

and

SELECT * FROM contacts
LEFT JOIN projects
ON contacts.idkey = projects.idkey;

and

SELECT * FROM contacts
RIGHT JOIN projects
ON contacts.idkey = projects.idkey;

and in each case I get output that seems to run infinitely (I've let it go until it hits as many as 6 million results before giving up) and generate a massive number of duplicates. I thought of INNER JOIN as the overlap of the Venn diagram where the table on the left contains all the contacts, the table on the right contains all the projects and the overlap in the middle contains just the contacts that have projects. I think I'm close, but I'm struggling.

Answer 1

Hi @Ariel Benjamin ,

Thanks for your update.

You could add one DISTINCT and then you could remove all duplicated records.

  SELECT distinct a.*  
 FROM azbContacts a  
 INNER JOIN azbMatters b  
 ON a.ILSKey=b.ILSKey  

Output:

FIRSTNAME	LASTNAME	ILSKey  
John	Public	1234  
Sam	Doe	9012  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Ariel Benjamin ,

Based on your description, you could use a INNER JOIN.

Please refer below a simple example:

create table contacts  
(idkey int primary key,  
contactid int)  
  
insert into contacts values  
(1,846558752),  
(2,548712841),  
(3,811326871)  
  
create table projects  
(idkey int primary key,  
projectid int)  
  
insert into projects values  
(1,5123),  
(2,6952)  
  
select a.*  
from contacts a  
inner join projects b   
on a.idkey=b.idkey  

If you still face any concern or issue, you could post the CREATE TABLE statements for your tables(contacts and projects) together with INSERT statements with sample data like I provided above.

We also need to see the expected result of the sample.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Here's a stripped down version. The CREATEs were generated by SQL (though I left out most of the fields) using the Script Table As tool:

CREATE TABLE [dbo].[azbContacts](
    [FIRSTNAME] [varchar](30) NULL,
    [LASTNAME] [varchar](30) NULL,
    [ILSKey] [varchar](36) NULL,
) 

CREATE TABLE [dbo].[azbMatters](
    [MatterID] [varchar](25) NOT NULL,
    [ShortDesc] [varchar](70) NULL,
    [ILSKey] [varchar](36) NULL,
) 

INSERT INTO azbContacts (FIRSTNAME, LASTNAME, ILSKey) VALUES
(‘John’, ‘Public’, ‘1234’),
(‘Jane’, ‘Smith’, ‘5678’),
(‘Sam’, ‘Doe’, ‘9012’);

INSERT INTO azbMatters (MatterID, ShortDesc, ILSKey) VALUES
(‘1002.1’, ‘Original dispute’, ‘1234’),
(‘1002.3’, ‘Some other dispute’, ‘1234’),
(‘1004.1’, ‘Original dispute’, ‘9012’)

SELECT a.*
FROM azbContacts a
INNER JOIN azbMatters b
ON a.ILSKey=b.ILSKey

The output should be a table that contains (‘John’, ‘Public’, ‘1234’) and (‘Sam’, ‘Doe’, ‘9012’).