Re: MySQL SELECT help

Addressed to: "Chris Lee" <lee@mediawaveonline.com> php-db@lists.php.net ** Reply to note from "Chris Lee" <lee@mediawaveonline.com> Fri, 10 Nov 2000 13:39:49 +0800 > > I have three tables > > table product =========== stockno name > > table product_category ================== stockno category > > table product_image ================ stockno data > > select * from product ================ 1234 truck > > select * from product_category ======================== 1234 automobile > > select * from product_image ====================== 1234 file1.jpg > > select * from product, product_category, product_image > ============================================ > 1234 truck 1234 automobile 1234 file.jpg > > see thats fine, but now what I want is if there are no entries in one of > the tables to still return the data it knows about. ie. > > select * from product ================ 1234 truck > > select * from product_category ======================== 1234 automobile > > select * from product_image ====================== Empty Set > > select * from product, product_category, product_image > ============================================ > 1234 truck 1234 automobile NULL NULL > > > Can this be done ? Maybe something like SELECT * FROM product LEFT JOIN product_category USING( stockno ) LEFT JOIN product_image USING( stockno ) Unless there is a lot more to your database, the better solution to this problem would be a database redesign. It appears to me that name, category, and data are all attributes of stockno, and should be a single table. Also unless you are going to use the same image for many stocknos I would eliminate the field and call the image for stockno=1234 1234.jpg. I would probably use something like this: CREATE TABLE Categories( CategoryID bigint not null auto_incmement, Category varchar(20), PRIMARY KEY( CategoryID )); CREATE TABLE Stock( StockID bigint not null auto_increment, StockNo varchar(10) not null, CategoryID bigint not null, Name varchar( 20 ), PRIMARY KEY( StockID ), KEY( StockNo ), KEY( CategoryID )); If you can allow the database to assign stock numbers, eliminate the StockNo field and use StockID. If you must have an image name in the record, rather than using "$StockNO.jpg" as the image, then you can add a field for it: ImageName varchar(100), Then your query becomes SELECT * FROM Stock LEFT JOIN Categories USING( CategoryID ); You don't have to store the Category string for each StockNo, which could save substantial space in a database with thousands of records. Rick Widmer Internet Marketing Specialists www.developersdesk.com