Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need help with a view with 6 tables

Need help with a view with 6 tables

Need help with a view with 6 tables

I',m using PSQL 9.5. I have a working view with 6 tables in it; it works fine.  Now the user wants me to add another table to it.  The new table is a system table, SyNote.  My problem is that the Slordnam table does not have notes for every order, so the link to the system table would have to be a left outer join.  Will all of the other tables need to be redefined as left outer joins?  Here is my view:


SELECT "t1" ."CustomerID" ,"t6" ."Name" ,"t1" ."OrderID" ,"t1" ."TypeID" ,

"t5" ."Description" "Type Desc" ,"t1" ."OrderDate" ,"t1" ."BeginDate" ,"t1" ."billaddress1" ,

"t1" ."billaddress2" ,"t1" ."billcity" ,"t1" ."billstate" ,"t1" ."billzip" ,"t1" ."FirstDate" ,

"t1" ."LastDate" ,"t1" ."Description1" ,"t1" ."Description2" ,"t1" ."Description3" ,

"t1" ."PurchaseOrder" ,"t1" ."ReleaseID" ,"t1" ."Contact" ,"t1" ."Feeable" ,"t1" ."FeeCodeID" ,

"t1" ."TaxCodeID" ,"t1" ."Taxable" ,"t1" ."TaxCertNo" ,"t1" ."TaxCertRecvDate" ,

"t1" ."SalespersonID" ,"t1" ."ZoneID" ,"t1" ."OrderStatus" ,"t1" ."InvoiceType" ,

"t1" ."CreditType" ,"t1" ."DiscountLevel" ,"t6" ."unitdiscount" "Discount By Level" ,

"t6" ."County" ,"t2" ."LocationID" ,"t2" ."ProductID" ,"t2" ."UpdatePrice" ,"t2" ."UpdateFreight" ,

"t2" ."UpdateProdDesc" ,"t2" ."Price" ,"t1" ."PriceListID" ,"t2" ."Frt1FreightRateType" ,

"t2" ."Frt1FreightRate" ,"t2" ."Frt1FreightPay" ,"t1" ."FreightFob" ,"t2" ."UnitCost" ,

"t2" ."OrderQty" ,"t2" ."ShipQty" ,"t2" ."MatlUnitID" ,"t3" ."Description" "Prod Desc" ,

"t1" ."RequirePO" ,"t1" ."carrierID"

FROM "slordnam" "t1" ,"slorder" "t2" ,"inproduc" "t3" ,"Sytype" "t5" ,"slcust" "t6"

WHERE "t1" ."orderID" = "t2" ."orderid"  AND "t2" ."productID" = "t3" ."productid"

AND "t1" ."OrderFlag" = 'O' AND ("t1" ."TypeID" = "t5" ."TypeID" AND "t5" ."Category" = '@ ORDER TYPE' )

AND "t1" ."customerID" = "t6" ."CustomerID"

The new table would be "Synote" "t7" and it links to Slordnam.NotesID = Synote.NotesID.  I don't even know if this is possible.

Thanks - Cathy

RE: Need help with a view with 6 tables

Greetings!  I have had almost sixty (60) tables linked as LEFT OUTER JOINS in Pervasive to create Crystal Reports with success.

  You may want to DTS into MS-SQL for improved performance with larger tables.


James Keep, PMP
Crystal Reports(tm) Certified Consultant 8.5 (CRCC)
Authorized Crystal Engineer (ACE)
Crystal Decisions Business Partner
Montreal, Qc, Canada

RE: Need help with a view with 6 tables

thanks for replying so fast.  I thought that that was the way I was going to have to go, but I needed to bounce it off of someone else.  Thansk.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close