Real Software Forums
http://forums.realsoftware.com/

SQL - determine Table name from multi-table SQL Statement
http://forums.realsoftware.com/viewtopic.php?f=3&t=47685
Page 1 of 1

Author:  superjacent [ Tue Apr 23, 2013 11:30 pm ]
Post subject:  SQL - determine Table name from multi-table SQL Statement

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?

Author:  DaveS [ Wed Apr 24, 2013 12:12 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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

Author:  superjacent [ Wed Apr 24, 2013 1:28 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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]

Author:  Jason_Adams [ Wed Apr 24, 2013 7:46 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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! :)

Author:  superjacent [ Wed Apr 24, 2013 10:10 pm ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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.

Author:  timhare [ Thu Apr 25, 2013 12:33 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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, ...

Author:  superjacent [ Thu Apr 25, 2013 1:10 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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>

Author:  timhare [ Thu Apr 25, 2013 2:13 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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.

Author:  kermit [ Thu Apr 25, 2013 2:39 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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.

Author:  superjacent [ Thu Apr 25, 2013 4:06 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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.

Author:  superjacent [ Thu Apr 25, 2013 6:59 am ]
Post subject:  Re: SQL - determine Table name from multi-table SQL Statemen

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.

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/