Problem Selecting! - 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

General SQL Server Forums

New to SQL Server Programming

Problem Selecting!

Author

Topic

wil
Starting Member

3 Posts

Posted - 2007-04-01 : 10:21:24

I just can not get this to work.... here is a brief example

3 tables,

table a) has columns ID, 1, 2, 3, 4, 5,

table b) has columns ID, 1, 2, 3, 4, 5,

table c) has columns ID, 1, 2, 3, 4, 5,

Now, table a's column 3 has the ID from table b, and table b's column 4 has the ID from table C.

I need to be able to display the data from table a column 1 for everyone that table c column 1 is.

So far I have...

SELECT CONCAT(1) AS ‘places’
FROM tablea
WHERE column3=(
SELECT ID
FROM tableb WHERE
tableb.column3=(
SELECT ID FROM tablec WHERE
tablec.4=’this is what I need to look up’));

Sorry, I may have done this a little wrong compared to my example, but anyway, This works 100% fine when there is only one result, however, there is many more than one result in tableb, and I am not sure how I can do multiples.

Is there a better way around this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-01 : 10:27:24

can you post the table DDL, some sample data and the result that you want ? It was not very clear from what you have posted here.

wil
Starting Member

3 Posts

Posted - 2007-04-01 : 10:37:02

Well, everything is just strings in all tables apart from ID which is a auto number.

I want the results to be:

[places]
London
Paris
Rome

This will display all the data from table A column 1, Where column 3 is the ID of table B and where column 4 is the ID of table C where ever column d contains the word 'this is the data'

Sorry, this is a example, but it does work, however only when there is one result, so I am guessing I am not using the correct query.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-01 : 11:42:56

Is this what your are trying to do?

SELECT 
	CONCAT(1) AS 'places'
FROM tablea a JOIN tableb b on a.column3 = b.ID
JOIN tablec c on b.column3 = c.id
where c.4 = 'this is what I need to look up'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-01 : 12:44:09

quote:
The concat() function in SQL Server only accepts values of type xs:string. Other values have to be explicitly cast to xs:string or xdt:untypedAtomic.

Peter Larsson
Helsingborg, Sweden

wil
Starting Member

3 Posts

Posted - 2007-04-01 : 15:28:28

I have been trying for about the past 2 hours but I cant get this working, thanks for your help and I think you are on to a winner, I am trying to edit it more now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources