Trying to be efficient and use SQL

SQL Apprentice Question
I have a particular problem that's really causing me some grief. I
know I can (easily) solve this problem programically, but I think there
must be a SQL query that designed for this, and I'd like to try to do
things right.


Essentially I have two tables. TABLE A contains productid and
productname. TABLE B contains productid1 (linked to productid),
productid2 (linked to productid) and productid3 (linked to productid).


What I need to do is create a query which will enable me to pass to my
application a column which contains the productname instead of the
productid.


Programically, I could just take the returned productid's and re-query
to find out the productname, but this seems inefficient and alot
slower... maybe its the only way?


Any help would be greately appreciated.



Celko Answers

>> Essentially I have two tables. TABLE A contains productid and productname. TABLE B contains productid1 (linked to productid), productid2 (linked to productid) and productid3 (linked to productid). <<


Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

It sounds like Table B (awful name even for an example) has repeated
groups in violation of 1NF.


CREATE TABLE Inventory
(product_id INTEGER NOT NULL PRIMARY KEY,
product_name VARCHAR(25) NOT NULL);


let's use 1NF for the table -- B is for Baskets?


CREATE TABLE Baskets
(basket_nbr INTEGER NOT NULL,
product_id INTEGER NOT NULL
REFERENCES Inventory (product_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
Item_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (item_nbr BETWEEN 1 AND 3),
PRIMARY KEY (basket_nbr, product_id));


This will let a basket have 1, 2, or 3 items in it, but no more.



>> What I need to do is create a query which will enable me to pass to my application a column [parameter] which contains the product_name instead of the product_id. <<


I am not sure what you mean by that. Here is a guess:

SELECT basket_nbr
FROM Baskets
WHERE product_id
IN (SELECT product_id
FROM Inventory
WHERE product_name = @my_product_name)'