CODEWITHSUNDEEP
sql
user1330649
user1330649

Reputation: 79

Referencing between two tables SQL?

I have two tables and I am trying to count the number of times a particular 'ID' occurs and then output a name that corresponds with that 'id'. For example, certain people within the list have multiple occurring IDs, and I want to output the people whose IDs occur more than 4 times.

Thanks to bvr, I have one part of it, I now know how to display the Values that occur more than 4 times, by using the following statement:

SELECT ID , COUNT(OrderID) FROM Table1
GROUP BY ID
HAVING COUNT(OrderID) > 4

Just need the second part.

Here is an example of the two tables.

Table 1

ID | OrderID
--------------
191 | 1020
150 | 1090
191 | 1023
140 | 1033
191 | 2132
191 | 1233
191 | 1321

Table 2

ID | FirstName
--------------
191 | Mat
150 | Stewart
191 | Mat
140 | John
191 | Mat
191 | Mat
191 | Mat

I want to output something like this

Occurrences  | FirstName 
-------------------------
         5   |  Mat

Upvotes: 2

Views: 100

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You want to join to the second table to get the name:

SELECT t1.ID , COUNT(t1.OrderID), min(t2.name) as name
FROM Table1 t1 join
     (select id, min(name) as name
      from Table2 t2
      group by id
     ) t2
     on t1.id = t2.id
GROUP BY t1.ID
HAVING COUNT(t1.OrderID) > 4;

Before doing the join, though, you need to aggregate by id to ensure that there are no duplicates. The subquery gets the minimum name on an id. If all names are the same, this will be the name. Otherwise, it is an arbitrary name assigned to the id.

EDIT:

You can do the query without subqueries, but I'm not sure that will be more efficient:

SELECT t1.ID , COUNT(distinct t1.OrderID), min(t2.name) as name
FROM Table1 t1 join
     Table2 t2
     on t1.id = t2.id
GROUP BY t1.ID
HAVING COUNT(distinct t1.OrderID) > 4;

This may look simpler, but it can be much worse in terms of performance. For instance, if one id had 1000 matches in both table 1 and table 2, then that id would end up with 1,000,000 rows to process after the join.

Upvotes: 1

peterm
peterm

Reputation: 92845

A version with DISTINCT

SELECT t2.FirstName, COUNT(*) Occurrences 
  FROM Table1 t1 JOIN 
(
  SELECT DISTINCT id, firstname
    FROM Table2
) t2
    ON t1.ID = t2.ID
 GROUP BY t2.ID, t2.FirstName
 HAVING COUNT(*) > 4

Output:

| FIRSTNAME | OCCURRENCES |
---------------------------
|       Mat |           5 |

Here is SQLFiddle demo

Upvotes: 0

Related Questions