473,581 Members | 2,861 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

complex tables - want to join to get one row of results from multiple rows

2 New Member
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,gold en
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,crite riaID4, value4, location4

1,labrador,gold en,1,good with children, c:/filepath,2,easy to train, c:/filepath2,NULL, NULL,NULL,NULL, NULL, NULL

2,beagle, tricolour,1,goo d 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,str anger 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_dogBr eeds.breedId, dbo.BREED_dogBr eeds.breedName, dbo.BREED_dogBr eeds.alternativ eName, dbo.BREED_dogBr eeds.shortDesc,
dbo.BREED_dogBr eeds.katShortDe sc, dbo.BREED_dogBr eeds.longDesc, dbo.BREED_dogBr eeds.katLongDes c,
dbo.BREED_dogBr eeds.thingsToCo nsider, dbo.BREED_dogBr eeds.temperamen t, dbo.BREED_dogBr eeds.history, dbo.BREED_dogBr eeds.feeding,
dbo.BREED_tblCo untry.Country_N ame, dbo.BREED_dogBr eeds.colour, dbo.BREED_dogBr eeds.breedProfi leLink, dbo.BREED_Groom ing.groomText,
dbo.BREED_Groom FrequencyValues .value, dbo.BREED_Train ing.intelligenc e, dbo.BREED_Train ing.trainingNot es, dbo.BREED_Train ing.exerciseTex t,
dbo.BREED_Train ing.exerciseTim e, dbo.BREED_Train ing.timePerDay, dbo.BREED_Suita bility.idealOwn er, 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_Socia bility.compatib ility FROM dbo.BREED_Socia bility RIGHT OUTER JOIN
dbo.BREED_dogBr eeds ON dbo.BREED_Socia bility.sociabil ityID = dbo.BREED_dogBr eeds.sociabilit yID LEFT OUTER JOIN
dbo.BREED_Size RIGHT OUTER JOIN
dbo.BREED_sizes ON dbo.BREED_Size. id = dbo.BREED_sizes .sizeid ON
dbo.BREED_dogBr eeds.sizeId = dbo.BREED_sizes .breedSizeId LEFT OUTER JOIN
dbo.BREED_Suita bility ON dbo.BREED_dogBr eeds.suitabilit yID = dbo.BREED_Suita bility.suitabil ityID LEFT OUTER JOIN
dbo.BREED_Train ing ON dbo.BREED_dogBr eeds.trainID = dbo.BREED_Train ing.trainID LEFT OUTER JOIN
dbo.BREED_Groom FrequencyValues RIGHT OUTER JOIN
dbo.BREED_Groom ing ON dbo.BREED_Groom FrequencyValues .gfvID = dbo.BREED_Groom ing.gfvID ON
dbo.BREED_dogBr eeds.groomID = dbo.BREED_Groom ing.groomID LEFT OUTER JOIN
dbo.BREED_tblCo untry ON dbo.BREED_dogBr eeds.country = dbo.BREED_tblCo untry.Country_I D

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_chi ldren.gif
186 3 suits elderly/disabled. Not too boisterous /Images/icons/suits_elderly_d isabled.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_hairlo ss.gif
186 15 Energy Level /Images/icons/energy_levels.g if
186 16 Affection with family /Images/icons/affectionate.gi f
186 22 may bite intruder /Images/icons/bite_intruder.g if
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_hairlo ss.gif
187 13 stranger friendly /Images/icons/stranger_friend ly.gif
187 15 Energy Level /Images/icons/energy_levels.g if
187 16 Affection with family /Images/icons/affectionate.gi f
187 22 may bite intruder /Images/icons/bite_intruder.g if
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_d isabled.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_hairlo ss.gif
188 13 stranger friendly /Images/icons/stranger_friend ly.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.g if
188 16 Affection with family /Images/icons/affectionate.gi f
188 19 Availability /Images/icons/availability.gi f
188 20 cat friendly /Images/icons/cat_friendly.gi f
188 22 may bite intruder /Images/icons/bite_intruder.g if
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_chi ldren.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_hairlo ss.gif
189 13 stranger friendly /Images/icons/stranger_friend ly.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.g if
189 16 Affection with family /Images/icons/affectionate.gi f
189 19 Availability /Images/icons/availability.gi f
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_d isabled.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_friend ly.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.g if
190 16 Affection with family /Images/icons/affectionate.gi f
190 19 Availability /Images/icons/availability.gi f
190 22 may bite intruder /Images/icons/bite_intruder.g if
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_chi ldren.gif
192 3 suits elderly/disabled. Not too boisterous /Images/icons/suits_elderly_d isabled.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_hairlo ss.gif
192 13 stranger friendly /Images/icons/stranger_friend ly.gif
192 15 Energy Level /Images/icons/energy_levels.g if
192 16 Affection with family /Images/icons/affectionate.gi f
192 17 dog friendly /Images/icons/dog_friendly.gi f
192 19 Availability /Images/icons/availability.gi f
192 20 cat friendly /Images/icons/cat_friendly.gi f
192 23 watchdog skills /Images/icons/watchdog_skills .gif

etc
Sep 29 '07 #1
1 2214
Vivienne
2 New Member
Hi 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!
Sep 30 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
1604
by: Dave | last post by:
Hi all, I have a problem with a query. (well, I actually have a few problems...) Here is my query. select FOCUS.SiteName, FOCUS.URL, OTWAdCars.* , REGION.SiteName as RegionSite, REGION.URL as RegionUrl from OTWSite as FOCUS right join OTWSite as REGION
8
3365
by: Rigga | last post by:
Hi, I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date order. So I need to write a query that retrieves all the information for one record, lets say I want to query on the main table any entry that is...
5
38626
by: Sami | last post by:
I can create queries and reports based on info from one table. How do I create one using information from multiple tables. What do I need to make sure the information from one table will be correctly assigned to specific person from another table? Thanks.
8
2952
by: Steve Jorgensen | last post by:
Mailing List management is a good example of a case where my conundrum arises. Say there is a m-m relationship between parties and groups - anyone can be a member of any combintation of groups. Now, let's say the user wants to be able to send mailings to people who have various combinations of membership and non-membership in those groups. ...
2
1416
by: commanderjason | last post by:
This seems like a very simple question but i have never been able to find an easy answer to it. I have a user table and i do a join with another table, we'll call the other table a results table. The results table has numerous rows with the userid foreign key. I want to make a query that will give me the number of rows in the
5
1497
by: Bob Stearns | last post by:
I have a table t1(id, other stuff) with 4 dependent (unrelated) tables ta(id, date, other stuff a), tb(id, date, other stuff b), tc(id, date, other stuff c), td(id, date, other stuff d). Any or all of the dependent tables may have rows associated with a given id from t1. What I would like to do is create a result set with at least 1 row for...
10
7687
by: Jim Devenish | last post by:
I have a split front end/back end system. However I create a number of local tables to carry out certain operations. There is a tendency for the front end to bloat so I have set 'compact on close' I think that I have read in some threads (althoug I cannot find them now) that others place such tables in a local, linked database. I could...
2
3603
by: KTosser | last post by:
I have two tables, one contains contacts and the other has all the events and years that the contacts participated in. What I want is to be able to choose the years, say 2005 and 2006, and get all the contacts that attended in both 2005 and 2006. Table1 ContactID ContactName 1 John 2 Bob 3 Amy
2
1709
by: =?Utf-8?B?c2lwcHl1Y29ubg==?= | last post by:
Have a complex process where I need to Import a large amount of data then run some transformations on this data then import into DataBase. The transformation involves multiple fields and multiple process - so the data needs to be read in 1 record at a time then run thru the transformation that may create new data value then everything is...
0
7876
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8156
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8310
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8180
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5681
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5366
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3809
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3832
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1144
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.