SQL query, pulling from 2 tables

[supersoup]

I have one table METER with the primary key serial, another table TC with the primary key tc, and a third table History with the primary consisting of the 2 foreign keys serial and tc along with week and create_date. Every TC has 0 or more METER's. How do I write a script that will give me a list of all tc's who have a status of 1 that only have METERS with a status of 0?

METER HISTORY TC
serial serial tc
status week status
tc
create_date

[tesuji]

Hello,

If I understand you correctly, there is a many-to-many relationship between METER and TC where the linking table is HISTORY (has got pk serial and tc from METER and TC). Primary key of HISTORY consists of (serial, tc, week, create_date). Then the select could be:

select h.`week`, h.create_date, ... further data ... 
from METER m join HISTORY h on m.`serial` = h.`serial` join TC t on h.tc = t.tc
 where t.status = 1 and m.status = 0 order by h.`week`, h.create_date;

Hope, this will help you a little.

-- tesu

[supersoup]

thank you tesu with your quick response, however i dont think i was clear enough on my description. the relationship should be many-to-one, with many METERS in one TC each METER only having one TC.

I ran the query and was double checking the results and it seems to be returning TCs that have METERS with a status of 1 and 0. The query would return the TC with the METER with a status of 0 but after filtering through the tables I found that the METER also had a status 1 assigned to it.

Here is the code i ran:

select distinct h.TC, h.serial
from METER m join RECONCILIATION_HISTORY h on m.serial = h.serial join TC t on h.tc = t.tc
where t.status = 1 and m.status = 0 order by tc;

[tesuji]

Your statement:

with many METERS in one TC
(and)
each METER only having one TC

defines a true one-to-many relationship (primary key of TC is attribute of METER).

However your linking table History contains foreign keys serial and tc of related tables. So this is a true many-to-many relationship what allows that one METER could have many TC.

You can examine whether your tables contain consistent data with this query:

select h.serial, count(h.tc) from TC t join history h on t.tc = h.tc group by h.serial;

If count(h.tc) > 1 then there are METER having more than one TC, thus inconsistent data.

As for your query, due to the clause "where t.status = 1 and m.status = 0" it must only list METER having status 0 and TC having status 1. Yet I think your are not that content with the query's result. Can you give more details how the result should look like?

-- tesu

[supersoup]

Ah, you are correct in that your query is returning greater than one, but a METER and a TC can have a duplicate if it has a different week.

So if history had the entry:
Serial TC WEEK PC_CODE
123 987 1 ABC
123 987 2 ABC

the query would return a count of 2 for METER 123.

I think the reason the code is returning TCs with status 1 METERs is because of the same problem?

The original query:

select distinct h.TC, h.serial
from METER m join RECONCILIATION_HISTORY h on m.serial = h.serial join TC t on h.tc = t.tc
where t.status = 1 and m.status = 0 order by tc;

returned:
TC SERIAL
00802 4263239

Yet when I look at the TC table I get:
TC STATUS
00802 1

And HISTORY table:
SERIAL TC
4263239 00802
6553284 00802

And METER table:
SERIAL STATUS
4263239 0
6553284 1

Therefore TC 00803 should not be showing up because it has an METER with a status of 1 (6553284). I would like there to only be TCs with all of its METERS STATUS's = 0.

[tesuji]

Hello

>>> I think the reason the code is returning TCs with status 1 METERs is because of the
same problem?

Yes that's it, that's a mess with the many-to-many relationship. Now I understand why you put week in primary key of history. you needed it to get unique row id.

>> Therefore TC 00803 should not be...
where is 00803 in your above examples?

The suggested select isn't appropriate for your problem because of many-to-many. So we need more information to filter the correct rows. This points to table HISTORY: Can you state a more complete example containing all columns of its primary key (serial, tc, week, ?) and their associate instances of TC and METER with their pk's too?

If you are further interested in examining data, week must be listed in query for it is an additional differentiator:

SELECT h.SERIAL, h.week, count(h.tc) FROM TC t JOIN history h on t.tc = h.tc GROUP BY h.SERIAL, h.week;

-- tesu

[supersoup]

Yes that query produced all 1s. Sorry that 00803 was supposed to be a 00802. My mistake.

Here is a more detailed example of the data:

TC table"
TC STATUS
99654 1
50701 1
25276 0
99205 1
54481 0
00802 1

HISTORY table:
SERIAL WEEK TC PC_CODE
4263239 1002 00802 CAR
4263239 0953 00802 CAR
6553284 1036 00802 CAR
4614132 0805 25276 APP
4614084 0721 50701 HAW
4614084 0723 50701 HAW
4614250 0723 54481 MIL
4219113 0721 99205 SPO
4219191 0729 99205 SPO

METERS Table:
SERIAL STATUS
4219113 0
4263239 0
4614084 1
4614132 0
4614250 0
6553284 1
4219191 0

So TC 00802 (which has a status of 1) would not be on the list because it has 6553284 with a status of 1 (even though it has 4263239 with a status of 0). TC 99205 would be on the list bc it has a status of 1 and all of its METERs (4219113 and 4219191) have a status of 0.

[tesuji]

Meanwhile I have got an idea which deals with difference operation on sets (A \ B = {x | x element(A) and not element(B)}, sorry this math is not that important) which is MINUS (Oracle specific) or EXCEPT, the latter is a standard operation in ANSI SQL, however mySQL does not support set operations except UNION.

One can simulate set difference by means of subselect. You may try this code:

select distinct tc, serial from history 
where tc not in (select a.tc from history a join meter b on a.serial = b.serial where b.status=1);

Pls try it out, I haven't test this code so far. Tell me the result asap. Then I'll dive into your examples.

-- tesu

[supersoup]

Yes! that worked perfectly! Exactly what I needed! Thank you so much tesu!

[tesuji]

I am glad that the result of the final query has met your notion.

Sometimes it is like not seeing the wood for the trees, for I was too much fixed on filtering the rows by both status in the where-clause what was a plain wrong idea.

When you mentioned that there could also be duplicates because I omitted week of history, the solution by way of set difference became clear. Unfortunately, I forced you to post an extensive example which was not any more necessary, sorry for this additional work.

-- tesu