outer join problem

outer join problem - hope I can explain it OK
4 tables
listingTypes
listingTypeID int
listings
listingID int, listingTypeID int, listingTitle varchar
listingKeys
keyID int, listingTypeID int, keyName varchar
listingKeyValues
listingID int, keyID int, keyValue varchar
In words:
I have a number of listingTypes defined
In a listingType I have multiple Listings
A listingType defines a set of keys that listings of this type may have e.g.
colour, size, etc
An individual listing may or may not have keyValues set
I'm trying to output the info so that even when keyValues aren't set I get a
row with a null in
e.g.
ListingType, listingTitle, keyName, keyValue
1, test1, colour, red
1, test1, size, big
1, test2, colour, null
1, test2, size, small
1, test3, colour, null
1, test3, size, null
Sounds like this should be easily solved with outer joins but I seem unable
to return rows like above
Any help gratefully receivedSelect ListingTitle, ListingID,
ListingTypeID,
KeyName, KeyID,
IsNull(KeyValue, 'NotSet') Value
From Listings L
Left Join ListingKeys K
On K.ListingTYpeID = L.ListingTypeID
Left Join ListingKeyValues V
On V.KeyID = K.KeyID
And V.ListingID = L.ListingID
"Joe Gass" wrote:

> outer join problem - hope I can explain it OK
> 4 tables
> listingTypes
> listingTypeID int
> listings
> listingID int, listingTypeID int, listingTitle varchar
> listingKeys
> keyID int, listingTypeID int, keyName varchar
> listingKeyValues
> listingID int, keyID int, keyValue varchar
> In words:
> I have a number of listingTypes defined
> In a listingType I have multiple Listings
> A listingType defines a set of keys that listings of this type may have e.
g.
> colour, size, etc
> An individual listing may or may not have keyValues set
> I'm trying to output the info so that even when keyValues aren't set I get
a
> row with a null in
> e.g.
> ListingType, listingTitle, keyName, keyValue
> 1, test1, colour, red
> 1, test1, size, big
> 1, test2, colour, null
> 1, test2, size, small
> 1, test3, colour, null
> 1, test3, size, null
> Sounds like this should be easily solved with outer joins but I seem unabl
e
> to return rows like above
> Any help gratefully received
>
>|||Thanks !!!
Would you beleive I've been trying to do this all day!
Cheers
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:346C2099-8997-4731-B60F-388CE4C9B44F@.microsoft.com...
> Select ListingTitle, ListingID,
> ListingTypeID,
> KeyName, KeyID,
> IsNull(KeyValue, 'NotSet') Value
> From Listings L
> Left Join ListingKeys K
> On K.ListingTYpeID = L.ListingTypeID
> Left Join ListingKeyValues V
> On V.KeyID = K.KeyID
> And V.ListingID = L.ListingID
> "Joe Gass" wrote:
>|||yr very welcome !
"Joe Gass" wrote:

> Thanks !!!
> Would you beleive I've been trying to do this all day!
> Cheers
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:346C2099-8997-4731-B60F-388CE4C9B44F@.microsoft.com...
>
>