CROSS JOIN WITH NON-TABLE OBJECTS

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)

CROSS JOIN WITH NON-TABLE OBJECTS

Author

Topic

johnny_crash
Starting Member

4 Posts

Posted - 2007-04-05 : 03:34:13

Hi everyone,

I have a little problem , if anyone can help me
I need to make a JOIN between a table and a list of
strings. I assume that somehow, i must convert that
list of strings to a table. Is this correct? If not, what
is the correct approach?

A simple example would be :
X: Y:
ID ID_Y ID type inf
1 4 3 A XX
5 8 4 B GH
6 7 4 D QW
33 1 4 A FD
3 9
7 2

My query should return, for X.ID=1 :
ID_X ID_Y type inf
1 4 A FD
1 4 B GH
1 4 C null
1 4 D QW

Thank you,
Andrei

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-05 : 03:45:02

Don't quite understand your example there.

1. What is X: Y: ?
2. "ID ID_Y ID type inf" is columns for which table ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 03:56:26

Something like this:

SELECT p1.ID_X, p1.ID_Y, p2.Type, p2.Inf
FROM (SELECT ID AS ID_X, ID_Y FROM X WHERE ID = @WantedID) AS p1
CROSS JOIN (SELECT Type, Inf FROM Y WHERE @WantedID IN (ID, ID_Y, Type)) AS p2

Peter Larsson
Helsingborg, Sweden

johnny_crash
Starting Member

4 Posts

Posted - 2007-04-05 : 05:14:04

Peso,
I am sorry, i haven't noticed my example was scrambled. The intended
formating was :

A simple example would be :


X:            Y:
ID ID_Y       ID type inf
1   4         3    A  XX
5   8         4    B  GH
6   7         4    D  QW
33  1         4    A   FD
3   9
7   2

My query should return, for X.ID=1 : 
ID_X    ID_Y   type inf
1       4      A    FD
1       4      B    GH
1       4      C    null
1       4      D    QW

khtan,
i think your query would work fine, for my problem.
I haven't thought of using multiple selects. I'll look
into this matter, and try to figure this out.

* If i have a third table, and that inner select statement
would have a join with another table, declared outside of it
, for example ... from Z t3 where (select ... where ID=t3.ID)...
the inner t3.ID should have another sintax than SQL standard?
(i am using Microsoft SQL Server 2005, and i get an
error when i execute this statement. Error is : the multipart identifier t3.ID could not be read)

* as an answer to my problem, i was expecting, that some part of the
statement would be :

type in ('A', 'B', 'C', 'D')

i assume that in the sql you wrote :

@WantedID IN (ID, ID_Y, Type)

can be modified with added types, something like:

@WantedID IN ((ID, ID_Y, Type1), ((ID, ID_Y, Type2))

Best regards,
Andrei

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-05 : 06:37:04

"khtan,
i think your query would work fine"
Not mine. It's Peter's

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 09:03:42

Why add an extra record with "C NULL" and not "Q NULL" ???

Peter Larsson
Helsingborg, Sweden

johnny_crash
Starting Member

4 Posts

Posted - 2007-04-05 : 15:26:37

Sorry, but i don;t understand your question. What is "C NULL"? What is "Q NULL" ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 17:55:39

Your expected output was

ID_X ID_Y type inf
1 4 A FD
1 4 B GH
1 4 C null -- Where do the Type C (Inf NULL) come from? Why not Q? Or J?
1 4 D QW

Peter Larsson
Helsingborg, Sweden

johnny_crash
Starting Member

4 Posts

Posted - 2007-04-05 : 18:18:06

Peso

quote:

I need to make a JOIN between a table and a list of
strings

I am sorry, i guess i haven't gaved enough explanations to my example

The ideea is that this list of strings is predefined, and i must
somehow integrate it within my query. To simplify things, i
considered it composed only by these strings : "A", "B", "C", "D"
Therefore, in my expected output, "A", "B", and "D" values corespond to a Y.inf row, but the "C" value doesn't. In the retrieved list, C must also appear, with a corresponding null value.

I know this would have been a trivial issue if all of the values from my list of strings (A B C D) would have been contained in a table(probably i would have used an outer join), but in this situation i don't know how to do it.

Thank you for bodering with these answers,

Best regards,
Andrei

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources