Help needed with query within query

Question

Post reply

Help needed with query within query

itsrainman

SSC Rookie

Points: 33

June 20, 2019 at 1:05 pm

#3655755

Hello,

I have followed this forum for a long time and made use of multiple posts to help me with my problems. Hoever I have one which is throwing me.

I have a database with multiple tables, I need to query the database for items recevied that we have not despatched. Relatively easy.

SELECT        Jobs.[RecFrom], ido.[Job #], ido.[test #], ido.[Meter model], ido.[Meter Serial #], ido.[Barcode meter], ido.[Probe Model], ido.[Probe serial #], 
                         ido.[Barcode Probe], ido.[Despatch Date], ido.[Despatch to subcontractor], ido.[Return from Subcontractor], ido.[Cal Date]
FROM            Instdat ido INNER JOIN
                         dbo.Jobs ON ido.[Job #] = Jobs.[Job number]
GROUP BY Jobs.[RecFrom], ido.[Job #], ido.[test #], ido.[Meter model], ido.[Meter Serial #], ido.[Barcode meter], ido.[Probe Model], ido.[Probe serial #], 
                         ido.[Barcode Probe], ido.[Despatch Date], ido.[Despatch to subcontractor], ido.[Return from Subcontractor], ido.[Cal Date]
HAVING        (ido.[Despatch Date] IS NULL) AND (ido.[Cal Date] IS NULL) AND (ido.[Despatch to subcontractor] IS NOT NULL) AND (ido.[Return from Subcontractor] IS NOT NULL) AND 
                         (dbo.Jobs.[RecFrom] IS NOT NULL) OR
                         (ido.[Despatch Date] IS NULL) AND (ido.[Cal Date] IS NULL) AND (ido.[Despatch to subcontractor] IS NULL) AND (dbo.Jobs.[RecFrom] IS NOT NULL)

Running the query gives the results expected. However I now need to check to see if the ido.[Barcode meter] and or the ido.[Barcode Probe] has been recieved in again usinf max job # so that it also displays next to ido.[Job #] a further column listing the max job # relative to the barcode probe and or the barcode meter.

Could someone point me in the right direction as getting a little perplexed.

many thanks

T

June 20, 2019 at 2:37 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246973 More actions · Answer 1

As you've been following the forum for a long time, you'll also know that it's considered good practice to provide sample DDL, data (in the form of INSERT statements) and desired results, so that someone can provide you with a working solution.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

itsrainman SSC Rookie Points: 33 More actions · Answer 2

Hello Phil,

Unforutunatley the data is sensitive so I cannot post it here.

I beleive I just need to add the following into the SELECT statement

(SELECT Max(id.[job #]) from instdat id where ido.[barcode meter] = id.[barcode meter]) as MaxJobNo)

Thom A SSC Guru Points: 99130 More actions · Answer 3

itsrainman wrote:

Hello Phil, Unforutunatley the data is sensitive so I cannot post it here.

The data doesn't need to be real data; as Phil said we need a sample. All it needs to be is representative.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

@itsrainman you've coded this:

WHERE 
(i.[Despatch Date] IS NULL) 
AND (i.[Cal Date] IS NULL) 
AND (i.[Despatch to subcontractor] IS NOT NULL) 
AND (i.[Return from Subcontractor] IS NOT NULL) 
AND (dbo.j.[RecFrom] IS NOT NULL) 

OR

    (i.[Despatch Date] IS NULL) 
AND (i.[Cal Date] IS NULL) 
AND (i.[Despatch to subcontractor] IS NULL) 
AND (dbo.j.[RecFrom] IS NOT NULL)

when you probably mean this:

WHERE (
(i.[Despatch Date] IS NULL) 
AND (i.[Cal Date] IS NULL) 
AND (i.[Despatch to subcontractor] IS NOT NULL) 
AND (i.[Return from Subcontractor] IS NOT NULL) 
AND (dbo.j.[RecFrom] IS NOT NULL) 
)
OR
(
    (i.[Despatch Date] IS NULL) 
AND (i.[Cal Date] IS NULL) 
AND (i.[Despatch to subcontractor] IS NULL) 
AND (dbo.j.[RecFrom] IS NOT NULL)
)

Please confirm.

Also, you can replace that crazy GROUP BY with DISTINCT. It will make your query more intuitive to read and understand.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden