mySQL Left Outer Join

So I don't want to disappoint my friend who describes this site as everything geek... Earlier this week I was trying to work out an efficient way to find which cocktails in my cocktail making database had a certain set of ingredients and no others. i.e. tell me what you have in your bar and I will tell you what drinks can be made with a subset of those ingredients.

The solution to pulling this data turned out to be what is called a Left Outer Join and is supported in mySQL 5.0 (and possibly below this but I don't have a lower version to check).

The best way to describe what a left outer join does is an example so let's name two tables: age and beauty (the names of the trial eights in my college boatclub when I was captain).

Age Name Sex Brad Pitt Male Mick Jagger Male Jen Anniston Female Mo Mowlam Female

Beauty Name Sex Brad Pitt Male Josh Hartnett Male Jen Anniston Female Shakira Female

I may well want to find those people who are beautiful but not old and if would be great to have an effective way to do this without doing some complex IS NOT IN statement.

So what I could do is as follows:

SELECT Beauty.Name AS Name_b, Beauty.Sex AS Sex_b, Age.Name AS Name_a, Age.Sex AS Sex_a FROM Beauty LEFT OUTER JOIN Age ON Beauty.Name = Age.Name

Which would give me the following result:

Name_b	Sex_b	Name_a	Sex_a
Brad Pitt	Male	Brad Pitt	Male
Josh Hartnett	Male	Null	Null
Jen Anniston	Female	Jen Anniston	Female
Shakira	Female	Null	Null

However I would like to just see young beautiful people and so I want to get rid of Brad and Jen (too old). In order to do that I simply select those rows where the Name_a variable is null.

SELECT Beauty.Name AS Name_b, Beauty.Sex AS Sex_b, Age.Name AS Name_a, Age.Sex AS Sex_a FROM Beauty LEFT OUTER JOIN Age ON Beauty.Name = Age.Name WHERE Name_a IS NULL

Job now done the output is as follows and I have gone from a list of old people and a list of beautiful people of all ages to a list which just contains young beautiful people... now off to Google!

Name_b	Sex_b	Name_a	Sex_a
Josh Hartnett	Male	Null	Null
Shakira	Female	Null	Null

Comments

[stock shoes wholesale]

Last month, promotional gifts the Indian ambassador to the U.S.,promotional items Ms. Hill presented the Dakar after diplomatic status by the United States remains strong airport security personnel search.wholesaler This makes India very dissatisfied.

[Viagra Online]

Hi there! There is a very useful information above, God knows how glad I am right now to find this source to use it in my own investigation. I hope you can come across my University to see my thesis defense, because this topic help me to develop the principal objective around. Again, thanks a lot for all the information posting here.

[Soft Cialis]

I just found your blog and is really interesting, it was great to found it because I didn't know how to make a good match of couples with that way, I will like to read more about this, can you put more examples?

[Kamagra]

Well what you are saying it remember me what I studied at school, I've always thought that this is something very interesting and I've never thought to make it to know how that pretty is gonna be some one

[wow account]

Dear experts,

I have table a (a_id, description), b(b_id, description), and x(a_id, b_id, description). x is a cross reference table providing many-to-many relationship between a and b.

Could you please help me make a select statement that would get all data from a and b (if there are records corresponding to selected a). I am not sure how to use left outer join with many-to-many relationship.
Thanks.

[Cheap viagra]

A pleasure to greet you. My name is Mattkin, I declare myself a fan of reading, I would first like to congratulate all the readers for finding this article so sensational, I think the new generations have a lot of good information at hand, years ago none of us would imagine turning a computer and locate items as cool as this. Thank you very much for allowing comments on your blog.

[investment costa rica]

To get the most accurate measurements for an individual's penis size, it has been recommended that several measurements be taken at different times, preferably with different erections on different days. The measurements are then to be averaged together. This is to account for what may be natural variability in size due to factors such as arousal level, time of day, room temperature, frequency of sexual activity, and unreliability of the measurement methods

[Pherbals]

Pherbals inc products offers all kind of natural herbal men's and women's health care products, general and skin care products, sexual and hair care,weight loss and much much more http://pherbals.com

[Pearl27Payne]

I received 1 st loans when I was a teenager and it aided my business very much. However, I need the short term loan once again.

[viagra online]

Hi guys I founnd your post "This is a really simple way to fix the problem, if you have experience with the existing "SELECT" method you can use that to create a new table with exactly the same properties as the table you are selecting from. An example statement below would copy exactly the table tbl_Ingredients from a cocktail database across to the placement tbl_Ingredients_temp."