[SOLVED] Link 1 table to 2 other tables with the same key?

[scooter41]

Hi There,

 

I am trying to link 1 table of video data, to different tables of girls data.

 

So I have for example in the video table:

 

videoID

videoFile

girlLink (this being the same ID)

 

Then 2 alternative girls tables (theGirls, and webJems) with a similar structure, the difference being one table the girlID's are like 10045,10046 and the other is 50045,50046 etc:

 

girlID

girlName

girlDetals

......

 

I am trying to view all the videos linking to each respective table but not having much luck. I am trying to do the following:

 

SELECT * from theVideos,theGirls, webGems where theVideos.girlLink=theGirls.girlID or theVideos.girlLink=webGems.girlID

 

But its not clever enough to determine which are form which table.

 

Is there anyway of doing an if statement (so if the ID is under 50000 use this table, else use that table)

 

Any help greatley appreciated!

 

[kickstart]

Hi

 

Try this.

 

SELECT *

FROM theVideos a

LEFT OUTER JOIN theGirls b ON a.girLink = b.girlID

LEFT OUTER JOIN webGems c ON a.girLink = c.girlID

 

All the best

 

Keith

[scooter41]

Thanks for your reply!

 

I am using the following based on your example, which does return all the databack, the only problem is that there are 2 columns with the girlName field from each table, so I am having troubles trying to address both of them! 

 

I guess I could assign one as girlName1 and one as girlName2, if one doesnt exist use the other?

 

SELECT *

FROM theVideos

LEFT OUTER JOIN theGirls ON theVideos.girlLink = theGirls.girlID

LEFT OUTER JOIN webJems  ON theVideos.girlLink = webJems.girlID

[kickstart]

Hi

 

I would just alias them (generally avoid using SELECT * for real code, just done for quickness in an example).

 

SELECT videoID, videoFile, girlLink, a.girlID AS GirlsGirlID, a.girlName AS GirlsGirlName, a.girlDetals AS GirlsGirlDetails, b.girlID AS GemsGirlID, b.girlName AS GemsGirlName, b.girlDetals AS GemsGirlDetails

FROM theVideos a

LEFT OUTER JOIN theGirls b ON a.girLink = b.girlID

LEFT OUTER JOIN webGems c ON a.girLink = c.girlID

 

All the best

 

Keith

[scooter41]

perfect thanks!