Trouble in Fetchichg Data in Sql Server

Question

1.00/5 (1 vote)

See more:

Ive Four Tables
1 Sale(saleID int Identity,AID)--Aid From Allocation Table
2 Allocation(AID int Identity, PurchaseID int)--PurchaseID From Purchase Table
3 Purchase(PurchaseID int,ProductID int)--ProductID From Product Table
4 Prodcut(ProductID int Identity,ProductName)

I want To Get ProductName From ProductTable Base On SaleID

i.e.
sale Table

saleID--AID
--1-----2

Allocation Table

AID---PurChaseID
1-----2
2-----1

Purchase Table

PurchaseID-ProductID
----1-------2
----2-------1

Product Table
ProductID-ProductName
---1-------CellPhone
---2-------Laptop

I want Result as

SaleID--ProductName
---1----Laptop

I can do This Using Table Variable but It will Take more Execution Time
So I Want a Small query....Im Quiet Confused What to do and What to not Please Help.......

Logic I used For Table Variable is
First I took All AID From Sale Master in Squence
Based On these AID's I'm Collecting All PurchaseID's From Allocation Table in Sequence
Based On These PurchaseID's I'm Collecting All ProductID's From Purchase Table in Squence
and Using these ProductID's I get their Names in Squence
After that I'm Inserting All SaleID's and All ProductName in One Table Variable and Selecting it as a Result

Posted 3-Jul-14 3:36am

prashantttt

Updated 3-Jul-14 3:44am

v2

Add a Solution

Comments

W∴ Balboos, GHB 3-Jul-14 10:11am

What (SQL) have you tried?

prashantttt 4-Jul-14 0:54am

as Ive Mentioned,Ive Done It Using Table Variable............

1 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)

CHill60 · Accepted Answer · 2014-07-03T04:18:00

You need to learn about JOIN [^]

Or use the tools provided in Visual Studio to build queries for you.

Like this

SELECT sale.SaleID, Product.ProductName
FROM sale 
INNER JOIN Allocation ON Allocation.AID = sale.AID 
INNER JOIN Purchase ON Allocation.PurChaseID = Purchase.PurchaseID 
INNER JOIN Product ON Purchase.ProductID = Product.ProductID