SQL query help needed...

Punt

Punt

Punt

Associate
Joined
17 Nov 2003
Posts
1,945
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 :)
 
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.
 
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
 
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:
Thanks to everyone to helped. I should have stated I'm using MS Sql Server 2008 R2.

HungryHippos said:
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
Click to expand...

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 :)
 
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
 
AndyCr15 said:
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
Click to expand...

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.
 
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)
 
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%'
 
AndyCr15 said:
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 :)
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top Bottom