Thread: Query Question

I have 4 tables: releases, artist_info, categories, and formats.

I am using this query:

SELECT DISTINCT *, categories.category_name as category_name,
categories.rec_num as category, formats.format_name as format_text,
releases.rec_num as release_rec_num, artist_info.name as artist_name FROM
releases, artist_info, formats, categories WHERE upper(releases.title) LIKE
upper('%get%') OR upper(artist_info.name) LIKE upper('%get%') AND
releases.artist_id = artist_info.rec_num AND releases.format =
formats.rec_num AND releases.category = categories.rec_num AND
releases.active_status = true ORDER BY title DESC;

to search and join the tables...

The intent is to search the releases table where title = %face% and the
artist_info table where name = %face% and return only rows that match that.

Somewhere my join is going wrong. The query is returning the results plus a
release titled 'Face to Face' joined to every artist and every format.

Where am I going wrong? It only occurs on searches where both the
releases.title and artist_info.name match the search criteria.

BTW, this is PG7.1 on MacOS X, though I'm sure it doesn't matter.

Hunter

On Mon, 23 Apr 2001, Hunter Hillegas wrote:

> I have 4 tables: releases, artist_info, categories, and formats.
>
> I am using this query:
>
> SELECT DISTINCT *, categories.category_name as category_name,
> categories.rec_num as category, formats.format_name as format_text,
> releases.rec_num as release_rec_num, artist_info.name as artist_name FROM
> releases, artist_info, formats, categories WHERE upper(releases.title) LIKE
> upper('%get%') OR upper(artist_info.name) LIKE upper('%get%') AND
> releases.artist_id = artist_info.rec_num AND releases.format =
> formats.rec_num AND releases.category = categories.rec_num AND
> releases.active_status = true ORDER BY title DESC;
>
> to search and join the tables...
>
> The intent is to search the releases table where title = %face% and the
> artist_info table where name = %face% and return only rows that match that.
>
> Somewhere my join is going wrong. The query is returning the results plus a
> release titled 'Face to Face' joined to every artist and every format.
>
> Where am I going wrong? It only occurs on searches where both the
> releases.title and artist_info.name match the search criteria.

I'd guess you want parentheses around the first two logical expressions
that are ORed together.

That fixed it! Thanks!

Hunter

> From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
> Date: Mon, 23 Apr 2001 14:54:50 -0700 (PDT)
> To: Hunter Hillegas <lists@lastonepicked.com>
> Cc: PostgreSQL <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] Query Question
>
> On Mon, 23 Apr 2001, Hunter Hillegas wrote:
>
>> I have 4 tables: releases, artist_info, categories, and formats.
>>
>> I am using this query:
>>
>> SELECT DISTINCT *, categories.category_name as category_name,
>> categories.rec_num as category, formats.format_name as format_text,
>> releases.rec_num as release_rec_num, artist_info.name as artist_name FROM
>> releases, artist_info, formats, categories WHERE upper(releases.title) LIKE
>> upper('%get%') OR upper(artist_info.name) LIKE upper('%get%') AND
>> releases.artist_id = artist_info.rec_num AND releases.format =
>> formats.rec_num AND releases.category = categories.rec_num AND
>> releases.active_status = true ORDER BY title DESC;
>>
>> to search and join the tables...
>>
>> The intent is to search the releases table where title = %face% and the
>> artist_info table where name = %face% and return only rows that match that.
>>
>> Somewhere my join is going wrong. The query is returning the results plus a
>> release titled 'Face to Face' joined to every artist and every format.
>>
>> Where am I going wrong? It only occurs on searches where both the
>> releases.title and artist_info.name match the search criteria.
>
> I'd guess you want parentheses around the first two logical expressions
> that are ORed together.
>