1 (edited by VascoMorais 2017-10-29 12:13:30)

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?

2 (edited by mathmathou 2017-11-10 02:18:13)

Re: An SQL Expression to pick up several names in a specific table?

Hello Vasco smile


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

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor