This is a hard problem that I have - I have only been using sql for a couple of weeks and have gone past my ability level quickly! The real tables are complex but I will post a simple and a real version with the hope someone can help me.
Any help would be much appreciated - I would also be happy to pay someone to actually do it if it takes time to work out as I know that its hard when all your help is free :)
================================================
SIMPLE VERSION
Table 1 Dog breeds
dogbreedID, dogBreedName, colour
1,labrador,golden
2,beagle, tricolour
3,great dane, marle
Table 2 - maps criteria to dog breeds
dogbreedID, criteriaID, value, location
3,2,easy to train, c:/filepath2
1,1,good with children, c:/filepath
1,2,easy to train, c:/filepath2
2,1,good with children, c:/filepath
3,3,stranger friendly, c:/filepath3
So that leads to table 3 sitting behind the scenes not used in this query:
criteriaID, value, location
1,good with children, c:/filepath
2,easy to train, c:/filepath2
3,stranger friendly, c:/filepath3
I want a view that has the following:
dogbreedID, dogBreedName, colour, criteriaID1, value1, location1, criteriaID2, value2, location2, criteriaID3, value3, location3,criteriaID4, value4, location4
1,labrador,golden,1,good with children, c:/filepath,2,easy to train, c:/filepath2,NULL,NULL,NULL,NULL, NULL, NULL
2,beagle, tricolour,1,good with children,NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL
3,great dane, marle,NULL, NULL, NULL,2,easy to train, c:/filepath2,3,stranger friendly, c:/filepath3
================================================== =====
more complicated view - you can see each table is actually a combination of table values but I dont think that matters to this problem - the above example is fine but I am not very good with this so may have left somehting out that you can derive from the example below:
Table 1:
SELECT distinct dbo.BREED_dogBreeds.breedId, dbo.BREED_dogBreeds.breedName, dbo.BREED_dogBreeds.alternativeName, dbo.BREED_dogBreeds.shortDesc,
dbo.BREED_dogBreeds.katShortDesc, dbo.BREED_dogBreeds.longDesc, dbo.BREED_dogBreeds.katLongDesc,
dbo.BREED_dogBreeds.thingsToConsider, dbo.BREED_dogBreeds.temperament, dbo.BREED_dogBreeds.history, dbo.BREED_dogBreeds.feeding,
dbo.BREED_tblCountry.Country_Name, dbo.BREED_dogBreeds.colour, dbo.BREED_dogBreeds.breedProfileLink, dbo.BREED_Grooming.groomText,
dbo.BREED_GroomFrequencyValues.value, dbo.BREED_Training.intelligence, dbo.BREED_Training.trainingNotes, dbo.BREED_Training.exerciseText,
dbo.BREED_Training.exerciseTime, dbo.BREED_Training.timePerDay, dbo.BREED_Suitability.idealOwner, dbo.BREED_Size.size,
dbo.BREED_sizes.bheightmin, dbo.BREED_sizes.bheightmax, dbo.BREED_sizes.bweightmin, dbo.BREED_sizes.bweightmax,
dbo.BREED_sizes.dheightmin, dbo.BREED_sizes.dheightmax, dbo.BREED_sizes.dweightmin, dbo.BREED_sizes.dweightmax,
dbo.BREED_Sociability.compatibility FROM dbo.BREED_Sociability RIGHT OUTER JOIN
dbo.BREED_dogBreeds ON dbo.BREED_Sociability.sociabilityID = dbo.BREED_dogBreeds.sociabilityID LEFT OUTER JOIN
dbo.BREED_Size RIGHT OUTER JOIN
dbo.BREED_sizes ON dbo.BREED_Size.id = dbo.BREED_sizes.sizeid ON
dbo.BREED_dogBreeds.sizeId = dbo.BREED_sizes.breedSizeId LEFT OUTER JOIN
dbo.BREED_Suitability ON dbo.BREED_dogBreeds.suitabilityID = dbo.BREED_Suitability.suitabilityID LEFT OUTER JOIN
dbo.BREED_Training ON dbo.BREED_dogBreeds.trainID = dbo.BREED_Training.trainID LEFT OUTER JOIN
dbo.BREED_GroomFrequencyValues RIGHT OUTER JOIN
dbo.BREED_Grooming ON dbo.BREED_GroomFrequencyValues.gfvID = dbo.BREED_Grooming.gfvID ON
dbo.BREED_dogBreeds.groomID = dbo.BREED_Grooming.groomID LEFT OUTER JOIN
dbo.BREED_tblCountry ON dbo.BREED_dogBreeds.country = dbo.BREED_tblCountry.Country_ID
Table 2:
SELECT [breedId]
,[breedCriteriaID]
,[value]
,[icon]
FROM [v1vw1n_dogmatch].[dbo].[vbreedCriterias]
where levelID>=3
order by breedId, breedCriteriaID asc
TABLE 3
SELECT [breedCriteriaID]
,[criteriaValue]
,[icon]
FROM [v1vw1n_dogmatch].[dbo].[BREED_Criteria]
Table1
186Afghan HoundTazi, Baluchi HoundA strikingly beautiful dog with dignified poise.Afghans are kept primarily as show dogs and can also be used for lure coursing. They are extremely loving and loyal to their owners and are gentle souled and good with children. Afghans can make companion dogs but their considerable needs means that only devoted owners keep them. Aloof with strangers but affectionate and loyal to their owners. Can be very clown like at play and are very people oriented. Love children and being included in family life. Can become introverted if excluded from social situations whilst pups.An ancient breed, the afghan looks as classy as its pedigree. Afghans were used in Afghanistan to protect the flocks and would hunt and kill panthers, leopards and other large predators. The first dog to be shown was in the UK in 1907 having previously been banned from export. Afghans can be fussy with their food and it is better to instill good eating habits when they are pups and ideally treats should be avoided. AfghanistanAfghans are a grooming salons dream come true - they demand regular grooming and can quickly become knotted and tangled without it. DailyAll dogs are bright but Afghans may not quite earn the MENSA of the dog world.Afghans can be hard to train with lots of perseverance needed. Highly strung, stubborn and sensitive natured dog making them difficult to train. Training cannot be rushed and as they are sensitive souls and it is very important not treat them harshly. Sometimes difficult to housebreak. As puppies, Afghans often appear awkward, with uneven growth, gawkiness and loose limbs, and for this reason, exercise must be carefully monitored to avoid injury to their growing bones.00Suitable for the experienced, comitted dog owner with time on their hands and a great love for the breed.large63cm69cm23kg25kg68cm74cm25kg (55lb)28kg (62lb)They are hunting stock and love to chase anything that moves so perhaps not the ideal dog to share a home with cats and small animals!
187Bluetick CoonhoundNULLNot found in Australia, the Bluetick Coonhound is a friendly hound that makes an excellent tracking or scent dog.NULLThis breed originated in the states and has a smooth, dense, tri colour coat. The base colour is white with heavy ticking of black and tan markings over their chest, eyes, muzzle, lower legs and feet. This breed is recognised as a competitive, fearless, dedicated hunter. The Bluetick has a typical hound bawl and so is not the quietest of dogs.NULLThis dog needs a lot of exercise. They love to do jobs, and keep busy. They need to be exercised vigorously or they run the danger of becoming destructive and will howl excessivly.The Coonhound is deeply devoted, fearless, attentive and loyal. They make very good guardians and family companions. They are reserved with strangers, but are not aggressive towards them. They are not the best with other animals, and get along better with older, considerate children.The Bluetick originated in Louisiana at the beginning of the 20th century. They were developed from crosses between the English Coonhound, the Foxhound and the french Grand Bleu de Gascogne. Their tricoloured, blue-speckled coat sets them apart from other Coonhound breeds. Originally they were registered as a variant of the English Coonhound, but in 1946 they were given separate recognition as a distinct breed in their own right.Today the English Coonhound is sometimes referred to as the Redtick. The original, old-fashioned Bluetick dog, which was larger and slower than the modern type, began to loose ground because they did less well in the increasingly popular field competitions and night trials. The smaller, faster version began to eclipse them and became one of the most popular and numerous of all coonhound breeds. This upset the traditionalists, who preferred the old-style Big Blue , and some of them reacted by switching their allegiance to other large-bodied breeds, such as the Blue Gascon, and the Majestic.This breed is not fussy when it comes to their diet, but they have quite a healthy appetite.United States of AmericaNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
188Australian Cattle DogCattledog, Queensland Heeler, Heeler, Blue Heeler, Red Heeler, BlueyA strong compact working dog, the Australian Cattle Dog is one of the most popular breeds of dog in Australia.A strong compact working dog, the Australian Cattle Dog is one of the most popular breeds of dog in Australia.The Australian Cattle Dog is a strong compact working dog with a combination of substance, power, balance and hard muscular condition that conveys the impression of great agility, strength and endurance with the ability and willingness to carry out his allotted task however arduous. His head is wedge shaped with a broad skull and muscular cheeks and oval shaped, dark brown eyes that express alertness and intelligence and have a warning or suspicious glint when approached by strangers. His ears are broad at base, muscular, pricked and moderately pointed. His rain resistant coat is a smooth double coat with a close, hard outer coat and a short dense undercoat AND comes in two colours: Blue: Blue, blue-mottled or blue speckled with tan markings. Red: An even red speckle all over, including the undercoat, with or without darker red markings on the head. The pups are born white, developing their colour gradually from approximately three weeks of age. The Australian Cattle Dog is a strong compact working dog with a combination of substance, power, balance and hard muscular condition that conveys the impression of great agility, strength and endurance with the ability and willingness to carry out his allotted task however arduous. His head is wedge shaped with a broad skull and muscular cheeks and oval shaped, dark brown eyes that express alertness and intelligence and have a warning or suspicious glint when approached by strangers. His ears are broad at base, muscular, pricked and moderately pointed. His rain resistant coat is a smooth double coat with a close, hard outer coat and a short dense undercoat AND comes in two colours: Blue: Blue, blue-mottled or blue speckled with tan markings. Red: An even red speckle all over, including the undercoat, with or without darker red markings on the head. The pups are born white, developing their colour gradually from approximately three weeks of age. The Australian Cattle Dog has a strong herding instinct and when playing may nip the heels of children.The Australian Cattle Dog needs a job, companionship and activity for the mind and body, all day, every day. Easily bored, he can become noisy and/or destructive. Renowned for his protectiveness and loyalty to master and property, he is very selective as to who is friend or foe. In 1840, Thomas Hall, a landowner in New South Wales, imported two smooth-haired blue merle Scotch Collies and crossed their progeny with the Dingo. The resulting litters became known as Hall's Heelers. The progeny were generally of Dingo type with the colour being either red or blue merle and were valued for their ability to handle wild cattle, stamina to travel great distances over all types of terrain, and their endurance in extremes of temperature. Later, Hall's Heelers were crossed with a Dalmatian, which changed the merle colour to red or blue speckle and instilled in the dogs a love of horses and protectiveness toward master and property. A further cross was made to the Kelpie to produce highly intelligent, controllable workers resembling thickset Dingoes and with peculiar markings known to no other dog. In 1903 a standard for the breed was drawn up and from these beginnings the Australian Cattle Dog has developed into one of the most popular breeds of dog in Australia today. AustraliaMinimal grooming although weekly brushing is required to remove dead hair. Does shed seasonally.Weekly - at homeHe is loving, playful, and eager to please his owner. Very quick to learn.Unless you can give him extensive exercise do not even consider owning an Australian Cattle Dog. He was bred to work all day in hard conditions and will become bored if not given sufficient exercise.602Single guy or gal, athletic, able to have the dog with them most of the time. Not a first time dog owner. Families with older children.medium43cm (17")48cm (19")16kg (35lb)20kg (44lb)46cm (18")51cm (20")16kg (35lb)20kg (44lbs)Not good with dogs of the same sex. OK with cats if raised with the cat since puppyhood. Not good with other small mammals.
TABLE 2
1861suits older children/Images/icons/older_children.gif
1862suits younger children/Images/icons/suits_young_children.gif
1863suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1866easy to transport/Images/icons/transport.gif
1868grooming needs/Images/icons/grooming_needs.gif
18610distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18612Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18615Energy Level/Images/icons/energy_levels.gif
18616Affection with family/Images/icons/affectionate.gif
18622may bite intruder/Images/icons/bite_intruder.gif
18623watchdog skills/Images/icons/watchdog_skills.gif
1871suits older children/Images/icons/older_children.gif
1876easy to transport/Images/icons/transport.gif
18710distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18712Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18713stranger friendly/Images/icons/stranger_friendly.gif
18715Energy Level/Images/icons/energy_levels.gif
18716Affection with family/Images/icons/affectionate.gif
18722may bite intruder/Images/icons/bite_intruder.gif
18723watchdog skills/Images/icons/watchdog_skills.gif
1881suits older children/Images/icons/older_children.gif
1883suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1884cattle friendly/Images/icons/cattle_friendly.gif
1886easy to transport/Images/icons/transport.gif
18810distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18811trainability/Images/icons/trainable.gif
18812Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18813stranger friendly/Images/icons/stranger_friendly.gif
18814How often this breed is found in the pound/Images/icons/found_in_pount.gif
18815Energy Level/Images/icons/energy_levels.gif
18816Affection with family/Images/icons/affectionate.gif
18819Availability/Images/icons/availability.gif
18820cat friendly/Images/icons/cat_friendly.gif
18822may bite intruder/Images/icons/bite_intruder.gif
18823watchdog skills/Images/icons/watchdog_skills.gif
1891suits older children/Images/icons/older_children.gif
1892suits younger children/Images/icons/suits_young_children.gif
1898grooming needs/Images/icons/grooming_needs.gif
18910distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18912Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18913stranger friendly/Images/icons/stranger_friendly.gif
18914How often this breed is found in the pound/Images/icons/found_in_pount.gif
18915Energy Level/Images/icons/energy_levels.gif
18916Affection with family/Images/icons/affectionate.gif
18919Availability/Images/icons/availability.gif
18923watchdog skills/Images/icons/watchdog_skills.gif
1901suits older children/Images/icons/older_children.gif
1903suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1906easy to transport/Images/icons/transport.gif
19010distress/destruction when left alone/Images/icons/distressed_when_alone.gif
19011trainability/Images/icons/trainable.gif
19013stranger friendly/Images/icons/stranger_friendly.gif
19014How often this breed is found in the pound/Images/icons/found_in_pount.gif
19015Energy Level/Images/icons/energy_levels.gif
19016Affection with family/Images/icons/affectionate.gif
19019Availability/Images/icons/availability.gif
19022may bite intruder/Images/icons/bite_intruder.gif
19023watchdog skills/Images/icons/watchdog_skills.gif
19024Gay Icon/Images/icons/gay_icon.gif
1921suits older children/Images/icons/older_children.gif
1922suits younger children/Images/icons/suits_young_children.gif
1923suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1924cattle friendly/Images/icons/cattle_friendly.gif
1925bunny/guinea pig friendly/Images/icons/bunny_friendly.gif
1926easy to transport/Images/icons/transport.gif
19210distress/destruction when left alone/Images/icons/distressed_when_alone.gif
19211trainability/Images/icons/trainable.gif
19212Shedding / Hair loss/Images/icons/shedding_hairloss.gif
19213stranger friendly/Images/icons/stranger_friendly.gif
19215Energy Level/Images/icons/energy_levels.gif
19216Affection with family/Images/icons/affectionate.gif
19217dog friendly/Images/icons/dog_friendly.gif
19219Availability/Images/icons/availability.gif
19220cat friendly/Images/icons/cat_friendly.gif
19223watchdog skills/Images/icons/watchdog_skills.gif
etcHi there
I got a response on another board. Basically I just used
SELECT dogBreedID,
crit1value = MIN(CASE criteriaID WHEN 1 THEN value END),
crit1location = MIN(CASE criteriaID WHEN 1 THEN location END),
crit2value = MIN(CASE criteriaID WHEN 2 THEN value END),
crit2location = MIN(CASE criteriaID WHEN 2 THEN location END),
...
FROM tbl
GROUP BY dogBreedID
and joined that to the first table.
Fantastic!
about: what does this comp. level actually do? When