List:General Discussion« Previous MessageNext Message »
From:Haluk Karamete Date:April 12 2012 6:56pm
Subject:Re: The most elegant/efficient way to pull this SQL query
View as plain text  
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_____
___1____________joe________
___2____________bob_______
___3____________charlie_____


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 peopleID<5","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 <hsv@stripped>

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

Thread
The most elegant/efficient way to pull this SQL queryHaluk Karamete11 Apr
  • Re: The most elegant/efficient way to pull this SQL queryPeter Brawley11 Apr
    • Re: The most elegant/efficient way to pull this SQL queryhsv12 Apr
      • Re: The most elegant/efficient way to pull this SQL queryHaluk Karamete12 Apr
        • Re: The most elegant/efficient way to pull this SQL queryhsv13 Apr
  • Re: The most elegant/efficient way to pull this SQL queryhsv12 Apr