Can MySQL Handle Return Data for Lookup Fields? 1

[smays]

I have a data table (and will soon have plenty more...) that has four load serial number fields, test_data. All the data associated with each load serial number is found in another table, load_ser_num.

CREATE TABLE test_data
(TestDataID INT,
ProductSerNum INT,
TargetInputVoltage TINYINT,
V_In DOUBLE,
InvaluableData DOUBLE,
LoadSerNum_I INT,
LoadSerNum_II INT,
LoadSerNum_III INT,
LoadSerNum_IV INT);

CREATE TABLE load_ser_num
(LoadSerNum INT,
LoadManufID TINYINT,
LoadTypeID SMALLINT);

I suppose the multiple LoadSerNum fields may not be perfectly normalized, but a complete data record is only defined with all four LoadSerNum's...the nature of the loads is such that the loads are truly unique and if the test was to ever be repeated, one would have to have the original loads. One simple example is if someone carelessly had loads from different manufacturers. The same basic argument would be made for test equipment serial numbers.

To access and report the additional information found in the load_ser_num table, I would ordinarily JOIN the test_data table and the load_ser_num tables, with little trouble.

SELECT test_data.ProductSerNum, test_data.TargetInputVoltage,
test_data.InvaluableData, test_data.V_In,
load_ser_num.LoadManufID AS LoadManufID_I,
load_ser_num.LoadManufID AS LoadManufID_II,
load_ser_num.LoadManufID AS LoadManufID_III, load_ser_num.LoadManufID AS LoadManufID_IV

FROM test_data, load_ser_num

WHERE test_data.LoadSerNum_I = load_ser_num.LoadSerNum AND
test_data.LoadSerNumII = load_ser_num.LoadSerNum AND
...;

Earlier attempts to return data for multiple LoadSerNum's resulted in data returned for only one LoadSerNum. I can see why the query doesn't work, but I have no clue how I would make it work.

To use and report the data, I write my own Winderz Apps. I can 'fix' the lookup issue with software if at design time I set up lookup fields to 'show' data for the other LoadSerNum's. The thing is, I have some software I wrote to pull test data from various test data tables in our 5.0.27 MySQL server to be dumped into a spreadsheet. Therefore I need the SQL query itself to be complete. At this point I am merely reporting the actual LoadSerNum's used and not the useful data behind it. If the user wants the useful information associated with the LoadSerNum's, the user (through ME, of course) has to dig through the database to put together the data.

Can it be done?

Thanks,
Steve.

 

[r937]

since there are four (unnormalized) columns, you need four separate joins...

SELECT test_data.ProductSerNum
     , test_data.TargetInputVoltage
     , test_data.InvaluableData
     , test_data.V_In
     , load_1.LoadManufID AS LoadManufID_I
     , load_2.LoadManufID AS LoadManufID_II
     , load_3.LoadManufID AS LoadManufID_III
     , load_4.LoadManufID AS LoadManufID_IV
  FROM test_data
INNER
  JOIN load_ser_num AS load_1
    ON load_1.LoadSerNum = test_data.LoadSerNum_I 
INNER
  JOIN load_ser_num AS load_2
    ON load_2.LoadSerNum = test_data.LoadSerNum_II
INNER
  JOIN load_ser_num AS load_3
    ON load_3.LoadSerNum = test_data.LoadSerNum_III
INNER
  JOIN load_ser_num AS load_4
    ON load_4.LoadSerNum = test_data.LoadSerNum_IV

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[smays]

r937,

Thanks for your prompt reply! I see you ALIAS'ed the JOIN'ed tables so correct, LoadSerNum specific data is available. I am guessing JOIN'ing all the unnormalized LoadSerNum's will result in a performance hit, but I would rather take the performance hit in place attempting to normalize the serial number fields.

I will try this next week!

Thanks,
Steve.

 

[smays]

Well, it worked. All the JOIN's in my queries, shame on me or whatever, were handled in the WHERE clause. As a result I had nothing but errors. I undid all those JOIN's and moved them after the FROM clause, etc... Anyway, *almost* in the immortal words of Bill Engvall, "Here's your s...tar!"

Thanks,
Steve!