Topic: An SQL Expression to pick up several names in a specific table?
Hi Guys, i have a table inside a table, i want to use the ID from the first table to retrieve the names from the second table, is that possible?
My Visual Database → SQL queries → An SQL Expression to pick up several names in a specific table?
Hi Guys, i have a table inside a table, i want to use the ID from the first table to retrieve the names from the second table, is that possible?
Hello Vasco
Yes this is possible.
I will take an example that I know well because it's part of one of my projects.
Let's say you have 3 tables :
- table 1 contains a list of products
- table 2 contains a list of sellers
With such an architecture, I can link a product with one and only one seller.
If I want to be able to link multiple products with multiple sellers, I need a table in between, this is table 3
- table 3 contains products IDs and sellers IDs
Let's start from table 3 and get all the names of all the sellers that are referenced in it (we don't take car of the products right now)
It's just a matter of selecting seller_name from the seller table where the seller ID is in table 3. This translates in SQL like :
SELECT
seller.seller_name
FROM
seller
INNER JOIN product_seller ON product_seller.id_seller = seller.id
Now, with this query, keep in mind that if a seller is referenced 150 times, it will be listed 150 times on the result query.
If you want it only once, even if he appears multiple times, you need an extra something in the query : the DISTINCT keyword :
SELECT DISTINCT
seller.seller_name
FROM
seller
INNER JOIN product_seller ON product_seller.id_seller = seller.id
This ensures that you will get each seller only once BUT...
As soon as you will add parameters to your query, the DISTINCT keyword will act differently.
For example, I you add the product ID to the query like :
SELECT DISTINCT
product.id,
seller.seller_name
FROM
seller
INNER JOIN product_seller ON product_seller.id_seller = seller.id
Even the DISTINCT keyword, the seller name will be repeated as many times as it is linked with unique IDs of products.
Anyway, this is how to do it. Hope this helped.
Mathias
My Visual Database → SQL queries → An SQL Expression to pick up several names in a specific table?
Powered by PunBB, supported by Informer Technologies, Inc.
Theme Hydrogen by Kushi