4
votes

Sélection de colonnes dans différentes tables en utilisant où dans

J'essaie de récupérer des données à partir de colonnes de différentes tables en utilisant la clause Where-In mais je ne parviens pas à le faire. La tâche requise est: "Lister le nom, le poids de l'emballage et les unités produites et le nombre de lots de tous les produits expirés"

La base de données ressemble à ceci

select ProductName, PackageWeight from Products where ProductId in(
select ProductId from Products
Intersect
select ProductId from Production where ExpiryDate < GETDATE())

Jusqu'à présent J'ai ceci:

create table Products(
    ProductId int,
    ProductName varchar(20) not null,
    PackageWeight float not null,
    PricePerUnit int not null,
    AmountInStore int,
    Primary Key(ProductId)
)

insert into Products values(1, 'Chicken Cutlet', 0.5, 500, 600)
insert into Products values(2, 'Chicken Cutlet', 0.25, 250, 0)
insert into Products values(3, 'Chicken Cutlet', 1, 1000, 500)
insert into Products values(4, 'Chicken Cutlet', 5, 4500, 600)
insert into Products values(5, 'Chicken Tenders', 300, 500, 500)
insert into Products values(6, 'Chicken Tenders', 600, 500, 100)
insert into Products values(7, 'Potato Fries', 1, 150, 0)
insert into Products values(8, 'Potato Fries', 5, 750, 800)

create table Production(
    ProductId int,
    BatchNo char(6) ,
    UnitsProduced int not null,
    DateOfProduction date not null,
    ExpiryDate date not null,
    Foreign Key(ProductId) references Products(ProductId),
    Primary Key(ProductId, BatchNo)
)

insert into Production values(1, '1-1001', 1000, '2014-01-01', '2014-01-10')
insert into Production values(2, '1-1001', 1000, '2014-01-01', '2014-01-10')
insert into Production values(3, '1-1001', 1000, '2014-02-01', '2014-02-10')
insert into Production values(4, '1-2001', 500, '2014-05-01', '2014-10-30')
insert into Production values(5, '1-2001', 500, '2014-05-10', '2030-11-10')

J'obtiens le nom du produit et le poids du colis corrects car ils proviennent tous les deux du même tableau, c'est-à-dire Produits, mais comment puis-je également récupérer LotNo , UnitsProduced à partir d'une table différente ie Production dans la même requête?

sql-server

1 commentaires

Production attendue?

3 Réponses :

0
votes

Vous devez joindre les tables basées sur ProductId , essayez comme la requête suivante.

Si vous souhaitez modifier votre requête existante, cela devrait ressembler à.

SELECT productname, 
       packageweight, 
       prdn.batchno, 
       prdn.unitsproduced 
FROM   products prd 
       INNER JOIN production prdn 
               ON prd.productid = prdn.productid 
WHERE  expirydate < Getdate()

Mais si vous le souhaitez, vous pouvez re-factoriser votre requête comme suit.

SELECT productname, 
       packageweight, 
       prdn.batchno, 
       prdn.unitsproduced 
FROM   products prd 
       INNER JOIN production prdn 
               ON prd.productid = prdn.productid 
WHERE  prd.productid IN(SELECT productid 
                        FROM   products 
                        INTERSECT 
                        SELECT productid 
                        FROM   production 
                        WHERE  expirydate < Getdate())

0 commentaires

0
votes

J'utiliserais simplement JOIN:

SELECT P.ProductName, P.PackageWeight, PD.BatchNo, PD.UnitsProduced
FROM Products P
JOIN Production PD ON P.ProductId = PD.ProductId
WHERE ExpiryDate < CONVERT(DATE,GETDATE())

0 commentaires

0
votes

Vous pouvez facilement y parvenir en utilisant les vues de serveur SQL . Voici comment procéder:

Créer une vue

SELECT Name,
   PackageWeight,
   BatchNo,
   UnitsProduced
FROM viewName
WHERE ExpiryDate < GETDATE();

Obtenir les résultats comme suit strong >

-- Note that Table Alias is Used For Query Clarity
-- 'P' => For Product Table
-- 'Pr' => For Production Table
CREATE VIEW viewName
AS
SELECT P.ProductId AS ID,
       P.ProductName AS Name,
       P.PackageWeight,
       P.PricePerUnit,
       P.AmountInStore,
       Pr.BatchNo,
       Pr.ExpiryDate,
       Pr.UnitsProduced,
       Pr.DateOfProduction
FROM Production AS Pr
INNER JOIN
Products AS P
ON Pr.ProductId = P.ProductId;

0 commentaires