SQL JOIN Syntax - 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

SQL JOIN Syntax

Author

Topic

dweb77
Starting Member

7 Posts

Posted - 2011-09-05 : 12:11:16

I think this is an easy one...I need help with the 'JOIN' statement (or the appropriate statement to connect data from two tables).

I have the following separate statements that work to get me the data I want from each of the two separate tables.

From the ads table:

SELECT DISTINCT id , school
FROM ads

From the cb table:

SELECT title, link FROM cb

I want to connect the two when the column 'id' that is in the ads table = the column cb_id that is in the cb table.

Any help here?

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-05 : 12:38:48

http://msdn.microsoft.com/en-us/library/ms191517.aspx

--
Gail Shaw
SQL Server MVP

dweb77
Starting Member

7 Posts

Posted - 2011-09-05 : 12:45:00

I'll certainly try again, but I have already tried multiple attempts using similar tutorials. I seem to be missing some aspect.

Kristen
Test

22859 Posts

Posted - 2011-09-05 : 13:09:38

This perhaps?


SELECT C.title, C.link, A.school 
FROM cb AS C
     JOIN ads AS A
         ON A.id = C.cb_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-05 : 13:28:09

is the relationship one to one or one to many between tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dweb77
Starting Member

7 Posts

Posted - 2011-09-05 : 13:43:26

It is one to many with the DISTINCT set being pulled from the ads table then matched with potentially multiple ids from the cb table

Thanks.

dweb77
Starting Member

7 Posts

Posted - 2011-09-05 : 13:56:43

Actually...when the first SELECT pulls the DISTINCT set, there are multiple of the same id giving this sample result:

id - school
1 - school A
4 - school c
4 - school d
7 - school f
8 - school g
7 - school y

etc...

then I need to join the records from a table where there is only one title and link that corresponds to a particular id
For example:

cbid - title - link
1 - title a - link a
2 - title b - link b
3 - title c - link c

etc...

And thanks Kristen, the only issue with your syntax is that the records need to be pulled from the first table so they are distinct as there are multiple of the 'school x' listings. That is why I used the DISTINCT statement which pulled only unique id-school combo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-05 : 14:01:35

ok. but when you pull only distinct of ids from first table, which associated school you want to return?for example for id = 4 when you take distinct which school you want school c or school d?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-05 : 14:05:33

"for example for id = 4 when you take distinct which school you want school c or school d?"

and I thought this one would be easy & unambiguous ...

I'll leave it to you V. !!

dweb77
Starting Member

7 Posts

Posted - 2011-09-05 : 14:10:43

Poor listing for my second table, so include one more listing of:

cbid - title - link
1 - title a - link a
2 - title b - link b
3 - title c - link c
4 - title d - link d

So end result of join would be:

1 - school a - title a - link a
4 - school c - title d - link d
4 - school d - title d - link d

There is at lest one match in the second table (with the title and link) for every id in the first table.

Thanks

Kristen
Test

22859 Posts

Posted - 2011-09-05 : 14:23:16

Did you try my suggestion? and if so explaining what it does wrong would help in advising you futher.


SELECT C.title, C.link, A.school 
FROM cb AS C
     JOIN ads AS A
         ON A.id = C.cb_id

dweb77
Starting Member

7 Posts

Posted - 2011-09-05 : 14:46:16

Kristen,

Thanks. This works except that the end result gives multiple of the same school. So the resulting table is:

title a - link a - school a
title a - link a - school a
title b - link b - school b
title b - link b - school b
title b - link b - school b

etc... With my DISTINCT syntax in the SELECT from the one table, I rid of the duplicates.

dweb77
Starting Member

7 Posts

Posted - 2011-09-05 : 14:48:29

Ok...I simply put 'SELECT DISTINCT' instead of 'SELECT' and I think that did the trick.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 04:43:47

quote:
Originally posted by dweb77

Ok...I simply put 'SELECT DISTINCT' instead of 'SELECT' and I think that did the trick.

Thanks.

i dont think that will help you out as per your original sample data. the moment you add school in the list it will return more than one per is as school names are distinct so again you will be left with same problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources