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.

I've got this relational mySQL table that ties peopleIDs from the people
table to the states IDs


people table is like this;


and finally the state table goes like this;

_______2_______new york____

What's the most straightforward way to achieve the following view with one
SQL statement?



if the final table ( PeopleAndStates ) view I want were to be as follows;


Then I would have called the function like this;


But if I want to get, the following view instead;


I would like to be able to call my function as follows;

showtable($dbh,$PeopleAndStates,"peopleID(PeopleTable.PeopleID >>

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

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:
> To unsubscribe:

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