Returning distinct rows meeting condition (1 Viewer)

bd528 · May 23, 2017

Hi,

I have a table in the format below (with sample data)

tblquotes
QuoteID / PetType
123 / 16
123 / 16
123 / 17
456 / 14
789 / 15

How can I distinct rows where a PetType of 16 does not exist for a QuoteID? So my returned results would be :-

QuoteID
456
789

Thanks in advance

plog · May 23, 2017

Your explanation and data, are a little incongruous. "Distinct rows" means all the data in every row meeting the criteria. Your sample data is distinct for the 2 quoteID values meeting the criteria and your expected result just shows their value, not the entire row.

To clear things up, tell me what you expect the results to be from this data in tblquotes:

tblquotes
QuoteID / PetType
123 / 16
123 / 16
123 / 17
456 / 14
789 / 15
456 / 23
456 / 19
832 / 11
832 / 12
832 / 16

bd528 · May 23, 2017

plog said:
Your explanation and data, are a little incongruous. "Distinct rows" means all the data in every row meeting the criteria. Your sample data is distinct for the 2 quoteID values meeting the criteria and your expected result just shows their value, not the entire row.

To clear things up, tell me what you expect the results to be from this data in tblquotes:

tblquotes
QuoteID / PetType
123 / 16
123 / 16
123 / 17
456 / 14
789 / 15
456 / 23
456 / 19
832 / 11
832 / 12
832 / 16

Sorry, what I'm actually after in unique QuoteIDs. My sample data is entirely fictitious, so I over simplified.

But, using your data, I'd like the below returned :-

QuoteID
456
789

As neither over those has a 16 PetType.

Thanks, and apologies.

RogerCooper · May 23, 2017

The easy way is to use 2 queries. The first query selects all quotes with PetType of 16. Then create a query to select all quotes not in the first query. (Use Find Unmatched in Query Wizard if you don't know how).

plog · May 23, 2017

No problem, that's what I thought, just making sure before writing code. To do this you will need a sub-query. This is that SQL:

Code:

SELECT QuoteID
FROM tblquotes
WHERE PetType=16
GROUP BY QuoteID

Paste that SQL into a query and save it as 'sub1'. It finds out the ones that do have a PetType of 16. Then to get the data you want, you use it against the main table to kick out those without:

Code:

SELECT tblquotes.QuoteID
FROM tblquotes
LEFT JOIN sub1 ON sub1.QuoteID = tblquotes.QuoteID
WHERE sub1.QuoteID Is Null
GROUP BY tblquotes.QuoteID

bd528 · May 26, 2017

plog said:
No problem....

Perfect - thank you (again).

Returning distinct rows meeting condition (1 Viewer)

bd528

Registered User.

plog

Banishment Pending

bd528

Registered User.

RogerCooper

Registered User.

plog

Banishment Pending

bd528

Registered User.

Similar threads

Users who are viewing this thread