DylanBlitz Posted October 1, 2003 Share Posted October 1, 2003 okay, here is my problem. I\'m building a custom shopping cart for online CD sales. These CD\'s are full CD\'s, split CD\'s and compilation CD\'s. Getting the full and comp CD\'s to list is easy but the split ones are giving me trouble. Maybe it\'s my db layout? Here is how my tables are layed out. Albums ----- album_id album_name album_band1 (contains a band_id from the bands table) album_band2 (contains a band_id from the bands table) album_type Bands ---- band_id band_name Type ---- type_id type_name now my problem is when I do a select on all bands that start with the letters A-D I only get full CD\'s or split CD\'s where the first band listed starts with A-D [php:1:4e968af46f] $query = \"SELECT album.album_id, album.album_name, album.album_qty, album_price, album_euprice, bands.band_name FROM album INNER JOIN bands ON album.album_band1 = bands.band_id WHERE ((bands.band_name LIKE \'A%\') || (bands.band_name LIKE \'B%\') || (bands.band_name LIKE \'C%\') || (bands.band_name LIKE \'D%\')) ORDER BY bands.band_name LIMIT $from, $max_results\"; [/php:1:4e968af46f] Is there a way I can join a table twice? I don\'t know any other way to check the names for the album_band2 id Quote Link to comment https://forums.phpfreaks.com/topic/1093-query-question/ Share on other sites More sharing options...
Barand Posted October 2, 2003 Share Posted October 2, 2003 $query = \"SELECT album.album_id, album.album_name, album.album_qty, album_price, album_euprice, A.band_name, B.band_name FROM (album INNER JOIN bands A ON album.album_band1 = A.band_id ) INNER JOIN bands B ON album.album_band2 = B.band_id WHERE (LEFT (A.band_name,1) IN (\'A\',\'B\',\'C\',\'D\') ) || (LEFT (B.band_name,1) IN (\'A\',\'B\',\'C\',\'D\') ) ORDER BY A.band_name LIMIT $from, $max_results\"; Of course it might be easier to design it with the bands in a child table rather than having the 2 repeated fields. Quote Link to comment https://forums.phpfreaks.com/topic/1093-query-question/#findComment-3743 Share on other sites More sharing options...
DylanBlitz Posted October 3, 2003 Author Share Posted October 3, 2003 thanks for the reply barand the query kind of works heh. It only will work if there is a band id in both fields, wont work if there is just one band. What did you mean by designing it with the bands in a child table? Quote Link to comment https://forums.phpfreaks.com/topic/1093-query-question/#findComment-3754 Share on other sites More sharing options...
Barand Posted October 3, 2003 Share Posted October 3, 2003 Like this : Albums ----- album_id album_name album_type Album_bands --------- album_id band_no album_band (contains a band_id from the bands table) Bands ---- band_id band_name Type ---- type_id type_name Quote Link to comment https://forums.phpfreaks.com/topic/1093-query-question/#findComment-3767 Share on other sites More sharing options...
Barand Posted October 3, 2003 Share Posted October 3, 2003 IF you change the INNER JOINS to LEFT JOINS, that should improve things if only one band. Quote Link to comment https://forums.phpfreaks.com/topic/1093-query-question/#findComment-3769 Share on other sites More sharing options...
DylanBlitz Posted October 3, 2003 Author Share Posted October 3, 2003 ah, I gotcha, that\'s a good idea, think I\'ll give that a go thanks Quote Link to comment https://forums.phpfreaks.com/topic/1093-query-question/#findComment-3770 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.