CODEWITHSUNDEEP
sqljoinrazor
702cs
702cs

Reputation: 331

SQL Join Tables and Inner Joins?

I have 3 tables. Products, Steps and Pictures.

I'm working with Microsoft Visual Studio and Razor currently I'm using for example UrlData to load the correct tables and fields in the db. www.example.com/Id=1 (Shows product #1)

var productId = UrlData[0].AsInt();
var product = db.QuerySingle("SELECT * FROM Steps WHERE Id = @0", productId);

I have successfully been able to pull the data I wanted to display but the problem now is im using a very simple +1 UrlData or -1 UrlData to go to the next step or backwards. Obviously this will not work if a step is deleted and the website will not stop at the end of the steps for a product.

I'm at the point now I believe I will need to use Join Tables I will need to display all the data above from the 3 tables by using UrlData.

I have created a test join table ex: "joinTest" Fields: Id, PictureId, StepId, ProductId

How would I query this table to display all of the data above in the first paragraph?

More examples of my razor queries:

string description = product.description;
var step = db.QuerySingle("SELECT * FROM Products WHERE Id = @0", product.ProductId);
var steps = db.Query("SELECT Id, StepName FROM Steps WHERE ProductId = @0", productId).ToList();
var photos = db.Query("SELECT Id, FileTitle FROM Pictures WHERE StepId = @0", productId).ToList();

Upvotes: 2

Views: 83

Answers (2)

Luciano Andress Martini
Luciano Andress Martini

Reputation: 186

Humm if you just want to display data from the 3 tables, why you created a third table? Just do: 

select products.name as Product, steps.description as Description, pictures.filetitle as MyPicture   
from pictures
join steps on steps.id=pictures.stepid
join products on products.id=steps.productid 
where steps.id=@yourvariable

But i dont think this will solve the problem with +1 or -1, actually i cant see a relation beetween that and join command!

Is not better to load an array with all available ids before changing page ?

You can specify any column and you will see all the data you want.

Is that you wanted?

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416083

You don't need to add a new table for this. Just a new field NextStepId or StepSeqNum to the existing Steps table. You also need to add the current step to the Url.

Upvotes: 1

Related Questions