homepage Welcome to WebmasterWorld Guest from 54.226.182.192
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Gaining data from the same table: MYSQL
Think need to do self join?
mn1dbp




msg:1290521
 4:09 pm on Dec 8, 2005 (gmt 0)

I have a problem that im currently trying to work through. I have table (called run_horses) that has columns as follows:

horse_id, sire_id, dam_id, damsire_id

then another table called sires that has:

id, sire_name

both the sire_id and damsire_id correspond to the id from the sires table.

Now i want to display results from the run_horses table to display the sire_name from sires that corresponds to sire_id and damsire_id but as both get this from the sires table and it will be different names for both im not sure how i do this. I beleive it involved something called a self join? But have never used or have any experience of this.

 

storevalley




msg:1290522
 4:26 pm on Dec 8, 2005 (gmt 0)

How about something like this ...

SELECT * FROM run_horses, sires as s, sires as d
WHERE (add your own clause to filter result set)
AND run_horses.sire_id = s.id
AND run_horses.damsire_id = d.id

From the resulting recordset ...

You should then be able to access sire info from alias s (s.id, s.sire_name) and damsire info from alias d (d.id, d.sire_name) ...

arran




msg:1290523
 4:30 pm on Dec 8, 2005 (gmt 0)

As you guessed, you give the same table two different aliases:

select sire.sire_name, damsire.sire_name from sires sire, sires damsire, run_horses where run_horses.sire_id = sire.id and run_horses.damsire_id = damsire.id

arran.

<added>Sorry storevalley, you beat me to it...</added>

mn1dbp




msg:1290524
 4:37 pm on Dec 8, 2005 (gmt 0)

Between posting and coming back to read i did a bit further research and came up with:

select t1.horse_name, t2.sire_name as 'sirename', t3.sire_name as 'damname' from run_horses AS t1, sires AS t2, sires AS t3 where (t1.sire=t2.id and t1.dam_sire=t3.id)

This seems to work fine. Now i need to incorporate it into a bigger query (having the smaller table names will also help here) but i find it helps to build up big queries from smaller ones that i have tested work.

Is there a name for what i have done - e.g. is it a type of join?

storevalley




msg:1290525
 5:00 pm on Dec 8, 2005 (gmt 0)

This is just a natural (inner) join using a couple of aliases as far as I can see :)

Ready to stand corrected of course ...

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved