CODEWITHSUNDEEP
sqljoin
Doug Fir
Doug Fir

Reputation: 21322

SQL one to many with inner join

Say I have two tables, table 1 and table 2. Table 1 has a one to many relationship with 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 would be returned when selecting any fields from table 2?

E.G. 

Table1
     Account ID |      email      
         1      |   [email protected]
         2      |   [email protected]
         3      |   [email protected]

Table 2
Subscription ID | Account ID  |       Email       |      Created
    100         |     1       |    [email protected]    |    2014-01-01
    102         |     2       |    [email protected]  |    2014-02-02
    103         |     1       |    [email protected]    |    2014-03-03

So if I ran a query to select the created date of an account based on subscriptions 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 onto table 2 on account ID and I select created field, which will be returned? 2014-01-01 or 2014-03-03?

Upvotes: 17

Views: 23762

Answers (1)

HLGEM
HLGEM

Reputation: 96650

You would return both records. When you JOIN tables, you get all the records that meet the criteria of the JOIN and the WHERE clauses and any GROUP BY / HAVING clauses. If you want only one returned, you have to write the code to specify which one you want.

Upvotes: 16

Related Questions