select from two tables where linked column can be null

Question

I have two tables in a database. Table 1 includes an order number which could be NULL. Table two contains all order data (including the order number).

Now I want to select all columns from table 1 and all order data from table two. So if a certain entry in table one doesn't contains this order number, all other columns should be null. But if it does contain an order number I want it to be linked to the second table and have these data selected.

Output should be something like:

column1tab1 column2tab1 order_number product amount
 xx            yy            123      p1      2
 xx            yy            456      p3      4
 xx            yy            NULL    NULL    NULL
 xx            yy            789      p2      1
 etc...

I tried different things, but I only get all the rows with an order number or all with null, but I can't get them both at the same time. Does someone know a solution, so I can do this in one query?

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html — OMG Ponies, Mar 19 '12 at 01:38

score 1 · Accepted Answer · answered Mar 19 '12 at 01:27

1

what did you try? A simple left join would do the trick. Example:

select *
from orders o left join orderdata od on o.orderId=od.orderid

answered Mar 19 '12 at 01:27

Diego

34,802
21
91
134

1

Thanks! I tried a lot of things with the ISNULL statement, but LEFT JOIN did the trick. Now I can also LEFT JOIN again to get the prices of the products (which are again in another table)! – simP Mar 19 '12 at 10:36

score 0 · Answer 2 · answered Mar 19 '12 at 01:39

0

 select t1.*
 from table1 as t1 right join table2 as t2  on t1.order_number=t2.order_number

answered Mar 19 '12 at 01:39

select from two tables where linked column can be null

2 Answers2

Linked