Home > Archive > SQL Server Programming > February 2005 > Help with Outer join
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Help with Outer join
|
|
| hals_left 2005-02-28, 8:59 am |
| How can I join 3 tables that represent many-many realtions, in a way
that returns all the values of one table and an extra column of
true/false or null or some way to to show that the record exists or
doesnt exist in the link table.
This is so that when I pass a paramter of an author ID I can create an
array of checkboxes form the record set that are either ticked or not
ticked.
E.g for Pubs, output something like this:
author exists title
1 False 1
1 True 2
Thanks
hals_left
| |
| David Portas 2005-02-28, 8:59 am |
| USE Pubs
SELECT A.au_id, T.title,
CASE WHEN U.title_id IS NOT NULL
THEN 'Y' ELSE 'N' END AS exist
FROM Authors AS A
JOIN Titles AS T
ON A.au_id = '267-41-2394'
LEFT JOIN TitleAuthor AS U
ON U.au_id = A.au_id
AND U.title_id = T.title_id
--
David Portas
SQL Server MVP
--
| |
| Roji. P. Thomas 2005-02-28, 8:59 am |
| use pubs
Go
SELECT A.au_id, CASE WHEN EXISTS(SELECT 1 FROM TitleAuthor T
WHERE T.Au_id= A.Au_id) Then 1 Else 0 End as Exist
FROM Authors A
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hals_left" <cc900630@ntu.ac.uk> wrote in message
news:c04136bd.0502280242.5c8a6a27@posting.google.com...
> How can I join 3 tables that represent many-many realtions, in a way
> that returns all the values of one table and an extra column of
> true/false or null or some way to to show that the record exists or
> doesnt exist in the link table.
>
> This is so that when I pass a paramter of an author ID I can create an
> array of checkboxes form the record set that are either ticked or not
> ticked.
>
> E.g for Pubs, output something like this:
>
> author exists title
> 1 False 1
> 1 True 2
>
> Thanks
> hals_left
|
|
|
|
|