text:
Showing 97-192 of 388 items found in Shopping
- Sort by:
Culture Stock is a social venture that operates as a hub for cultural activities and community programs while serving as a used book and media re-seller. Culture Stock, located in the heart of Downtown Aurora, offers up-and-coming musicians, poets and other artists a place to display their talents. Stop in for a taste of Aurora's culture, read a good book in a cozy corner, listen to a new performer or join a language circle. Kids have events at Culture Stock too. Check their Facebook page for a Downtown Aurora cultural experience update today!
Two stories of quality, solid wood furniture since 1938. Huge selection of finished, unfinished home, office furniture by Amish and other American manufactures.
CITY:St. Charles
This custom dress shop specializes in wedding gowns but also makes cocktail and eveningwear for women of all ages and taste. Dame also restyles and reconstructs vintage bridal. The shop's charming vintage atmosphere makes a lovely backdrop for a selection of jewelry by local artisans as well as veils, bags and a few off-the-rack dresses.
Best known for his jewelry and timepieces, David Yurman creates contemporary and timeless treasures for both men and women. His designs have graced red carpets throughout the world.
Built in the popular Romanesque style, Chicago's oldest railroad station is now a retail shopping mall and an urban neighborhood marketplace. It is also a National Historic Landmark.
Deer Park Town Center features over 70 of the most exclusive stores and restaurants in Chicago's northwest suburbs. Located at the intersection of Rand (Route 12) and Long Grove Roads in Deer Park, IL, Deer Park Town Center is an easy drive from surrounding suburbs, downtown Chicago or O'Hare Airport. Park in front of your favorite store and spend an hour or a day. At Deer Park Town Center, there's something for everyone.
CITY:Deer Park
Different from your average shopping mall, Deer Park Town Center is an open-air lifestyle center providing you with leisurely amenities in addition to some of the best shopping and dining in Illinois. Deer Park Town Center features over 70 of the most exclusive shops and restaurants in Chicago's northwest suburbs. Stores include Apple, Crate & Barrel, Barnes & Noble, Sephora, Gap, Pottery Barn, Restoration Hardware and more. Entertainment includes Century Theatres. Park in front of your favorite store and spend an hour or a day shopping and dining, or catch a movie at the Century Theater.
CITY:Deer Park
An exceptional neighborhood bakery with great tasting, high-quality products, nice ambiance and friendly customer service. They have a variety of theme cakes and a huge selection of buttery cookies, doughnuts, sweet rolls, coffeecakes and more.
Designers Desk Complete Needlework Shoppe Specialty Shops
Complete needlework -- classes, framing, supplies -- and a lot of fun!
Dick Pond Athletics is America's running shoe store specializing in footwear, clothing and accessories for runners, walkers and triathletes. Open daily. Across from Salerno's on the Fox Restaurant on Second Street (Rt. 31)
CITY:St. Charles
Dick's Sporting Goods - Schaumburg
Dick's Sporting Goods leads more athletes to victory by providing the edge they need to perform at their peak. They offer a deep assortment of sporting goods and apparel for baseball, football, hunting, golf, fishing and more.
Didier Farms offers a variety of vegetables and flowers in our greenhouse this spring, and our farmers market in summer. U-pick opportunities are plentiful throughout the summer, weather permitting. During our Pumpkinfest, the last week of September and all of October, we offer hayrides, pony rides, corn maze and an exotic zoo, in beautiful Lake County. Also enjoy the Buffalo Creek Mining Co, pony & camel rides, Animal Land, Pumpkin Playland, kiddie rides, Parakeet Encounter, Pig Races (Sat, Sun & Mon) Pumpkin Patch Café, homemade apple cider & pumpkin donuts made daily. Antique tractor display, fall produce, birthday parties and school groups by reservation only.
Classic clothing with a flair.
CITY:St. Charles
Come to DNA Athletics to get outfitted in comfortable, durable shoes and accessories so you can focus on your fitness, not your gear. Whether you're a casual sportsman or a serious athlete, our knowledgeable staff will help you find just the item you're looking for. Drop by our store to try on our clothing or test our shoes on our in-store treadmill. If you lead an active lifestyle, you depend on your shoes and gear to perform under pressure. Our shoes, clothing and accessories are top of the line – made to last by the world's leading manufacturers.
CITY:Crest Hill
Nestled in the heart of the Fox Valley, and home to dozens of delightful shops, restaurants and galleries is Geneva – a picture postcard. Come stroll along the lush, tree-lined streets and visit a historic town with exquisite dining, boutique shopping, world-class lodging and award-winning festivals. Located 50 miles from downtown Chicago, Geneva is a quick getaway from the hustle and bustle by I-88, I-90 or Metra train.
Downtown Joliet is experiencing an exciting resurgence in entertainment options and business opportunities. Downtown Joliet serves as the city's main municipal center.
Located on the banks of the Fox River, historic St. Charles is known for its great antique shops housed in turn-of-the-century buildings. Visit Century Corners Historic Shopping District and Old St. Charles, where you'll find landmark buildings, unique boutiques and nearly 30 restaurants.
CITY:St. Charles
Warm, friendly service is the hallmark of historic Downtown Wheaton. Stroll the tree-lined streets of this upscale retail and service district and delight in the quaint shops, award-winning restaurants, indulgent day spas and unique boutiques.
Unique Judaic giftware and ritual items. Many imported from Israel and handmade.
East of the Tracks offers an eclectic and ever-changing mix of antique and vintage furnishings, circa 1800s to 1940s. We feature a finely curated collection of furniture, lighting, art and mirrors. Stop by and be surprised-you never know what you will find!
"We cook what we sell, and we sell what we cook." Eataly is an original "marketplace" model inviting consumers to be active participants in an innovative food and beverage experience where they shop, taste and savor high-quality, traditional, Italian food products and beverages along with local produce and artisanal products. This multifunctional marketplace includes a premier retail center for Italian delicacies and wine, a culinary educational center and a diverse slate of boutique eateries. Eataly ambassadors are here to provide a high-quality experience to all of our guests, whether you are here to shop or enjoy a meal.
Egor's Tobacco and Gift Shoppe
Cigar and pipe tobacco accessories.
DuPage County, Chicago's Western Suburbs - Choose from a large variety of restaurants from fine dining to casual fare. Catch a movie or browse our wonderful shops.
It happens the moment you turn onto the long, gravel road; it might be the charm of the converted century-old farmhouse filled with handcrafted pottery, homemade candles and other unique finds. It might be savoring the last bite of Blackberry Peach Cobbler after a delicious, freshly prepared lunch in the tearoom. Or it might be the peaceful serenity found only in the countryside. Whatever "it" is, friends young and old, male and female, have been coming back to Emerson Creek to experience it again and again.
Eve Alfille Gallery and Studio
Eve's Gallery is a magical place. Eve, an award-winning artist and former archaeologist, designs exquisite jewelry and her pieces range from the simple to the simply amazing - only things to lust for here. Alfillé's Gallery is a frequent stop for jewelry lovers and now Eve has captured the cachet of her brick and mortar venue in her online store where you can shop in your pj's and bunny slippers. Eve is continually adding new pieces so take a look and get a one-of-a-kind treasure for your self or as a gift.
We are a printmaking workshop open to the public, displaying local artists' original prints at reasonable prices. We also offer jewelry, cards, pottery and one-of-a-kind works.
CITY:Oak Park
Serving the Oak Park-River Forest area for over 65 years! Famous Liquors, Wine Discount Center, and Famous Coffee Shop all under one roof!
CITY:Forest Park
More than one million items fill a block-long store of fun and fantasy. Must see it to believe it. Thousands of costumes for all occasions and seasons. Vast selection of fun and everyday wigs, makeup, masks, novelties, accessories, Santa Claus, Easter bunny, passion plays, Purim, Mardi Gras and patriotic costumes for every season or whatever the event. Theatre, film, corporate and sports-themed events. We can handle it all. Open daily. Sales & rental.
FAO Schwarz at Macy's on State Street
Our mission is to be a source of wonder and a trusted resource for each new generation, a challenging and nurturing environment for all our employees, and a relentless supporter of quality and innovation in the life of every child.
The new Fashion Outlets of Chicago is an unparalleled shopping environment, where fashion meets art and fine dining! Shop over 130 designer outlets, including Tory Burch, Coach, Prada, Bloomingdales The Outlet Store and Last Call by Neiman Marcus. Located just minutes from O'Hare International Airport.
An upscale resale boutique that offers designer women's clothing and accessories on consignment for a fraction of their original cost.
CITY:Oak Park
Visit the Ferrara Pan Candy Company outlet store and see how many ways you can satisfy your candy cravings!
CITY:Forest Park
Visit the many shops located in the new development along the river. There is a large parking garage adjacent for shoppers' convenience and 2 Visitor Information Centers.
CITY:St. Charles
DuPage County, Chicago's Western Suburbs - 100's of shops, quality merchandise at bargain prices. Craft/collectibles mall.
CITY:Villa Park
Flavour Cooking School is a unique and fabulous place to learn culinary techniques through a wide variety of hands-on and demonstration cooking classes. Visit our retail store, which showcases a wide variety of professional cookware, utensils and gourmet ingredients. Available for private events.
CITY:Forest Park
At Fleet Feet Sports Chicago you will find a welcoming environment and customer service that is the cornerstone of health and wellness in the community. Whether you walk, run, exercise or simply need advice and motivation to lead an active lifestyle, the experts at Fleet Feet Sports can help. The staff will work with you to evaluate your individual needs and natural bio mechanics to help select the products that offer the best fit and function for you. Commitment to the betterment of the health and well being of the community runs deep through Fleet Feet educational resources, fitness training and professionally organized award winning events.
Fly Bird is a gift boutique filled with curious items for the happy home, body and mind with an emphasis on fun and design. Fly Bird offers a selection of carefully curated items from around the world as well as local designers. Stop by if you're in need of a fun place to shop!
CITY:Oak Park
A one-of-a-kind gallery featuring American artists in a variety of monthly thematic shows. The shows feature Santas, angels, witches, arks, cats, dogs, and more. Also featuring country antiques and vintage jewelry.
Forest Park Chamber of Commerce & Development
The Forest Park Chamber of Commerce & Development is dedicated to the continued growth and revitalization of Forest Park's business districts as well as the enhancement of quality of life for both businesses and residents. Investment in the Chamber partners you with a comprehensive group of business owners and residents throughout the community who want to be involved, stay informed and work together towards making Forest Park an even better place to live, work, shop, dine and play. We invite you to join us!
CITY:Forest Park
Forever 21 offers the newest fashion trends for women and men at reasonable and affordable prices, so everyone can be fashion forward.
Located on Chicago's north side, found-vintage living is a lifestyle store specializing in vintage furniture, barware, lamps, art and accessories. Serving as Andersonville's destination for affordable vintage pieces, it is a haven for authentic home décor from the 50s, 60s and 70s.
A family- and friends-owned dealership, Fox River Harley-Davidson fosters an environment that makes it easy to do business, which is why our customers choose us for their Harley-Davidson service and product needs.
CITY:St. Charles
Fox Valley Mall - Centennial Collection
Fox Valley Mall is the premier shopping center in Illinois, featuring 130+ specialty stores and dining options! Anchored by Macy's, Carson Pirie Scott, J C Penney and Sears. Fox Valley Mall has three levels of shopping, a full food court, and a merry-go-round. This mall is a must-shop-stop for everyone! The NEW Fox Valley Train is located on Level 2 near JCPenney.
Front Street Downtown Wheaton
Fun Inc. is a manufacturer and wholsale distributor of magic tricks and gag novelties. Our knowledgeable and experienced factory staff, most of whom have been with the company for over 20 years, take pride in paying attention to the smallest details. This makes accurate fulfillment of orders and timely shipments a top priority.
Specializing in all dog care. Dog training; doggie day care; overnight dog boarding; pet sitting; dog grooming; pooch parties.
CITY:St. Charles
Galena Cellars Winery & Tasting Room
You're in good company. Experience the charm of Galena in suburban Geneva. The newest addition to the Galena Cellars Family, the Geneva Wine Tasting Room & Gift Shop hosts a beautiful oak bar in a light, airy setting. In the warmer months, enjoy live music with wine and cheese on the outdoor patio. Sit. Swirl. Sip & Savor. Relax on our beautiful Wine Patio after shopping Geneva's historic district.
Gallery 200 is fast becoming a haven for art lovers as a quality destination for buying and browsing great local art. The gallery, a cultural initiative supported by the City of West Chicago, hosts artist opening receptions, art talks and workshops.
CITY:West Chicago
Gallery 28 Fine & Functional Art
Tucked away in the quaint historic district of downtown Geneva, in an historic Victorian home is where you'll discover Gallery 28, an art gallery for both fine and functional art. Featuring a fresh mix of breath-taking paintings, exquisite jewelry, sculpture, photography, pottery and unique works in wood and metal along with music by premier musicians. The variety of art will interest serious fine art collectors as well as fine craft enthusiasts.
The gallery's mission is to continue to expose the art community and provide a portal to fine art and artists of different parts of the African Diaspora. A collaborative of artists, collectors and art administrators who have come together to create a unique art experience, and ensure fine art has a place in the rebirth of Bronzeville.
Soho-style art gallery featuring contemporary art that is thought-provoking as well as aesthetic.
Our Farm Stand is a collection of produce grown in our field, our greenhouses and the best produce that other local farms have to offer. Garden Patch Farms is a playground for visitors that like to pick all of their own produce. In season, we offer a variety of items to pick on our farm.
CITY:Homer Glen
Seven minutes west of downtown St. Charles in Wasco off Rt. 64 (west Main Street). Weddings our specialty.
Visit the Garmin Store for a hands-on experience with the latest in GPS technology. Rent select automotive and fitness devices and try before you buy.
Garrett Popcorn Shops are a third-generation family-owned business specializing in a variety of tantalizing flavors of popcorn. Featuring caramel crisp, which has been dubbed "the best in the world."
A multi-dealer market loaded with wonderful vintage items from jewelry to furniture. Come see our many treasures! We lead the Midwest in Vintage/antique Christmas items.
Geneva Commons has a wonderful mix of 75-plus merchants, from familiar favorites to new and unique shops. Spend time shopping, eating and relaxing in this open-air center. Dine at a wide variety of eateries, most of which provide outdoor seating.
The Geneva French Market is held every Sunday May-October in the parking lot at the Metra Station in downtown Geneva. Offering fresh produce, fine textiles, fresh baked goods, and unqiue gifts, the French Market is a great place for a Sunday stroll.
The Geneva Green Market promotes local, organic and sustainable-produced products from local artisan farmers and producers who provide freshness, biodiversity, the highest quality food and the basis of regional cuisine. Geneva Green Market is an educational forum for consumers to learn the uses and benefits of quality, locally grown or prepared food products. This is designed to establish an appreciation of nutritional values of produce and to provide more flavor, freshness and variety of locally grown product. Held every Thursday, 7am to 1pm, June through October.
Experience a unique approach to fulfill all of your running & walking needs with personalized shoe fittings, apparel and accessories.
A European-inspired toy shop, Geppetto's offers "unplugged" toys that encourage imagination and creative learning. All products are safe, sustainable, and fun! With free gift wrapping, concierge shopping service, and an attentive staff, it's easy to get just the right plaything for the kids in your life at Geppetto's.
CITY:Oak Park
Goebbert's Pumpkin Farm and Garden Center
Fall: festival with haunted house, corn maze, farm animals, and more. Spring/Summer: large selection of annuals, perennials, vegetable plants, trees, shrubs, aquatic plants, roses, planters, and gourmet food items. Admission is free - cost for some activities.
CITY:South Barrington
Candy and gourmet chocolates, party bags, balloons, beanie babies, and a variety of gift items.
When was the last time you visited a bread store that made everything from scratch? It's the ultimate experience! Owner, Dave Schaps, and his crew start by milling wheat fresh every day at 4 a.m. and baking it into big, beautifully designed loaves. The handmade bread making process allows you to get the best tasting bread on earth. Most of the basic breads are guaranteed fresh for 7-10 days. Specializing in whole-wheat, handcrafted breads made from scratch. There are delicious sweets and treats made fresh daily.
Green City Outdoor/Indoor Market
Green City Market is Chicago's only year-round farmer's market promoting local, sustainable farmers, producers and chefs by connecting them to the public through educational programming and special events. Enjoy the Outdoor market May through October.
Gucci carries everything from men's and women's clothing and shoes to leather goods, luggage, jewelry, timepieces, silks and eyewear.
Trendy, high-quality fashion at great prices from this European chain with a definite flair for chic.
Premium imported cigars, Davidoff White Label Merchant Humidors, lighters, pipes, and specialty cigarettes and accessories. On-site smoking lounge with multi-media entertainment.
CITY:Rolling Meadows
This fourth generation, family-owned pottery company features an on-site museum and a factory store that holds an annual summer sale.
Original fashion jewelry designs from romantic to Renaissance. Special occasion, wedding and prom, are our specialty.
CITY:West Dundee
Enclosed shopping center, just north of Oak Park. Featuring over 100 stores, including Carson Pirie Scott, Kohl's, Target and Best Buy.
Scheduled sales once or twice a month featuring a fresh selection of vintage, industrial, farmhouse items for yourself and your home.
CITY:St. Charles
Have to Have carries women's clothing sizes 0-14, Miss Me Jeans, shoes, jewelry, purses and gifts. Open Tuesday-Friday 10:00am-5:00pm, Saturdays 10:00am-5:00pm and Sundays Noon-4:00pm. Closed Mondays.
Resale shop. No consignments. Weekly specials.
Heinz Brothers Greenhouse Garden Center
Open daily, year-round. A greenhouse, garden center and gift shop offering annuals, perennials, bulbs, ground covers, potting needs, garden art, tools and everything else imaginable for the gardener.
CITY:St. Charles
It's not just a bike shop, and it's not just a coffee shop—it's an artistic space, a working space, a space to live in. They encourage everyone to bring suggestions and feedback; to bring their kids, or their laptops, or both; and to make new friends by elbowing into the community table. Everything they do is centered around this sense of community, right down to their manufacturing. Heritage bikes are designed, welded (by hand, out of American steel), and assembled right here in the city of Chicago. Plus, almost every bike is entirely unique, as Heritage focuses on full customization. Whether it's sparkly colors or Italian leather accessories that float your boat, they'll find something to match your style.
Farm dinners at Heritage Prairie Farm, in Elburn have been a tradition since 2007. All farm dinners include a welcome cocktail upon arrival to the farm with butler-passed appetizers, and live acoustic music. For dinner, an upscale five-course meal, introduced by the chef himself, is served family-style in a charming atmosphere, regardless of the season. Get an inside look at how the farm operates from one of their knowledgeable staff with a private tour of the farm.
With their signature orange color, this high-end retailer is known for their leather goods, men and women's accessories and jewelry collections.
This engaging retail experience features the full line of Hershey's products, as well as one-of-a-kind gifts and souvenirs Be entertained by the singing baker at the bake shoppe, offering cupcakes, cookies and brownies all topped with Hershey's products.
The City of Lockport has a unique downtown. With a history that drove the economy of Illinois and the Midwest through the early 19th century, Lockport has many historical markers, some visible, some hidden, that attest to its influence.
Nestled in historic homes and buildings that date back to the community's roots in the 1800's, Long Grove's shops specialize in unique gifts, boutiques, antiques, clothing, exceptional art and home décor, and distinctive menus. Visitors can stroll the cobblestone walks that wind through the historic downtown and enjoy seasonal lush gardens and scenic views. And they can make it a quick trip or a day's outing, shopping and indulging their tastes with a variety of food and bakery products, cafés, and fine eating establishments. Fun festivals throughout the year including the famous Chocolate Fest, Summer Fest, Craft Beer Days, Apple Fest and a month of Holiday Festivities to name a few.
CITY:Long Grove
Full service flower and gift shop with an excellent reputation and creative professionals. Extensive offerings including beautiful fresh floral arrangements, long stem premium roses, orchids, green and blooming plants, fruit and gift baskets. Gift Certificates available. Reader's Choice Winner (Best Florist) at Patch.com in April 2012.
Has been making its own delightful signature toffee in Lockport for 30 years. Hollingworth Signature toffees and candies are locally handcrafted with the finest and freshest, natural ingredients. Pure ingredients. Pure indulgence. Pure Bliss.
Fireplaces, glass doors, gas logs, tool sets, screens, and related accessories.
We at HouseRed are committed to providing our customers with an educational, enjoyable and cultural experience. Peruse our unique, hand-picked selection of wines from around the globe, ask us about hosting your next party or special event, take a class in the SchoolHouseRed series, attend an educational and fun wine tasting, come listen to live music -- the possibilities are endless!
CITY:Forest Park
I Love Dirt / Cherle Enterprises
At I Love Dirt, we carry everything but the bike! We have unique apparel & accessories for avid motocross & extreme sports competitors and fans. Cherle Enterprises is an innovative full-service advertising & promotional specialty company.
Home furnishings that combine good design, function and quality at affordable prices.
Located at I-355 and I-55 at the Boughton Rd. exit. IKEA offers home delivery, rental vans, assembly services and an IKEA credit card so that you won't have any worries while you are shopping for your home. Don't forget to visit the restaurant while you are there!
Illinois Artisan Shop - Chicago
Sales gallery/shop featuring the work of over 500 Illinois artisans. Established in 1985, was the first shop of its kind in the state, showcasing one of the most diverse collections of handcrafted work in the Midwest.
Home of Fantasia Confections. Specializing in hand made chocolates, custom confections, dessert tables, gift baskets, corporate gifts, party trays, bulk candy and nuts. The entire store is certified kosher. Vegan, lactose-free and gluten-free products offered.
Immense glass and steel structure, designed by Helmut Jahn, houses State of Illinois government offices and three levels of stores and restaurants in an atrium mall.
Jeanine A Guncheon Gallery Etcetera
An unusual shop that runs the gamut from art, gifts & vintage. Uniquely individual. Owner and artist, Jeanine Guncheon, has a wide collection of her hand painted bowls, furniture, and one-of-a-kind sculptures. Commission pieces on request.
CITY:Forest Park
If you're shopping for that perfect diamond, don't miss the dozens of jewelers who sell their exquisite stones and designs directly to the public from their Wabash Avenue studios. The bargains are incredible.
Considered the "must-have" brand among fashionistas throughout the world, this boutique not only boasts women's shoes, but also handbags, fine leather goods and eyewear.
Open area with both indoor buildings and outdoor displays. Special events are held here nearly every weekend, including the famous Kane County Flea Market.
CITY:St. Charles
Kate Spade has hand bags, small goods, paper goods, shoes, baby bags, luggage, glasses, beauty products, jewelry. Jack Spade has work and travel bags, small goods.


text:
IAtv Mixtape vol. II
Posted in: Sessions
What? You're already releasing another mixtape? Have there even been enough IAtv Sessions this year to make up a proper mixtape? I'm Ron...
Intl. Tour Management with Ben Gram and Ben Hoffman
Posted in: Profiles
We talk to international tour managers Ben Hoffman and Ben Gram about declaring merchandise at customs, what your financial goals should be, securing...
Company of Thieves (Chicago)
Posted in: Sessions
Anyone who's read the story of Company of Thieves as told by lead singer Genevieve Schatz has an idea of the adversity she's overcome throughout...
O'Brother (Atlanta)
Posted in: Sessions
There was once a time when music business deals were made for the sake of music, not the other way around. When people worked predominantly on their...
Adam Arcuragi & The Lupine Chorale Society
Posted in: Sessions
Think of all the ways your favorite musicians try to break down the barrier between them and you. Some emphasize their personality in Direct to Fan...
Mirror Lady (Los Angeles)
Posted in: Sessions
Watch Mirror Lady play unreleased track "For Good" live on Indie Ambassador TV. Raybans and brightly colored thrift-store sweaters and miniature...
The Need to be Heard
Posted in: Panels
This month, Ryan, along with panelists Michael Creamer (manager), Will Daily (musician), and David Day (editor), talked about topics like when to...
The Winter Sounds (Nashville)
Posted in: Sessions
"You gotta start crazy and then it's easy." One would expect that quote to come from the mouth of a deranged person, and probably expect it to...

subset: commoncrawl


text:
You are searching for
Car Detailing for Sale in Chullora, New South Wales 2190
Seven Hills NSW - Western Sydney
Car Wash Seven Hills
Hand Car Wash – Western Suburbs Price $680,000 - negotiable Profit $300,000 including wages to 1 owner and after add backs Turn Over $810,000 p/a ...
Car WashCar/Bus/Truck
Blacktown NSW - Western Sydney
Jim's Car Cleaning Blacktown
When you think about Jim's Car Cleaning don't get fooled into thinking that we only clean cars, in fact we clean cars, trucks, boats, and caravans and in...
Warren Muller
St Marys NSW - Western Sydney
$350,000 - $550,000
Premium Hand Car Wash & Detailing Franchise Opportunity - Global Brand - Proven Business Model In...
Note: Greenfield location in cooperation with BP service station. Images used are from an existing store in Joondalup WA. This is not a trading store that we...
Penrith NSW - Western Sydney
NEW Exclusive Territory - Nanotek Car Cleaning Sydney South-West
Price: $47,400 + Car Lease. Inc. Startup package. Nanotek Car Cleaning makes owning your own business an attainable dream, with low upfront investment, no le...
Brent May
Richmond NSW - Western Sydney
NEW Exclusive Territory – Nanotek Car Cleaning Richmond
Be the first to own & operate a Nanotek franchise business in Richmond Be part of Nanotek, the pioneers and industry leaders of mobile car cleaning, renowned...
Warren Muller


text:
Kyocera works with an ecosystem of partners to design, optimise, and service complete document solutions for our customers. Through our national network of authorised independent dealers, customers receive expert consultation on products and technology, coordinated product installations and support with a proven service network focused on assuring maximum uptime for Kyocera document solutions.
Why Become a Kyocera Authorised Dealer?
If you would like to find out more about becoming a Kyocera Authorised Dealer please email your details to
Please enter suburb/town or post code


text:
by Susan Martin,, Published January 20, 2011
Good morning, Students, Faculty and Staff,
Many of you may have watched or heard Governor Rick Snyder's inaugural State of the State last evening. His statements were clear -- reinventing Michigan will require a period of adjustment and sacrifice. With the state facing a budget deficit of $1.8 billion, the Governor challenged the people, businesses and institutions of this state with a message that the old way of doing things in Lansing will not solve the budget deficit and he will look to bold restructuring to erase the deficit and prepare Michigan for a more dynamic and prosperous future. It is clear from the State of the State presentation last night, and from a meeting I attended with Governor Snyder and Michigan's other 14 university presidents last week, that as every rock is overturned in a search of budget restraint, higher education will be considered in the mix. Yet, it is also clear that Governor Snyder is a Michigander who was schooled at public universities and values education and hopes to be able to reinvest in education in the future. I hosted the Education Policy Roundtable for Governor Snyder in October 2010 at the Student Center where he engaged in a dialogue with leaders in education from P-20 and those leaders provided numerous recommendations for him to consider in education policy that can advance education in this state. Governor Snyder told the public university presidents that he considers us a "world-class asset" and that universities are usually behind entrepreneurs and INNOVATION that he consistently trumpets as necessary for reinvigorating Michigan's economy.
With this new future ahead of us that includes tighter budgets and the anticipation of less funding from government, at least in the near term, it is important that Eastern maintain the positive growth and momentum we have achieved over the last two years. This includes the enrollment growth of nearly 7 percent as we emphasize the accessibility, affordability and quality of the university; investment in new faculty -- 46 last year and 30 searches this year; and the revitalization of campus as evidenced by the new addition to the Science Complex and the dynamic learning environment we have created, improvements to residence halls and grounds, and the complete overhaul and renovation of our largest classroom building, Pray-Harrold, to be completed this fall. From 2008-2009 through 2010-2011 to date we have grown by 6.8 percent recovering over 35,000 of the 56,323 credit hours that were lost from 2003-2004 to 2008-2009. The "Invest.Inspire." capital campaign has already raised $39.7 million toward our $50 million goal and we will need the financial support of the Eastern family of alumni, faculty, staff and friends to afford the investments we need to make in more financial aid for our students, supporting our faculty, renovation of our facilities and to assure our future is bright.
It is important that as we address a future that may include further state funding reductions for all of Michigan's universities, we at Eastern determine priorities that will allow our growth and success to continue. This will require a more comprehensive strategic planning process to identify our key growth areas and guide our reinvestment to our strengths. We can not afford to sit back and wait for change to come to us, to be forced on us. We must lead the change through effective planning and dialogue with all of our key partners -- students, faculty, staff, alumni and friends. We must maintain the strong excitement and morale that brought over 1,700 of us together to form the "big fat zero" http://www.emich.edu/zero/images/Big_Fat_Zero_Final.jpg cheering Eastern's bold leadership in these extraordinarily difficult economic times. School spirit and pride never mattered more than now. GO EASTERN!
The Lumina Foundation for Education, an Indianapolis-based, private, independent foundation that strives to help people achieve their potential by expanding access to and success in education beyond high school, has developed a phrase I like that describes the restructuring that must take place. Lumina calls it "the new normal," and recently unveiled a project titled "Navigating the New Normal" http://www.luminafoundation.org/publications/Navigating_the_new_normal.pdf. I encourage you to read the report. Lumina's goal is to increase the percentage of Americans who hold high-quality degrees and credentials to 60 percent by 2025. Michigan currently stands at 35.6 percent of adults with college degrees -- 34th in the nation. We will need to graduate an additional 1,322,257 degrees, 9,722 each year between now and 2025, to reach Lumina's goal of 60 percent. Lumina also poses productivity measures and metrics to reward increases in degrees and certificates awarded, to support lower costs per degree without sacrificing access or equity, and to ensure that academic quality continues to improve.
I remain committed to accountability and transparency. We will be discussing and working together to step up to the challenges of "the new normal" this semester and through the next year. We plan to host additional budget forums and communications opportunities to share and discuss the challenges ahead and our efforts to lead the change and to get your ideas, input and support. While it will require some difficult conversations, we will strive to do so in a positive way that allows us to continue to build on the positive momentum we have established in the past two years and move this great historic public university forward.
Thank you, and as always you can reach me at email@example.com.
Sue Martin
Eastern Michigan University, Education First
Ypsilanti, MI, USA 48197 | 734.487.1849
Non-Discrimination Statement | Report a Title IX incident
Privacy Statement | Copyright © 2017


text:
Introductiontuberculosis (TB), contagious, wasting disease caused by any of several mycobacteria. The most common form of the disease is tuberculosis of the lungs (pulmonary consumption, or phthisis), but the intestines, bones and joints, the skin, and the genitourinary, lymphatic, and nervous systems may also be affected.
There are three major types of tubercle bacilli that affect humans. The human type ( Mycobacterium tuberculosis ), first identified in 1882 by Robert Koch, is spread by people themselves. It is the most common one. The bovine type ( M. bovis ) is spread by infected cattle but is no longer a threat in areas where pasteurization of milk and the health of cattle are strictly supervised. The avian type ( M. avis ) is carried by infected birds but can occur in humans. The tubercle bacillus can live for a considerable period of time in air or dust. The most common means of acquiring the disease is by inhalation of respiratory droplets.
Sections in this article:
The Columbia Electronic Encyclopedia, 6th ed. Copyright © 2012, Columbia University Press. All rights reserved.
See more Encyclopedia articles on: Pathology


text:
Community Information Database
You are browsing the Subject Heading Teenage Parents which contains 5 record(s)
- Adolescent Family Life Program
Adolescent Family Life Program (AFLP) Public Health Nurses provide home visits to pregnant and parenting teenagers, male and female, under age 19 and their children. Services include assessments, edu... [Full Record] [Add to Personal List]
- Alternative Education Programs Santa Cruz County Office of Education
Our programs are student-centered and adapt to meet individual needs. Staff works collaboratively to facilitate learning and change. Dedicated teachers assist students to achieve social, interpersonal... [Full Record] [Add to Personal List]
- Parents Center
Professional counseling in a confidential atmosphere for parents and children. Parenting skill classes according to a court approved curriculum. Education and counseling for prevention of child abuse ... [Full Record] [Add to Personal List]
- Walnut Avenue Women's Center
The Walnut Avenue Women's Center is a 501(c)3 public benefit organization dedicated to improving the quality of life for women, children, and families in the local community for over 75 years. Th... [Full Record] [Add to Personal List]
- Watsonville Community School TAP Program
The purpose of this program is to enable teen parents to attend and graduate from high school as parents of young children. It is a priority of this program to teach parenting through modeling and ins... [Full Record]


text:
Business in brief
Paul Queally, co-president of Welsh, Carson, Anderson & Stowe, will moderate a panel of Wall Street executives at "The Word from Wall Street" Nov. 29 at 6 p.m. at University of Richmond's Queally Hall, Ukrop Auditorium. Queally received a bachelor of arts degree from Richmond in 1986 and an M.B.A. from Columbia Business School in 1990. He was a general partner at The Sprout Group, Donaldson, Lufkin & Jenrette's private equity group prior to joining Welsh, Carson, Anderson & Stowe in 1996. "The Word from Wall Street" is a featured event in the Robins Executive Speaker Series. The program is free and open to the public, but registration is required at http://tinyurl.com/d93jr5p.
nTelos Wireless has activated a new cell site near the intersection of Staples Mill Road and Hilliard Road, which will enhance coverage for areas that include the Dumbarton Business Center, Staples Mill Amtrak Station, Hermitage Court and Eldridge Grove. The new cell site was activated Sept. 26.
Virginia Home for Boys and Girls (VHBG) recently hired Stacey Williams as admissions coordinator and Deborah Cooper as program coordinator/assistant principal. Monica Manns was promoted to vice president of education. VHBG Teaching-Parents Erin Lawrence and Wakeena Corbin received Teaching-Family Association (TFA) Certification. TFA Certification indicates a high level of satisfaction with the quality of the Teaching-Parent's services from their youth, community stakeholders and administration. Monika Mitchell received her second certification. Wakeena Corbin also has been given the Teaching-Family Distinguished Service Award from the TFA. The annual award honors Teaching-Parents who distinguish themselves by their commitment to helping youth and families reach their greatest potential.
Long & Foster Real Estate, Inc. has appointed Diane Lewis, a sales associate with its Short Pump office, to serve on the board of directors of EarthCraft Virginia, an organization whose mission is to advance sustainable, affordable, resource and energy efficient construction through education and technical support. She will serve on the board's education committee.
Dreama Nuttycombe has joined Long & Foster Real Estate, Inc. in their Short Pump office. She has been a real estate professional for eight years.
FeedMore's board of directors has named Douglas "Doug" Pick as chief executive officer effective Nov. 26. Pick succeeds Fay Lohr, who is retiring after serving as president and CEO of FeedMore since 2008 and, prior to that, as president of the Central Virginia Food Bank from 1998 to 2008.
Nancy Bagranoff, dean of University of Richmond's Robins School of Business, has been named to the American Institute of CPAs board of directors. AICPA is the national professional organization of more than 370,000 CPAs, setting generally accepted professional and technical standards and providing credentialing, technical support and other services to its members. From 2009-10, Bagranoff was president of the American Accounting Association. She holds a doctorate of business administration from George Washington University, a master's of accounting from Syracuse University and undergraduate degrees from The Ohio State University and Briarcliff College.
Martin's Food Markets and Giant Food Stores recently donated a total of nearly $37,000 to four organizations in Virginia, Maryland and Pennsylvania that support breast cancer research, education and awareness. Locally, the stores donated $8,250 to VCU's Massey Cancer Center in Richmond. The donations were made in honor of Breast Cancer Awareness Month, celebrated in October. During October, the stores sold pink reusable shopping bags to customers, who purchased 64,000 of them. Martin's and Giant donated a portion of those sales to the four organizations.
Agents with Cushman & Wakefield | Thalhimer recently handled the following lease negotiations: Southern Wine and Spirits of America, Inc. – leased 11,800 square feet in Windsor Business Park II at 8570 Magellan Pkwy.; Environmental Alliance, Inc. – leased 3,268 square feet in Staples Mill Business Center at 8201-8261 Hermitage Rd.; Insurance Doctor – leased 3,150 square feet at 7111 W. Broad St.; Dominion Sign – renewed its lease of 2,992 square feet in Gaskins Centre at 3801-3827 Gaskins Rd.; School of Rock Short Pump – leased 2,602 square feet in Short Pump Commons at 4300 Pouncey Tract Rd.; Cardinal Camera Video Center, Inc. – leased 2,330 square feet in The Shoppes at Westgate at 11709 W. Broad St.; Dragon's Den, LLC – leased 1,776 square feet in the IceZone Retail Shops at Downtown Short Pump, 4350 Pouncey Tract Rd.; James Ringer Insurance – leased 1,713 square feet in Westerre Commons Office Condos at 3701-3781 Westerre Pkwy.; OptimumEd – leased 1,517 square feet in Interchange Corporate Center at 3850 Gaskins Rd.; Effex Management Solutions – leased 1,400 square feet in East Towne Plaza at 2925 Williamsburg Rd.; and The Hair Institute – leased 1,215 square feet at 8503 Patterson Ave.
Villa's Flagler Housing wins national NAEH award
St. Joseph's Villa's Flagler Housing & Homeless Services was one of three entities to earn the National Alliance to End Homelessness' Champion of Change Award. The awards were presented Nov. 17 during a ceremony at the Newseum in Washington, D.C.
NAEH annually recognizes proven programs and significant achievements in ending child and family homelessness.
Flagler completed its transition from an on-campus shelter to the community-based model of rapid rehousing in 2013, and it was one of the nation's first rapid re-housing service providers to be certified by NAEH. > Read more.
RIR's Christmas tree lighting rescheduled for Dec. 12
Richmond International Raceway's 13th annual Community Christmas tree lighting has been rescheduled from Dec. 6 to Monday, Dec. 12, at 6:30 p.m., due to inclement weather expected on the original date.
Entertainment Dec. 12 will be provided by the Laburnum Elementary School choir and the Henrico High School Mighty Marching Warriors band. Tree decorations crafted by students from Laburnum Elementary School and L. Douglas Wilder Middle School will be on display. Hot chocolate and cookies will be supplied by the Henrico High School football boosters. > Read more.
Weekend Top 10
Given the warm weather lately, Saturday's RVA Polar Plunge Winter Fest, benefiting Special Olympics Virginia, might actually be enjoyable! Other weekend events you're sure to enjoy include the 14th annual Richmond Kids Expo at the Richmond Raceway Complex, the Richmond Symphony and The Taters in concert at The Cultural Arts Center at Glen Allen, and the Richmond Ballet Minds in Motion Team XXL performing at the Henrico Theatre. This is also the last weekend to check out HATTheatre's production of "Bill W. and Dr. Bob." For all our top picks this weekend, click here! > Read more.
ACLU urges McAuliffe to veto anti-immigration bills
Business in brief
Henrico to offer free workshop March 4 on outdoor improvements
Governor signs bills to fight Virginia's opioid crisis
New law lets concession stands sell cans of beer
- More News
Print Edition
Feb. 16, 2017Click here to read the print edition.
Weekend Top 10
Restaurant Watch
Weekend Top 10
American Tap Room at Willow Lawn closes
Weekend Top 10
- More Entertainment
Obituary – Anne Faudree Barker
Obituary – Jonathan "Bryan" Lawler
Obituary – Jean Harless
- More Obituaries
Villa's Flagler Housing wins national NAEH award
RIR's Christmas tree lighting rescheduled for Dec. 12
project:HOMES' 'Renew Crew' helps Henrico citizen
- More Community
Thanksgiving, Part II
Storm woes for e-junkies
'Good old days' were good – for some
- More Opinions
RIR to admit children 12 and younger free next year
Henrico teams win district, state championships
Varina HS to host Basketball Skill Development Academy June 20-23
- More Sports
|February 2017
Place an Ad | More Classifieds
CalendarThe Modlin Center for the Arts at the University of Richmond will screen NT Live's broadcast of "Amadeus" at 2 p.m. Feb. 2 and at 7 p.m. Feb. 9 in Camp Concert Hall, Booker Hall of Music. Lucian Msamati (Luther, Game of Thrones, NT Live: The Comedy of Errors) plays Salieri in Peter Shaffer's iconic play. NT Live brings the best of British theatre direct from the stages of London to movie theatres around the world. Tickets are $7 to $14. For details, call 289-8980 or visit http://www.modlin.richmond.edu. Full text


text:
Mississippi Small Business
Development Center
Development Center
Helping to Transform Mississippi One Small Business at a Time.
Northwest MS Community College
DeSoto Center
DeSoto County
This seminar is designed to assist in preparing a business plan. The parts of the business plan are explained, i. e., industry research, description of company products and services, identifying target customer groups and a marketing plan. Financial components discussed are startup costs and preparation of a projected cash flow statement. To register by telephone, please call 1-800-725-7232 (for MS area codes only).
Speaker(s): Rick Schneider
Fee: Free
5197 W.E. Ross Parkway
Southaven, MS 38671


text:
Paul Brown
Paul Brown Hawaii AKI Styling Iron
- Overview
- Features
- Adjustable temperature control ranges from 180 degrees F to 450 degrees F to maintain optimum heat levels for all hair types
- Engineered curved-edge design for styling flexibility
- Effortlessly create sleek hair styles or add beautiful waves and curls
- Anti-slip, ultra-smooth plates capture even the thickest of hair and prevent strands from slipping outside
- Highest-quality tourmaline plates do away with static and eliminate frizz
- 30-second heating time provides a quicker heat recovery for consistent styling results
- Professional 9-foot rotating swivel cord prevents tangling
- On-Off switch located inside iron avoids accidental shut off
- About the brand
Paul Brown Hawaii AKI Styling Iron features top grade 1" plates ideal for all hair types.
Paul Brown is a world renowned master hairstylist, salon owner, educator and businessman. Driven by his long experience and knowledge of hair styling and also due to his love of Hawaii, Paul took local Hawaiian plant and sea essences and in 1989 adapted them into exclusive shampoos, conditioners and other beauty products versatile for all hair types, in all cultures.
Great Iron!I love this iron! It is really light weight and so cute! I really like the tattoo design, but more importantly than looking good, it works great! I can curl and straighten with it. It heats up very quickly and the pouch is great for when I travel! Highly recommended!!
Was this review helpful?(Yes)(No)Jul 27, 2013Kat / HawaiiHAIR TYPEBlonde / Color Treated Hair / Fine or Thin Hair
Use this link to send this review via e-mail, Twitter, or to post to your blog.Close
Aero-dynamic ToolSuper light iron and the design is beautiful, but more importantly I like how it feels in my hands -- super light. Glides through my super coarse Asian hair. I judge an iron by how my hair looks and feels after using it. A lot of iron leaves my hair feeling dry. This one left my hair soft and shiny. I like, too, the five years warranty. I figure, what do I have to lose?
Was this review helpful?(Yes)(No)Apr 18, 2013Thuy H. / San FranciscoHAIR TYPEBlack / Thick or Coarse Hair
Use this link to send this review via e-mail, Twitter, or to post to your blog.Close


text:
Topic: Foreign Key issue... Need help once again!
I am currently attempting to implement the AJAX-driven table listing as described in http://dev.nozav.org/rails_ajax_table.html .
I am having a bit of an issue, however. I am trying to list jobs. One of the parameters of a job, however, is a customer. Now a customer is part of a location, which is related to a job in the following way:
class Job < ActiveRecord::Base
belongs_to :location
has_and_belongs_to_many :crewmembers
has_many :items
class Location < ActiveRecord::Base
has_many :jobs
customer is a column in the location table.
Now I am running into a problem:
In the list action in the job_controller, there is a section:
jobs_per_page = 20
sort = case @params['sort']
when "customer" then "customer"
when "customer_reverse" then "customer DESC"
when "date" then "date"
when "date_reverse" then "date DESC"
conditions = ["customer LIKE ?", "%#{@params[:query]}%"] unless @params[:query].nil?
@total = Job.count(:conditions => conditions)
@jobs_pages, @jobs = paginate :jobs, :order => sort, :conditions => conditions, :per_page => jobs_per_page
Conditions is fetching a customer name from a query box.
Now, this would work ok, if the customer was part of the job table. Problem is, a customer is part of a location. So, how can I modify the count arguments to fetch from the location table, via a foreign key? I need a final query like, "SELECT COUNT(*) AS count_all FROM jobs, locations WHERE locations.id = jobs.location_id"
In addition, the paginate command fails as well, based on this foreign key problem.
Now, I would assume I need to use a joins argument somewhere. But where, and how? I am going bald from all the hair I've pulled out in the last couple of hours...
P.S. Sorry for always begging for help here...
Last edited by Caydel (2006-07-26 10:27:30)


text:
I believe I've read the same theory.
One of my friends told me about it and sent me the link to it. When I read I was just amazed by the amount of thought the guy put into it and the great evidence he stated.
The theory is here. It's a bit of a long read, but very interesting.


text:
Skip to comments.Texas BBQ Brisket, step by step(Vanity w/ PICS)
Posted on 08/07/2008 11:21:18 AM PDT by eastforker
Texas style BBQ, for all to enjoy. Step by step pics included, start to finish.
Thanks a lot, east. I drooled all over my keyboard.
Dude...there is no upside to this thread...we can't eat the finished product....and will be left to drool.
Legends of Texas Barbecue Cookbook: Recipes and Recollections from the Pit Bosses by Robb Walsh
my mouth is watering. time for another meeting where soggy deli sandwiches are served.
Maybe after 130 years my family roots link to the State of Texas has somewhat worn off. I prefer a sweet but tangy dark and rich sauce on my ribs. And the beer to wash it down - Guinness Stout
You realize of course that those "ingredients" are not available everywhere.
I am going to forward this to my son in California! He is a huge BBQ fan and loves to cook for his girl friend.
Thank you for all the work you did to share this with
us FReepers. :)
But all in all, if I was offered a plate of what you fixed up, I'd be drooling all the way to the ice chest with the beer in it.
You can get the same or better results with a lot less effort on a ceramic grill like a Big Green Egg. I prefer a dry rub but that is a minor difference. Cook till 190 Deg internal. OH ya!
"Maybe after 130 years my family roots link to the State of Texas has somewhat worn off."
But, Ill give your recipe a try. My beer though.
Great thread. Bookmarked for when I give this a shot. Thank you for posting it.
Nah. What was I thinking?
I'll wait 'til November. ;-)
ha ha ha ha ha ha ha ha ha ...
stop it, you're killin' me
I've had eastforker's barbecue a few times. It is the schizzle, yo.
Drool Central.
Thank you, sorry I haven't replied to everyone, this is real time and I been kinda busy.
~FR BBQ Ping~
When I was a young kid and through my middle years I used to go fishing a lot in the highland lakes of Texas—up near the German settlements.
Alongside the roads you'd find bar-b-q pits that were really dug into the ground. They used native oak and mesquite for the smoking. There wasn't any building other tham maybe a tin roofed shed with a wooden table or two.
The meat they used wasn't brisket (that was considered too cheap/tough a cut) but big old porterhouse or T-bone steak cuts. Or you could get some German venison sausage or a half chicken. They made some damn fine sauce that they dabbed on it while cooking and it was wonderful stuff.
You pointed out your cut and it was served on a sheet of butcher paper. If you wanted 'sides' you got a whole Bermuda onion, a loaf of bread, and a huge Dill pickle. That was it for the food.
They had a washtub full of beer and and one for soft drinks for the kids.
The last stand like that that I saw was about 1972 in Mason Texas. God, I can taste that bar-b-q now and wish I was back in the German Hillcountry.
Ditto on the drool central. His BBQ is the best! YUM, makes the salad I'm having for lunch really, really wilt!
Hey Allegra, how are you doing?
Hi Rika! I'm fine. How are you doing? Long time, no see!
If it makes you feel any better, I had a salad and a bunch of grapes for dinner. :-p
Yummy! I'm sssoooo hungry.
What do you have against hickory? And for the record, I ate like a pig at lunch today and this thread already has me hungry again.
This thread rules.
I love how this thread is in real-time. You should totally make this a regular thread. "Real-time barbecue with Eastforker", and do a different meat each week.
I betcha they could do a beer-can chicken pretty easily.
I've been all over the South-there's good BBQ everywhere-but the best is in TX. Not just the brisket, but the sausage-even the cabrito. In fact I think Texas and Louisiana are the 2 best states in the Union to put on the feedbag. Thanks for the post.
This Texan is doing a little different these days...
Try a shoulder clod. Inject with "Cajun Injector" brand "Roasted Garlic & Herb" injectable maranade. Dust the outside with "Slap Your Mama" cajun spice mix. Wrap in a double layer of BBQ foil with a sliced sweet onion and some minced garlic.
About three hours at 325ºf then open & smoke for another thirty minutes... cuts with a fork!
I've done a bunch of briskets like your's... lose the Accent and replace the Dale's with Lea & Perrin's... ;^)
The shoulder clod cut does give a better cost per pound of lean cooked than brisket...
This is one FR clique I'm really jealous I'm not a part of.
Brisket bump.
I have had shoulder clod, they are good but does not have the taste of brisket. It is so amazing the different taste of differnt cuts all from the same cow. The most horrible thing I have ever seen is BBQ swimming in sauce. I like a very lite brush if any at all on any of my meats. When done, this brisket will be extremely tender and moist. BTW Just got done eating some of the chicken, nothing like it hot off the grill.
Can't participate right now, I'm BBQ deprived and hungry. Looking at that brisket is making me cranky.
You might enjoy the thread. Somewhere I've got a receipe for large groups which starts, with a backhoe, dig a trench 3x3 feet, 30 feet long.
I prefer to smoke my meats @ 225-250.
Takes a bit longer, but worth the extra time.
My smoker also holds water so the meats are always extremely moist.
It does look good!
Swimming in sauce... SWEET sauce! That's why I never liked the KC & Memphis styles
PS. I'm hosting Christmas this year and plan on slow-smoking at least 2 7-rib Prime Rib-Roast.
This was just last month right before I left home. Eastforker is featured prominently in this one as well.
Oh...and we pigged out on one of his briskets that day.
Disclaimer: Opinions posted on Free Republic are those of the individual posters and do not necessarily represent the opinion of Free Republic or its management. All materials posted herein are protected by copyright law and the exemption for fair use of copyrighted works.


text:
MySQL supports the following
JOIN syntaxes
for the
table_references part of
SELECT statements and
DELETE and
UPDATE statements:
escaped_table_reference] ...
table_reference| { OJ
tbl_name[PARTITION (
partition_names)] [[AS]
alias] [
index_hint_list] |
alias| (
table_reference[INNER | CROSS] JOIN
join_condition] |
table_reference{LEFT|RIGHT} [OUTER] JOIN
table_referenceNATURAL [{LEFT|RIGHT} [OUTER]] JOIN
join_condition: ON
conditional_expr| USING (
index_hint] ...
index_hint: USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] ([
index_list]) | IGNORE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (
index_list) | FORCE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (
index_name] ...
A table reference is also known as a join expression.
A table reference (when it refers to a partitioned table) may
contain a
PARTITION option, including a
comma-separated list of partitions, subpartitions, or both. This
option follows the name of the table and precedes any alias
declaration. The effect of this option is that rows are selected
only from the listed partitions or subpartitions—in other
words, any partitions or subpartitions not named in the list are
ignored For more information, see
Section 21.5, "Partition Selection".
The syntax of
table_factor is
extended in comparison with the SQL Standard. The latter accepts
table_reference, not a list of
them inside a pair of parentheses.
This is a conservative extension if we consider each comma in a
list of
table_reference items as
equivalent to an inner join. For example:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
is equivalent to:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
In MySQL,
JOIN, and
INNER JOIN are syntactic
equivalents (they can replace each other). In standard SQL, they
are not equivalent.
INNER JOIN is used with
ON clause,
is used otherwise.
In general, parentheses can be ignored in join expressions containing only inner join operations. MySQL also supports nested joins (see Section 184.108.40.206, "Nested Join Optimization").
Index hints can be specified to affect how the MySQL optimizer makes use of indexes. For more information, see Section 9.9.4, "Index Hints".
The following list describes general factors to take into account when writing joins.
A table reference can be aliased using
tbl_name alias_name:
SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
table_subqueryis also known as a subquery in the
FROMclause. Such subqueries must include an alias to give the subquery result a table name. A trivial example follows; see also Section 126.96.36.199, "Derived Tables (Subqueries in the FROM Clause)".
SELECT * FROM (SELECT 1, 2, 3) AS t1;
INNER JOINand
,(comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).
However, the precedence of the comma operator is less than that of
LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form
Unknown column 'may occur. Information about dealing with this problem is given later in this section.
col_name' in 'on clause'
conditional_exprused with
ONis any conditional expression of the form that can be used in a
WHEREclause. Generally, you should use the
ONclause for conditions that specify how to join tables, and the
WHEREclause to restrict which rows you want in the result set.
If there is no matching row for the right table in the
USINGpart in a
LEFT JOIN, a row with all columns set to
NULLis used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:
SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;
This example finds all rows in
left_tblwith an
idvalue that is not present in
right_tbl(that is, all rows in
left_tblwith no corresponding row in
right_tbl). See Section 188.8.131.52, "Left Join and Right Join Optimization".
USING(clause names a list of columns that must exist in both tables. If tables
bboth contain columns
c2, and
c3, the following join compares corresponding columns from the two tables:
a LEFT JOIN b USING (c1,c2,c3)
NATURAL [LEFT] JOINof two tables is defined to be semantically equivalent to an
INNER JOINor a
LEFT JOINwith a
USINGclause that names all columns that exist in both tables.
RIGHT JOINworks analogously to
LEFT JOIN. To keep code portable across databases, it is recommended that you use
LEFT JOINinstead of
{ OJ ... }syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.
SELECT left_tbl.* FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id } WHERE right_tbl.id IS NULL;
You can use other types of joins within
{ OJ ... }, such as
INNER JOINor
RIGHT OUTER JOIN. This helps with compatibility with some third-party applications, but is not official ODBC syntax.
STRAIGHT_JOINis similar to
JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.
Some join examples:
SELECT * FROM table1, table2; SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;
Join Processing Changes in MySQL 5.0.12
Natural joins and joins with
including outer join variants, are processed according to the
SQL:2003 standard. The goal was to align the syntax and
semantics of MySQL with respect to
JOIN and
JOIN ... USING according
to SQL:2003. However, these changes in join processing can
result in different output columns for some joins. Also, some
queries that appeared to work correctly in older versions
(prior to 5.0.12) must be rewritten to comply with the
These changes have five main aspects:
The way that MySQL determines the result columns of
USINGjoin operations (and thus the result of the entire
Expansion of
SELECT *and
SELECTinto a list of selected columns.
Resolution of column names in
Transformation of
USINGjoins into
Resolution of column names in the
ONcondition of a
The following list provides more detail about several effects of current join processing versus join processing in older versions. The term "previously" means "prior to MySQL 5.0.12."
The columns of a
NATURALjoin or a
USINGjoin may be different from previously. Specifically, redundant output columns no longer appear, and the order of columns for
SELECT *expansion may be different from before.
Consider this set of statements:
CREATE TABLE t1 (i INT, j INT); CREATE TABLE t2 (k INT, j INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); SELECT * FROM t1 NATURAL JOIN t2; SELECT * FROM t1 JOIN t2 USING (j);
Previously, the statements produced this output:
+------+------+------+------+ | i | j | k | j | +------+------+------+------+ | 1 | 1 | 1 | 1 | +------+------+------+------+ +------+------+------+------+ | i | j | k | j | +------+------+------+------+ | 1 | 1 | 1 | 1 | +------+------+------+------+
In the first
SELECTstatement, column
jappears in both tables and thus becomes a join column, so, according to standard SQL, it should appear only once in the output, not twice. Similarly, in the second SELECT statement, column
jis named in the
USINGclause and should appear only once in the output, not twice. But in both cases, the redundant column is not eliminated. Also, the order of the columns is not correct according to standard SQL.
Now the statements produce this output:
+------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+
The redundant column is eliminated and the column order is correct according to standard SQL:
First, coalesced common columns of the two joined tables, in the order in which they occur in the first table
Second, columns unique to the first table, in order in which they occur in that table
Third, columns unique to the second table, in order in which they occur in that table
The single result column that replaces two common columns is defined using the coalesce operation. That is, for two
t2.athe resulting single join column
ais defined as
a = COALESCE(t1.a, t2.a), where:
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
If the join operation is any other join, the result columns of the join consists of the concatenation of all columns of the joined tables. This is the same as previously.
A consequence of the definition of coalesced columns is that, for outer joins, the coalesced column contains the value of the non-
NULLcolumn if one of the two columns is always
NULL. If neither or both columns are
NULL, both common columns have the same value, so it doesn't matter which one is chosen as the value of the coalesced column. A simple way to interpret this is to consider that a coalesced column of an outer join is represented by the common column of the inner table of a
JOIN. Suppose that the tables
t2(a,c)have the following contents:
t1 t2 ---- ---- 1 x 2 z 2 y 3 w
SELECT * FROM t1 NATURAL LEFT JOIN t2;+------+------+------+ | a | b | c | +------+------+------+ | 1 | x | NULL | | 2 | y | z | +------+------+------+
Here column
acontains the values of
SELECT * FROM t1 NATURAL RIGHT JOIN t2;+------+------+------+ | a | c | b | +------+------+------+ | 2 | z | y | | 3 | w | NULL | +------+------+------+
Here column
acontains the values of
Compare these results to the otherwise equivalent queries with
SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);+------+------+------+------+ | a | b | a | c | +------+------+------+------+ | 1 | x | NULL | NULL | | 2 | y | 2 | z | +------+------+------+------+
SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);+------+------+------+------+ | a | b | a | c | +------+------+------+------+ | 2 | y | 2 | z | | NULL | NULL | 3 | w | +------+------+------+------+
Previously, a
USINGclause could be rewritten as an
ONclause that compares corresponding columns. For example, the following two clauses were semantically identical:
a LEFT JOIN b USING (c1,c2,c3) a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
Now the two clauses no longer are quite the same:
With respect to determining which rows satisfy the join condition, both joins remain semantically identical.
With respect to determining which columns to display for
SELECT *expansion, the two joins are not semantically identical. The
USINGjoin selects the coalesced value of corresponding columns, whereas the
ONjoin selects all columns from all tables. For the preceding
SELECT *selects these values:
COALESCE(a.c1,b.c1), COALESCE(a.c2,b.c2), COALESCE(a.c3,b.c3)
For the
SELECT *selects these values:
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
With an inner join,
COALESCE(a.c1,b.c1)is the same as either
b.c1because both columns will have the same value. With an outer join (such as
LEFT JOIN), one of the two columns can be
NULL. That column will be omitted from the result.
The evaluation of multi-way natural joins differs in a very important way that affects the result of
USINGjoins and that can require query rewriting. Suppose that you have three tables
t2(c,b), and
t3(a,c)that each have one row:
t2(10,2), and
t3(7,10). Suppose also that you have this
NATURAL JOINon the three tables:
SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
Previously, the left operand of the second join was considered to be
t2, whereas it should be the nested join
(t1 NATURAL JOIN t2). As a result, the columns of
t3are checked for common columns only in
t2, and, if
t3has common columns with
t1, these columns are not used as equi-join columns. Thus, previously, the preceding query was transformed to the following equi-join:
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c;
That join is missing one more equi-join predicate
(t1.a = t3.a). As a result, it produces one row, not the empty result that it should. The correct equivalent query is this:
SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;
If you require the same query result in current versions of MySQL as in older versions, rewrite the natural join as the first equi-join.
Previously, the comma operator (
,) and
JOINboth had the same precedence, so the join expression
t1, t2 JOIN t3was interpreted as
((t1, t2) JOIN t3). Now
JOINhas higher precedence, so the expression is interpreted as
(t1, (t2 JOIN t3)). This change affects statements that use an
ONclause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.
CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); INSERT INTO t3 VALUES(1,1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
Previously, the
SELECTwas legal due to the implicit grouping of
(t1,t2). Now the
JOINtakes precedence, so the operands for the
ONclause are
t3. Because
t1.i1is not a column in either of the operands, the result is an
Unknown column 't1.i1' in 'on clause'error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the
ONclause are
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
Alternatively, avoid the use of the comma operator and use
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
This change also applies to statements that mix the comma operator with
LEFT JOIN, and
RIGHT JOIN, all of which now have higher precedence than the comma operator.
Previously, the
ONclause could refer to columns in tables named to its right. Now an
ONclause can refer only to its operands.
CREATE TABLE t1 (i1 INT); CREATE TABLE t2 (i2 INT); CREATE TABLE t3 (i3 INT); SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
Previously, the
SELECTstatement was legal. Now the statement fails with an
Unknown column 'i3' in 'on clause'error because
i3is a column in
t3, which is not an operand of the
ONclause. The statement should be rewritten as follows:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
Resolution of column names in
USINGjoins is different than previously. For column names that are outside the
FROMclause, MySQL now handles a superset of the queries compared to previously. That is, in cases when MySQL formerly issued an error that some column is ambiguous, the query now is handled correctly. This is due to the fact that MySQL now treats the common columns of
USINGjoins as a single column, so when a query refers to such columns, the query compiler does not consider them as ambiguous.
SELECT * FROM t1 NATURAL JOIN t2 WHERE b > 1;
Previously, this query would produce an error
ERROR 1052 (23000): Column 'b' in where clause is ambiguous. Now the query produces the correct result:
+------+------+------+ | b | c | y | +------+------+------+ | 4 | 2 | 3 | +------+------+------+
One extension of MySQL compared to the SQL:2003 standard is that MySQL enables you to qualify the common (coalesced) columns of
USINGjoins (just as previously), while the standard disallows that.
(Background: This database is used to keep track of scores for students in my classes.)
So in this case, I have three tables, one has student's "codename" (as posting their real name on the web is a no-no) and an index (there is more data in this table, but this is all you really need to know.) Then there's a table with the assignments, containing the assignment name, and an index for each assignment. Finally, there is a scores table, which has for each paper I get turned in, a student_id (releated to the student index) an act_id (related to the assignments index) and a score.
It looked something like this:
students table:
+----+---------------+assignments table:
| id | codename |
| 1 | Budy |
+--------+------------+scores table:
| act_id | name |
| 1 | Activity 1 |
| 2 | Activity 2 |
+------------+--------+-------+Now the problem was, I wanted to have the assignments listed across the top, and the scores next to the names. Something like this:
| student_id | act_id | score |
| 1 | 1 | 10 |
| 1 | 2 | 10 |
+---------------+------------+------------+-------+So here's how the sql statement ended up:
| codename | Activity 1 | Activity 2 | Total |
| budy | 10 | 10 | 20 |
SELECT names.codename,
s1.score AS "Score1", s1.comment AS "Comments1",
s2.score AS "Score2", s2.comment AS "Comments2",
SUM(st.score) AS "Total"
FROM students names
LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id
LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id
LEFT JOIN scores st ON names.id=st.student_id
WHERE names.codename <> ''
GROUP BY names.codename
ORDER BY names.codename;
As you can see, for each activity, I need to add another left join, but it looks exactly like the last one, thus it is easy to build through a program like php. I hope this helps someone out.
t1 to make sure that ALL grouped records are shown
t(n+1) for use per condition
and as mentioned above, the JOIN condition must be used as well for the primary key AND for the condtion per sum!
Here is an example:
drop table if exists testtable;
create table testtable
(mykey int not null,
mygroup int,
cond int,
value int,
primary key (mykey));
insert into testtable
values (1, 1, 1, 5), (2, 1, 1, 6), (3, 1, 2, 3), (4, 2, 2, 4), (5, 3, 3, 5);
-- returns nothing
select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3
from testtable t1
left join testtable t2 on t1.mykey=t2.mykey
left join testtable t3 on t1.mykey=t3.mykey
left join testtable t4 on t1.mykey=t4.mykey
where t2.cond=1
and t3.cond=2
and t4.cond=3
group by 1
order by 1;
-- returns correct sums
select t1.mygroup, sum(t2.value) as cond_1, sum(t3.value) as cond_2, sum(t4.value) as cond_3
from testtable t1
left join testtable t2 on t1.mykey=t2.mykey and t2.cond=1
left join testtable t3 on t1.mykey=t3.mykey and t3.cond=2
left join testtable t4 on t1.mykey=t4.mykey and t4.cond=3
group by 1
order by 1;
mygroup | cond_1 | cond_2 | cond_3
1 | 11 | 3 | 0
2 | 0 | 4 | 0
3 | 0 | 0 | 5
FROM table 1
LEFT JOIN table 2 on (table1.id = table2.id)
LEFT JOIN table 3 on (table1.id2 = table3.id2)
LEFT JOIN table 4 on (table1.id3 = table4.id3)
your comment & others helped emensely!
Here's a left-join select that also has a regular join to another table.
I want to get all Plans (& associated SubType info),
but also see which plans user 13 is signed up
for, but only if the expire_date hasn't passed.
This select will show all Plan & SubType info,
but user-info only if the user is signed up,
and the expire-date hasn't passed.
FROM mt_SubTypes, mt_Plans as t1
LEFT JOIN mt_UserPlans as t2
on (t1.id_plan = t2.id_plan
and t2.expire_date > '2003-11-12'
and t2.id_user = 13)
t1.id_subType = mt_SubTypes.id_subType;
SELECT d.degDegId, m1.majDescription AS major_1, m2.majDescription AS major_2
FROM degree AS d
LEFT OUTER JOIN major AS m1
ON d.degMajor1 = m1.majMajId
LEFT OUTER JOIN major AS m2
ON d.degMajor2 = m2.majMajId
select a1.a,a1.b,a2.a,a2.b from a1,a2 where a1.a=a2.b(+);
Is expressed like:
select a1.a,a1.b,a2.a,a2.b from a1 left join a2 on a2.b=a1.a where 1=1;
NOTE: a2 is the optional table:
(PLEASE NOTE you must NOT specify a2 in from line(!!).
The 1=1 is optional,but I added it here so you could see where to add other conditions).
full example:
drop table a1; drop table a2;
create table a1 (a integer,b integer);
create table a2 (a integer,b integer);
insert into a1 (a,b) values (1,2);
insert into a1 (a,b) values (3,5);
insert into a2 (a,b) values (2,1);
insert into a2 (a,b) values (3,5);
select a1.a,a1.b,a2.a,a2.b from a1 left join a2 on a2.b=a1.a where 1=1;
drop table a1; drop table a2;
I prefer the Oracle syntax and would like to see it supported by MySql too.
Normally, the common column name is "wiped out" by the null record, but here is a workaround for it: You simply alias that common column name in the select. For instance,
CREATE TABLE t1 (INT id NOT NULL, ....);
CREATE TABLE t2 (INT id NOT NULL, ....);
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;
would result in the column 'id' being null on each selected row. Instead, you can do:
SELECT *, t1.id AS id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;
And now the 'id' column will be preserved since the alias is evaluated *after* the LEFT JOIN.
SELECT products.id
FROM products
INNER JOIN author ON author.id = author2products.authorid
) ON products.id = author2products.productsid
This enables you to OUTER JOIN with the results of an INNER JOIN within one query.
Requirement: Create a query to generate a list of companies, showing the count of publications and subscriptions for each company.
create table company ( cid int not null, cref char(64) not null,primary key (cid));
create table publication ( pid int not null, pcid int not null, pref char(64) not null,primary key (pid));
create table subscription ( scid int not null,spid int not null,primary key (scid,spid));
insert into company values (1,'A Corp');
insert into company values (2,'B Corp');
insert into company values (3,'C Corp');
insert into company values (4,'D Corp');
insert into company values (5,'E Corp');
insert into publication values (10,1,'A News');
insert into publication values (11,1,'A Digest');
insert into publication values (12,1,'A Review');
insert into publication values (20,2,'B News');
insert into publication values (51,5,'E Digest');
insert into publication values (52,5,'E Review');
insert into subscription values (1,10);
insert into subscription values (1,11);
insert into subscription values (1,20);
insert into subscription values (1,51);
insert into subscription values (1,52);
insert into subscription values (2,10);
insert into subscription values (4,10);
insert into subscription values (4,52);
select cref,count(distinct pid) as pubs,count(distinct spid) as subs from company left join publication on cid=pcid left join subscription on cid=scid group by cid order by cref;
| cref | pubs | subs |
| A Corp | 3 | 5 |
| B Corp | 1 | 1 |
| C Corp | 0 | 0 |
| D Corp | 0 | 2 |
| E Corp | 2 | 0 |
This is a fairly complex example, with lots of joins, aliases and grouping... Its aim is to extract a list of cows which have not calved every year in a given period. It accounts for cows which have not been in the database for the entire period, are not currently on our property (locations.local==1), or which are too young to have calves for the entire period (they can't calve before 2 years old).
There are many actions that signal entry/exit of an animal - eg birth/buy/sell/death. many (or none) of them may be recorded in the history table. As a fallback, animal.yob is the year of birth of the animal.
Cows and their calves are all stored as "animals" in the animals table (the mob field distinguishes between them).
The query returns the number of calves they did have in the time frame (num_calves) and the number of calves they should have had (max_calves).
Tables: (columns not relevant to query have been removed)
create table history (id int(10), date datetime, action int(2) not null, info text, primary key(id,date));
create table actions (id int(2) auto_increment primary key, name char(20), type char(20));
create table mob_types (id int(2) primary key, name char(20));
create table locations (id int(2) auto_increment primary key, name char(20), alive tinyint(1), local tinyint(1));
create table animals (id int(10) auto_increment primary key, yob int(4), tag1 char(10), mother int(10), mob int(2), location int(2));
Foreign keys:
animals.id <- history.id
animals.location -> locations.id
animals.mob -> mobs.id
mobs.type -> mob_types.id
history.action -> actions.id
The SQL to make it happen:
The original formatting is easier to read, but the leading spaces are not preserved here (the site won't let me use html to get it either)
$xxx are the input variables:
$start_year, $end_year: date range to check (inclusive)
$all_animals: 0= alive and on property, 1= everything
$nomiss: 0= those that missed a year, 1=those that didn't miss
$allow_miss: number of misses considered acceptable.
select a1.*,aquire.date,disposal.date,count(a2.id) num_calves,
IF(DATE_FORMAT(disposal.date,'%Y') < $end_year,DATE_FORMAT(disposal.date,'%Y'),$end_year) -
+1) as max_calves
from animals as a1
left join animals as a2
on a1.id=a2.mother
and a2.yob>=$start_year
left join history as aquire
on a1.id=aquire.id
and aquire.action in (select id from actions where type='aquire')
left join history as disposal
on a1.id=disposal.id
and disposal.action in (select id from actions where type='disposal')
where a1.mob in (select id from mobs where mobtype in (select id from mob_types where name='Cow'))
and (($all_animals>0)
or a1.location in (select id from locations where alive=1 and local=1))
group by a1.id
having IF($nomiss>0, num_calves>=(max_calves-$allow_miss), num_calves<(max_calves-$allow_miss))
order by a1.location,cast(a1.tag1 as unsigned),a1.tag1;
Speed: with about 6000 records in the animals table, and appropriate indexes, this returns in under half a second on a modern machine (versus several seconds without indexes)
I still need to add support for twins (two events in same year should be counted as one), but I'm not sure how to do that yet (any suggestions?).
That really helped me!
............+-> s_c
.....................+-> s_t_i
SELECT distinct sc.TType, si.Id
FROM s_g_a_t st LEFT JOIN s_c sc ON(st.GA = sc.GA and sc.id_app = 'XXX' and sc.Select = 1) INNER JOIN s_t_i si ON (sc.TType = si.DL)
Order by si.Id
tblA(idA, idB1, idB2)
tblB(idB, Name)
relations: tblA.idB1->tblB.IdB
and we want to obtain all records of tblA with its related tblB.Name
The sql statment could be like this:
tblB.Name AS Name1,
tblB_1.Name AS Name2
tblA INNER JOIN
tblB tblB_1 ON tblA.IdB1 = tblB_1.IdB
INNER JOIN tblB
ON tblA.IdB2 = tblB.IdB
(tested in MySql v4.0.24)
with two tables t1, t2:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
with three tables t1, t2, t3:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id
- rows from the result of the inner join
- rows from A that don't have corresponding rows in B
- rows from B that don't have corresponding rows in A
Formally, the corresponding SQL statement looks like this:
FROM A JOIN B ON A.id = B.id
FROM A LEFT JOIN B ON A.id = B.id
WHERE B.id IS NULL
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL
Due to the fact that the first union represents a left outer join, the statement can be simplified:
FROM A LEFT JOIN B ON A.id = B.id
FROM A RIGHT JOIN B ON A.id = B.id
WHERE A.id IS NULL
t0 contains 1,3,5,7,
t1 contains 2,3,6,7,
t2 contains 4,5,6,7
(ti contains j iff 2^i xor j = 1).
The suggested solution:
SELECT * FROM t0 LEFT JOIN t1 ON t0.id = t1.id LEFT JOIN t2 ON t1.id = t2.id UNION
SELECT * FROM t0 RIGHT JOIN t1 ON t0.id = t1.id LEFT JOIN t2 ON t1.id = t2.id UNION
SELECT * FROM t0 RIGHT JOIN t1 ON t0.id = t1.id RIGHT JOIN t2 ON t1.id = t2.id
id id id
7 7 7
where "5" appears 2 times. To get the correct result, use only LEFT JOIN, start once with each table, and name the columns:
SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t0 LEFT JOIN t1 USING(id) LEFT JOIN t2 USING(id) UNION
SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t1 LEFT JOIN t0 USING(id) LEFT JOIN t2 USING(id) UNION
SELECT t0.id as id0, t1.id as id1, t2.id as id2 FROM t2 LEFT JOIN t1 USING(id) LEFT JOIN t0 USING(id)
ORDER BY COALESCE(id0,id1,id2)
The ORDER BY is optional. The length of the query grows as the square of the number of tables, which is quite bad. FULL OUTER JOIN would be really welcome.
mysql> select * from NAME;
+------+------+mysql> select * from DESCR;
| 1 | bala |
| 2 | renu |
+------+-------+mysql> SELECT t1.ID,t1.NAME,t2.DESCR FROM NAME t1 LEFT JOIN DESCR t2 ON (t1.ID = t2.ID) UNION SELECT t2.ID,t1.NAME,t2.DESCR FROM NAME t1 RIGHT JOIN DESCR t
2 ON (t1.ID = t2.ID);
| 1 | bala | NULL |
| 2 | renu | BBB |
For example:
SELECT T1.a,T1.b,T2.a,GROUP_CONCAT(T2.b ORDER BY T2.b DESC SEPARATOR '|') as d FROM T1 INNER JOIN T2 ON T1.a = T2.b GROUP BY T1.a
When this query might otherwise duplicate rows, adding the group_concat coalesces them as a blob which when printed returns all the values concatenated by the defined separator string!
For example:
select a.x, a.y, sum(a.price), sum(b.price)
from A a full join B b on a.x = b.x and a.y = b.y
where ...
group by a.x, a.y
order by a.x, a.y
A full join is needed because there are (x,y) combinations that exist only in A or only in B.
A group by is needed because in B the (x,y) combination isn't unique.
- MySQL join tutorials and examples: http://mysqljoin.com
SELECT st.codename
, SUM(CASE sc.act_id WHEN 1 THEN sc.score ELSE NULL END) AS "Activity 1"
, SUM(CASE sc.act_id WHEN 2 THEN sc.score ELSE NULL END) AS "Activity 2"
, SUM(sc.score) AS "Total"
FROM students AS st
LEFT JOIN scores AS sc ON st.student_id = sc.student_id
WHERE st.codename != ''
GROUP BY st.codename
ORDER BY st.codename
Personally, I think the syntax is easier to read. Additionally, you are eliminating the number of nested loops required to join the same table multiple times.


text:
The Individual Development Plan (IDP) is an organized approach to professional development activities and programs that are designed to improve the employee's professional skills and the Bureau's productivity. The IDP is developed jointly by the supervisor and employee. It outlines the employee's developmental objectives and the developmental activity for achieving these objectives. The purpose of the IDP is to target competencies and skills to be developed for two purposes: 1) for use and growth in the employee's current position 2) for future career goals.
Technical credibility is the cornerstone for a successful career in any organization. Development in your technical area coupled with leadership development will set a solid foundation for career growth. Use your IDP as a tool to balance development in these two essential areas.
All employees are encouraged to use this form. The IDP is mandatory for all executives, managers, and supervisors.
Click here to access the IDP Form