Thread: Problems with select statement
OK, this may be just my brain being foggy because it's late at night and I've been working on this project too long, but I have a SELECT statement that's not doing what I want, and I can't figure out why. The statement is: SELECT * FROM users_to_plans p, users u, relation_types r, languages l WHERE p.plan_id = [some plan number here] AND p.user_id = u.user_id AND u.language_id = l.language_id AND u.relation_type_id = r.relation_type_id ORDER BY u.relation_type_id What I want it to do: Bring up all the users who are included in a particular plan (i.e. all the entries in users_to_plans for that plan number) and for each one, also bring up their language preference and their relationship to the primary contact for that plan. Each user has a relation_type_id listed that corresponds to a particular kind of relationship in the relations table (primary contact, spouse, child, etc.) and a language_id that corresponds to one of the languages in the language table. What it's doing instead: Bringing up only the primary contact, even though there are more users listed under that plan in the users to plans table. When I tried cutting out the relationship and language, it worked fine, but something about including those - either of them; I tried it with each one individually as well as together - makes it bring up only one result. Does anyone know why it's doing this, and how I might rephrase this so that it works properly? I suppose I could just do separate queries of the languages and relations tables for each user that comes up, but that seems messy and inefficient. There's got to be a way to do it with just one query statement... Any assistance would be appreciated. Lynna -- Spider Silk Design - http://www.spidersilk.net 509 St Clair W Box 73576, Toronto ON Canada M6C 1C0 Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289
On Thu, 19 Jan 2006, Lynna Landstreet wrote: > OK, this may be just my brain being foggy because it's late at night and > I've been working on this project too long, but I have a SELECT statement > that's not doing what I want, and I can't figure out why. > > The statement is: > > SELECT * FROM users_to_plans p, users u, relation_types r, languages l > WHERE p.plan_id = [some plan number here] > AND p.user_id = u.user_id > AND u.language_id = l.language_id > AND u.relation_type_id = r.relation_type_id > ORDER BY u.relation_type_id What does something like: select * from users_to_plans p inner join users u using (user_id) left join relation_types r using (relation_type_id) left join languages l using (language_id) where p.plan_id = [some plan number here] order by u.relation_type_id give you?
On 1/19/06 9:55 AM, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
> What does something like:
> select * from
> users_to_plans p inner join
> users u using (user_id) left join
> relation_types r using (relation_type_id) left join
> languages l using (language_id)
> where p.plan_id = [some plan number here]
> order by u.relation_type_id
>
> give you?
Yay! That worked perfectly! Thank you!
I really need to read up on the different kinds of joins. I'm still not
clear on how all that works. I've done queries across multiple tables, but
only in the fairly simple form that was in my previous e-mail, which
obviously is not suitable for all situations.
The one PostgreSQL book I have ("Beginning Databases with PostgreSQL" by
Richard Stones and Neil Matthew) doesn't go into a lot of detail on them -
would you happen to know of any online tutorials about that sort of thing?
Thanks,
Lynna
--
Spider Silk Design - http://www.spidersilk.net
509 St Clair W Box 73576, Toronto ON Canada M6C 1C0
Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289
On Thu, 19 Jan 2006, Lynna Landstreet wrote:
> On 1/19/06 9:55 AM, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:
>
> > What does something like:
> > select * from
> > users_to_plans p inner join
> > users u using (user_id) left join
> > relation_types r using (relation_type_id) left join
> > languages l using (language_id)
> > where p.plan_id = [some plan number here]
> > order by u.relation_type_id
> >
> > give you?
>
> Yay! That worked perfectly! Thank you!
Okay, if that works, that probably implies that some of the users aren't
matching up with relation_types or languages. Are you getting NULL outputs
for the r and/or l columns for the people that were missing before?
> I really need to read up on the different kinds of joins. I'm still not
> clear on how all that works. I've done queries across multiple tables, but
> only in the fairly simple form that was in my previous e-mail, which
> obviously is not suitable for all situations.
There's a little information in the documentation for SELECT about the
different types.
> The one PostgreSQL book I have ("Beginning Databases with PostgreSQL" by
> Richard Stones and Neil Matthew) doesn't go into a lot of detail on them -
> would you happen to know of any online tutorials about that sort of thing?
I don't know how good they are, but...
http://www.devx.com/dbzone/Article/17403/0/page/1
http://www.w3schools.com/sql/sql_join.asp
At 02:47 PM 1/19/06, Lynna Landstreet wrote:
>I really need to read up on the different kinds of joins.
http://en.wikipedia.org/wiki/JOIN
----- Original Message ----- From: "Lynna Landstreet" <lynna@spidersilk.net> > would you happen to know of any online tutorials about that sort of thing? > See Join Basics - How to Join Queries and Tables - http://www.varlena.com/varlena/GeneralBits/56.php Regards, George
On 1/19/06 3:47 PM, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > Okay, if that works, that probably implies that some of the users aren't > matching up with relation_types or languages. Are you getting NULL outputs > for the r and/or l columns for the people that were missing before? Yes - it looks like some of them didn't have values in those columns (I need to more clearly mark those fields in the form where people enter data as required). So I guess that was what threw it somehow? > I don't know how good they are, but... > http://www.devx.com/dbzone/Article/17403/0/page/1 > http://www.w3schools.com/sql/sql_join.asp Thanks! And thank you to everyone else who recommended resources as well - I definitely have my next batch of reading lined up now... Lynna -- Spider Silk Design - http://www.spidersilk.net 509 St Clair W Box 73576, Toronto ON Canada M6C 1C0 Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289