SQL Help

Question

Post reply

SQL Help

senthilj

SSC-Addicted

Points: 442
More actions
November 6, 2002 at 9:46 am

#357649

I have 2 tables with the following structure and values
1. cut_table
cut (integer), batch (integer)
10000,100
10000,101
10001,102
10001,103
2. Batch_table
batch (integer) value (integer)
101,1
102,1
103,1
104,2
I need a SQL statement which will give me the cut from the 1st table where all the batch has a value of 1. With the above data I should get only cut 10000 because only that has all batches whose values are 1.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

mahesh_jm SSC Rookie Points: 44 More actions · Answer 1

Select cut_table.Cut from cut_table, Batch_Table where batch_table.batch=cut_table.batch and batch_table.value = 1

Maheshwar

senthilj SSC-Addicted Points: 442 More actions · Answer 2

Maheswar, your SQL will give me both 10000 & 10001. I need only 10000

paulhumphris Hall of Fame Points: 3496 More actions · Answer 3

Is your test data correct.

10001 is the only cut with all its batches with a value of 1 (102, 103)

1000 has one batch with a value of 1 (101) and no cut relating to batch 100.

Can you please clarify.

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

I'm confused. Why do you want 10000, when batch 100 does not have a value at all in batch_table. Plus don't all the batch's associated with cut 10001 have a value of 1 in the batch_table?

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

senthilj SSC-Addicted Points: 442 More actions · Answer 5

Sorry.

The test data should be

batch (integer) value (integer)

100,1

101,1

102,1

103,2

mdburr Default port Points: 1483 More actions · Answer 6

I agree with Paul, your test data seems flawed, given the results you are expecting to see. Regardless, I believe the following query will generate your desired results:



SELECT DISTINCT cut

  FROM cut_table

 WHERE NOT EXISTS (SELECT *

                     FROM cut_table AS c1

                     JOIN Batch_table AS b1

                       ON c1.batch = b1.batch

      AND c1.cut = cut_table.cut

                      AND b1.value <> 1)

Because the data you provided does not accurately reflect what you are trying to accomplish, I suggest either inserting the following row:



INSERT INTO Batch_table VALUES (100, 2)

which will cause that only cut 10001 is returned (since 10000 will have a batch that does not have a value of 1), or updating a row in Batch_table:



UPDATE Batch_table

   SET value = 2

 WHERE batch = 102

In which case, only row 10000 will be returned because any of its cuts which do have a matching row in the batch table all have batches with a value of 1, whereas one of the matching batches for 10001 has a value that is not 1.

Hope this works for you.

Matthew Burr

senthilj SSC-Addicted Points: 442 More actions · Answer 7

senthilj

SSC-Addicted

Points: 442

November 6, 2002 at 10:26 am

#440508

Thanks Mathew. It works!!!