Joining three tables and getting ID value

Question

2.00/5 (1 vote)

See more:

For eg:
I am having three tables as apple, ball and cat

apple contains only ID column(ID's of both ball and cat tables)

ball contains ID and Ball Names

cat contains ID and Cat Names.

Solution I Need:
how do I put join for these three tables to get a name of a single ID which I am passing?

My Query:

SQL

select b.ball_name, c.cat_name from tbl_ball b join tbl_apple a join tbl_cat c
on ((b.ballId = a.Id) or (c.catId = a.Id)) where a.Id = 1100

Posted 9-Jun-13 20:19pm

riodejenris14

Add a Solution

Comments

kumar2413 10-Jun-13 2:35am

What is your table structure then. Put it so that it will help us to solve your solution

RelicV 10-Jun-13 3:12am

Could you please provide the Schema and the data of the tables?

3 solutions

Solution 1

Try

SQL

select b.ball_name, c.cat_name 
from tbl_ball b join tbl_apple a on b.ballId = a.Id
join tbl_cat c on c.catId = a.Id
where a.Id = 1100

[edit]Code block fixed[/edit]

Posted 9-Jun-13 20:26pm

Abhinav S

Updated 9-Jun-13 22:17pm

v3

Comments

riodejenris14 10-Jun-13 2:31am

it showing error abhinav 'incorrect syntax near keyword join'

Abhinav S 10-Jun-13 2:35am

Perhaps this will help you - http://msdn.microsoft.com/en-us/library/aa213227%28v=sql.80%29.aspx.
Also note that you don't seem to have a common join between tables b and c.

Solution 2

SQL

SELECT b.ball_name,c.cat_name from tbl_apple a JOIN  tbl_ball b on a.id=b.id 
JOIN tbl_cat c on c.catid=a.id WHERE a.id=1100

Try this and see

Posted 9-Jun-13 20:34pm

kumar2413

Comments

riodejenris14 10-Jun-13 2:47am

its not retrieving any value the data set is empty.

kumar2413 10-Jun-13 3:09am

What is your table structure then. Put it so that it will help us to solve your solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Amy · Accepted Answer · 2013-06-09T22:30:00

Solution 3

If your table structure is like this:

SQL

--Apple Table
ID      cat_id      ball_id
---------------------------
1100        1         1

--Ball Table
ball_id         ball_name
---------------------------
1                  Ball 1

--Cat Table
cat_id          cat_name
---------------------------
1                  Cat 1

Then your query should be like:

SQL

SELECT B.ball_name,C.cat_name FROM apple A
INNER JOIN ball B ON A.ball_id = B.ball_id
INNER JOIN cat C ON A.cat_id = C.cat_id
WHERE A.ID = 1100 -- ID should be identity column of apple table

Please refer:
MSDN : Join Fundamentals[^]
MSDN : Join Hints (Transact-SQL)[^]

--Amit

Posted 9-Jun-13 22:30pm

_Amy

Comments

riodejenris14 10-Jun-13 4:37am

---Apple table
ID
1100
1101
1102
1103
---Ball table
ID Name
1100 rubber
1102 stumper

---cat table
ID Name
1101 felix
1103 boose

this is my table structure.

_Amy 10-Jun-13 4:39am

Then try this:


SELECT B.Name,C.Name FROM apple A
INNER JOIN ball B ON A.ID = B.ID
INNER JOIN cat C ON A.ID = C.ID
WHERE A.ID = 1100

But inner join will not return any rows. If you want all the rows from table apple then use Left Outer Join.
--Amit

riodejenris14 10-Jun-13 4:46am

retrieves null value

_Amy 10-Jun-13 4:48am

Since Inner Join will retrieve only matching value from all three tables, and you don't have any matching records, thats why it is returning empty recordset. Did you try Left Outer Join ? Try this:


SELECT B.Name,C.Name FROM apple A
LEFT OUTER JOIN ball B ON A.ID = B.ID
LEFT OUTER JOIN cat C ON A.ID = C.ID
WHERE A.ID = 1100

--Amit

riodejenris14 10-Jun-13 5:01am

hurrah hey amy thankx yar its working!!!!!

_Amy 10-Jun-13 5:01am

Welcome. :)

riodejenris14 10-Jun-13 5:10am

amy sorry to disturb u again i have an issue in setting the value with this query...
set @id1 = (SELECT B.Name,C.Name FROM apple A
LEFT OUTER JOIN ball B ON A.ID = B.ID
LEFT OUTER JOIN cat C ON A.ID = C.ID
WHERE A.ID = @id1)
'coz i want to insert the output value to another table.
how can i make it up?
getting error as : "Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

_Amy 10-Jun-13 5:16am

Here I can see the following issues.
1. The join statement is returning more than one column, and it may return more than one row also. If you need to set the ID, then statement should select only ID column.(Hope you understood this part)
2. Here I can see @id1 variable used in both places, in where condition of the statement and set clause also.

You do need to check these two points.
--Amit

riodejenris14 10-Jun-13 8:29am

how to check amy????

riodejenris14 10-Jun-13 8:33am

got it thru coalesce function amy