Thread: ok, I have another select for ya

bug_id is a field present in all three of my tables.

From my earilier question, I understand I need to use joins to avoid a
cartesian join.

This is my question, how can I do a:

select * from summary,env_info,plat_info where summary.subject like
'%test1%';

properly?

Basically I believe what I need to do is find the subject in summary, then
pull the bug_id, which is common across all for that subject, then do a

select * from summary,env_info,plat_info where summary.bug_id = `result of
the subject field select`

kind of thing.

Thanks for any tips.  I realize a lot of this is basic sql but I am
learning a lot from these examples.

Thanks
-jeremy
--
salad.

On Fri, 10 Aug 2001 pgsql-general@commandprompt.com wrote:

> On Fri, 10 Aug 2001, Jeremy Hansen wrote:
> >bug_id is a field present in all three of my tables.
> >>From my earilier question, I understand I need to use joins to avoid a
> >cartesian join.
> >This is my question, how can I do a:
> >
> >select * from summary,env_info,plat_info where summary.subject like
> >'%test1%';
> >properly?
>
> I'd recommend using the JOIN syntax to create your set, and then applying
> a WHERE clause to that set. For example:
>
> SELECT * FROM summary s
>          INNER JOIN env_info e ON (s.bug_id=e.bug_id)
>          INNER JOIN plat_info p ON (s.bug_id=p.bug_id)
>          WHERE s.subject LIKE '%test1%';

I love you.

> That way you're only doing the WHERE on the new set.
>
> Note that the letters following the table names are used later for the dot
> notation of the columns. This just makes it a bit more readable (to my
> eye).
>
>
>
> Regards,
> Jw.
> --
> jlx@commandprompt.com
> by way of pgsql-general@commandprompt.com
>
>

--
salad.

On Fri, 10 Aug 2001, Jeremy Hansen wrote:
>bug_id is a field present in all three of my tables.
From my earilier question, I understand I need to use joins to avoid a
>cartesian join.
>This is my question, how can I do a:
>
>select * from summary,env_info,plat_info where summary.subject like
>'%test1%';
>properly?

I'd recommend using the JOIN syntax to create your set, and then applying
a WHERE clause to that set. For example:

SELECT * FROM summary s
         INNER JOIN env_info e ON (s.bug_id=e.bug_id)
         INNER JOIN plat_info p ON (s.bug_id=p.bug_id)
         WHERE s.subject LIKE '%test1%';

That way you're only doing the WHERE on the new set.

Note that the letters following the table names are used later for the dot
notation of the columns. This just makes it a bit more readable (to my
eye).



Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com