How to Join Multiple Joins yet return Distinct Values

Question

Hi,

I am trying to Join Multiple Tables. Table 1 has distinct ID's Table 2 has multiple names for each ID SAY 3 NAMES Table 3 has 2 dates for each ID

when i join the Three tables, I get 6 rows of data for each ID with each of the Names appearing Twice and each of the dates appearing thrice.

I want only 3 rows to be written returning distinct 3 names and Distinct 2 dates for each ID. Is there any way to do this while using Joins in SQL?

Answer 1

If you preface your query with SELECT DISTINCT, you should return only the distinct rows.

Answer 2

If I'm understanding the question correctly, you'll want to use the GROUP BY clause

http://www.w3schools.com/sql/sql_groupby.asp

Answer 3

You really need to understand what it is you are trying to return and why.

Please post your example source rows and the results you want to achieve (and why).

The reason you get 6 rows is because there are 6 possibilities which match your model as you have stated it.

If this is "too many", you need to add an additional criteria, as yet unstated, that determines which date is the one you wish to display with each name.

Answer 4

To get one result row per row in table 1, you have to use a grouping function on the other tables. For example, if you want the last (in ascending sort order) name and date, you'd use

select t1.id,
       max(t2.name),
       max(t3.datefield)
  from table1 t1
  join table2 t2 on t2.id = t1.id
  join table3 t3 on t3.id = t1.id
 group by t1.id
;

Answer 5

I think you would need to do two queries.

SELECT id, name FROM names WHERE id = 1
SELECT id, date FROM dates WHERE id = 1

The type of operation that it sounds like you are trying to do isn't really valid since the results wouldn't really be a relation. (you'd have one column with 3 rows and one column with 2 rows)

Answer 6

Here is what I am trying to do

Table1 ID 401

Table 2 ID NameType Name 401 Primary Anu1 401 AKA Anu2 401 Maiden Anu3

Table 3 ID DateType Date 401 DOB 1983-09-25 401 DOD 2008-05-07

I write my join like Select a.ID,b.NameType,b.Name,c.datetype,c.date FROM Table1 a LEFT JOIN Table2 b ON a.ID=b.ID LEFT JOIN Table3 c ON a.ID=c.ID

This returns me ID NameType Name Datetype Date 401 Primary Anu1 DOB 1983-09-25 401 Primary Anu1 DOD 2008-05-07 401 AKA Anu2 DOB 1983-09-25 401 AKA Anu2 DOD 2008-05-07 401 Maiden Anu3 DOB 1983-09-25 401 Maiden Anu3 DOD 2008-05-07

Is it possible to get the results like this:

ID NameType Name Datetype Date 401 Primary Anu1 DOB 1983-09-25 401 AKA Anu2 DOD 2008-05-07 401 Maiden Anu3

Or else in any other way so that the same Names or Dates are not repeated multiple times.

Answer 7

You have to decide what one record you want from each table and join so that only that record joins to it. The way you are displaying data you cannot do all on one line because the information is differnt in the joining tables, so you must determine what the business rule is for choosing which of the multiple records you want.

NOw if you want the primary name and both the DOb and the DOD, this is what I would do: Select a.ID,b.NameType,b.Name,c.date as DOB, d.date as DOD FROM Table1 a LEFT JOIN Table2 b ON a.ID=b.ID and b.name_type = 'Primary' LEFT JOIN Table3 c ON a.ID=c.ID and c.datetype= 'DOB' LEFT JOIN Table3 d ON a.ID=d.ID and d.datetype= 'DOD'

Now you could do the same with multiple joins for the other names, but you probably don't know how many joins you would want in advance. Theres a solution for that too but it is more complicated and I don't have time to get it for you, but basically you have to create a process to concatenate all the namess together in a temp table of some sort (which also has the id) and then join to that temp table.

Answer 8

What about something like this which doesn't use explicit JOINs:

SELECT T1.ID
  FROM Table1 AS T1
 WHERE EXISTS (
               SELECT * 
                 FROM Table2 AS T2
                WHERE T2.ID = T1.ID
              )
       AND EXISTS (
                   SELECT * 
                     FROM Table3 AS T3
                    WHERE T3.ID = T1.ID
                  );