New topic: SQL - determine Table name from multi-table SQL Statement
<http://forums.realsoftware.com/viewtopic.php?t=47685> Page 1 of 1 [ 11 posts ] Previous topic | Next topic Author Message superjacent Post subject: SQL - determine Table name from multi-table SQL StatementPosted: Tue Apr 23, 2013 11:30 pm Joined: Sat Oct 01, 2005 4:47 am Posts: 107 Location: Melbourne, Australia Here's an SQL statement (I've split and spaced for readability): SELECT tbl_Contacts.Contact_ID, tbl_Contacts.Last_Name, tbl_Contacts.First_Name, tbl_Contacts.Middle_Name, tbl_Contacts.DateOfBirth, tbl_Contacts.Gender, tbl_Contacts.Starting_Balance, tbl_Contacts.IsCriminal, tbl_Contacts.Details, tbl_Contacts.Colour_ID, tbl_Colours.Colour_ID, tbl_Colours.Colour_Name, tbl_Colours.Colour_Area_ID FROM (tbl_Contacts INNER JOIN tbl_Colours ON tbl_Contacts.Colour_ID = tbl_Colours.Colour_ID) WHERE tbl_Contacts.Colour_ID = 1 AND tbl_Contacts.Contact_ID = 5; When I extract the field names via a Recordset.IdxField(i).name loop, the field names are as follows: Field 1 : Contact_ID Field 2 : Last_Name Field 3 : First_Name Field 4 : Middle_Name Field 5 : DateOfBirth Field 6 : Gender Field 7 : Starting_Balance Field 8 : IsCriminal Field 9 : Details Field 10 : Colour_ID Field 11 : Colour_ID Field 12 : Colour_Name Field 13 : Colour_Area_ID As you can see, fields 10 & 11 have the same field name "Colour_ID". Is there any way to retrieve the table name from the above SQL statement for a particular field? _________________ Steve rs2012 r2.1 Windows 7. Top DaveS Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Wed Apr 24, 2013 12:12 am Joined: Sun Aug 05, 2007 10:46 am Posts: 4807 Location: San Diego, CA I'm amazed that the SQL did not fail with an "ambiguous column name" error. But that not withstanding.... There are two ways.... One I consider the "right" way and the other is "another way" The right way is to rename the second occurance. This removes the possibilty of the above error, and gives proper access to both occurrances (assuming you really even need to keep them both [doesn't the INNER JOIN link equal values???]... tbl_Colours.Colour_ID as Colour_ID2 The "other way" is to reference the field by index instead of by name rs.field(11) but I'm betting you could just leave it out of your select statement and not have to worry about it... just keep ONE _________________ Dave Sisemore iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top superjacent Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Wed Apr 24, 2013 1:28 am Joined: Sat Oct 01, 2005 4:47 am Posts: 107 Location: Melbourne, Australia Thanks Dave. Yes, the query works as is and I believe it does as the table name is explicitly used. I thought by explicitly including the table name that I could also retrieve that table name later. In the example provided above, yes, the data would be the same as it's the linking field but still I couldn't extract the correct table for given field name. This may be a problem for me when dealing with same field names across multiple tables that are not linked or related (ie. notes, details, audit time stamp etc. that type of data). At present I'm in the process of writing a class with functions that handles all the SQL creation routines and related stuff. At the window level I merely pass in the relevant table(s), Keyfield(s), Join data, Where etc. On the window, relevant controls (sub-classed TextField etc.) have properties (Table name, Field name) which are the controls to receive the recordset data. My idea is to simply cycle through these controls, gather the relevant data from the recordset (from the class), the matching data being Table & Field names. [Thinking Aloud] I might have to reconsider my approach but I didn't want to hardcode SQL statements at the window level, though having said that, if controls have to be created for the window anyway, that's a form of hardcoding, if that makes sense. I might look at the SQL creation routine again. If using an alias (which I was trying to avoid) I'm thinking that the field alias name could be the field name prepended with an "n_" . Left/First table = 1_, Second table = 2_ etc. [/Thinking Aloud] _________________ Steve rs2012 r2.1 Windows 7. Last edited by superjacent on Wed Apr 24, 2013 7:59 am, edited 1 time in total. Top Jason_Adams Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Wed Apr 24, 2013 7:46 am Joined: Fri Nov 10, 2006 4:10 pm Posts: 1794 Location: Michigan, USA Greetings! I'm not sure how useful this may or may not be, but I go about this a bit differently: (I'll refer the CRUD (Create Read Update Delete) model.) I have two class types when referring to databases: tables and views. I use Tables for Creating, Updating, and Deleting. Reading, on the other hand, I use views. Queries are definitely the most used aspect of databases, and are almost always more complex than "Select * from mytable". Views are a great way to contain complex queries, give another layer for versioning, and adjust names to avoid conflicts. (The versioning benefit comes about that if you change the table, you can keep the view and adjust it to support old queries from the new table structure.) So in your situation, I would just create a view that fits exactly what I'm looking for, and use that for your custom controls. Hope this helps! _________________ Windows 7 Ultimate x64 Windows XP Pro SP3 Ubuntu 11.04 via Virtual Box RS Enterprise 2012r1.1 Programming Tutorials & Free Projects: http://www.JasonTheAdams.com "Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton Top superjacent Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Wed Apr 24, 2013 10:10 pm Joined: Sat Oct 01, 2005 4:47 am Posts: 107 Location: Melbourne, Australia Thanks Jason, your suggestions make sense (it's the same approach I took for an Access database that I'm trying to convert the front-end to RS ---- Xojo). Having said that, as I review the Access code, the code is very repetitive (written many years ago) and I cringe a little knowing how much more efficient and generic the code could be. I'm not prepared to spend the time improving the access code , I'd rather start afresh with RS. ("if it ain't broke don't fix it" - that pops into my head) I'm at the point of fine-tuning the SQL (viewing) process. There are a heap of windows and I am attempting not to specifically create the SQL field list at the window level. At the window level I pass in the table names and only specifically reference keyfields, JOIN, ORDER, WHERE components etc. Knowing the table name the extraction of the field names are possible and therefore I thought superfluous if specifically passed in at the window level. So, if I can't clearly delineate a field from a particular multi-table SQL statement I think I might have to modify my approach and simply - hardcode - the fields required at the window level and also pass them into the Class SQL creation functions. The editing, adding of data is a piece of cake, in my Access database it is not possible to directly edit/add from the viewing screen, click a button and open up a specific form (window) in modal mode - do the edit/add and return to the calling form. I will be following this same approach. If I've waffled on, I apologise. I've sort of come to a halt as I'm undecided on what approach to take regarding the SQL creation process - regarding the fields - hardcode it at the window level or not. _________________ Steve rs2012 r2.1 Windows 7. Top timhare Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Thu Apr 25, 2013 12:33 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12262 Location: Portland, OR USA Since you're creating the sql field list in code, it doesn't matter how verbose it gets, so concatenate the table and field names for the alias, rather than use a number. That way, you can do the same when you loop through the controls to populate them. select tbl_Contacts.Contact_ID as tbl_Contacts_Contact_ID, ... Top superjacent Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Thu Apr 25, 2013 1:10 am Joined: Sat Oct 01, 2005 4:47 am Posts: 107 Location: Melbourne, Australia timhare wrote:Since you're creating the sql field list in code, it doesn't matter how verbose it gets, so concatenate the table and field names for the alias, rather than use a number. That way, you can do the same when you loop through the controls to populate them. select tbl_Contacts.Contact_ID as tbl_Contacts_Contact_ID, ... Thanks Tim. This makes sense and achieves what I want to achieve without too much extra effort required. This approach is what I'm leaning towards. I'm assuming then that it is in fact impossible to directly extract the table name of a field from a multi-table SQL statement. <Thinking Aloud Again> >From a theoretical point of view I suppose it is possible to extract the table >name of a given field from a multi-table SQL statement but requires a number >of steps. I noticed that the returned field names are in order of the original >SQL field list and therefore a string array could be maintained where each >element would be the table name of the corresponding field. Therefore when >scanning controls it's a matter of checking against the array to determine the >correct associated table name. Knowing this, the correct Field index number of >the recordset is used to retrieve and populate the control. </Think Aloud Again> _________________ Steve rs2012 r2.1 Windows 7. Top timhare Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Thu Apr 25, 2013 2:13 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 12262 Location: Portland, OR USA superjacent wrote:I'm assuming then that it is in fact impossible to directly extract the table name of a field from a multi-table SQL statement. Correct. If you run the sql in a query browser, you'll notice that the columns shown do not have table names either. Quote:<Thinking Aloud Again> >From a theoretical point of view I suppose it is possible to extract the table >name of a given field from a multi-table SQL statement but requires a number >of steps. I noticed that the returned field names are in order of the original >SQL field list and therefore a string array could be maintained where each >element would be the table name of the corresponding field. Therefore when >scanning controls it's a matter of checking against the array to determine the >correct associated table name. Knowing this, the correct Field index number of >the recordset is used to retrieve and populate the control. </Think Aloud Again> Yes, the columns are returned in the same order as the fields listed in the sql. So assuming that you traverse the controls in the same order, you could use idxfield to retrieve the value. I personally would use the alias as there's no possibility of error. Top kermit Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Thu Apr 25, 2013 2:39 am Joined: Mon May 30, 2011 12:56 am Posts: 673 In practice, I find that in these cases, Access tends to noble the field name in the resulting output. You might get one of them as Expr1, or you might find the file names are actually tbl_Contacts_Colour_ID and tbl_Colours_Colour_ID BUT: Since you are joining the tables on Color_ID, they by definition have to both be the same value. tbl_Contacts.Colour_ID = tbl_Colours.Colour_ID Therefore in this case, you dont even need to select both columns. Omit one of them and all ambiguity goes away. Top superjacent Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Thu Apr 25, 2013 4:06 am Joined: Sat Oct 01, 2005 4:47 am Posts: 107 Location: Melbourne, Australia kermit wrote:Since you are joining the tables on Color_ID, they by definition have to both be the same value. tbl_Contacts.Colour_ID = tbl_Colours.Colour_ID Already addressed. superjacent wrote:In the example provided above, yes, the data would be the same as it's the linking field but still I couldn't extract the correct table for given field name. This may be a problem for me when dealing with same field names across multiple tables that are not linked or related (ie. notes, details, audit time stamp etc. that type of data). kermit wrote:Therefore in this case, you dont even need to select both columns. Omit one of them and all ambiguity goes away. You are right regarding the ambiguity but I think you're missing my point. I don't want to or preferring not to, for every window, hardcode the field list; knowing the tables required should be sufficient. Yes, if hardcoding the field list then only the absolute fields required for the window would be included. If generically coding the field list some windows will not display all the fields, I can live with that. _________________ Steve rs2012 r2.1 Windows 7. Top superjacent Post subject: Re: SQL - determine Table name from multi-table SQL StatemenPosted: Thu Apr 25, 2013 6:59 am Joined: Sat Oct 01, 2005 4:47 am Posts: 107 Location: Melbourne, Australia timhare wrote:Since you're creating the sql field list in code, it doesn't matter how verbose it gets, so concatenate the table and field names for the alias, rather than use a number. That way, you can do the same when you loop through the controls to populate them. select tbl_Contacts.Contact_ID as tbl_Contacts_Contact_ID, ... I have now coded for this and currently testing. The re-code only took a couple of minutes, only had to add to an existing line the string manipulation for the 'AS' clause. Throughout testing it became obvious that only alpha-numeric characters were allowed. Couldn't use a dot notation, or ! or $ etc. In the end had to settle on 3 underscores as I extensively use single underscores in table and field names and could foresee future searches being problematic (which underscore is the delimiter). I definitely do not use 2 or more underscores together, so 3 of them should suffice. _________________ Steve rs2012 r2.1 Windows 7. Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 11 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
