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
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
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.
>> 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
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:
IN (SELECT product_id
WHERE product_name = @my_product_name)'