1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL query help needed...

Discussion in 'HTML, Graphics & Programming' started by Punt, Feb 22, 2019.

  1. Punt

    Wise Guy

    Joined: Nov 17, 2003

    Posts: 1,600

    Location: Gateshead

    Morning,

    I'm needing help with a query and I'm hoping you clever lads and lasses could help me get my head around it.

    Lets say I have a table like so...

    ----------------------------------------------------
    | id | serial | code | date |
    ----------------------------------------------------
    | 1 | 811827 | 211.037 | 2019-02-12 00:00:00 |
    | 2 | 635278 | 124.002 | 2019-02-13 00:00:00 |
    | 3 | NULL | 124.002 | 2019-02-13 00:00:00 |
    | 4 | NULL | 137.033 | 2019-01-22 00:00:00 |
    | 5 | 263842 | 332.199 | 2019-02-01 00:00:00 |
    | 6 | NULL | 332.199 | 2019-02-01 00:00:00 |
    | 7 | 554283 | 193.137 | 2019-01-14 00:00:00 |
    | 8 | 384023 | 193.137 | 2019-01-14 00:00:00 |
    ----------------------------------------------------

    I need to return all rows where serial is null and of those, rows that have duplicate codes and dates.

    Eg. id 3, 4 & 6 because they're null and id 2 & 5 because they have duplicates where serial is null but not is 8 because it's duplicate is not null.

    Hope that make sense?!

    Cheers :)
     
  2. skyripper

    Wise Guy

    Joined: Jul 19, 2011

    Posts: 2,059

    Code:
    SELECT * FROM whatever_table
    WHERE id IN (
    SELECT id FROM whatever_table WHERE ISNULL(serial) /* or whatever function variant your DB supports */ 
    )
    AND id IN 
    (SELECT ID FROM whatever_table GROUP BY 
    CODE, DATE
    HAVING COUNT(SERIAL) > 1)
    ;
    
    No guarantees on validity or results.
     
  3. HungryHippos

    Sgarrista

    Joined: Mar 25, 2004

    Posts: 7,583

    Location: Fareham

    This *might* work but I'd probably have to play with some actual data to test it.

    Change Table to your actual table name.

    I am attempting to join the data with null serials back to itself using inner join, but only where the code and dates match.

    Code:
    SELECT * FROM
    (
        SELECT *
        FROM Table
        WHERE Serial IS NULL
    ) AS t1
    INNER JOIN Table AS t2
    ON t2.code = t1.code
    AND t2.date = t2.date
     
  4. h4rm0ny

    Soldato

    Joined: Jun 25, 2011

    Posts: 5,474

    Location: Yorkshire and proud of it!

    You haven't said what database software you are using. E.g. MySQL, Postgres, etc. That's kind of helpful. ;) Version is also useful.

    Here's something that would work for MySQL v.5.6+ (I include the create table statements for convenience):

    Table def:
    Code:
    CREATE TABLE punt (id INT UNSIGNED NOT NULL, serial INT UNSIGNED NULL, code VARCHAR(7) NOT NULL, date DATETIME NOT NULL) ENGINE=InnoDB;
    
    Table data:
    Code:
    INSERT INTO punt (id, serial, code, date) VALUES
    (1,811827,'211.037','2019-02-12 00:00:00'),
    (2,635278,'124.002','2019-02-13 00:00:00'),
    (3,NULL  ,'124.002','2019-02-13 00:00:00'),
    (4,NULL  ,'137.033','2019-01-22 00:00:00'),
    (5,263842,'332.199','2019-02-01 00:00:00'),
    (6,NULL  ,'332.199','2019-02-01 00:00:00'),
    (7,554283,'193.137','2019-01-14 00:00:00'),
    (8,384023,'193.137','2019-01-14 00:00:00');
    
    Query:
    Code:
    SELECT id, serial, code, date FROM punt WHERE serial IS NULL
    UNION
    SELECT a.id, a.serial, a.code, a.date FROM punt a INNER JOIN punt b ON (a.code = b.code AND a.date = b.date AND a.id <> b.id) WHERE b.serial IS NULL
    ORDER BY id ASC;
    
    Results:
    Code:
    +----+--------+---------+---------------------+
    | id | serial | code    | date                |
    +----+--------+---------+---------------------+
    |  2 | 635278 | 124.002 | 2019-02-13 00:00:00 |
    |  3 |   NULL | 124.002 | 2019-02-13 00:00:00 |
    |  4 |   NULL | 137.033 | 2019-01-22 00:00:00 |
    |  5 | 263842 | 332.199 | 2019-02-01 00:00:00 |
    |  6 |   NULL | 332.199 | 2019-02-01 00:00:00 |
    +----+--------+---------+---------------------+
    
    Rows 2,3,4,5 and 6 as requested.

    EDIT: As in many cases, judicious use of unique indexes might head off this problem depending on what the rules for the data are supposed to be.
     
    Last edited: Feb 23, 2019
  5. Brian The Snail

    Gangster

    Joined: Jan 11, 2005

    Posts: 175

    Location: Sunny Suffolk

    Nice.

    I introduced an ID 9 which matches the code and date in ID 2 - works as expected - 9 pulled out.

    I can stop now and watch the rugby :)
     
  6. h4rm0ny

    Soldato

    Joined: Jun 25, 2011

    Posts: 5,474

    Location: Yorkshire and proud of it!

    Glad I could help. :) Next time post DB and version, though! -.-
     
  7. HungryHippos

    Sgarrista

    Joined: Mar 25, 2004

    Posts: 7,583

    Location: Fareham

    Weird, that's not the op!
     
  8. Brian The Snail

    Gangster

    Joined: Jan 11, 2005

    Posts: 175

    Location: Sunny Suffolk

    To close the circle I was using MS SQL Server - version irrelevant
     
  9. Punt

    Wise Guy

    Joined: Nov 17, 2003

    Posts: 1,600

    Location: Gateshead

    Thanks to everyone to helped. I should have stated I'm using MS Sql Server 2008 R2.

    HungryHippos solution worked a treat with my production data. Didn't think about joining to itself! Thanks for that.

    Also happy that my thread could help someone else :)
     
  10. HungryHippos

    Sgarrista

    Joined: Mar 25, 2004

    Posts: 7,583

    Location: Fareham

    Glad to hear it, did that one without any testing off the top of my head too! :)
     
  11. AndyCr15

    Capodecina

    Joined: Apr 28, 2011

    Posts: 10,196

    Location: London, UK

    I have a request too, would it be silly to just have one SQL thread? I'll post here, but let me know if you prefer and I'll make a new thread :)

    So, trying to search one term in two tables and return the first table ID if it's found in the second table, which is linked A.id to B.taskid -

    Code:
    SELECT A.id      AS id,
           A.task    AS task,
           A.measure AS measure
    FROM   `A`
           JOIN B
             ON A.id = B.taskid
    WHERE  `wheel` = 'profit'
           AND ( A.task LIKE '%search%'
                  OR A.measure LIKE '%search%'
                  OR B.subtask LIKE '%search%' )
    GROUP  BY A.id
    ORDER  BY A.task 
     
  12. HungryHippos

    Sgarrista

    Joined: Mar 25, 2004

    Posts: 7,583

    Location: Fareham

    I like being more specific with queries and joins, for example where you say wheel = 'profit', you should reference a.wheel or b.wheel = 'profit'.

    If you're selecting other fields and using a GROUP BY, you'll probably need to either add the SELECT fields to the GROUP BY, or otherwise have the SELECT statements be a native grouping mechanism, for example SUM(field) would group that field together by summing it.

    Is there a reason you need to query both tables? you said that you need to return the ID from TableA if you find a matching task in TableB, but are there scenarios where data in TableA might not be in TableB?

    There are various ways to query this kind of data, but it definitely helps to have a sample of the data as per how the OP listed theirs, and expected return rows.
     
  13. AndyCr15

    Capodecina

    Joined: Apr 28, 2011

    Posts: 10,196

    Location: London, UK

    Thanks, when I have a bit more time I'll get some sample data, but basically

    Table A - id, task, owner, measure
    Table B - id, subtask, taskid (to match to A.id), owner

    What I'm trying to do, if someone searches a term, it shows the task that either has the term in the task (the name) or the measure, or has a subtask containing the search... and then show the main task (A.task)
     
  14. HungryHippos

    Sgarrista

    Joined: Mar 25, 2004

    Posts: 7,583

    Location: Fareham

    Try this without the grouping or ordering bits? can add those later.

    Code:
    SELECT
    a.id,
    a.task,
    a.measure
    FROM TableA AS a
    INNER JOIN TableB AS b
    ON b.taskid = a.id
    WHERE a.task LIKE '%Search%'
    OR a.measure LIKE '%Search%'
    OR b.subtask LIKE '%Search%'
    
     
  15. AndyCr15

    Capodecina

    Joined: Apr 28, 2011

    Posts: 10,196

    Location: London, UK

    Thanks, I'll give it a go.
     
  16. h4rm0ny

    Soldato

    Joined: Jun 25, 2011

    Posts: 5,474

    Location: Yorkshire and proud of it!

    No. But anyone posting a request for help without specifying type and version of the database should be banned for a week!
     
  17. AndyCr15

    Capodecina

    Joined: Apr 28, 2011

    Posts: 10,196

    Location: London, UK

    Sure. What are the different types? How do I know what mine is? How do they differ?

    Thanks.
     
  18. HungryHippos

    Sgarrista

    Joined: Mar 25, 2004

    Posts: 7,583

    Location: Fareham

    Worse are people that don't confirm if my suggestion helped or not haha! :D
     
  19. AndyCr15

    Capodecina

    Joined: Apr 28, 2011

    Posts: 10,196

    Location: London, UK

    I've not had chance to do any coding for some time... so I don't know at the moment :( I don't yet know how you offered advice not knowing the type and version of database. Hoping to learn :)
     
  20. HungryHippos

    Sgarrista

    Joined: Mar 25, 2004

    Posts: 7,583

    Location: Fareham

    There are different versions of SQL, I exclusively use Microsoft SQL (or MS SQL). There are others though like MYSQL for instance.

    They are pretty similar and have similar syntaxes, but of course some differences too and it's not a one size fits all when it comes to what works on one version working on another.