CODEWITHSUNDEEP
sqlselect
Dreshar
Dreshar

Reputation: 117

Select distinct rows in MySQL

i am experiencing a problem which i can't figure out why is causing me trouble.

I want to select all offers from a database with a DISTINCT statement, so every offer ID is pulled uniquely. Here's what i got in screenshot form on Navicat:

Screenshot http://www.dreshar.com/sql.jpg http://www.dreshar.com/sql.jpg

For anyone who cant see the image, this is the select - where is my error?

SELECT DISTINCT
guf_offers.id as OfferId,
guf_offers.validfrom AS OfferValidFrom,
guf_offers.validtill AS OfferValidTill,
guf_offers.days AS OfferDays,
guf_offers.active,
guf_offers.roomtype1 AS OfferRoomtype1,
guf_offers.roomprice1 AS OfferRoomprice1,
guf_countries.caption AS CountryCaption,
guf_courts.caption AS CourtCaption,
guf_hotels.caption AS HotelCaption,
guf_hotels.id AS HotelId,
guf_hotel_images.image AS HotelImage,
guf_offer_types.caption AS OffertypeCaption,
guf_regions.caption AS RegionCaption,
guf_offers.hoteloncourse AS OfferHotelOnCourse,
guf_offers.wellnessspa AS OfferWellnessSpa,
guf_offers.18hole AS Offer18Hole,
guf_offers.topangebot AS OfferTopangebot
FROM guf_offers , guf_countries , guf_courts , guf_hotels , guf_hotel_images , guf_offer_types , guf_regions
WHERE guf_offers.country_id = guf_countries.id AND guf_offers.court_id = guf_courts.id AND guf_offers.hotel_id = guf_hotels.id AND guf_hotel_images.hotel_id = guf_offers.hotel_id AND guf_offers.offer_type_id = guf_offer_types.id AND guf_offers.region_id = guf_regions.id AND guf_offers.active = 1 AND STR_TO_DATE(guf_offers.validtill, '%d.%m.%Y') > STR_TO_DATE('29.04.2013', '%d.%m.%Y')

Upvotes: 1

Views: 107

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you want distinct ids, then you probably want to use a group by. The query would look something like:

SELECT
guf_offers.id as OfferId,
guf_offers.validfrom AS OfferValidFrom,
guf_offers.validtill AS OfferValidTill,
guf_offers.days AS OfferDays,
guf_offers.active,
guf_offers.roomtype1 AS OfferRoomtype1,
guf_offers.roomprice1 AS OfferRoomprice1,
guf_countries.caption AS CountryCaption,
guf_courts.caption AS CourtCaption,
guf_hotels.caption AS HotelCaption,
guf_hotels.id AS HotelId,
guf_hotel_images.image AS HotelImage,
guf_offer_types.caption AS OffertypeCaption,
guf_regions.caption AS RegionCaption,
guf_offers.hoteloncourse AS OfferHotelOnCourse,
guf_offers.wellnessspa AS OfferWellnessSpa,
guf_offers.18hole AS Offer18Hole,
guf_offers.topangebot AS OfferTopangebot
FROM guf_offers , guf_countries , guf_courts , guf_hotels , guf_hotel_images , guf_offer_types , guf_regions
WHERE guf_offers.country_id = guf_countries.id AND guf_offers.court_id = guf_courts.id AND guf_offers.hotel_id = guf_hotels.id AND guf_hotel_images.hotel_id = guf_offers.hotel_id AND guf_offers.offer_type_id = guf_offer_types.id AND guf_offers.region_id = guf_regions.id AND guf_offers.active = 1 AND STR_TO_DATE(guf_offers.validtill, '%d.%m.%Y') > STR_TO_DATE('29.04.2013', '%d.%m.%Y')
group by guf_offers.id

The other columns are arbitrary values from different rows. This uses an extension to the group by where columns can be in the select even when they are not in an aggregation function or in the group by clause.

By the way, you should learn to do your joins in the from clause using propoer join syntax. It makes queries more readable and maintainable.

Upvotes: 1

Philip Crumpton
Philip Crumpton

Reputation: 127

DISTINCT returns any distinct COMBINATION of columns. For example, the first two instances of OfferId = 89 in your image shows a different value in the HotelImage column, so therefore those are DISTINCT results.

Upvotes: 2

Related Questions