Conditional query question

I can tell I had a rough week-end because my brain isn’t working today.
I have the below query with a one to many join.

SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid;

Below is a sample of the data returned.

booking_id item_type
C122742 Day Tour
C122742 Flight
C122742 Tour
C122742 Day Tour
C122865 Hotel
C122865 Rail
C122865 Hotel
C122881 Day Tour
C122881 Flight
C122881 Rail
C122881 Hotel
C122894 Free Form
C122945 Day Tour
C122945 Flight

I would like to exclude booking_id record if one of its item_type is Tour.

SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE (((public_ipm_booking_items.item_type) Not Like "Tour"));

The above SQL only gets rid of the one record C122742, Tour.
I need to drop all C122742 records, not just the one with item_type like Tour.

Any help is appreciated.

h2fcell wrote:
I can tell I had a rough week-end because my brain isn't working
today.
I have the below query with a one to many join.

SELECT public_ipm_bookings.booking_id,
public_ipm_booking_items.item_type FROM public_ipm_bookings INNER
JOIN public_ipm_booking_items ON public_ipm_bookings.oid =
public_ipm_booking_items.oid;


I would like to exclude booking_id record if one of its item_type is
Tour.

SELECT public_ipm_bookings.booking_id,
public_ipm_booking_items.item_type FROM public_ipm_bookings INNER
JOIN public_ipm_booking_items ON public_ipm_bookings.oid =
public_ipm_booking_items.oid
WHERE (((public_ipm_booking_items.item_type) Not Like "Tour"));

The above SQL only gets rid of the one record C122742, Tour.
I need to drop all C122742 records, not just the one with item_type
like Tour.

wildcards ...

Not Like "*Tour*"

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Give this a try:

SELECT public_ipm_bookings.booking_id,
public_ipm_booking_items.item_type
FROM public_ipm_bookings
INNER JOIN public_ipm_booking_items
ON public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE public_ipm_bookings.oid NOT IN
(SELECT public_ipm_booking_items.oid
FROM public_ipm_booking_items
WHERE public_ipm_booking_items.item_type Not Like "Tour");

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"h2fcell" wrote:

I can tell I had a rough week-end because my brain isn’t working today.
I have the below query with a one to many join.

SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid;

Below is a sample of the data returned.

booking_id item_type
C122742 Day Tour
C122742 Flight
C122742 Tour
C122742 Day Tour
C122865 Hotel
C122865 Rail
C122865 Hotel
C122881 Day Tour
C122881 Flight
C122881 Rail
C122881 Hotel
C122894 Free Form
C122945 Day Tour
C122945 Flight

I would like to exclude booking_id record if one of its item_type is Tour.

SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE (((public_ipm_booking_items.item_type) Not Like "Tour"));

The above SQL only gets rid of the one record C122742, Tour.
I need to drop all C122742 records, not just the one with item_type like Tour.

Any help is appreciated.

SELECT public_ipm_bookings.booking_id
, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items
ON public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE public_ipm_bookings.booking_id NOT IN
(SELECT public_ipm_booking_items.oid
FROM public_ipm_booking_items
WHERE public_ipm_booking_items.Item_Type = "Tour")

OR use

SELECT public_ipm_bookings.booking_id
, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items
ON public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE NOT Exists
(SELECT *
FROM public_ipm_booking_items as TEMP
WHERE TEMP.Item_Type = "Tour"
AND TEMP.oid = public_ipm_bookings.oid)

If those are too slow, post back for a version that is probably faster but
where you cannot update the data.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

h2fcell wrote:
I can tell I had a rough week-end because my brain isn’t working today.
I have the below query with a one to many join.

SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid;

Below is a sample of the data returned.

booking_id item_type
C122742 Day Tour
C122742 Flight
C122742 Tour
C122742 Day Tour
C122865 Hotel
C122865 Rail
C122865 Hotel
C122881 Day Tour
C122881 Flight
C122881 Rail
C122881 Hotel
C122894 Free Form
C122945 Day Tour
C122945 Flight

I would like to exclude booking_id record if one of its item_type is Tour.

SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE (((public_ipm_booking_items.item_type) Not Like "Tour"));

The above SQL only gets rid of the one record C122742, Tour.
I need to drop all C122742 records, not just the one with item_type like Tour.

Any help is appreciated.

Hi Jerry,
Thanks for the reply. You were right about using a sub query but the code
that worked was:

SELECT public_ipm_bookings.oid, public_ipm_bookings.booking_id,
public_ipm_bookings.booking_status, public_ipm_bookings.cancelled_y_or_no,
public_ipm_booking_items.item_type INTO tblQuery1
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE (((public_ipm_bookings.oid) Not In (SELECT
public_ipm_booking_items.oid FROM public_ipm_booking_items WHERE
public_ipm_booking_items.item_type Like "Tour")) AND
((public_ipm_bookings.booking_status)=3) AND
((public_ipm_bookings.cancelled_y_or_no)=1));

Because if the Not In , requires the WHERE
public_ipm_booking_items.item_type Like "Tour".

Thanks.

"Jerry Whittle" wrote:

Give this a try:

SELECT public_ipm_bookings.booking_id,
public_ipm_booking_items.item_type
FROM public_ipm_bookings
INNER JOIN public_ipm_booking_items
ON public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE public_ipm_bookings.oid NOT IN
(SELECT public_ipm_booking_items.oid
FROM public_ipm_booking_items
WHERE public_ipm_booking_items.item_type Not Like "Tour");

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"h2fcell" wrote:

I can tell I had a rough week-end because my brain isn’t working today.
I have the below query with a one to many join.

SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid;

Below is a sample of the data returned.

booking_id item_type
C122742 Day Tour
C122742 Flight
C122742 Tour
C122742 Day Tour
C122865 Hotel
C122865 Rail
C122865 Hotel
C122881 Day Tour
C122881 Flight
C122881 Rail
C122881 Hotel
C122894 Free Form
C122945 Day Tour
C122945 Flight

I would like to exclude booking_id record if one of its item_type is Tour.

SELECT public_ipm_bookings.booking_id, public_ipm_booking_items.item_type
FROM public_ipm_bookings INNER JOIN public_ipm_booking_items ON
public_ipm_bookings.oid = public_ipm_booking_items.oid
WHERE (((public_ipm_booking_items.item_type) Not Like "Tour"));

The above SQL only gets rid of the one record C122742, Tour.
I need to drop all C122742 records, not just the one with item_type like Tour.

Any help is appreciated.