CODEWITHSUNDEEP
sqlloops
Smudger
Smudger

Reputation: 10771

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

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.

Upvotes: 0

Views: 298

Answers (3)

Ans
Ans

Reputation: 344

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..........

Upvotes: 0

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

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.

Upvotes: 2

shree.pat18
shree.pat18

Reputation: 21757

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.

Upvotes: 1

Related Questions