Impossible WHERE noticed after reading const tables

[thesavior]

Okay, this is really weird, so bear with me.

In my image table, I have rows with id 4 and 6.

If i run a query like this:
SELECT i.*,
u.*,
u.username as username,
DATE_FORMAT(i.dateuploaded,'%M %e, %Y') AS date2
FROM images AS i
INNER JOIN users as u
ON i.posterid=u.id
WHERE i.id='4'

I get all the information I want.

However if I run something like this:
SELECT i.*,
u.*,
u.username as username,
DATE_FORMAT(i.dateuploaded,'%M %e, %Y') AS date2
FROM images AS i
INNER JOIN users as u
ON i.posterid=u.id
WHERE i.id='6'

It doesn't give me any rows.

If I do this:
SELECT * from images WHERE id='6'

It gives me the row with that id.

If I use:
EXPLAIN
SELECT i.*,
u.*,
u.username as username,
DATE_FORMAT(i.dateuploaded,'%M %e, %Y') AS date2
FROM images AS i
INNER JOIN users as u
ON i.posterid=u.id
WHERE i.id='6'

I get from mysql:
Impossible WHERE noticed after reading const tables

However if I get dump data from mysql, it gives me:

INSERT INTO `images`
VALUES (4, 8, '2006-09-26 14:10:33', 'Interface Sig', 'This is a signature I made for myself to test out some interface looking stuff. The tribal brush in the background gives it a great extra touch.', 'http://images.image-space.net/060926141029632.JPG', 2, 29);

INSERT INTO `images`
VALUES (6, 8, '2006-09-28 04:01:47', 'Animated Halo', 'This is an avatar that gave me some practice with animating in Image Ready. The color of the helmet and the text gradually change. A bit slow, but it looks cool.', 'http://images.image-space.net/060928040139209.GIF', 5, 18);


So we definitely know that the row exists, but I can't figure out for the life of me why the query is going through for one row, but not the other.

[SQL_F1]

Hi thesavior

You clearly know your SQL so, if I'm right, you'll kick yourself - or I'm puting my foot in something:

Guessing at what your fieldnames and logic means, the user who posted image with id 6 is missing from your users table.

The INNER JOIN users as u ON i.posterid=u.id
must be excluding the row where i.id = '6'.

as an asside, why
SELECT ... u.*,u.username as username
username as username is already there?

Brian

[thesavior]

I posted both of them. From the same user, which has the same Id. This is why I am so utterly confused.

and the impossible where is on the image id, so i don't see how that one could be effected by the missing row in the users db (which there isn't). The only thing that would be effected is the ON statement.

[SQL_F1]

Hi
I think you mean that you posted both records from the images table.
Maybe we're talking at cross purposes but can we see the user table dump?
Brian

[thesavior]

CREATE TABLE `users` (
`id` int(30) NOT NULL auto_increment,
`email` varchar(30) NOT NULL,
`username` varchar(20) NOT NULL,
`password` varchar(33) NOT NULL,
`registered` timestamp NOT NULL default CURRENT_TIMESTAMP,
`usertype` varchar(20) NOT NULL default 'member',
`views` int(50) NOT NULL default '0',
`avatar` varchar(100) NOT NULL,
`aboutme` text NOT NULL,
`shortdescr` varchar(40) NOT NULL,
`stylesheet` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;



INSERT INTO `users` VALUES (1, 'eli@powerwd.net', 'Eli_White', 'PW', '2006-08-08 00:00:00', 'admin', 0, 'http://www.powerwd.com/forum/img/avatars/2.png', 'My name is Eli White and I am a web designer. I currently work for Power Software. I have experience coding in PHP, MYSQL, CSS, and XHTML. I have made many websites and codes. Check them out by clicking on the appropriate link to the left. To contact me, please click on the contact link at the bottom of the page.', '', '');
INSERT INTO `users` VALUES (2, 'thesavior@cox.net', 'TheSavior', 'PW', '2006-08-10 01:08:24', 'member', 268, 'http://image-space.net/img/avatars/2.png', 'My name is Eli White and I am a web designer. I currently work for Power Software. I have experience coding in PHP, MYSQL, CSS, and XHTML. I have made many websites and codes. Check them out by clicking on the appropriate link to the left. To contact me, please click on the contact link at the bottom of the page.', 'Site Administrator', '');
INSERT INTO `users` VALUES (7, 'iphageenia@excite.com', 'iphageenia', 'PW', '2006-08-25 02:00:45', 'member', 9, '', '', 'New Member', '');
INSERT INTO `users` VALUES (6, 'oo7dark@mac.com', 'oo7dark', 'PW', '2006-08-23 17:16:29', 'member', 14, '', '', 'New Member', '');

[SQL_F1]

Hi TheSaviour

I'm assuming that the second field in the images table is
the i.posterid
So the wierd thing is why you are getting any rows returned in a join ON
i.posterid=u.id
since there is no u.id=8 and all the i.posterid values are 8?

Brian

[thesavior]

You are actually looking at the wrong line for posterid, posterid is the second to last one. I thought I posted the table structure for the image table, but I guess not. Here it is:


CREATE TABLE `images` (
`id` int(10) NOT NULL auto_increment,
`parentid` int(10) NOT NULL,
`dateuploaded` timestamp NOT NULL default CURRENT_TIMESTAMP,
`name` varchar(30) NOT NULL,
`description` text NOT NULL,
`location` varchar(120) NOT NULL,
`posterid` int(11) NOT NULL,
`readviews` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

The 8 is the id of the category the image is in.

[SQL_F1]

Hi TheSaviour

The problem is the same just with different values.
You have no user with id = 5.
Your join will exclude all images that don't comply with i.posterid=u.id
Create a user with id=5 and you will get the image listed
Brian

[thesavior]

I guess the question I have to figure out, Is how did the second parentid get to be 5? Cause I posted it from the same user, going through the same code. Hmm, well...thanks for your help.

[syosoft]

Consider using LEFT JOIN's and then use php to decide if images are returned instead of not getting any data back.

[thesavior]

What are the differences between left join, right join, inner join, and outer join?

[syosoft]

That'll be better answered by someone more qualified in SQL than myself.

[guelphdad]

the two main joins are inner joins and outer joins. the word inner and the word outer are optional. I always use them so I can remember which kind I'm doing.

An inner join can be written as: INNER JOIN or JOIN.

An outer join can be either LEFT or RIGHT, it just tells the tables which order you are joining them in. Stick to one or the other, if you mix them up it is easier to forget which table you are putting the conditions on. Since I read left to right I always use LEFT joins.

A left outer join can be written as LEFT OUTER JOIN or LEFT JOIN.

The difference between the two types of joins are that inner joins only return rows where your data matches in both tables. Say you join on an ID column. Only those rows where you have the same id in both tables are returned.

An outer join returns all rows even if there are no matching rows.

Perhaps you want a list of all students and their classes, you want to show the students even if they haven't signed up for a class yet, then use an outer or LEFT OUTER join.

There are other types of joins as well though they are less common.

A CROSS JOIN returns every row in the one table for each row in the other table. Say table A has 5 rows and table B has 4 rows. for each row of table A, all rows of table B will be returned. In this case 20 rows.

FULL JOINS are not supported by mysql, it returns all rows in both tables, matching and unmatched. I am not versed with FULL JOINS at all so I can't offer an explanation clearer than that. Someone else might have more info.

[thesavior]

Thanks, thats a pretty impressive description.