MySQL: extracting data from 3 tables

ShaqDiesel · 09-03-2013, 04:25 AM

I have the following schema that I want to create a sql query from:

Code:

TABLE Metainfo:
attrName    attrValue                               instanceId
host        https://www.site1.com                   1
timestamp   2013-08-02 16:04:20.142                 1
host        https://www.site2.com                   2
timestamp   2013-08-05 01:00:11.001                 2
host        https://www.site3.com                   3
timestamp   2013-08-05 02:09:01.782                 3

TABLE PrimaryData:
attrName        attrValue        metaInstanceId
portNum         80                1
serverName      pegasus           1
inUse           F                 1
portNum         400               2
serverName      hercules          2
inUse           T                 2
portNum         4040              3
serverName      colossus          3
inUse           T                 3

TABLE AuditData
result          metaInstanceId
Pass             1
Pass             1
Fail             1
Pass             2
Pass             2
Pass             2
Fail             3
Pass             3
Pass             3

each row in AuditData tells us whether a configuration in PrimaryData passed or failed some test. There are 3 configurations tested 3 times, which is why PrimaryData and AuditData have 9 rows. Each of these tables has columns I haven't shown to make things easier. I want a query that gives me the timestamp value, the configuration name, the configuration value, and the result. So in the sample data shown this query would result in:

Code:

timestampVal                configName    configValue    result
2013-08-02 16:04:20.142     portNum       80             Pass
2013-08-05 01:00:11.001     portNum       400            Pass
2013-08-05 02:09:01.782     portNum       4040           Fail
2013-08-02 16:04:20.142     serverName    pegasus        Pass
2013-08-05 01:00:11.00      serverName    hercules       Pass
2013-08-05 02:09:01.782     serverName    colossus       Pass
2013-08-02 16:04:20.142     inUse         F              Fail
2013-08-05 01:00:11.001     inUse         T              Pass
2013-08-05 02:09:01.782     inUse         T              Pass

I've tried this which gives me duplicates:

Code:

SELECT m.attrValue AS metaval, p.attrName AS configname, p.attrValue AS configvalue,  
       a.result AS auditresult FROM Metainfo m, PrimaryData p, AuditData a 
                                    WHERE m.attrName='timestamp' AND  
                                    m.instanceId=p.metaInstanceId=a.metaInstanceId  
                                    ORDER BY configname, metaval;

grail · 09-03-2013, 07:15 AM

Firstly, please use [code][/code] tags when showing code or data.

As for your problem, please show us what you have tried and where you are stuck?

Habitual · 09-03-2013, 09:56 AM

Code:

Select distinct

...?

ShaqDiesel · 09-03-2013, 09:59 AM

Select distinct on what, i cant wrap it around all the desired columns can i?

sundialsvcs · 09-03-2013, 10:24 AM

Read up on some basic SQL, specifically inner join.

To query data from multiple tables, you identify rows that the two have in common (whether or not their names are the same), and you JOIN on those columns. This produces a row-set consisting of every unique combination from all the joined-by-value columns in all tables. The, you SELECT [DISTINCT] from that.

If necessary, you must dis-ambiguate column names by using tablename.columnname syntax.

ShaqDiesel · 09-03-2013, 10:27 AM

I tried an inner join which does the same thing im doing, it also produced duplicates

grail · 09-03-2013, 07:45 PM

So I know you have tried to help us by excluding columns, however, your last 2 tables (with current data shown) is useless.

If you look at the data as presented, for all instanceId from Metainfo could be in one of 3 states from either table, hence for each row
in Metainfo you should expect 9 rows of data (3 x 3)

I would suggest you go back and look at other columns to find more restrictive data options.

Unfortunately, the suggestion to use distinct is moot as it will also return unexpected information:

Code:

TABLE AuditData
result          metaInstanceId
Pass             2
Pass             2
Pass             2

If you used distinct on just this table it would return only a single row, whereas looking at your required output, all 3 rows show supposedly different associated information.

ShaqDiesel · 09-04-2013, 09:10 AM

Quote:

Originally Posted by grail

So I know you have tried to help us by excluding columns, however, your last 2 tables (with current data shown) is useless.

If you look at the data as presented, for all instanceId from Metainfo could be in one of 3 states from either table, hence for each row
in Metainfo you should expect 9 rows of data (3 x 3)

I would suggest you go back and look at other columns to find more restrictive data options.

Unfortunately, the suggestion to use distinct is moot as it will also return unexpected information:

Code:

TABLE AuditData
result          metaInstanceId
Pass             2
Pass             2
Pass             2

If you used distinct on just this table it would return only a single row, whereas looking at your required output, all 3 rows show supposedly different associated information.

But I am restricting it to only the 'timestamp' metainfo....

grail · 09-04-2013, 10:25 AM

Ok, so let us look at the outcome of just one entry from metainfo:

Code:

attrName    attrValue                               instanceId
timestamp   2013-08-02 16:04:20.142                 1

So after getting this entry the next thing you do is link to primarydata based on the instanceid from metainfo and the metaInstanceId in primarydata.
So now for this single entry there are 3 rows in primarydata that have the corresponding id. I will simply show as if we had done select *:

Code:

p.attrName      p.attrValue       p.metaInstanceId  m.attrName    m.attrValue                       m.instanceId
portNum         80                1                 timestamp     2013-08-02 16:04:20.142           1
serverName      pegasus           1                 timestamp     2013-08-02 16:04:20.142           1
inUse           F                 1                 timestamp     2013-08-02 16:04:20.142           1

Lastly you have added a third table which also has 3 entries related to the id = 1 and since there is no way to distinguish from any other line this join will be a product of the current rows
multiplied by the newly added rows, so we end up with:

Code:

p.attrName      p.attrValue       p.metaInstanceId  m.attrName    m.attrValue                       m.instanceId    a.result          a.metaInstanceId
portNum         80                1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
portNum         80                1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
portNum         80                1                 timestamp     2013-08-02 16:04:20.142           1               Fail              1
serverName      pegasus           1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
serverName      pegasus           1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
serverName      pegasus           1                 timestamp     2013-08-02 16:04:20.142           1               Fail              1
inUse           F                 1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
inUse           F                 1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
inUse           F                 1                 timestamp     2013-08-02 16:04:20.142           1               Fail              1

So as you can see there is no way here to reduce the list of information presented as there is nothing particularly unique about the data you have returned.

Now if primarydata and auditdata were linked on a second id there returned a single unique entry in auditdata for the corresponding id in primarydata, then you would be able to reduce
this to the 3 single entries you seem to be looking for.

So again I say, you need to go back and look at the other fields to find better items to join on or this will be the best you get.

Lastly I would add, the suggestion of using distinct would make the above look like:

Code:

p.attrName      p.attrValue       p.metaInstanceId  m.attrName    m.attrValue                       m.instanceId    a.result          a.metaInstanceId
portNum         80                1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
portNum         80                1                 timestamp     2013-08-02 16:04:20.142           1               Fail              1
serverName      pegasus           1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
serverName      pegasus           1                 timestamp     2013-08-02 16:04:20.142           1               Fail              1
inUse           F                 1                 timestamp     2013-08-02 16:04:20.142           1               Pass              1
inUse           F                 1                 timestamp     2013-08-02 16:04:20.142           1               Fail              1

As there are 2 unique results in each set it will reduce to 6 rows but still not what is required.