|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
query again, again, i am sorry..============= xx 1 2 3 4 table2 =============== xx T3070 T30S1 1 70TX 3 30TX table3 ============= xx hori verti 1 550 2205 1 500 2205 1 550 825 2 490 2300 2 665 155 3 296 1525 4 80 2400 4 60 2400 result table after query join ============================== xx hori verti T3070 T30S1 1 550 2205 70TX 1 550 2205 70TX 1 550 2205 70TX 2 490 2300 2 665 155 3 296 1525 30TX 4 80 2400 4 60 2400 how query?? *** Sent via Developersdex http://www.developersdex.com *** SELECT table1.xx, table3.hori, table3.verti,
COALESCE(table2.T3070, '') AS T3070, COALESCE(table2.T30S1, '') AS T30S1 FROM table1 LEFT JOIN table2 ON table1.xx = table2.xx LEFT JOIN table3 ON table1.xx = table3.xx produces the results you asked for. Show quote "tom taol" <t**@yahoo.com> wrote in message news:en$4sWlvGHA.4876@TK2MSFTNGP04.phx.gbl... > table1 > ============= > xx > 1 > 2 > 3 > 4 > > table2 > =============== > xx T3070 T30S1 > 1 70TX > 3 30TX > > table3 > ============= > xx hori verti > 1 550 2205 > 1 500 2205 > 1 550 825 > 2 490 2300 > 2 665 155 > 3 296 1525 > 4 80 2400 > 4 60 2400 > > result table after query join > ============================== > xx hori verti T3070 T30S1 > 1 550 2205 70TX > 1 550 2205 70TX > 1 550 2205 70TX > 2 490 2300 > 2 665 155 > 3 296 1525 30TX > 4 80 2400 > 4 60 2400 > how query?? > > *** Sent via Developersdex http://www.developersdex.com *** Actually the results are slightly different. I put the differences in
parentheses: xx hori verti T3070 T30S1 1 (500) 2205 70TX 1 550 (825) 70TX 1 550 2205 70TX 2 490 2300 2 665 155 3 296 1525 30TX 4 60 2400 4 80 2400 Show quote "Mike C#" <x**@xyz.com> wrote in message news:GWsDg.1059$Rh7.869@newsfe11.lga... > > SELECT table1.xx, table3.hori, table3.verti, > COALESCE(table2.T3070, '') AS T3070, > COALESCE(table2.T30S1, '') AS T30S1 > FROM table1 > LEFT JOIN table2 > ON table1.xx = table2.xx > LEFT JOIN table3 > ON table1.xx = table3.xx > > produces the results you asked for. > > "tom taol" <t**@yahoo.com> wrote in message > news:en$4sWlvGHA.4876@TK2MSFTNGP04.phx.gbl... >> table1 >> ============= >> xx >> 1 >> 2 >> 3 >> 4 >> >> table2 >> =============== >> xx T3070 T30S1 >> 1 70TX >> 3 30TX >> >> table3 >> ============= >> xx hori verti >> 1 550 2205 >> 1 500 2205 >> 1 550 825 >> 2 490 2300 >> 2 665 155 >> 3 296 1525 >> 4 80 2400 >> 4 60 2400 >> >> result table after query join >> ============================== >> xx hori verti T3070 T30S1 >> 1 550 2205 70TX >> 1 550 2205 70TX >> 1 550 2205 70TX >> 2 490 2300 >> 2 665 155 >> 3 296 1525 30TX >> 4 80 2400 >> 4 60 2400 >> how query?? >> >> *** Sent via Developersdex http://www.developersdex.com *** > > tom taol (t**@yahoo.com) writes:
Show quote > table2 It's a good idea to also include a narrative that explains what the>=============== > xx T3070 T30S1 > 1 70TX > 3 30TX > > table3 >============= > xx hori verti > 1 550 2205 > 1 500 2205 > 1 550 825 > 2 490 2300 > 2 665 155 > 3 296 1525 > 4 80 2400 > 4 60 2400 > > result table after query join >============================== > xx hori verti T3070 T30S1 > 1 550 2205 70TX > 1 550 2205 70TX > 1 550 2205 70TX > 2 490 2300 > 2 665 155 > 3 296 1525 30TX > 4 80 2400 > 4 60 2400 > how query?? purpose of the query is. One thing I don't understand is why the result set has three identical rows for xx = 1, but this does not happen for xx = 4. Is there a reason for this, is that just a typo? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics