join with where clause

[anatak]

I have a table bike and a table bike_text

in bike I store all the technical information about the bike like CC, model year, and mileage

in bike_text I store the explanation and impressions of the bike.

The bike_text has a languageId field to make it possible to have the text for one bike in multiple language

It is also possible that there is no bike_text entry for a given bike.

 

Now I want to select all the bike values and the bike_text text values for a specific bike in one language

Select bike.*, bike_text.text
From bike
Left Join bike_text on bike.id = bike_text.bikeid
Where bike.id = 1

this works without problem

but when I make it

Select bike.*, bike_text.text
From bike
Left Join bike_text on bike.id = bike_text.bikeid
Where bike.id = 1 and bike_text.languageid = 1

 

it returns an empty row if there is no text for bike 1 in the bike_text column

 

Is it even possible to do what I want in 1 Query ?

It would be easy to do in 2 Queries since both queries can use the bike.id as the search key but I just got wondering if it is possible to do.

 

kind regards

anatak

 

[kickstart]

Hi

 

The WHERE is performed after the JOIN, hence excludes rows.

 

What you want to do is exclude rows of the bike_text table prior to the JOIN so that null columns are returned there. Put the check in the ON clause:-

 

SELECT bike.*, bike_text.text
FROM bike
LEFT OUTER JOIN bike_text 
ON bike.id = bike_text.bikeid
AND bike_text.languageid = 1
WHERE bike.id = 1 

 

Another way to look at it would be to do it as a subselect (not suggesting you do this unless you have to use MS Access, just a way to show you what is required):-

 

SELECT bike.*, bike_text.text
FROM bike
LEFT OUTER JOIN (SELECT * FROM bike_text WHERE bike_text.languageid = 1) bike_text_deriv
ON bike.id = bike_text_deriv.bikeid
WHERE bike.id = 1 

 

All the best

 

Keith