Join that displays null values 1

[marabou]

I have three tables, Customers, Licences and Notes. Customers have many licences and many or none Notes. There is a Note Type which is a licence.

I need to display the Licences, the Customer Name and the Notes if the Licence has a note type 'licence' attached to it.
So far, I can only display the licences for the customers who have notes.
I know of using the descriptor indicator to insert nulls, but you can only use this in dynamic SQL and I need to turn this query into a view for use within Crystal Reports.

Please help, I am really stuck.

Thanks in advance

 

[grega]

Sounds like you need to do an "outer join". For example, if I am joining table A to table B, but for each record in A there may not be a corresponding record in B, and I still want to see all the rows in A, then that's an outer join.

Without knowing your database setup it's difficult to advise exactly. Post a little more info on your setup, or have a look in your manual for differnent join types. Outer joins are a standard feature across all platforms.

Greg.

 

[marabou]

Thanks Greg

I have made a little progress with it but now the query seems to be in a loop and is returning way more records than it should do. Any ideas??

[Select Customers.customerID, LicenceID,
(Select Notes.NoteText
FROM Notes
Where Notes.TypeID = 3
AND Notes.customerID = Customers.customerID)
FROM Customers, Licences, Notes
Where Customers.customerID = Licences.customerId]

I realise I should probably include some more info in the select statement but not sure what.

Ella

 

[grega]

I think this is the sort of query you want. It will return all Customers who have licences (1 for each licence) and if there is a note, it will be displayed. If there is no note, you should still get the customer and licence info.

SELECT Customers.CustomerID, LicenceID, NoteText
FROM Customers, Licence, Notes
WHERE Customers.CustomerID = Licences.CustomerID
AND Licences.CustomerID *= Notes.CustomerID
AND Notes.TypeID = 3

Please note the *= above in the join between Licences and Notes. This is an Outer Join using the Sybase database. It will probably be different on your database.

Hope this helps.

Greg.

 

[marabou]

Thanks, that was v. helpful.

It didn't work the way you suggested but

[Select Customers.customerID, LicenceID,
(Select Notes.NoteText
FROM Notes
Where Notes.TypeID = 3
AND Notes.customerID (+)= Customers.customerID)
FROM Customers, Licences
Where Customers.customerID = Licences.customerID,]

worked perfectly!

N.B. (+) is the outer join.