March 7, 2006 at 12:54 pm
I am setting up a query that accesses multiple tables. In the primary table, most of the data is stored as keys to the other tables (ie. instead of storing the text I store a key to that text in the other table). Is there an easy way to pull back all the text than creating a really complex select/from statement? What is the best practice?
March 7, 2006 at 1:03 pm
Not sure how complex it will be, but yes a select is the best way to go. If it's something you do a lot, create a view and select from the view.
March 8, 2006 at 4:39 am
I am pulling data from nine different tables. It makes the select statement very cumbersome. If I want to use a field in the WHERE clause, it must be included in the SELECT portion of the statement, correct? Is it possible to return only a portion of the fields used in the select statement?
March 8, 2006 at 7:13 am
Hi Mark,
no, WHERE clause can contain any column from the respective tables, not only those that are returned.
If you need to issue several different select statements, always using data from the same 9 tables but returning different columns, create a VIEW that will return all columns that you need in at least 1 query. Then, instead of writing the long and complicated SQL joining 9 tables again and again, just use SELECT <column list> FROM <view_name> WHERE <condition>. All the joins and conditions from view definition are applied automatically, so be careful what you include in the view definition... conditions that apply only sometimes must be left out and and added only when selecting from the view.
BTW, 9 tables is a lot, but it isn't too bad. I've seen more complicated selects quite often.
March 8, 2006 at 11:24 am
I've got it working. Thanks for all the help. It's appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply