Join when 2 columns in one table point to 1 column in another table 2

[shaunieb]

Using Join when 2 columns in one table point to 1 column in another table

I have spent hours on this problem and no research has turned in my favour. Does anyone have any examples they can put forward for me.

I am really desperate to sort this out, and any help will be greatly appreciated.

Thanks
Shaun

 

[Savil]

Are you reffering to

SELECT *
FROM tblNames_Separated AS a
INNER JOIN tblNames_Joined AS b ON
a.Firstname+a.SecondName = b.WholeName

I am not quite sure what you are asking for but hopefully this should put you on the right track.

 

[shaunieb]

thanks for your response Savil. Still trying. Heres the breakdown.

I have 2 tables

1. Colorcanvass
- id
- colorname
- colorpic

2. Colorcombo
- color1 'reference to colorcanvass-id
- color2 'reference to colorcanvass-id

I need to reference the colorcanvass info into the colorcombo results.

colorcanvass
id colorname colorpic
1 Black h1_black.gif
2 maroon h2_maroon.gif
3 cherise h3_cherise.gif
4 red h4_red.gif
5 tomato red H5_tom-red.gif
6 blue H6_blue.gif
7 teal H7_teal.gif
8 brown H8_brown.gif
9 tan H9_tan.gif
10 white H11_white.gif


Colorcombo
ID color1 color2
17 6
18 13
19 10
20 8
21 1 10
22 14 10
23 1 11
24 2 10
25 18 10
26 4 10

Thanks
Shaun

 

[Savil]

This should work

You will probably get duplicate rows so i have put in the distinct

SELECT DISTINCT *
FROM ColorCanvass AS a
INNER JOIN ColorCombo AS b ON
a.ID = b.Color1 OR a.ID = b.Color2

Savil

 

[shaunieb]

so close

I've been playing around with your example but im having no luck.

The result i need is

colorcombo-id
Colorcanvass-colorname reference to colorcombo.color1
Colorcanvass-colorname reference to colorcombo.color2

any ideas?

 

[vidru]

Give this a shot:
[tt]
SELECT CB.ID,
Color1 = CV1.ColorName,
Color2 = CV2.ColorName
FROM ColorCombo CB
JOIN ColorCanvass CV1 ON CB.Color1 = CV1.ID
LEFT OUTER JOIN ColorCanvass CV2 ON CV2.ID = CB.Color2
[/tt]
-dave

 

[shaunieb]

Thanks for the help dave. This is the error message i receive.

Syntax error in FROM clause

regards
Shaun

 

[Savil]

SELECT ColorComboid, b.ColorName AS ColorName1, c.ColorName AS ColorName2
FROM ColorCombo AS a
LEFT JOIN (SELECT b1.id, ColorName FROM ColorCanvass AS b1 INNER JOIN ColorCombo b2 ON b1.id = b2.Color1) AS b ON
a.id = b.id
LEFT JOIN (SELECT c1.id, ColorName FROM ColorCanvass AS c1 INNER JOIN ColorCombo c2 ON c1.id = c2.Color2) AS c ON
a.id = c.id

With a bit of tweaking this should work
You may have to change the left joins to right joins.
I have used left joins to allow for the null values in color2.

HTH

Savil UK

 

[shaunieb]

THANK YOU Savil and DAVE.

It finally works! Using MSAccess instead of MSSQL added to the problem, using SQL now.

I really appreciate the help you guys gave me, thank you!

All the Best
Shaun B
SOuth Africa