Join a table where a column has a particular value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join a table where a column has a particular value

Author

Topic

pcdj
Starting Member

5 Posts

Posted - 2009-02-25 : 03:04:27

I have the roughly the following structure in my database:

table a
id int
summary varchar
owner varchar
time datetime
c_id int
e_id int
f_id int

table b
id int
description varchar
notes varchar
due datetime
a_id int
d_id int

table c
id int
impact int

table d
id int
team varchar

table e
id int
reference varchar

table f
id int
type int
description varchar

I currently have the following SQL query to run a report for me:

SELECT
a.id,
b.id,
c.id,
b.description,
convert(varchar(20), b.due, 106) AS due_date,
convert(varchar(20), b.due, 112) AS due_date_sorted,
convert(varchar(20), a.time, 112) AS sort_date,
d.team,
b.notes,
a.summary,
a.owner,
e.reference,
convert(varchar(20), a.time, 106) AS outage_date,
c.impact
FROM
table_a AS a
JOIN table_b AS b
ON a.id = b.a_id
JOIN table_c AS c
ON a.c_id = c.id
JOIN table_d AS d
ON b.d_id = d.id
LEFT JOIN table_e AS e
ON a.e_id = e.id
WHERE
a.time >= CAST('$opt_S' AS DATE)
AND a.time <= CAST('$opt_E' AS DATE)
ORDER BY sort_date DESC, e.reference DESC, due_date_sorted DESC

I would now like to link to table f to return values in the f.description column, where 'f.type = 6' (a.f_id = f.id).

I have placed a 'JOIN table_f AS f ON a.f_id = f.id' join in, and included the appropriate where statement 'AND f.type=6', as well as selecting an additional 'f.description' column, but this only returns rows where 'f.type=6'. Which I guess it to be expected.

The problem I have however, is that there may not be an entry in table f with 'f.type=6' for any given record in table a. I would still like records matching the rest of my 'where' clauses to be returned from table a even if no rows in table f have f.type=6.

P.S. $opt_S and $opt_E are variables being input by perl that correspond to a start date and end date respectively.

Many thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 03:32:35

LEFT JOIN table_f AS f ON a.f_id = f.id AND f.type=6

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources