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

Transact-SQL (2005)

SELECT

Author

Topic

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 18:22:03

what is the syntax for selecting all of items from one table and one from another? I know it's a stupid question, but...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 18:27:15

Select a.*,b.[columnname]
FROM a inner join b
on a.LinkingColumn = b.LinkingColumn

Note, I do not like using *...but that would do what you ask.

Poor planning on your part does not constitute an emergency on my part.

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 18:44:14

and how do you add more linking columns? same tables
can't find it in syntax manuals

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 18:45:31

Select a.*,b.[columnname]
FROM a inner join b
on a.LinkingColumn = b.LinkingColumn
AND a.AnotherColumn = b.AnotherColumn --repeat for more...

If you can't find the syntax for a join in the manual, you are not looking at the right manual. This is T-SQL 101 stuff.

Poor planning on your part does not constitute an emergency on my part.

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 18:53:56

obviously i tried with AND but maybe mistake is not with that. if you can check this, please. i am joining two from big table with one column in second table. and get error

ALTER PROCEDURE dbo.StoredProcedure1
@klub1 int,
@klub2 int

AS
select tekme_nogomet.*, klubovi.[naziv_kluba]
from tekme_nogomet inner join klubovi on tekme_nogomet.ID_domacina=klubovi.ID_kluba and tekme_nogomet.ID_gosta=klubovi.ID__kluba

where tekme_nogomet.ID_domacina=@klub1 and
tekme_nogomet.ID_gosta=@klub2

RETURN

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-08 : 19:16:32

what error do you get?
you do realize that you have 2 underscores in your ID__kluba, right?

so what will be the name of the football (soccer, for the yanks

) page?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 19:24:00

quote:
Originally posted by goranp

obviously i tried with AND but maybe mistake is not with that. if you can check this, please. i am joining two from big table with one column in second table. and get error

ALTER PROCEDURE dbo.StoredProcedure1
@klub1 int,
@klub2 int

AS
select tekme_nogomet.*, klubovi.[naziv_kluba]
from tekme_nogomet inner join klubovi on tekme_nogomet.ID_domacina=klubovi.ID_kluba and tekme_nogomet.ID_gosta=klubovi.ID__kluba

where tekme_nogomet.ID_domacina=@klub1 and
tekme_nogomet.ID_gosta=@klub2

RETURN

Should have just started with that...post back with the answer to Spirit's questions..

Poor planning on your part does not constitute an emergency on my part.

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 19:31:46

that was stupid mistake with that underscore (shouldn't do sql late at night) :)

but still i don't get back any values and it should. Must check ID's of klubovi and retrieve rows where they both exists + check names in second table using IDs and retrieve them too. And it is not doing that.

spirit1, hehe it's going to be something like almanah, not just for football, for other sports too so slovenia maybe will be in it :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-08 : 19:48:58

hmm... well if it's going to be for other sports other than foorball too, are you planning on having a table per sport?
i'm thinking that you might have a bit bad and unflexible db design going on...

for all others:
nogomet = football in slovenian/croatian/serbian/probably some other slavic language too

klub* = sports club

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 19:51:04

these table names seem familiar...I think I recall on earlier post on these tables.

not sure, but since the id_gosta and ID_domacina are both joining to the same column in klubovi...
@klub1 AND @klub2

would both have to exist in klubovi? if so, they can't be different or it is like saying where 1=0

I think your statement above doesn't match the query written...

Does a select statement with no filter return results?

A working query with no results is a lot different than an non-working query. Since this works, but returns no results...it is a criteria or concept problem...

Poor planning on your part does not constitute an emergency on my part.

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 20:03:10

yeah, it is a working query with no results

spirit1: yes, i have table for sports, leagues(lige), teams(klubovi), and tables for games for each sport. don't see the design problem, all clubs are in one table defined with sportID and leagueID and name

dataguru1971: they both exists in klubovi (ID_kluba) and that is a PK in table klubovi.
In that row i am trying to retrieve one team(klub) is playing at home and one is a guest.
In my app i am selecting values from two dropdown lists so i have two parameters and need two names from table klubovi

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-08 : 20:07:48

so when you add a new sport you'll have to add a new table, new data access methods etc?
wouldn't it be better to have a table called Games and a flag to which sport it belongs?
have a small lookup table called sports that is PK-FK to the Games table sport flag
that way when you have a new sport you just add it's name to the Sports lookup table and that's it.

or am i missing something here?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 20:15:04

i created more tables because every sport has different statistics, it was more on that principle than common sense. So when adding new sport i would have to enter in games table and statistics for that sport, it much easier to create new table. Or i am wrong?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 20:16:56

hmmm...


where ID_kluba = @klub1 or ID_kluba =@klub2

How about changing the where clause?

based on what you said, my guess is this one returns results. If it does not, then the @klub parameter needs to be "scrubbed" for trailing spaces or something.

Are the primary key fields also integer data types?

Are you sure when passing them from the app they are being passed correctly?

Poor planning on your part does not constitute an emergency on my part.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-08 : 20:24:36

i don't know. Is it easier? having to also modify all data access thus recompiling the app, etc...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 20:25:31

TABLE KLUBOVI
---------------
ID_kluba int, PK
ID_liga int,
naziv_kluba nvarchar(50) //name

TABLE TEKME_NOGOMET
--------------------
ID_domacina int, //home team ID PK and FK references klubovi.ID_kluba
ID_gosta int //away team ID PK and FK references klubovi.ID_kluba
datum datetime PK

i am testing this proc in visual studio and i am entering parameters values by myself and i know they exists in database

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 20:32:46

It won;t work the way you want.

I get no results with the following:


Create table #t (ID_home int null, ID_away int null)

Insert into #t
Select 1,2 UNION ALL
Select 3,4 UNION ALL
Select 1,4 UNION ALL
Select 2,3 UNION ALL
Select 4,1 

Create Table #b (ID_Team int not null,[name] char(4) not null)

Insert into #b
Select 1,'yaks' UNION ALL
Select 2,'cows' UNION ALL
Select 3,'skay' UNION ALL
Select 4, 'swoc'


Select #t.*, #b.*
FROM #t   join #b on #t.ID_Home = #b.ID_Team
    and #t.ID_Away = #b.ID_Team


Select #t.*, #b.*
FROM #t   join #b on #t.ID_Home = #b.ID_Team
    and #t.ID_Away = #b.ID_Team
Where #t.ID_Home = 1 and #t.ID_Away = 2



Select #t.*, #b.*
FROM #t inner join #b on #t.ID_Home = #b.ID_Team
    and #t.ID_Away = #b.ID_Team
Where #t.ID_Home = 1 and #t.ID_Away = 3

drop table #t
Drop table #b

which is actually the same thing you are trying to do.

...wait for it......

..in effect you are telling the query to join 2 columns in the SAME ROW to ONE ROW in the other table.

table looks like this:
ID_home ID_away ID_Team name
1 2 1 yaks
3 4 3 skay
1 4 1 yaks
2 3 2 cows
4 1 4 swoc

By asking for an ID_Home and ID_Away, you cannot join to ID Team ON BOTH.

I hope this helps, but it appears this explains your issue.
this:


Select #t.*, #b.*
FROM #t  inner join #b on #t.ID_Home = #b.ID_Team
Where #t.ID_Home = 1 
UNION
Select #t.*, #b.*
FROM #t  inner join #b on #t.ID_Home = #b.ID_Team
Where  #t.ID_Away = 3

produces 1 record since the effect of the query will only produce 1 unique row since the home/away team must be in the same row

ID_home ID_away ID_Team name
1 2 1 yaks

Poor planning on your part does not constitute an emergency on my part.

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 20:52:08

thank you

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 21:03:46

So with some sample data and desired output we can help make it do what you want, or are you can take some more stabs at it?

Poor planning on your part does not constitute an emergency on my part.

goranp
Starting Member

17 Posts

Posted - 2008-03-08 : 21:20:06

and what if i put name of away team in game table so i can just read it from there? is it against some of norms? it looks like easy solution

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 21:25:38

Before I can answer that..

You said, you are selecting the two names from two different combo boxes. The procedure goes and gets "something"...

what are you trying to return to your application?

Poor planning on your part does not constitute an emergency on my part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources