Help with query - unlinked tables...

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 2005 Forums

Transact-SQL (2005)

Help with query - unlinked tables...

Author

Topic

hanifbarik
Starting Member

9 Posts

Posted - 2009-01-28 : 13:01:26

Hi All,

I need your help to create a query which returns the name of a JOB and the associated COLOUR. I have two tables:

JOB table

ID NAME COLOURID
1 Barclays 1
2 Apple 2
3 Natwest 2
4 AOL 1

COLOUR table

ALIAS NAME
Text1 Red
Text2 Blue

The JOB table and the COLOUR table are not linked. However, where the COLOURID is 1, the colour is 'Red'. Where the colour is 2, the colour is 'Blue'. In other words:

JOB.COLOURID = COLOUR.ALIAS (minus the 'Text' part)

I would like a query which returns the JOB.NAME and COLOUR.NAME. For example,

JOB.NAME COLOUR.NAME
Barclays Red
Apple Blue
Natwest Blue
AOL Red

Do I need to use a function (Right function) to cut the 'Text' part from the COLOUR.ALIAS?

If any one can help me with the query, I would appreciate it very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 13:04:15

you've in hand. why hesitate to try yourself?


SELECT j.NAME,c.NAME
FROM JOB j
JOIN COLOUR c
ON RIGHT(c.ALIAS,1)*1=j.COLOURID

hanifbarik
Starting Member

9 Posts

Posted - 2009-01-28 : 13:18:28

Hi visakh16. Thank you very much. This is exactly what I needed. I was using the wrong joins and making it overly complicated. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources