Jump to content


Photo

Query question


  • Please log in to reply
5 replies to this topic

#1 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 01 October 2003 - 10:21 PM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,020 posts

Posted 02 October 2003 - 06:31 PM

$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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 03 October 2003 - 03:50 AM

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?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,020 posts

Posted 03 October 2003 - 02:57 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,020 posts

Posted 03 October 2003 - 03:02 PM

IF you change the INNER JOINS to LEFT JOINS, that should improve things if only one band.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 DylanBlitz

DylanBlitz
  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 03 October 2003 - 03:45 PM

ah, I gotcha, that\'s a good idea, think I\'ll give that a go

thanks :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users