Join with two list of values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join with two list of values

Author

Topic

Elgasen
Starting Member

2 Posts

Posted - 2012-11-02 : 09:35:13

I Have two tables:

TABLE1
TYPE        CONTAINS     
Car         Engine
Car         Wheels
Car         Steering wheel
Airplain    Wings
Airplain    Engine
Airplain    Steering wheel


TABLE2
BRAND               SPECIFICATION     
Boing               Wings
Boing               Engine
Boing               Steering wheel
Boing               Seats
Boing               Pilot
Ford                Engine
Ford                Wheels
Ford                Radio
Ford                Steering wheel
Toyota              Engine
Toyota              Wheels
Toyota              Rooof
Toyota              Steering wheel
Harley Davidsson    Engine
Harley Davidsson    Wheels

And I would like to present result like this:


BRAND               TYPE
Boing               Airplain
Ford                Car
Harley Davidsson
Toyota              Car

How should I do that join between TABLE1 and TABLE2?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-02 : 09:40:06

select distinct t2.brand, t1.type
from table1 t1
join table2 t2
on t2.specification = t1.type

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-02 : 09:42:58

The relation is only in your brain - not in the data.
There is not reliable way so far.

Too old to Rock'n'Roll too young to die.

mfemenel
Professor Frink

1421 Posts

Posted - 2012-11-02 : 09:44:16

Select DISTINCT t2.brand,t1.Type
FROM Table1 t1
INNER JOIN Table2 t2
On t2.Specification=t1.contains

Mike
"oh, that monkey is going to pay"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 10:22:09

To add some color to what Fred was saying, when you join the two tables using the CONTAINS column of TABLE1 with the SPECIFICATIONS column of Table2, you will get results such as:

TYPE = Airplane, BRAND = Boeing
TYPE = Airplane, BRAND = Harley Davidson.

While you do want the first, clearly you don't want the second row because Harley does not make any airplanes. So there has to be something else in your data or tables that would allow Airplanes to be associated with Boeing, but not with Harley. Do you have anything like that?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-02 : 10:26:52

>> Harley does not make any airplanes.

Maybe they do?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 11:55:27

quote:
Originally posted by nigelrivett

>> Harley does not make any airplanes.

Maybe they do?

May be they do, but my mental picture of Harley is not of soaring skies and open fields as far as the eye can see, but one of crowded highways and motor cycles dangerously squeezing between cars.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-02 : 12:17:44

http://www.wheelsthroughtime.com/index.php?option=content&task=view&id=328

(note - the one here has two s's in Davidsson so maybe a different company.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources