• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

mysql qerry

I have a table with three columns: id (index), subid (index), code (string)

for every id there may be several subid's but there is at least one. For every id and given subid the code should be the same. ie there should be a one to one relationship between codeid and code for a given id. Should have done this in two tables of course. What I need is a query that shows the rows where this is not true.
1 Solution
SharathData EngineerCommented:
try this

SELECT t1.* 
FROM   your_table AS t1 
       INNER JOIN (SELECT   id, 
                   FROM     your_table 
                   GROUP BY id, 
                   HAVING   Count(DISTINCT code) > 1) AS t2 
         ON t1.id = t2.id 
            AND t1.subid = t2.subid

Open in new window


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now