Clever Geek Handbook

SQL one to many with inner join

Let's say I have two tables, table 1 and table 2. Table 1 has a one-to-many relationship to table 2.

If table 1 is my base table ( SELECT something FROM table1

) with an inner join to table 2, then what values ​​from table two will be returned when selecting any fields from table 2?

  • First return value? How with excel vlookup function?
  • The last value in the connection?

eg.

Table1
     Account ID |      email      
         1      |   abc@def.com
         2      |   cats@dogs.com
         3      |   ex@example.com

Table 2
Subscription ID | Account ID  |       Email       |      Created
    100         |     1       |    abc@def.com    |    2014-01-01
    102         |     2       |    cats@dogs.com  |    2014-02-02
    103         |     1       |    abc@def.com    |    2014-03-03

So, if I ran a query to select the subscription-based account created date, I could just run SELECT min(Created)

to get the first one.

But what's the default behavior? If my left table is table 1 joined to account id table 2 and I select the generated field which will be returned? 2014-01-01 or 2014-03-03?

+3


source to share


1 answer


You will get both records back. When you have JOIN

a table, you get all the records that meet the criteria for proposals JOIN

and WHERE

and any suggestions GROUP BY

/ HAVING

. If you only want one to return, you need to write code to indicate which one you want.



+6


source






More articles: