1

I really need some help with some SQL.

Question 1:

It's easy enough to get all "assets" that belong to a "presentation" if the asset has a corresponding "presentationid" within it's table.

SELECT * FROM asset WHERE presentationid = 3

But how to I accomplish the same thing by joining? What's the best way to say:

SELECT * FROM asset WHERE ... asset is connected to presentation via "presentationasset":

TABLE asset
id
name

TABLE presentation
id
name

TABLE presenationasset
id
presentationid
assetid

I hope this makes sense. I want to list out all of the actual assets and their columns, not the association table. :)

Question 2: (not as important)

I have my application setup so that "presentation" is a class and "asset" is a class...

With question 1 in mind, how do I return each of the associated assets as "asset" objects? Or does that even matter?

hakre
  • 193,403
  • 52
  • 435
  • 836
dcolumbus
  • 9,596
  • 26
  • 100
  • 165
  • If they're so cool, would you mind posting an example using my tables above? – dcolumbus Apr 26 '12 at 02:39
  • Regarding Q2: There is no way to simply convert a database record to an object... That's where the fun stuff is ;). Either use an ORM (http://stackoverflow.com/questions/108699/good-php-orm-library) or you'll have to write the translation layer yourself. – NotMe Apr 26 '12 at 02:46
  • @ChrisLively, thanks for responding. It actually doesn't matter in this case... I just need to be able to get the IDs and such in order to pass that information long for a save. Thanks for shooting over that link. – dcolumbus Apr 26 '12 at 17:47

2 Answers2

2

Q1:

SELECT a.id, a.name
FROM asset a 
JOIN presentationasset pa ON pa.assetid = a.id AND pa.presentationid = 3
JOIN presentation p ON p.id = pa.presentationid
hkf
  • 4,440
  • 1
  • 30
  • 44
1

Fair enough :)

Select * from asset where id in
(select assetid from presentationasset where id in
(select id from presentation where name = "whateva"))

or

Select * from asset where id in
(select assetid from presentationasset where presentationid = 3)
Jeremy
  • 5,365
  • 14
  • 51
  • 80