Working with multiple tables

Need to select data based on negative criteria.

[barns101]

Apologies if this is a stupid question. I have searched and posted on another forum but cannot fund a solution.

I'm having problems working with multiple tables. I need to select data from two tables based on the contents of a third table.

The tables are products, patterns and incompatibilities.

The products table has four dinner sets, which can each cost three different amounts, based on which pattern they are decorated in.

The patterns table lists all of the patterns and also which of the three price groups they belong to.

Unfortunately not every pattern will fit every dinner set. For example the Cappa Cottage pattern cannot go on an Amber shape dinner set but can be applied to the other three shaped dinner sets.

So I have created an "incompatibilities" table to list which pattern cannot go with which shape.

For each of the styles, I want to list what patterns it can be decorated in and the price for each one.

So I guess that I'll need a query (one for each style) that selects the pattern names, price group and then displays the corresponding price AS LONG AS the style and pattern are not found together on the incompatibilities table.

I've researched this but I am totally stumped at the moment. I guess this is a fairly simple and common thing to accomplish once you know how. Does anyone have any idea how to do it?

P.S. I tried to post the database schema but couldn't wok out how to display it correctly - all the spacing disappears. :(

[Argblat]

Your question is very hard to answer without at least a little more information.

Why don't you give us the columns for each table like this:

Table_1
-----------
Column 1 - int(4) PK
Column 2 - varchar(50)

Table_2
-----------
etc, etc.....

With the column names, primary keys, and foreign keys, I think you'll get some more people to understand your problem and be able to help you

-Mike

[Dijkgraaf]

Usually you would have a not exists clause in your where statement.

For example the following will have all products names and pattern names as a full outer join

select product.name, pattern.name
from product, patern

if you then add a
where not exists (select 1 from incompatible where incompatible.productid = product.productid and
incompatible.patternid = pattern.patternid)

you will then exclude the ones that don't match.

[barns101]

Thank you for your replies.

The tables are like so:

products
--------
productID - smallint(6) PK
product_type - enum('dinner_service', 'tea_set')
style - varchar(10)
price_1 - float
price_2 - float
price_3 - float

patterns
--------
patternID - smallint(2) PK
pattern - varchar(100) <- name of patern
price_band - enum('1', '2', '3')

incompatibilities
-----------------
incompatibilityID - smallint(6) PK
product_type - enum('dinner_service', 'tea_set')
style - varchar(10)
pattern - varchar(100)

The products table holds a list of dinner services and tea sets in various shapes (the style) and the possible prices, which depends on which decorative pattern is chosen.

The patterns table simply lists all patterns and which price band they belong to. But not all patterns will fit on each shape (the style) of dinner service.

So the incompatibilities table lists which product type, style and pattern are not compatible.

I would like to select all patterns that are available for a certain style of dinner service but am stumped!

(I should have mentioned that my host is running MySQL version 4.0.22 and so sub-selects are not an option. :( )

Thanks in advance :)

[Argblat]

I'm hoping that I understand your problem correctly....why dont' you do something like this

Table: Products
---------------------
productID - PK
productName

Table: Patterns
---------------------
patternID - PK
patternName

Table: Product_Pattern_Price
----------------------------
productID - FK
patternID - FK
price

Here you have lookup tables for the different products and patterns, and you only add records to the third table if the pattern / product combination is a valid one and actually has a price...instead of putting what is INcompatible you only put what IS compatible...

"I would like to select all patterns that are available for a certain style of dinner service but am stumped!"

SELECT patternName
FROM Patterns, Product_Pattern_Price
WHERE Patterns.patternID = Product_Pattern_Price.patternID
AND productID = 'XYZ'

-Mike

[barns101]

Hi Mike, thanks for the reply. You have understood perfectly, and your solution does achieve the desired effect. I just thought that an incompatibilities table would be easier because there are fewer incompatible variations than there will be compatible ones. ;)