Need help with SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with SQL query

Author

Topic

jb777
Starting Member

2 Posts

Posted - 2008-09-18 : 21:38:08

Hi helpfull people,

I hope someone is able to solve this SQL riddle for me, I have tryed diffrent things without success. But first some background on what I want to achive here.

I have 4 diffrent tables, and there has been little or no thought about relationships when they first created these tables.

Table1
part,task,seq_step_id,seq_step_rev_no,timestamp,test_result,test_status,symptom,plant_no

Table2
seq_step_id,seq_step_rev_no,test_function_name,seq_step_description,lower_limit,upper_limit,symptom

Table3
part,test_seq_name,test_seq_order,test_seq_rev_no,seq_step_id,seq_step_rev_no

Table4
Symptom, Symptom_Description

I can create a simple INNER JOIN between all the 4 tables and get out a result that is giving me what I want, but also more then that. How I can solve this is by using the MAX function, which I have successfully tested in a smaller query. But I cant connect the two together.
Her are my querys:

Working INNER JOIN
SELECT *
FROM ((Table1
INNER JOIN Table2 ON (Table1.seq_step_rev_no = Table2.seq_step_rev_no)
AND (Table1.seq_step_id = Table2.seq_step_id))
INNER JOIN Table4 ON Table1.symptom = Table4.symptom)
INNER JOIN Table3 ON (Table1 .seq_step_rev_no = Table3.seq_step_rev_no)
AND (Table1.seq_step_id = Table3.seq_step_id)
AND (Table1.part = Table3.part)
WHERE (((Table1.seq_step_id)=63978) AND ((Table1.part)='521793') AND ((Table1.seq_step_rev_no)=2));

Working MAX
SELECT part, test_seq_name, seq_step_id, seq_step_rev_no, MAX(test_seq_rev_no) AS rev_no
FROM Table3
WHERE seq_step_rev_no = 2 AND seq_step_id = 63978 AND part = '521793'
GROUP BY seq_step_id, part, test_seq_name, seq_step_rev_no;

The diffrence on the two is that the first one lists all the columns I need but to many rows, where the second one lists a single row that I need but not all the columns.

The big problem here is the Table2 because it can have multiple same part numbers, with the same seq_step_id and the ssame seq_step_rev_no ... but what I need is the MAX or latest test_seq_rev_no.
The test_seq_rev_no is always changing, and I need to find the last one that matches with the above seq_step_rev_no & seq_step_id & part

I hope its understandable. But to sum up, what I need is to get the MAX part into the first SQL query string.

Cheers, John

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-18 : 23:04:48

It’s easy to help if you give us sample data and sample output.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

hey

jb777
Starting Member

2 Posts

Posted - 2008-09-21 : 17:29:59

Hi,

Okay I hope all of this works and makes more sense.

TABLE1

CREATE TABLE table1 (part CHAR(50), serial CHAR(50), task INT, seq_step_id INT, seq_step_rev_no INT, timestamp DATE, test_result CHAR(50), test_status CHAR(50), symptom CHAR(50), symptom_offset CHAR(50), plant_no CHAR(50), palette_no CHAR(50))

INSERT INTO table1 (part, serial, task, seq_step_id, seq_step_rev_no, timestamp, test_result, test_status, symptom, symptom_offset, plant_no, palette_no)
SELECT '999998', '098765', 233, 12345, 3, TO_DATE( '2-FEB-2004', 'DD-MON-YYYY' ), '112', 'F', '4536', '', '2304', '0' UNION ALL

TABLE2

CREATE TABLE table2 (seq_step_id INT, seq_step_rev_no INT, test_function_name CHAR(50), seq_step_description CHAR(50), result_data_type CHAR(50), test_setup_value CHAR(50), lower_limit CHAR(50), upper_limit CHAR(50), record_result CHAR(50), check_against_limits CHAR(50), task CHAR(50), symptom CHAR(50), symptom_offset CHAR(50), added_by CHAR(50), date_added DATE)

INSERT INTO table2 (seq_step_id, seq_step_rev_no, test_function_name, seq_step_description, result_data_type, test_setup_value, lower_limit, upper_limit, record_result, check_against_limits, task, symptom, symptom_offset, added_by, date_added)
SELECT 12345, 3, 'GRAB_IMAGE', 'some problem occured', 'F', 'Picture', '230', '400', 'A', 'Y', '', '4536', '', '', TO_DATE( '2-DEC-2005', 'DD-MON-YYYY' ) UNION ALL

TABLE3

CREATE TABLE table3 (part CHAR(50), test_seq_name CHAR(50), test_seq_order INT, test_seq_rev_no INT, seq_step_id INT, seq_step_rev_no INT, seq_step_status CHAR(50), modified_by CHAR(50), date_modified DATE)

INSERT INTO table3(part, test_seq_name, test_seq_order, test_seq_rev_no, seq_step_id, seq_step_rev_no, seq_step_status, modified_by, date_modified)
SELECT '999998', 'KKLM12', 89, 2, 12345, 3, 'T', 'Me', TO_DATE( '2-DEC-2006', 'DD-MON-YYYY' ) UNION ALL
SELECT '999998', 'KKLM12', 91, 3, 12345, 3, 'T', 'Me', TO_DATE( '2-DEC-2006', 'DD-MON-YYYY' ) UNION ALL
SELECT '999998', 'KKLM12', 94, 4, 12345, 3, 'T', 'Me', TO_DATE( '2-DEC-2006', 'DD-MON-YYYY' ) UNION ALL
SELECT '999999', 'KLM155', 102, 3, 12345, 2, 'T', 'Me', TO_DATE( '2-DEC-2006', 'DD-MON-YYYY' ) UNION ALL

TABLE4

CREATE TABLE table4 (symptom CHAR(50), symptom_desc CHAR(50))

INSERT INTO table4(symptom, symptom_desc)
SELECT 3456, 'Sympt1' UNION ALL
SELECT 4536, 'Sympt2' UNION ALL
SELECT 7534, 'Sympt3' UNION ALL

Now the result I want is the following:
Everything from table1 WHERE part = '999998' AND serial = '098765'
Combined with
Everything from table2 WHERE table1.seq_step_id = table2.seq_step_id AND table1.seq_step_rev_no = table2.seq_step_rev_no
Combined with
Everything from table3 WHERE table1.part = table3.part AND table1.seq_step_id = table3.seq_step_id AND table1.seq_step_rev_no = table3.seq_step_rev_no BUT it needs to be MAX(test_seq_rev_no)
Combined with
Everything from table 4 WHERE table1.symptom = table4.symptom

From my first post, I can combind everything by the INNER JOIN and I can find the MAX(test_seq_rev_no) - but I cant figure out how to combind the two of them, so I only get the 3rd line out from table3. ("SELECT '999998', 'KKLM12', 94, 4, 12345, 3, 'T', 'Me', TO_DATE( '2-DEC-2006', 'DD-MON-YYYY' ) UNION ALL") WHERE the '4' in this case is the MAX key.

Hope this gives some more insigt,

Cheers, John

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-22 : 00:47:55

what are all field you need as output?

hey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources