MySQL query returning no rows

Question

I have two MySQL tables. What I am trying to do is to export the information where Value 1 is 1 less than Value 2 AND where ID_1 does not have its Value 1 and Value 2 equal.

Note:

Fields Value 1 and 2 are just integers.
Each distinct ID_A has the same Value_2
If there are two Value_1s that are one less than Value_2, look to Value_3 and select one that is higher
The reason why I have two tables here is because I am going to output information from both tables
We can write a script for this, but I need to do this in a single command for bonus points (which my instructor declared is possible)... I haven't even started a script for this, as I don't really know how to do that...

tableA looks like this:

ID_1  ID_2
A     A
A     B
B     A
B     B
C     A
C     B
C     C

tableB looks like this:

ID_1  ID_2  Value_1  Value_2  Value_3
A     A     2        3        NULL
A     B     3        3        NULL
B     A     4        5        NULL
B     B     7        5        NULL
C     A     7        8        98
C     B     3        8        NULL
C     C     7        8        56

The query should return this:

ID_1  ID_2
B     A
C     A

Here is what I have so far... And it keeps returning no hits, which is making me confused. I believe it is the AND clause after the first WHERE statement where I need to fix

SELECT CONCAT(...)
  INTO OUTFILE '/tmp/outfile.tab'
       FIELDS TERMINATED BY '\t'
       ESCAPED BY ''
  FROM tableA
 INNER
  JOIN tableB
    ON tableA.ID_1 = tableB.ID_1
   AND tableA.ID_2 = tableB.ID_2
 WHERE tableB.Value_1 - 1 = tableB.Value_2
   AND tableA.ID_1 !=
        ( SELECT DISTINCT
                 ID_1
            FROM tableB
           WHERE ID_1 = tableA.ID_1
             AND Value_1 = Value_2
        )
;

One final note: we issue all commands through putty, in which we can access MySQL

what is tableA and tableB?? If they have both of the id values, does it mean you can use only tableB for your task? — melihcelik
– melihcelik, Commented Dec 17, 2011 at 0:52

ruakh · Accepted Answer · 2011-12-17 00:45:31Z

2

To be honest, I still don't understand exactly what you're trying to do, but I can explain why your query is returning no rows.

Look at this clause:

   AND tableA.ID_1 !=
        ( SELECT DISTINCT
                 ID_1
            FROM tableB
           WHERE ID_1 = tableA.ID_1
             AND Value_1 = Value_2
        )

The subquery will necessarily always return either tableA.ID_1 or NULL. (Do you see why?) So the comparison is never "true"; it's always either "false" (because tableA.ID_1 != tableA.ID_1 is necessarily "false") or "null/indeterminate" (because tableA.ID_1 != NULL is "null/indeterminate"). Therefore, this clause filters out all results from your query — regardless of what the rest of your query might say.

answered Dec 17, 2011 at 0:45

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eugen Rieck · Accepted Answer · 2011-12-17 01:16:15Z

1

I am not 100% sure of the question, but if I get it right, the first row of tableB (Line 25 in https://i.sstatic.net/nQLj7.jpg) should NOT be selected, because ID_1=A has Value_1=3 in the second row (Line 26 in https://i.sstatic.net/nQLj7.jpg), which is the same as Value_1 of the first row (Line 25 in https://i.sstatic.net/nQLj7.jpg).

So you could start with something like

SELECT .... FROM
tableA NATURAL JOIN tableB
WHERE Value_1=Value_2-1
AND Value_2 NOT IN (SELECT tb.Value_1 from tableB AS tb WHERE tb.ID_1=tableB.ID_1)

which fullfills requirements #1 and #2. For requirement #3 (if there are two rows for an ID_1, chose the one with the highest Value_3), we need to sort that on Value_3 and wrap it in a superquery for grouping:

SELECT .... FROM (
SELECT * FROM
tableA NATURAL JOIN tableB
WHERE Value_1=Value_2-1
AND Value_2 NOT IN (SELECT tb.Value_1 from tableB AS tb WHERE tb.ID_1=tableB.ID_1)
ORDER BY Value_3 DESC
) AS innerview
GROUP BY Value_1,Value_2

which gives the correct answer for the test data in your example.

edited Dec 17, 2011 at 1:16

answered Dec 17, 2011 at 1:10

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

2 Comments

Raymosrunerx Over a year ago

This error keeps being returned: ERROR 1054 (42S22): Unknown column 'tableA.ID_1' in 'field list'

Raymosrunerx Over a year ago

The first example works flawlessly, I am grateful for that, thank you!

DRapp · Accepted Answer · 2011-12-17 04:16:31Z

You'll FIRST have to apply a test for your "Value_3" criteria grouped by the respective "ID_1" classification and Value1, value2. By applying the WHERE clause here, you are getting your final set of records INCLUSIVE of what WOULD be the highest value 3 entry in its result set. Now, that gets joined again to tableB AGAIN, but matching the qualifying entries. Since the COALESCE() will change any NULL value to 0 in the first result set, the JOIN clause must also match that. As in the case for the "A" and "B" groups where no Value_3 was applied, yet in the "C" group, it WILL have a valid value and pre-result in the entry with the max value of 98. That said, when re-joined back to instance "tb2" for TableB a second time will get the proper ID_2 of "A" for that set.

select
      MaxQualified.ID_1,
      tb2.ID_2,
      MaxQualified.Value_1,
      MaxQualified.Value_2,
      tb2.Value_3
   from 
      ( select
              tb.ID_1,
              tb.Value_1,
              tb.Value_2,
              MAX( COALESCE( tb.Value_3, 0 ) ) as HighestVal3
           from
              TableB tb
           where
              tb.Value_1 +1 = tb.Value_2
           group by
              tb.ID_1,
              tb.Value_1,
              tb.Value_2 ) MaxQualified

         JOIN TableB tb2
            on MaxQualified.ID_1 = tb2.ID_1
           AND MaxQualified.Value_1 = tb2.Value_1
           AND MaxQualified.Value_2 = tb2.Value_2
           AND MaxQualified.HighestVal3 = COALESCE( tb2.Value_3, 0 )

Now, that being said, and this is homework, this COULD fail or give multiple answers if you had multiple ID1, Value1, Value2, Value3 entries. It would return all "ID2" instances of the exact same common criteria. You would have to do even one more level nested to remove that level of distinction.

Your answer should ALSO return "A", "A", 2, 3

Collectives™ on Stack Overflow

MySQL query returning no rows

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related