How to Get Referenced Values from Another Table

How to get referenced values from another table?

For Example if you are trying to get A country's Name by using Country_ID in this Project table shown in your question, You could do something like

SELECT C.CountryName_Colummn, C.SomeOtheColumn, ......
FROM Projects P INNER JOIN Countries C
ON P.id_Country = C.id_Country
WHERE SomeColum = 'Your Condition'

1) SELECT Clause you select the columns you need in your result set.

2) FROM you Mention table(s) name(s)

3) ON Clause you define the relationship between the tables Say Projects table had a Column id_Country which refers to id_Country in Countries table defines the relationship between these two tables.

4) After you have Selected the column list, Source of data (tables), Their relationship (On Clause) then you can filter the number of rows returning from you query, like you can something like id_Country

WHERE C.CountryName = 'UK' will return results only from UK.

5) In from Clause the Letters 'C' and 'P' are alias so we dont have to type full table names again n again, makes our code simpler and easier to read and debug.

No matter how many tables you have to join to get the required data as long as you can define the relationship between them table in your query it should work fine. SQL server rarely you would find all the required data in one table normally you would join 2-3 tables or more tables. For example you want some data that is present in 3 different tables you would join them all three in one query something like this ...

SELECT C.CountryName_Colummn, C.SomeOtheColumn, ......
FROM Projects P INNER JOIN Countries C
ON P.id_Country = C.id_Country
INNER JOIN Table_3_Name T3
ON T3.CommonColumn = P.SomeCommonColumn (it will be a common column between table 3 and Projects OR Countries)
WHERE SomeColum = 'Your Condition'

But you really need to look in joins as you can do different types of joins between tables, Hers in this example I used INNER JOIN which returns only the matching rows between all these tables, you could do LEFT JOIN or RIGHT JOIN.

LEFT JOIN returns all the rows from the table on LEFT side of the JOIN key word and only matching rows from other tables.

RIGHT JOIN returns all the rows from the on the right side of the JOIN key word and only mantching rows from other tables.

Queries with only desired Columns

Select customers.customer_name, Products.Product_type, Manufacturers.Manuf_name 
from Projects inner join customers
on     customers.cust_id= Projects.proj_cust_id
inner join Products
on     Products.prod_id= Projects.proj_prod_id
inner join Manufacturers
on     Manufacturers.man_id= Projects.proj_man_id

Making use of Alias will give you exactly the same reuslt but easy to read code also try this ....

Select C.customer_name, Prod.Product_type, M.Manuf_name 
from Projects Proj inner join customers C
on     C.cust_id= Proj.proj_cust_id
inner join Products Prod
on     Prod.prod_id= Proj.proj_prod_id
inner join Manufacturers M
on     M.man_id= Proj.proj_man_id

Select values from one table depending on referenced value in another table

The = operator compares a single value against another, so it is assumed that the subquery returns only a single row.

To check whether a (column) value is in a set of values, use IN:

SELECT *
FROM Table2
WHERE fileid IN (SELECT FileID
                 FROM Table1
                 WHERE F_Property1 > 1)

Finding SQL records referenced by another table

You need to first join the two tables. This will return a new table with the all the stock records and their product details on the condition that the quantity is grater than zero.

Then because a product can be referenced by multiple stock records, we perform the grouping.

see inner join and group by

try this:

SELECT product.* FROM product INNER JOIN stock ON product.id = stock.product_id AND stock.quantity > 0 GROUP BY product_id

SQL Reference values in another table without a join

You certainly can use a JOIN. You can use BETWEEN in the ON condition.

SELECT t1.name, t1.number, t2.category
ON Table1 AS t1
JOIN Table2 AS t2 ON t1.number BETWEEN t2.min AND t2.max

If you thought that joins could only use foreign key equality, you're mistaken.

SELECT all values from one table with reference to another table but the value there is missing

You are using implicit inner join syntax, when you need a left join:

SELECT A.value1, A.value2, A.value3
FROM TableA A
LEFT JOIN TableB B
   ON A.PrimaryKey=B.PrimaryKey
WHERE B.value1 IS NULL;

SAP HANA get referenced values from another table

You have a problem with your data model, if you have columns such as age and name repeating. You can do what you want using:

select distinct a.*, b.age, b.name
from a join
     b
     on a.id = b.id;

Alternatively, you could phrase this as:

select a.*, b.age, b.name
from a join
     (select distinct id, name, age
      from b
     ) b
     on a.id = b.id;

You would have to test which is faster on your data. Performance depends on the size of the tables, the number of matches, and available indexes.

Get comma-separated set of values from table where another reference value on another table appears twice (or more)

Nice starting fiddle, thanks! If we just take what you already have and put it in a CTE, we can write a standard string aggregation around it:

;WITH subs AS 
(
  SELECT prod.CODE, sub.SUBSTITUTECODE
  FROM @SUBSTITUTE AS sub
  INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
  WHERE sub.SUBSTITUTECODE IN (SELECT sub.SUBSTITUTECODE
    FROM @SUBSTITUTE AS sub
    INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
    GROUP BY sub.SUBSTITUTECODE
    HAVING COUNT(sub.SUBSTITUTECODE) > 1)
)
SELECT CODES = STUFF((SELECT ',' + CODE 
  FROM subs AS s2 WHERE s2.SUBSTITUTECODE = subs.SUBSTITUTECODE
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,''),
    SUBSTITUTECODE FROM subs
  GROUP BY SUBSTITUTECODE;

• Example db<>fiddle

But we can simplify this code slightly, most importantly to avoid referencing both tables twice, like this:

;WITH subs AS
(
  SELECT s.ITEID, s.SUBSTITUTECODE, m.CODE, 
    c = COUNT(*) OVER (PARTITION BY s.SUBSTITUTECODE)
  FROM @SUBSTITUTE AS s
  INNER JOIN @MATERIAL AS m
  ON m.ID = s.ITEID
)
SELECT CODES = STUFF((SELECT ',' + CODE
  FROM subs AS s2 WHERE s2.SUBSTITUTECODE = subs.SUBSTITUTECODE
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,''),
    SUBSTITUTECODE
  FROM subs 
  WHERE c > 1 
  GROUP BY SUBSTITUTECODE;

• Example db<>fiddle

Note that on more modern versions of SQL Server (2017+), STRING_AGG() makes this much easier:

SELECT CODES = STRING_AGG(m.CODE, ','), s.SUBSTITUTECODE
  FROM @SUBSTITUTE AS s
  INNER JOIN @MATERIAL AS m
  ON m.ID = s.ITEID
  GROUP BY s.SUBSTITUTECODE
  HAVING COUNT(*) > 1;

• Example db<>fiddle

---

Related Topics