Query Problem

[marky]

First Say hello to everybody, and sorry for my english I am from Spain.

I am running a lot of tables, but I will make a short example.

We got one table called cars:

CREATE TABLE cars (
id int(5) NOT NULL auto_increment,
colorid smallint(5) NOT NULL default '0',
modelid smallint(5) NOT NULL default '0',
PRIMARY KEY (id)
);

And then two other tables, color and model:

CREATE TABLE color (
colorid int(5) NOT NULL auto_increment,
color varchar(255) NOT NULL default '',
PRIMARY KEY (colorid)
);

CREATE TABLE model (
modelid int(5) NOT NULL auto_increment,
model varchar(255) NOT NULL default '',
PRIMARY KEY (modelid)
);

So I can make a relation between, cars and color - model with id values.

Ok, till here all works perfectly. But let´s think if we don´t know the color of the car we put the id value in the car table as 0. So when I run a query like:

SELECT car.id as id, color.color as color, model.model as model FROM car, color, model WHERE car.colorid=color.colorid AND car.modelid=model.modelid;

It doesn´t see the id with value 0, becuase it´s autoincrement, and it doesn´t exist. Well a solution is to create the id=0 with a value like 'No Color'. Till here I made all this, the problem is when I try to transfer the database with mysqldump, I get duplicate errors. And the transfer always stop when it arrives to this id=0.

Is there any SQL function that avoid´s to check de id=0, if this one isn´t created?

I hope I have explain my problem well, sorry again for my english, and thanks 🙂

[Anon]

Rather than using the id=0 technique, you could use LEFT JOIN to join your tables, rather than straight joins.

So your query would become something like:

SELECT
car.id as id, color.color as color, model.model as model
FROM
car LEFT JOIN color ON car.colorid=color.colorid
LEFT JOIN model ON car.modelid=model.modelid;

That should do the trick (and bring back NULL columns where no color/model exists).

[marky]

Thanks a lot it worked 😃