Picking info using junction table(SQL SERVER 2005) [ SET BASED]

Question

I have 3 tables

1) tblPurchaser having 2 columns:

PurchaserId   PurchaserName
1             A1
2             A2
3             A3

2) tblCar having 2 columns:

CarId   Carname
11      C1
12      C2
13      C3
14      C4

And the last is a junction table tblInformation where the information about those persons are given who has purchased cars.

PurchaserId  CarId
1            11
1            12
2            11
2            13

Now I need to write a set based query where I can be able to obtain the information of those cars which has not been purchased by the persons

Desired Output

PurchaserId  CarId
1            13
1            14
2            12
2            14      
3            11
3            12
3            13
3            14

Note: This is a real time problem which I am implementing in my project. Because of privacy of company, I have changed the tables and information. But my situation is something similar Please help me

Edited

So far I have written this query:

SELECT 1 as purchaserid,carid from tblcar
where carid not in (select carid from tblinformation where purchaserid = 1)
union all
SELECT 2 as purchaserid,carid from tblcar 
where carid not in (select carid from tblinformation where purchaserid = 2)
union all
SELECT 3 as purchaserid,carid from tblcar 
where carid not in (select carid from tblinformation where purchaserid = 3)

But as you can make out that i am hardcoding the purchaserid's. And also in real time I will not know how many id's will be there. So everything has to be done at runtime.

Please helpenter code here

Answer 1

Clue: NOT EXISTS

You should really try to do some homework yourself... 3rd question today...

Answer 2

Try this:

SELECT PurchaserID, CarID
FROM Purchasers
CROSS JOIN Cars
    EXCEPT
SELECT *
FROM tblInformation

---

Here is a SQL script that demonstrates that this technique works correctly:

declare @soPurchaser table(PurchaserId int,  PurchaserName varchar(4));
insert @soPurchaser select 1,'A1'
insert @soPurchaser select 2,'A2'
insert @soPurchaser select 3,'A3'

Declare @SOtblCar table(CarId int, Carname varchar(4))
insert @SOtblCar select 11,'C1'
insert @SOtblCar select 12,'C2'
insert @SOtblCar select 13,'C3'
insert @SOtblCar select 14,'C4'

Declare @SOtblInfo table(PurchaserId int, CarId int)
insert @SOtblInfo select 1,11
insert @SOtblInfo select 1,12
insert @SOtblInfo select 2,11
insert @SOtblInfo select 2,13

SELECT PurchaserID, CarID
FROM @soPurchaser
CROSS JOIN @SOtblCar
    EXCEPT
SELECT *
FROM @SOtblInfo

---

The SQL Set operators (UNION, INTERSECT, and EXCEPT) all operate on two table-sets. You will note that they have no way to map the columns from one set to the other. In all cases in SQL when column must be mapped to each other, but there is no syntax to do it explicitly, then they are always mapped based on column order.

So in this one case, if you have one of the table's column order wrong, then it will not work correctly.

Answer 3

LEFT JOIN ... WHERE ... IS NULL to the rescue:

SELECT tblPurchaser.PurchaserId, tblCar.CarId 
  FROM tblPurchaser JOIN tblCar 
  LEFT JOIN tblInformation ON(
    tblPurchaser.PurchaserId = tblInformation.PurchaserId 
    AND tblCar.CarId = tblInformation.CarId) 
  WHERE tblInformation.CarId IS NULL

Answer 4

Try this

 select pur.PurchaserId, car.CarId
   from tblPurchaser pur, tblCar car
  where not exists (select 1 from tblInformation  where PurchaserId = pur. PurchaserId and CarId = car. CarId) 
 order by pur.PurchaserId;