how to include a record that "isn't there"

sfullman

Hi There, I'll be as brief as possible.

I have a table called packages. You put together packages to create a bundle. For a bundle, you can have package A be the main package and package B and C be sub-packages. Similarly, package B can have package A and D as sub packages.

So, any package can be a sub-package of another, to comprise a bundle of services. E.g. I might buy web hosting but get dial-up as part of the deal. I might order a T1 but add web hosting for a special promotional bundle.

OK, so of course I have a table called bundles with basically two foreign key fields:

Parent_Package_ID      Package_ID
1                             2
1                             3
1                             4
1                             5
5                             1
5                             3
5                             7

So, we have package 1 with 2,3,4, and 5. We also have package 5 with 1,3, and 7. So far so good right?

OK, BUT I need to run a query which will INCLUDE the parent package (either 1 or 5) in the recordset, because I need the information about that package in the same column?

Any suggestions on this query? I think it might involve a left join as well.

Incidentally I could always add 1-1 and 5-5 (another record for the main package in bundles) but that seems unelegant.

Sincerely,
Sam Fullman

drawmack

What you should do is use three tables

services - holds all the information about the service
bundles - holds all the information about the bundles including the parent package
adtlServices - links all the sub-services to the bundles.

barand

I think a UNION may be what you need

SELECT package.id, package.name FROM package WHERE
package_id = $id
UNION
SELECT package.id, package.name FROM package
INNER JOIN bundle ON package.id = bundle.package_id
WHERE bundle.parent_package_id = $id

If your db doesn't support unions, create a temp file and INSERT output from the 2 parts to it, using

INSERT INTO temp SELECT ....

hth