2
Hi there
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
186 Afghan Hound Tazi, Baluchi Hound A 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. Afghanistan Afghans are a grooming salons dream come true - they demand regular grooming and can quickly become knotted and tangled without it. Daily All 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. 0 0 Suitable for the experienced, comitted dog owner with time on their hands and a great love for the breed. large 63cm 69cm 23kg 25kg 68cm 74cm 25kg (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!
187 Bluetick Coonhound NULL Not found in Australia, the Bluetick Coonhound is a friendly hound that makes an excellent tracking or scent dog. NULL This 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. NULL This 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 America NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
188 Australian Cattle Dog Cattledog, Queensland Heeler, Heeler, Blue Heeler, Red Heeler, Bluey A 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. Australia Minimal grooming although weekly brushing is required to remove dead hair. Does shed seasonally. Weekly - at home He 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. 60 2 Single 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. medium 43cm (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
186 1 suits older children /Images/icons/older_children.gif
186 2 suits younger children /Images/icons/suits_young_children.gif
186 3 suits elderly/disabled. Not too boisterous /Images/icons/suits_elderly_disabled.gif
186 6 easy to transport /Images/icons/transport.gif
186 8 grooming needs /Images/icons/grooming_needs.gif
186 10 distress/destruction when left alone /Images/icons/distressed_when_alone.gif
186 12 Shedding / Hair loss /Images/icons/shedding_hairloss.gif
186 15 Energy Level /Images/icons/energy_levels.gif
186 16 Affection with family /Images/icons/affectionate.gif
186 22 may bite intruder /Images/icons/bite_intruder.gif
186 23 watchdog skills /Images/icons/watchdog_skills.gif
187 1 suits older children /Images/icons/older_children.gif
187 6 easy to transport /Images/icons/transport.gif
187 10 distress/destruction when left alone /Images/icons/distressed_when_alone.gif
187 12 Shedding / Hair loss /Images/icons/shedding_hairloss.gif
187 13 stranger friendly /Images/icons/stranger_friendly.gif
187 15 Energy Level /Images/icons/energy_levels.gif
187 16 Affection with family /Images/icons/affectionate.gif
187 22 may bite intruder /Images/icons/bite_intruder.gif
187 23 watchdog skills /Images/icons/watchdog_skills.gif
188 1 suits older children /Images/icons/older_children.gif
188 3 suits elderly/disabled. Not too boisterous /Images/icons/suits_elderly_disabled.gif
188 4 cattle friendly /Images/icons/cattle_friendly.gif
188 6 easy to transport /Images/icons/transport.gif
188 10 distress/destruction when left alone /Images/icons/distressed_when_alone.gif
188 11 trainability /Images/icons/trainable.gif
188 12 Shedding / Hair loss /Images/icons/shedding_hairloss.gif
188 13 stranger friendly /Images/icons/stranger_friendly.gif
188 14 How often this breed is found in the pound /Images/icons/found_in_pount.gif
188 15 Energy Level /Images/icons/energy_levels.gif
188 16 Affection with family /Images/icons/affectionate.gif
188 19 Availability /Images/icons/availability.gif
188 20 cat friendly /Images/icons/cat_friendly.gif
188 22 may bite intruder /Images/icons/bite_intruder.gif
188 23 watchdog skills /Images/icons/watchdog_skills.gif
189 1 suits older children /Images/icons/older_children.gif
189 2 suits younger children /Images/icons/suits_young_children.gif
189 8 grooming needs /Images/icons/grooming_needs.gif
189 10 distress/destruction when left alone /Images/icons/distressed_when_alone.gif
189 12 Shedding / Hair loss /Images/icons/shedding_hairloss.gif
189 13 stranger friendly /Images/icons/stranger_friendly.gif
189 14 How often this breed is found in the pound /Images/icons/found_in_pount.gif
189 15 Energy Level /Images/icons/energy_levels.gif
189 16 Affection with family /Images/icons/affectionate.gif
189 19 Availability /Images/icons/availability.gif
189 23 watchdog skills /Images/icons/watchdog_skills.gif
190 1 suits older children /Images/icons/older_children.gif
190 3 suits elderly/disabled. Not too boisterous /Images/icons/suits_elderly_disabled.gif
190 6 easy to transport /Images/icons/transport.gif
190 10 distress/destruction when left alone /Images/icons/distressed_when_alone.gif
190 11 trainability /Images/icons/trainable.gif
190 13 stranger friendly /Images/icons/stranger_friendly.gif
190 14 How often this breed is found in the pound /Images/icons/found_in_pount.gif
190 15 Energy Level /Images/icons/energy_levels.gif
190 16 Affection with family /Images/icons/affectionate.gif
190 19 Availability /Images/icons/availability.gif
190 22 may bite intruder /Images/icons/bite_intruder.gif
190 23 watchdog skills /Images/icons/watchdog_skills.gif
190 24 Gay Icon /Images/icons/gay_icon.gif
192 1 suits older children /Images/icons/older_children.gif
192 2 suits younger children /Images/icons/suits_young_children.gif
192 3 suits elderly/disabled. Not too boisterous /Images/icons/suits_elderly_disabled.gif
192 4 cattle friendly /Images/icons/cattle_friendly.gif
192 5 bunny/guinea pig friendly /Images/icons/bunny_friendly.gif
192 6 easy to transport /Images/icons/transport.gif
192 10 distress/destruction when left alone /Images/icons/distressed_when_alone.gif
192 11 trainability /Images/icons/trainable.gif
192 12 Shedding / Hair loss /Images/icons/shedding_hairloss.gif
192 13 stranger friendly /Images/icons/stranger_friendly.gif
192 15 Energy Level /Images/icons/energy_levels.gif
192 16 Affection with family /Images/icons/affectionate.gif
192 17 dog friendly /Images/icons/dog_friendly.gif
192 19 Availability /Images/icons/availability.gif
192 20 cat friendly /Images/icons/cat_friendly.gif
192 23 watchdog skills /Images/icons/watchdog_skills.gif
etc
