pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to do this query?
9+ messages / 6 participants
[nested] [flat]
* how to do this query?
@ 2003-05-20 16:00 ww zz <ts_999@yahoo.com>
2003-05-20 16:36 ` Re: how to do this query? Richard Huxton <dev@archonet.com>
2003-05-20 16:46 ` Re: how to do this query? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 2 replies; 9+ messages in thread
From: ww zz @ 2003-05-20 16:00 UTC (permalink / raw)
To: pgsql-sql
New to sql and may be a simple question for you:
I have four tables
sample: sid, sname
make: sid, mdate
measure: sid, mdate, rid
result: rid, resultdata
for a gaven sample it always has a sid and sname, but
it may or may not have any entries in the other
tables, the sample could be measured more than once
and each measure give a result row.
question:
how do I do a query that given a sid, it will retrieve
all the information available in the four tables, if
there are available. If, for example, all tables have
some entry for a gave sample it will retreve all the
information, if the "measure" and "result" are empty
for that sample it will give only the 'sample' and
'make' information....
please help
Thanks
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: how to do this query?
2003-05-20 16:00 how to do this query? ww zz <ts_999@yahoo.com>
@ 2003-05-20 16:36 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2003-05-20 16:36 UTC (permalink / raw)
To: ww zz <ts_999@yahoo.com>; pgsql-sql
On Tuesday 20 May 2003 5:00 pm, ww zz wrote:
> New to sql and may be a simple question for you:
>
> I have four tables
>
> sample: sid, sname
> make: sid, mdate
> measure: sid, mdate, rid
> result: rid, resultdata
>
> for a gaven sample it always has a sid and sname, but
> it may or may not have any entries in the other
> tables, the sample could be measured more than once
> and each measure give a result row.
You'll want to use a LEFT JOIN, something like:
SELECT s.sid, s.sname, m.mdate, m.rid, r.resultdata
FROM sample s LEFT JOIN measure m ON s.sid=m.sid LEFT JOIN result ON
m.rid=r.rid
You'll get nulls where there are no matches.
--
Richard Huxton
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: how to do this query?
2003-05-20 16:00 how to do this query? ww zz <ts_999@yahoo.com>
@ 2003-05-20 16:46 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-20 17:19 ` Re: how to do this query? ww zz <ts_999@yahoo.com>
1 sibling, 1 reply; 9+ messages in thread
From: Stephan Szabo @ 2003-05-20 16:46 UTC (permalink / raw)
To: ww zz <ts_999@yahoo.com>; +Cc: pgsql-sql
On Tue, 20 May 2003, ww zz wrote:
> New to sql and may be a simple question for you:
>
> I have four tables
>
> sample: sid, sname
> make: sid, mdate
> measure: sid, mdate, rid
> result: rid, resultdata
>
> for a gaven sample it always has a sid and sname, but
> it may or may not have any entries in the other
> tables, the sample could be measured more than once
> and each measure give a result row.
> question:
> how do I do a query that given a sid, it will retrieve
> all the information available in the four tables, if
> there are available. If, for example, all tables have
> some entry for a gave sample it will retreve all the
> information, if the "measure" and "result" are empty
> for that sample it will give only the 'sample' and
> 'make' information....
Well, you're not going to get a limited column set, but you can
get NULLs in the non-applicable table columns by using outer joins.
Maybe something like something like
select * from
sample left outer join make using (sid)
left outer join measure using (sid)
left outer join result using (rid)
where sid=<value>;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: how to do this query?
2003-05-20 16:00 how to do this query? ww zz <ts_999@yahoo.com>
2003-05-20 16:46 ` Re: how to do this query? Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-05-20 17:19 ` ww zz <ts_999@yahoo.com>
2003-05-20 17:49 ` Re: how to do this query? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 9+ messages in thread
From: ww zz @ 2003-05-20 17:19 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
another question is:
if I do left outer join on a few big tables, will it
be very slow?
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
> On Tue, 20 May 2003, ww zz wrote:
>
> > New to sql and may be a simple question for you:
> >
> > I have four tables
> >
> > sample: sid, sname
> > make: sid, mdate
> > measure: sid, mdate, rid
> > result: rid, resultdata
> >
> > for a gaven sample it always has a sid and sname,
> but
> > it may or may not have any entries in the other
> > tables, the sample could be measured more than
> once
> > and each measure give a result row.
> > question:
> > how do I do a query that given a sid, it will
> retrieve
> > all the information available in the four tables,
> if
> > there are available. If, for example, all tables
> have
> > some entry for a gave sample it will retreve all
> the
> > information, if the "measure" and "result" are
> empty
> > for that sample it will give only the 'sample' and
> > 'make' information....
>
> Well, you're not going to get a limited column set,
> but you can
> get NULLs in the non-applicable table columns by
> using outer joins.
>
> Maybe something like something like
> select * from
> sample left outer join make using (sid)
> left outer join measure using (sid)
> left outer join result using (rid)
> where sid=<value>;
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: how to do this query?
2003-05-20 16:00 how to do this query? ww zz <ts_999@yahoo.com>
2003-05-20 16:46 ` Re: how to do this query? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-20 17:19 ` Re: how to do this query? ww zz <ts_999@yahoo.com>
@ 2003-05-20 17:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2003-05-20 17:49 UTC (permalink / raw)
To: ww zz <ts_999@yahoo.com>; +Cc: pgsql-sql
On Tue, 20 May 2003, ww zz wrote:
> another question is:
> if I do left outer join on a few big tables, will it
> be very slow?
In general, it shouldn't be particularly, or at least not particularly
worse than an inner join that gave the same number of result rows.
^ permalink raw reply [nested|flat] 9+ messages in thread
* how to do this query
@ 2010-01-09 04:26 Wes James <comptekki@gmail.com>
2010-01-09 09:07 ` Re: how to do this query Ian Barwick <barwick@gmail.com>
2010-01-09 11:36 ` Re: how to do this query msi77 <msi77@yandex.ru>
0 siblings, 2 replies; 9+ messages in thread
From: Wes James @ 2010-01-09 04:26 UTC (permalink / raw)
To: pgsql-sql
I have two tables:
students
stu_name
schols_selected
scholarships
schol_name
short_name
schols_selected is made up of scholarships the students have selected,
the field content will look like schol1:schol2:schol3
I need a select that does something like this
select schol_name, short_name, stu_name, schols_selected
from scholarships, students
where short_name is in schols_selected
I have tried:
where short_name like '%' || schols_selected || '%'
but i'm not sure how to include a field result in between like operators.
how do I get a list of scholarships based one which student selected
it where the selected result is a string of colon seperated options?
thx,
-wj
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: how to do this query
2010-01-09 04:26 how to do this query Wes James <comptekki@gmail.com>
@ 2010-01-09 09:07 ` Ian Barwick <barwick@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ian Barwick @ 2010-01-09 09:07 UTC (permalink / raw)
To: Wes James <comptekki@gmail.com>; +Cc: pgsql-sql
2010/1/9 Wes James <comptekki@gmail.com>:
> I have two tables:
>
> students
> stu_name
> schols_selected
>
> scholarships
> schol_name
> short_name
>
> schols_selected is made up of scholarships the students have selected,
> the field content will look like schol1:schol2:schol3
>
> I need a select that does something like this
>
> select schol_name, short_name, stu_name, schols_selected
> from scholarships, students
> where short_name is in schols_selected
>
> I have tried:
>
> where short_name like '%' || schols_selected || '%'
>
> but i'm not sure how to include a field result in between like operators.
Theoretically this construction is correct, but as the LIKE pattern is
probably longer than the contents of the field you are operating on,
it won't work in the way you intend it to.
> how do I get a list of scholarships based one which student selected
> it where the selected result is a string of colon seperated options?
I'm not quite sure what you mean here, but in order to be able to
match scholarships selected to an individual student given your table
structure, you could try turning the fields round, e.g.
WHERE schols_selected LIKE '%' || short_name || '%'
However this is a lousy way of doing things and will cause performance
problems with a non-trivial amount of data. (An additional table is
what would would help you here).
HTH
Ian Barwick
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: how to do this query
2010-01-09 04:26 how to do this query Wes James <comptekki@gmail.com>
@ 2010-01-09 11:36 ` msi77 <msi77@yandex.ru>
2010-01-09 22:52 ` Re: how to do this query msi77 <msi77@yandex.ru>
1 sibling, 1 reply; 9+ messages in thread
From: msi77 @ 2010-01-09 11:36 UTC (permalink / raw)
To: Wes James <comptekki@gmail.com>; pgsql-sql
How about
schols_selected like '%' || short_name || '%'
> I have two tables:
> students
> stu_name
> schols_selected
> scholarships
> schol_name
> short_name
> schols_selected is made up of scholarships the students have selected,
> the field content will look like schol1:schol2:schol3
> I need a select that does something like this
> select schol_name, short_name, stu_name, schols_selected
> from scholarships, students
> where short_name is in schols_selected
> I have tried:
> where short_name like '%' || schols_selected || '%'
> but i'm not sure how to include a field result in between like operators.
> how do I get a list of scholarships based one which student selected
> it where the selected result is a string of colon seperated options?
> thx,
> -wj
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Здесь спама нет http://mail.yandex.ru/nospam/sign
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: how to do this query
2010-01-09 04:26 how to do this query Wes James <comptekki@gmail.com>
2010-01-09 11:36 ` Re: how to do this query msi77 <msi77@yandex.ru>
@ 2010-01-09 22:52 ` msi77 <msi77@yandex.ru>
0 siblings, 0 replies; 9+ messages in thread
From: msi77 @ 2010-01-09 22:52 UTC (permalink / raw)
To: Wes James <comptekki@gmail.com>; pgsql-sql
> schols_seleced like '%' || short_name || '%' doesn't make sense to me
select 'Ya-Ya' from
(select
'schol1:schol2:schol3' as schols_selects
) AS X
where schols_selects like '%schol2%'
> If you look at the contents of these fields short name containts
> "schol1", schols_selects containts "schol1:schol2:schol3" so
> schols_seleced like '%' || short_name || '%' doesn't make sense to me
> since the like operator is trying to find text1 in text two with %%
> right?
> 2010/1/9 msi77 <msi77@yandex.ru>:
> > Развернуть
> > How about
> >
> > schols_selected like '%' || short_name || '%'
> > > Развернуть
> > > I have two tables:
> > > students
> > > stu_name
> > > schols_selected
> > > scholarships
> > > schol_name
> > > short_name
> > > schols_selected is made up of scholarships the students have selected,
> > > the field content will look like schol1:schol2:schol3
> > > I need a select that does something like this
> > > select schol_name, short_name, stu_name, schols_selected
> > > from scholarships, students
> > > where short_name is in schols_selected
> > > I have tried:
> > > where short_name like '%' || schols_selected || '%'
> > > but i'm not sure how to include a field result in between like operators.
> > > how do I get a list of scholarships based one which student selected
> > > it where the selected result is a string of colon seperated options?
> > > thx,
> > > -wj
> > > --
> > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-sql
> > Здесь спама нет http://mail.yandex.ru/nospam/sign
>
Здесь спама нет http://mail.yandex.ru/nospam/sign
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2010-01-09 22:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-20 16:00 how to do this query? ww zz <ts_999@yahoo.com>
2003-05-20 16:36 ` Richard Huxton <dev@archonet.com>
2003-05-20 16:46 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-20 17:19 ` ww zz <ts_999@yahoo.com>
2003-05-20 17:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2010-01-09 04:26 how to do this query Wes James <comptekki@gmail.com>
2010-01-09 09:07 ` Re: how to do this query Ian Barwick <barwick@gmail.com>
2010-01-09 11:36 ` Re: how to do this query msi77 <msi77@yandex.ru>
2010-01-09 22:52 ` Re: how to do this query msi77 <msi77@yandex.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox