Thread: how to do this query?
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
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
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 likeselect * from sample left outer join make using (sid) left outer join measure using (sid) left outer join result using (rid)where sid=<value>;
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
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.