poe Posted March 8, 2007 Share Posted March 8, 2007 i am trying to build my own banner tracking db. i have 5 tables: #1 = user id | name 1 | chris 2 | mike #2 = banners id | url | filename | user_id | zone_id 1 | sony.com | style.jpg | 1 | 1 2 | nike.com | swoosh.jpg | 1 | 2 3 | mazda.com | zoomzoom.jpg | 2 | 1 #3 = zones id | description 1 | top of page 2 | bottom of page #4 = priceplan id | cost | zone_id | time 1 | 25.00 | 1 | 1mth 2 | 50.00 | 1 | 3mths 3 | 150.00 | 1 | 1yr 4 | 15.00 | 2 | 1mth 5 | 100.00 | 2 | 1yr #5 = sales id | zone_id | user_id | startmth | duration(mths) 1 | 1 | 1 | 5 | 1 2 | 1 | 2 | 7 | 1 3 | 2 | 1 | 5 | 1 when someone buys an ad spot 'sales' table is updated to record 'zone' (top of page ad, or bottom page ad), the user, when to start showing ads, duration(in mths) to run the ad. i am stuck trying to list all sales and their asociated banner, zone info the result i want is: zone:top | user:chris | url:sony.com zone:top | user:mike | url:mazda.com zone:bottom | user:chris | url:nike.com i have : $qx = " SELECT z.id, z.description, s.id, s.zone_id, s.user_id, s.startmth, s.duration, b.id, b.url, b.filename, b.user_id, b.zone_id "; $qx .= " FROM sales=s "; $qx .= " RIGHT JOIN zones=z ON s.zone_id = z.id "; $qx .= " RIGHT JOIN banners=b ON s.user_id = b.user_id "; $qx .= " ORDER BY z.id ASC "; but it isnt working help please. is there something wrong with my table logic, or am i going about this the wrong way Quote Link to comment Share on other sites More sharing options...
artacus Posted March 8, 2007 Share Posted March 8, 2007 but it isnt working Can you be more vague please? Do you get an error, whats the error message? Use "FROM sales AS s" instead of "FROM sales=s" Also, while left join and right join do pretty much the same thing, Left join is pretty much the industry convention. Quote Link to comment Share on other sites More sharing options...
poe Posted March 8, 2007 Author Share Posted March 8, 2007 my query is: $qx = " SELECT z.id, z.description, s.id, s.zone_id, s.user_id, s.startmth, s.duration, b.id, b.url, b.filename, b.user_id, b.zone_id "; $qx .= " FROM sales AS s "; $qx .= " LEFT JOIN zones=z ON s.zone_id = z.id "; $qx .= " LEFT JOIN banners=b ON s.user_id = b.user_id "; $qx .= " ORDER BY z.id ASC "; my result should be 3 array keys, not 5 as it outputs: Array ( [0] => Array ( [id] => 1 [description] => top of page [zone_id] => 1 [user_id] => 1 [startmth] => 5 [duration] => 1 => sony.ca [filename] => walkman.jpg ) [1] => Array ( [id] => 2 [description] => top of page [zone_id] => 2 [user_id] => 1 [startmth] => 5 [duration] => 1 => nike.com [filename] => swoosh.jpg ) [2] => Array ( [id] => 3 [description] => top of page [zone_id] => 1 [user_id] => 2 [startmth] => 7 [duration] => 1 => mazda.com [filename] => zoomzoom.jpg ) [3] => Array ( [id] => 1 [description] => bottom of page [zone_id] => 1 [user_id] => 1 [startmth] => 5 [duration] => 1 => sony.ca [filename] => walkman.jpg ) [4] => Array ( [id] => 2 [description] => bottom of page [zone_id] => 2 [user_id] => 1 [startmth] => 5 [duration] => 1 => nike.com [filename] => swoosh.jpg ) ) Quote Link to comment Share on other sites More sharing options...
artacus Posted March 8, 2007 Share Posted March 8, 2007 Ok, you probably need a group by. If you have 1 row from table 1 and join it to another table in a 1 to many relationship, you'll end up with as many records matched in the second table. So 1 row in table 1 joined w/ 3 rows in table 2 and joined with 4 rows in table 3 will give you 12 rows in your result. Quote Link to comment Share on other sites More sharing options...
poe Posted March 8, 2007 Author Share Posted March 8, 2007 ahhhh! i see, that looks better, thanks Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.