asked on

How to join 2 SQL tables without primary key

Greetings Experts.

How do I join 2 tables where there is no primary key. There is way to determine each record uniquely by using a combination of 2 fields, for example

Table1
Study Site CRA <---column headings
INOT11 001 Ben
INOT11 002 Bill
INOT11 003 Jen
INOT20 001 Jeff

Table2
Study Site Category Count
INOT11 001 Unreviewed 50
INOT11 001 Closed 21
INOT11 001 Pending 10
INOT11 001 Resolved 9

I am looking to combine both tables on Study and site, then determine the COUNT of unreviewed only. So my output would look like this,

Study Site Category Count CRA
INOT11 001 Unreviewed 50 Ben

I cant figure it out. Your help is greatly appreciated. Thanks.

Guy Hengel [angelIII / a3]

here we go:

select t2.*, t1.CRA
 from table2 t2
 join table1 t1
   on t1.Study = t2.Study
  and t1.Site = t2.Site

Open in new window

hernst42

You can just join them like:

Select * from table1 join table2 on table1.Study=table2.Study and table1.Site=table2.Site

Pharmica

ASKER

Hi,

I tried that and although I can successfully join them, my output is not what I expect as far as the COUNT goes. For example, if I do this (see below) I get some odd results. Maybe I did not explain myself entirely or this just morphed into another question.

select
t2.*,
t1.CRA,
(select t2.count WHERE t2.category = 'unreviewed') AS Unreviewed

from table2 t2
join table1 t1
on t1.Study = t2.Study
and t1.Site = t2.Site

calpurnia

I think it's simpler than that:

SELECT t2.*, t1.CRA
FROM table1 t1 INNER JOIN table2 t2
ON t1.Study = t2.Study
AND t1.Site = t2.Site
WHERE t2.Category="Unreviewed";

Pharmica

ASKER

I kow this question is morphing, sorry about that. I tried the above solution
SELECT t2.*, t1.CRA
FROM table1 t1 INNER JOIN table2 t2
ON t1.Study = t2.Study
AND t1.Site = t2.Site
WHERE t2.Category="Unreviewed";

Technically it does what it should, but I need to also show everything from Table1 with NULLs if there is no match. If I use Left outer join (instead of inner join) I still only get limited results.....ie results restricted by the where clause. How do I show ALL records from Tabl1e, count of reviewed for each record, and show NULL if there is no count. Let me know if this doesnt make sense. Thanks again.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Pharmica

ASKER

Thanks, your solution helped me greatly. I am going to post another question regarding this question, but more focused on summing a field.