problem while writing one query.
-
Hi All,
I am facing a one issue while writing one query.First of all please find the tables fields details.
1. TR_TRGCAL - TABLE NAME (Training Calender)
1.1 BATCH_ID - INT
1.2 FAC_CDS - VARCHAR (STORE FACULTY CODES,VALUE MAY BE 295,475 ID MORE
THEN ONE FACUYLY INVLOED ON THE CORESPODING BATCH.)
Means there will be one to many relation between batch and faculty.1 batch may have >1 faculties.
2.TR_FACMST - Table Name(Faculty Master Table)
2.1 FAC_CD - INT
2.2 FAC_NAME - VARCHAR.
I have to write a query which should display
Batch ID,Faculty Code,Faculty Name in a single query.
Now the problem is this FAC_CDS is defined as VARCHAR in the TR_TRGCAL table and may have value like 124,234,456 if more then one faculties involved in the single traing batch.Whereas Faculty master table TR_FACMST containing the FAC_CD as INT.
How do I join this two fields to get the faculty code with thier name for the corespoding Batch.
Thanks in adnavce for the help.
Mitesh
-
Gosh - it makes me queasy just reading about such impossible designs and tasks..
how are new faculty ids added or deleted from the TR_TRGCAL table?!?!
Mitesh - your best bet would be to parse your varchar FAC_CDS column (function/export to csv file and reimport etc..); import the 2 columns BATCH_IDS & FAC_CDS into a temp "transition" table (& the FAC_CDS will be int not varchar) - add BATCH_IDS column to your TR_FACMST table and then update using the transition table.
Then you can use a single query w/out having to join to any other table etc.
**ASCII stupid question, get a stupid ANSI !!!** -
Yes, it would be good to use DDL rather than your idiosyncratic way of outlining the problem. But at the same time, if you are clear on the issue at stake (and you are), posting a simplified version can help other forum users since they don't have to trawl through pages of largely irrelevant code to find the problem. Of course you need to make sure that your simplified version does actually reproduce the core problem!
Sushila and I don't need any DDL to know that you should get those values out of a comma-delimited list and into the rows of one column in a 'batch_faculties' table which has FK to batch_id. This should be a permanent change to your schema. If anyone protests ask them if they have ever heard of 1st normal form. Until you make this change, every query involving batch and faculty will be slow and hugely overcomplicated, while the data itself will be open to corruption - e.g. faculties taht don't exist, letters instead of numbers, the same faculty occurring twice, low maximum number of faculties per batch...
Do I detect a (trivial and inapplicable) version of Wittgenstein's so-called private language argument in Joe Celko's post?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
"Do I detect a (trivial and inapplicable) version of Wittgenstein's so-called private language argument in Joe Celko's post?"...
Not to do Mr.JC a disservice or anything by implying that he didn't have any "arguments" in mind but I think this is a standard template used when the time to berate and chide is short on hand
- but then again since he's a stickler for many things and a hardcore conformist when it comes to certain rules, anything that deviates ever so slightly from HIS norm becomes a "personal language" - for my part I'm devoutly grateful for not having enough gray matter to be able to tell the difference...
**ASCII stupid question, get a stupid ANSI !!!** -
Then we're all agreed. The data should be stored in classical relational form (subject to SQL compromises), for all the (one hopes) obvious reasons. The only (mildish) dispute, certainly from my point of view, was over the format of the original public-language narrative. Must get rid of all those brackets. People will think my posts are written using a query-design GUI.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
"I am trying to figure out why another poster thinks that 20+ years of basic netiquette, 30+ years of RDBMS practices, and 40+ years of ANSI and ISO Standards somehow belong to just me. Or do they just look funny to him because of his inexperience and ignorance?"
hmm..I should probably not belabour the point but since I get the distinct impression that the reference to "another poster" may actually mean me (despite the incorrect gender reference), I feel compelled to explain for what it's worth!
Certainly the dispute (mild to the point of being inconsequential) was about language (public & private if you will)!! When someone says "fields" I understand that they mean "columns" just as I know that "records" means "rows" - just as I've schooled myself to grit my teeth and ignore words like "irregardless" since it's irrelevant, immaterial and impertinent and has nothing to do with the problem posed in the thread.
"Violating 1NF" - sure that's serious enough to launch a diatribe - but the method of instruction leaves much to be desired. Not all of us are fortunate enough to learn at the feet of maestros - many of us are thrown in (in the deep end and head first) and are forced to sink or swim - for the most part many stay (barely) afloat while others go on to do swimmingly (
) well!Of your own admission (in several other posts) you have a reputation to maintain and must therefore contrive to be as pugnacious as possible - but fight someone your own size! If the goal is really to educate the "newbies" and raise the standard, then scaring them away by sending them scuttling into dark & deep corners will not do the trick! A modicum of civility has never killed anyone and a hand extended in help will (always) work better than browbeating and intimidation!
Lastly - I'll readily admit to both inexperience and ignorance - even of basic netiquette - but if you strip the word off its "n" then I may (just) have the upper hand!
ps: No query-design GUIs here either! Have (always) had a weakness for brackets!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply