SELECT UNION and JOIN from two tables with column varchar instead int-CodePudding

In database "prodbase" i have three tables:

Table Products

|id  | productname |   productprice  | productsupplierID (int11)  |
 ---- ------------- ----------------- ---------------------------- 
|  1 |   Shirt     |     20          |         3                  |
|  2 |   Tshirt    |     25          |         5                  |

Table Supplier

|supplierid (int11)  | suppliername (varchar) |
 -------------------- ------------------------ 
|  3                 |    CompanyA            |
|  5                 |    CompanyB            |

Table Staff

|id  |  staffname  |   staffposition   | 
 ---- ------------- ------------------- 
|  1 |    John     |   sales           |
|  2 |    Megan    |   accounting      |

Table Products uses a look-up field from table Supplier: supplierID.

I have created a database View to union table Products and Staff. with following query.

SELECT 'prodbase'.'Products'.'productname' AS 'Product', 'prodbase'.'Products'.'productsupplierID' AS 'Supplier' FROM 'prodbase'.'Products'
UNION
SELECT 'prodbase'.'Staff'.'staffname' AS 'Staff', 'prodbase'.'Staff'.'staffposition' AS 'Position' FROM 'prodbase'.'Staff'
ORDER BY 'Supplier'
DESC LIMIT 5;

Query is working, but it displays number (ID) in Supplier column.

Product: Shirt  
Staff: John  
Position: sales    
Supplier: 3

How to create this UNION query with JOIN, so it displays Suppliername insted of supplierID? example of desired output:

Product: Shirt  
Staff: John  
Position: sales  
Supplier: CompanyA

CodePudding user response：

There are a few problems you need to sort out to have the data returned the way you want. First, I don't think you need a UNION here. A UNION is when you want to combine multiple queries into one. I think you just simply need to use an INNER JOIN on your data to get the desired output. Speaking of relationships, you are missing an important one. There is no relationship between staff, product and supplier, thusly there is no way to provide the output you desire unless you incorporate a relationship table. I created an example of a conceivable relationship table called Sale which links a staff to a product sold. This is not a perfect example, but it will allow you to actually work with the data. With the relationship table, you can INNER JOIN to get all products sold by staff members, in this simple example.

CREATE TABLE Products(id INT, productname VARCHAR(50), productprice int, productsupplierID int);
CREATE TABLE Supplier(supplierid INT, suppliername VARCHAR(50));
CREATE TABLE Staff(id INT, staffname VARCHAR(50),staffposition VARCHAR(50));

CREATE TABLE Sale(staffid INT, productId INT);

INSERT INTO Products VALUES(1,'Shirt',20,3),(2,'Tshirt',25,5);
INSERT INTO Supplier VALUES(3,'CompanyA'),(5,'CompanyB');
INSERT INTO Staff VALUES(1,'John','Sales'),(2,'Megan','Accounting');

INSERT INTO Sale VALUES(1,1);

SELECT
    Products.productname AS Product,
    Staff.staffname AS Staff,
    Staff.staffposition AS Position,    
    Products.productprice AS Price, 
    Supplier.suppliername AS Supplier 
FROM 
  Sale 
    INNER JOIN Products ON Products.id=Sale.productId         
    INNER JOIN Supplier ON Supplier.supplierid=Products.productsupplierID
    INNER JOIN Staff ON Staff.id = Sale.staffid

Product | Staff | Position | Price | Supplier
:------ | :---- | :------- | ----: | :-------
Shirt   | John  | Sales    |    20 | CompanyA

db<>fiddle here

Edited - I removed Sale based on comment. I guess you could cross join on staff, however, without a relationship between a product/supplier and staff there is no logical way to lay that data over it.

SELECT
    Products.productname AS Product,
    Products.productprice AS Price, 
    Supplier.suppliername AS Supplier 
FROM  
    Products 
    INNER JOIN Supplier ON Supplier.supplierid=Products.productsupplierID