asked on

Help formulating a sql where clause

As an example lets use the following tables with the data indicated. I am using Sybase ASE 15.5.

Create table PERSON(
	PERSON_ID	int not null,
	FIRST_NAME	varchar(50) not null,
	LAST_NAME	varchar(50) not null
)
INSERT INTO PERSON VALUES (1, CHRIS, DAUGHTRY)
INSERT INTO PERSON VALUES (2, BEYONCE, KNOWLES)
INSERT INTO PERSON VALUES (3, KELLY, CLARKSON)
INSERT INTO PERSON VALUES (4, MICHAEL, JACKSON)

Open in new window

Create table SHOE_COLOR (
	SHOE_COLOR_ID int not null,
	COLOR varchar(50)
)
INSERT INTO SHOE_COLOR VALUES (1, WHITE)
INSERT INTO SHOE_COLOR VALUES (2, BLACK)
INSERT INTO SHOE_COLOR VALUES (3, BROWN)
INSERT INTO SHOE_COLOR VALUES (4, RED)
INSERT INTO SHOE_COLOR VALUES (5, BLUE)

Open in new window

Create table IMAGE(
	PERSON_ID	int not null,
	SHOE_COLOR_ID	int not null,
	FILENAME	varchar(50) not null
)
INSERT INTO IMAGE VALUES (1, 3, CHRIS_IN_BROWN_SHOES.JPG)
INSERT INTO IMAGE VALUES (1, 1, CHRIS_IN_WHITE_SHOES.JPG)
INSERT INTO IMAGE VALUES (2, 4, BEYONCE_IN_RED_SHOES.JPG)
INSERT INTO IMAGE VALUES (2, 5, BEYONCE_IN_BLUE_SHOES.JPG)
INSERT INTO IMAGE VALUES( 2, 1, BEYONCE_IN_WHITE_SHOES.JPG)
INSERT INTO IMAGE VALUES (3, 2, KELLY_IN_BLACK_SHOES.JPG)

Open in new window

SELECT P.FIRST_NAME, P.LAST_NAME, I.FILENAME
FROM (PERSON P
	INNER JOIN IMAGE I ON I.PERSON_ID = P.PERSON_ID
		LEFT JOIN SHOE_COLOR S ON S.SHOE_COLOR_ID = I.SHOE_COLOR_ID)
WHERE P.PERSON_ID = 4

Open in new window

^^^ That query will give me (MICHEAL JACKSON NULL)

SELECT P.FIRST_NAME, P.LAST_NAME, I.FILENAME
FROM (PERSON P
	INNER JOIN IMAGE I ON I.PERSON_ID = P.PERSON_ID
		LEFT JOIN SHOE_COLOR S ON S.SHOE_COLOR_ID = I.SHOE_COLOR_ID)
WHERE P.PERSON_ID = 4
AND I.SHOE_COLOR_ID = 1

Open in new window

^^^ That query gives me nothing even though I am doing a left join.

Now... I know you are thinking. Crazy woman, that is what it is supposed to do and I understand that but my query is dynamic and I will not know the PERSON_ID until runtime. The shoe color will forever and always be 1(white). I need it to return the first and last name even if there is no entry in the IMAGE table for that person. My users can only see people in white shoes.

My question is. How do I formulate a where clause to do that? Give me the first and last names of the person even when there is no entry in the IMAGE table for said person WITHOUT removing I.SHOE_COLOR_ID = 1?

wilcoxon

You should be able to just change the inner join on image to a left join on image...

SELECT P.FIRST_NAME, P.LAST_NAME, I.FILENAME
FROM (PERSON P
	LEFT JOIN IMAGE I ON I.PERSON_ID = P.PERSON_ID
		LEFT JOIN SHOE_COLOR S ON S.SHOE_COLOR_ID = I.SHOE_COLOR_ID)
WHERE P.PERSON_ID = 4
AND I.SHOE_COLOR_ID = 1

Open in new window

However, I'm not sure if the "and i.shoe_color_id = 1" will screw it up or not (I don't think it should).

ASKER CERTIFIED SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wilcoxon

Wouldn't the p.person_id = 4 still be in the where clause since it is on the person table (whereas i.shoe_color_id is on image which may be non-existent)?

I know ANSI join is now the norm and people keep telling me it is better - however, I still hate the syntax and find the older proprietary join syntax much easier to read (biggest issue with it is that it is incompatible between database vendors).

Monica Stanley

ASKER

Thank you John. This gave me exactly what I was looking for. I moved SHOE_COLOR_ID = 1 into the join filter and left PERSON_ID = 4 in the where clause.

John_Vidmar

wilcoxon: to answer your question:

Yes, p.person_id = 4 could've remained in the where-clause without affecting the final result-set.

My understanding is that the on-clause is performed prior to the where-clause. So, if you could eliminate records prior to hitting the where-clause then the query may perform better. In this simple example, I doubt there would be any performance gain, but what if the where-clause contained a correlated sub-select?

Also, I wanted to show an on-clause with multiple filters... lol :)