MSSQL - Loop through table and use field value in new query

Question

I have a factory table with factoryname: select factoryname from factory

I then have a products table: select productcode from products

I want to create a list that has products for all factory.

so output :

mill 1 product 1
mill 1 product 2
mill 1 product 3
mill 2 product 1
mill 2 product 2
mill 2 product 3
mill 3 product 1
mill 3 product 2
mill 3 product 3

I have something like this:

DECLARE @numrows int
DECLARE @i int
DECLARE @department int

 SET @numrows = (SELECT COUNT(*) FROM factory)
WHILE (@i <= @numrows)
    BEGIN
        SELECT factoryname,product FROM products,factory )
        SET @i = @i + 1
    END

I am obviously off the mark, here, any advice? Thanks as always.

why not join the two tables and order by factory ?

Saddam Abu Ghaida
– Saddam Abu Ghaida

2014-02-21 11:07:20 +00:00
Commented Feb 21, 2014 at 11:07 — Saddam Abu Ghaida
– Saddam Abu Ghaida, Commented Feb 21, 2014 at 11:07

Frederik Gheysels · Accepted Answer · 2014-02-21 11:06:43Z

2

SQL is a set based language, so you should try not to use loops in SQL whereever possible. It will hurt performance.

What you want to do, is use a JOIN to JOIN 2 tables together:

SELECT factory.factoryname, products.productcode
FROM factory, products
INNER JOIN products.factory_id = factory.factory_id

Something like this. This will give you a collection of products that are made in a certain factory.

Displaying this in a nice way to the user should be done in your UI layer in your application. Do not try to use SQL to properly format data.

answered Feb 21, 2014 at 11:06

Frederik Gheysels

57.2k12 gold badges103 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shree.pat18 · Accepted Answer · 2014-02-21 11:06:05Z

1

If you want to list all the factory names and all products, you can use a cross join like this:

    select factoryname, productname from factory cross join products

A Cross Join is basically a Cartesian product of 2 tables, resulting in (m * n) records if table 1 has m and table 2 has n records.

answered Feb 21, 2014 at 11:06

shree.pat18

21.8k3 gold badges45 silver badges65 bronze badges

3 Comments

shree.pat18 Over a year ago

No problems! But remember that this can quickly get ugly for tables with huge number of rows, even if only one of them is large.

Frederik Gheysels Over a year ago

This will give you, for every row in 'products', every row in factories. In fact, it will return 'number of rows in products' * 'number of rows in factories'. Not quite what the topicstarter had in mind, I guess ...

shree.pat18 Over a year ago

Possible, which is why my first line says 'If you want to list all the factory names and all products'. Not knowing if the sample output represented that condition or a filtered set, I took a guess at the requirement.

Ans · Accepted Answer · 2014-02-21 11:20:18Z

0

JOIN will be effective solution for the above but there should be common column in both the tables. INNER JOIN is most suitable if for all factories you have some product. LEFT JOIN will work if you want all factories to be listed though there are factories for which there is no product. RIGHT JOIN will work if you want all products to be listed and there are products for which there is no corresponding factory in factory table.

Hope it helps....someway..........

answered Feb 21, 2014 at 11:20

Ans

3443 gold badges4 silver badges11 bronze badges

Collectives™ on Stack Overflow

MSSQL - Loop through table and use field value in new query

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related