Sql inner joins query

Question

I was working on this query and just don't know how to use a inner join on 3 different tables where one table is linked with the other 2 tables only.

Individual queries work fine:

SELECT sl_letter_batch,cctvpcn_run_date,cctvpcn_post_date 
FROM cctvpcn_batches,statutory_letter 
WHERE sl_system_ref = 1095278 and sl_letter_batch = cctvpcn_batch 
ORDER BY cctvpcn_run_date

SELECT sl_letter_batch,nto_run_date,nto_post_date 
FROM nto_batches,statutory_letter 
WHERE sl_system_ref = 1095278 and sl_letter_batch = nto_batch 
ORDER BY nto_run_date

Now if I want to inner join the same tables :

SELECT sl_letter_batch,cctvpcn_run_date,cctvpcn_post_date 
FROM cctvpcn_batches,statutory_letter 
  INNER JOIN nto_batches,statutory_letter and sl_letter_batch = nto_batch
 and sl_letter_batch = cctvpcn_batch  
 WHERE sl_system_ref = 1095278
 ORDER BY nto_run_date

I know this is a syntax error just trying something different. Because sl_letter_batch has different values in two tables. the result I get is null.

sl_letter_batch cctvpcn_run_date    cctvpcn_post_date
21326   2014-10-07 12:45:06.000 2014-10-07 00:00:00.000
21571   2014-11-25 14:13:55.000 2014-11-25 00:00:00.000

sl_letter_batch nto_run_date    nto_post_date
21502   2014-11-13 09:06:24.000 2014-11-13 00:00:00.000
21785   2015-01-05 14:30:42.000 2015-01-05 00:00:00.000

IS there anyway to write this query to get both table results with a join.

possible duplicate of [SQL Inner-join with 3 tables?](http://stackoverflow.com/questions/10195451/sql-inner-join-with-3-tables) — Tab Alleman, Mar 31 '15 at 19:20

Rahul Tripathi · Answer 1 · 2015-03-31T16:37:29.357

0

Try like this:

select sl_letter_batch,cctvpcn_run_date,cctvpcn_post_date 
from cctvpcn_batches inner join statutory_letter on sl_letter_batch = cctvpcn_batch
inner join nto_batches on sl_letter_batch = nto_batch
 where sl_system_ref = 1095278
order by nto_run_date

EDIT:

You need to have a column with which you can join your tables. Something like this:

select sl_letter_batch,cctvpcn_run_date,cctvpcn_post_date 
from cctvpcn_batches c inner join statutory_letters s on c.id= s.id
inner join nto_batches n on s.id= n.id
 where s.sl_system_ref = 1095278
order by n.nto_run_date

edited Mar 31 '15 at 16:37

answered Mar 31 '15 at 16:32

Rahul Tripathi

168,305
31
280
331

Rahul, thats what I mentioned the sl_letter_batch has different values if you see my result it will end up null values as result. – pretyv5 Mar 31 '15 at 16:35
@pretyv5:- Do you have any id column with which you can join the two tables? – Rahul Tripathi Mar 31 '15 at 16:35
statutory_letter table is common for both tables and sl_letter is the id column for both. I don't know whether this is a dumb question or am i thinking out of box... – pretyv5 Mar 31 '15 at 16:40
@pretyv5:- The only thing you have to change in above query is to change the id column with the column name which you will use to join the three tables. – Rahul Tripathi Mar 31 '15 at 16:41

parfilko · Accepted Answer · 2015-03-31T18:00:23.510

here mixed 2 sql styles - old and new. in old - 2 tables joined using ","

.. FROM cctvpcn_batches, statutory_letter WHERE ...

in new - used syntax "JOIN tableName ON id1 = id2 ..." - here only one table per "JOIN" and each "JOIN" word should have matching "ON".

SELECT ...
FROM cctvpcn_batches
INNER JOIN statutory_letter
      ON    sl_letter_batch = cctvpcn_batch
INNER JOIN nto_batches
      ON    sl_letter_batch = nto_batch
WHERE ...

You still can combine styles.

SELECT ...
FROM cctvpcn_batches, statutory_letter
INNER JOIN nto_batches
        ON sl_letter_batch = nto_batch
WHERE sl_letter_batch = cctvpcn_batch
      ...

Pair "JOIN ... ON ..." can olso be used as brackets:

SELECT ...
FROM cctvpcn_batches
INNER JOIN nto_batches
    INNER JOIN statutory_letter
    ON    sl_letter_batch = cctvpcn_batch
ON sl_letter_batch = nto_batch
WHERE ...

here we join "nto_batches" but before doing so join "statutory_letter" to "cctvpcn_batches". That's why there are two sequential "ON" - first is for "statutory_letter", second - for "nto_batches"

if you need OUTER join (including mismatches) then:

SELECT ...
FROM statutory_letter 
LEFT OUTER JOIN cctvpcn_batches
      ON    sl_letter_batch = cctvpcn_batch
LEFT OUTER JOIN nto_batches
      ON    sl_letter_batch = nto_batch
WHERE ...

Sql inner joins query

2 Answers2