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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-30 : 23:54:19
|
yves writes "I have 6 tables in my database which are related as follows:
CustomerInfo(CUSTOMERID, Name, Add)
CallInfo(CALLNO, Calltime, Message, CUSTOMERID)
ServiceProfile(SERVICENO, Advertisement, Develop, Others, CALLNO)
Advertisement(ADNO, AdType, Caption, Cost, SERVICENO)
Develop(DEVNO, DevType, Cost, SERVICENO)
Others(PROJID, Description, Cost, SERVICENO)
What I need to do is retrieve a customer record (including all his transactions). I know that can be easily done by a bunch of inner joins IF all the tables were filled out. But what makes this tricky is that a customer, most of the time, would only have something advertised (or developed or other service) which would result in the two other tables being empty. For example, if a customer called to have something advertised, only the Advertisement table would be filled and the Develop and Others tables would be left empty. (Of course its also possible that a customer wants to Advertise AND have something Developed, therefore leaving only the Others table blank).
I know that the records can be retrieved using "brute force" method by doing a lot of if-then conditions and executing SQL statements accordingly (but what if there were a dozen tables like that? I wouldn't wanna "brute force" that!) So, I'm wondering if there's a way to retrieve a record from this database by using just a single SQL statement...
Thanks and more power!
Yves" |
|
|
|
|
|
|
|
Retrieving records when some tables MAYBE blank...