Clever Geek Handbook

SQL SELECT from multiple tables or JOIN

I have two tables from which I need to get data in the same SELECT

output. The point is, I need to limit the number of results.

Let's say I have a column ID

that is unique in table1

, but in table2

it has many rows with ID

.

Now I just want to indicate how many different ones ID

I have in table1

and some other information stored in table2

.

How can I get the desired result, which I show at the end?

To make it clear that I have used the messenger database for the example.

Table

T1


Id_thread            Date
1                    13Dic
2                    12Dic

T2


Id_thread            Message        Name
1                    Hi             Someone
1                    Hi to you      Someone
2                    Help me?       Someother
2                    Yes!           Someother

Desired output


T1.Id_thread         T2.Name     T1.Date
1                    Someone     13Dic
2                    Someother   12Dic
+3


source to share


5 answers


Use JOIN

and GROUP BY

:

SELECT t1.Id_thread, t2.Name, t1.Date
FROM t1
JOIN t2 ON t1.Id_thread = t2.Id_thread
GROUP BY t1.Id_thread


Note that if it is the Name

same for all rows in t2

that have the same Id_thread

, that column should probably be in t1

. If you fix this, you won't need it JOIN

.

+2


source


I would join and use distinct

:



SELECT DISTINCT t1.id_thread, t2.name, t1.date
FROM   t1
JOIN   t2 ON t1.id_thred = t2.id_thread
+3


source


Try the following:

SELECT DISTINCT T1.Id_thread, T2.Name, T1.Date 
FROM T1 
LEFT OUTER JOIN T2 ON T1.Id_thread = T2.Id_thread
+1


source 


select T1.Id_thread,T2.Name,T1.Date from T1 
inner join T2 on T1.Id_thread = T2.Id_thread
group by T1.Id_thread
order by T1.Id_thread
0


source


You have not specified how you want to constrain the results from Table 2. Given that you just want one row, you can use CROSS APPLY:

Select T1.Id_thread,T2Table.Name,T1.Date From T1 
Cross Apply (Select Top 1 T2.Name From T2 Where T2.Id_thread=T1.Id_thread) T2Table

You can specify other conditions in the inner Select statement if you like.

0


source






More articles:


All Articles