Re: The most elegant/efficient way to pull this SQL query
2012/04/11 17:51 -0500, Peter Brawley
select b.peopleID, concat('(',p.fname,,')'), b.stateID, concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;
Since the names are the same in the tables, it works to use USING, too, and
you are relieved of the burden of an alias:
from bridge
join people USING(peopleID)
join state USING(stateID)
If the fields peopleId and stateID are the only field names in common,
NATURAL JOIN also works.
from bridge
NATURAL join people
NATURAL join state
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
My initial goal was to write a very convenient php function that display a
table view based on arguments that are super simple to write - without
requiring the developer to type-in ( or know ) the ins and outs of joins,
natural joins etc.
Something like this
function showtable($dbh,$table,$fields){
//get the $fields argument parse it out to come up
//with one of the SQL statements you guys are discussing.
SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}
The magic would be happening in the $fields argument.
The example in my original question was like this.
I'm repeating it for convenience purposes.
quote
I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs
peopleID___stateID
1__1
2__4
3__5
people table is like this;
___peopleID_FName_
___1joe
___2bob___
___3charlie_
and finally the state table goes like this;
___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__
What's the most straightforward way to achieve the following view with one
SQL statement?
peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
/quote
if the final table ( PeopleAndStates ) view I want were to be as follows;
peopleID__stateID_
1_1___
2_4___
3_5___
Then I would have called the function like this;
showtable($dbh,$myTable,peopleID,stateID)
But if I want to get, the following view instead;
peopleID__stateID_
1_(joe)___1___
2_(bob)___4___
3_(charlie)___5___
I would like to be able to call my function as follows;
showtable($dbh,$PeopleAndStates,peopleID(PeopleTable.PeopleID
FName),stateID)
To mean the following;
When you are outputting the peopleID, provide the corresponding Fname
field from the PeopleTable where peopleID there is equal to the peopleID
you are outputting.
What I was seeking from you guys was to find out to most simplistic SQL
statement so that when I parse the area with (PeopleTable.PeopleID
FName), I can extract the pieces and place it in the final SQL.
I'm not sure if you all get the idea of how such a function make debugging
super easy.
Once you write the parser, you can deploy it over many different cases such
as
ShowRecord($dbh,$table,$fields,where peopleID5,limit 100) and so on.
So, the simpler the SQL, the easier the transition from the starting slate
which is really no different than
SELECT peopleID(PeopleTable.PeopleID FName),stateID from PeopleAndStates
2012/4/12 Halász Sándor h...@tbbs.net
2012/04/11 17:51 -0500, Peter Brawley
select b.peopleID, concat('(',p.fname,,')'), b.stateID,
concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;
Since the names are the same in the tables, it works to use USING, too,
and you are relieved of the burden of an alias:
from bridge
join people USING(peopleID)
join state USING(stateID)
If the fields peopleId and stateID are the only field names in common,
NATURAL JOIN also works.
from bridge
NATURAL join people
NATURAL join state
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
; 2012/04/12 11:56 -0700, Haluk Karamete
My initial goal was to write a very convenient php function that display a
table view based on arguments that are super simple to write - without
requiring the developer to type-in ( or know ) the ins and outs of joins,
natural joins etc.
Something like this
function showtable($dbh,$table,$fields){
//get the $fields argument parse it out to come up
//with one of the SQL statements you guys are discussing.
SQL = BuildSQL($table,$fields)
//and then use this SQL to output the table
}
The magic would be happening in the $fields argument.
What's the most straightforward way to achieve the following view with one SQL
statement?
peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
/quote
if the final table ( PeopleAndStates ) view I want were to be as follows;
peopleID__stateID_
1_1___
2_4___
3_5___
Then I would have called the function like this;
showtable($dbh,$myTable,peopleID,stateID)
But if I want to get, the following view instead;
peopleID__stateID_
1_(joe)___1___
2_(bob)___4___
3_(charlie)___5___
I would like to be able to call my function as follows;
showtable($dbh,$PeopleAndStates,peopleID(PeopleTable.PeopleID
FName),stateID)
To mean the following;
When you are outputting the peopleID, provide the corresponding Fname field
from the PeopleTable where peopleID there is equal to the peopleID you are
outputting.
What I was seeking from you guys was to find out to most simplistic SQL
statement so that when I parse the area with (PeopleTable.PeopleID FName), I
can extract the pieces and place it in the final SQL.
I'm not sure if you all get the idea of how such a function make debugging
super easy.
Once you write the parser, you can deploy it over many different cases such as
ShowRecord($dbh,$table,$fields,where peopleID5,limit 100) and so on.
So, the simpler the SQL, the easier the transition from the starting slate
which is really no different than
SELECT peopleID(PeopleTable.PeopleID FName),stateID from PeopleAndStates
(note that in MySQL '' is only right-shift.)
I fear that for this function in the end you will need
information_schema.COLUMNS.
Peter Brawley already gave you a good answer for one of your examples (but I
thus would write it):
select concat(peopleID, ' (',fname,')') AS peopleID, concat(stateID, '
(',state,')') AS stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)
The middle example:
select peopleID, stateID from PeopleAndStates
The last:
select concat(peopleID, ' (',fname,')') AS peopleID, stateID
from people
join PeopleAndStates USING(peopleID)
join state USING(stateID)
I have assumed that you mean to join only on same-named fields with equality;
if not, JOIN ... ON ... is needed. In any case, you have to keep track of it,
whether in a result field the table name, too, is needed. If you use USING, for
that field leave the table name out.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
On 4/11/2012 1:30 PM, Haluk Karamete wrote:
I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs
peopleID___stateID
1__1
2__4
3__5
people table is like this;
___peopleID_FName_
___1joe
___2bob___
___3charlie_
and finally the state table goes like this;
___stateID_State___
___1___california
___2___new york
___3___washington__
___4___texas___
___5___florida__
What's the most straightforward way to achieve the following view with one
SQL statement?
peopleID__stateID_
1_(joe)___1__(california)_
2_(bob)___4__(texas)__
3_(charlie)___5__(florida)
select b.peopleID, concat('(',p.fname,,')'), b.stateID,
concat('(',s.state,')')
from bridge b
join people p on b.peopleID=p.peopleID
join state s on b.stateID=s.stateID;
PB
-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: The most elegant/efficient way to pull this SQL query
; 2012/04/11 11:30 -0700, Haluk Karamete I've got this relational mySQL table that ties peopleIDs from the people table to the states IDs peopleID___stateID 1__1 2__4 3__5 people table is like this; ___peopleID_FName_ ___1joe ___2bob___ ___3charlie_ and finally the state table goes like this; ___stateID_State___ ___1___california ___2___new york ___3___washington__ ___4___texas___ ___5___florida__ What's the most straightforward way to achieve the following view with one SQL statement? peopleID__stateID_ 1_(joe)___1__(california)_ 2_(bob)___4__(texas)__ 3_(charlie)___5__(florida) Look at Stephen Tu s original post under the subject forcing mysql to use batched key access (BKA) optimization for joins. That his query solves a problem very much like yours--but use explicit JOINing, not implicit. He also uses an unhappy style of making every field name in the database unique. Joining is easier if the fields to be joined on have same names. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
