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 |
|
bballer217
Starting Member
2 Posts |
 Posted - 2011-11-17 : 16:11:43
|
| I am trying to create a VIEW that produces a report for cars that are for sale at a car dealership. When I have one row in the salesInvoice table, it works correctly, but when I try to add a second salesInvoice, the cars that were not purchased from the first sales invoice AND all cars not purchased in the second sales invoice show up.salesInvoice table contains records of cars that have been sold. vehicle table contains a record of all cars that came through the dealership(in stock and sold)Code:CREATE OR REPLACE VIEW vehiclesForSale_view ASSELECT v.vehicleSerialNo, v.make, v.model, v.color, v.yearFROM vehicle v JOIN salesInvoice siON (si.vehicleSerialNo <> v.vehicleSerialNo)Example:Vehicles table includes: porsche, ford, bmwWith first sales invoice of the BMW, the VIEW says "porsche, ford"When you add a second sales invoice of the Ford, the VIEW says "porsche, ford, porsche, bmw" |
|
|
pduffin
Yak Posting Veteran
68 Posts |
Posted - 2011-11-17 : 19:01:46
|
| try this:CREATE OR REPLACE VIEW vehiclesForSale_view ASSELECT v.vehicleSerialNo, v.make, v.model, v.color, v.yearFROM vehicle v where v.vehicleSerialNo not in (select si.vehicleSerialNo from salesInvoice si)Likes to run, hates the runs! |
 |
|
|
bballer217
Starting Member
2 Posts |
Posted - 2011-11-17 : 21:24:31
|
| yes! that worked! thanks so much!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-18 : 01:38:47
|
| looks like Oracle------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Help with VIEWs