MySQL joined select

Pete pete at mollysrevenge.com
Tue Mar 20 12:38:01 EDT 2012


Hi Ken,
Good catch on the multiple FROM clauses, not sure what that's all about.
 Probably just needs to qualify the columns in the condition with the table
name.

The SELECT statement has a lot of problems in general.  There's no JOIN
statement to link table1 and table2 together, for example.  That might work
as long as the column(s) to be used to join the tables together have the
same name in both tables but in general, it's much better to explicitly
define the linkage with a JOIN statement - for clarity reasons if no other.

Just one comment on the need to qualify columns with table names.  I
definitely agree it's good practice to do that for clarity but as long as a
column name is unique among all tables, SQL will figure out which table an
unqualified column name belongs to.

Pete

On Tue, Mar 20, 2012 at 8:46 AM, Ken Ray <kray at sonsothunder.com> wrote:

>
> On Mar 20, 2012, at 4:20 AM, Marek Reichenbach wrote:
>
> > Thanks for the replies.
> > I've made it workin, but I wanna add something else:
> >
> > So the question in SQL is:
> >
> > Maybe there is a way to hide columns it the result, like:
> > SELECT car_nr, model, travel_days FROM table1,table2 WHERE number <> 414
> > AND (car_nr > truck_nr) from transport;
> > and show only result of the colums travel_days, but ONLY on*
> > **separate* columns
> > and  HIDE OTHER COLUMNS.
>
> You only include the columns you want back in the SELECT part of the query:
>
> SELECT travel_days FROM …
>
> Also, although I know you provided a dummy query, be aware that there are
> two inherent problems with the one you provided:
>
> 1) If you have multiple tables in the FROM clause, you need to identify
> the table attached to the column in the SELECT clause (although some SQL
> engines might just assume you meant the first table, it's good form to
> identify the table), either by full name:
>
>  SELECT table1.travel_days FROM table1,table2 WHERE…
>
> or by alias:
>
>  SELECT t1.travel_days FROM table1 t1,table2 t2 WHERE…
>
> 2) I'm not sure what "from transport" means since you already have a FROM
> clause in your example query
>
> The bottom line is that you don't have to include columns in the SELECT
> portion of the query that you don't want back, so long as you identify them
> properly and they actually exist in the table you're referencing.
>
> Just be aware that LiveCode's text parsing is second to none, so there are
> usually times where it may actually be easier and more efficient to let SQL
> handle getting the data, but let LiveCode handle parsing the data after it
> arrives. As I mentioned in an earlier post, you *can* use SQL to change
> your numbers to "+" symbols, but it's ugly - Mark's method to post-process
> it in LC with a single statement if far better (IMHO).
>
> Ken Ray
> Sons of Thunder Software, Inc.
> Email: kray at sonsothunder.com
> Web Site: http://www.sonsothunder.com/
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>


-- 
Pete
Molly's Revenge <http://www.mollysrevenge.com>



More information about the use-livecode mailing list