asked on

Help with MS SQL Server Query

I am trying to figure out how to build a query to select data from a specific table that is tied to two other tables.

The schema can be broken down to the following tables:
dbo.Orders
ID and OrderNumber

dbo.OrdersASIDocumentLink
ID, OrderID

dbo.ASIDocuments
ID, DocumentContents

What I need to do is to be able to select all rows in ASIDocuments that match the OrderNumber from Orders. Both tables are joined by OrdersASIDocumentLink.

How can I do this? I have a SQL Fiddle with the schema already created with some sample data. Here is a link to a SQL Fiddle.

Hugo Alves

Hello,

I believe this should provide what you want.

Select ord.OrderNumber, doc.*
FROM dbo.Orders ord, dbo.OrdersASIDocumentLink lnk, dbo.ASIDocuments doc
WHERE ord.ID = lnk.OrderID
AND lnk.ID = doc.ID
AND ord.OrderNumber = [your_order_number]

Open in new window

Thank you

Jim Horn

> ... select all rows in ASIDocuments that match the OrderNumber from Orders.
Ok. Please take your mock schema up top and add some content regarding foreign keys, i.e. how would one relate ASIDocuments to Orders? I would expect to see something like ASIDocuments has a foreign key OrderID.

When I see 'ID' I assume that that's the primary key for that table, and not a foreign key to another table.

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Riddles

ASKER

@Hugo
I tried your suggestion, however it yields 0 results. See here.

@ Jim
Sorry, I did fail to mention the relationships. I also realized that I left out a column.
dbo.Orders
ID and OrderNumber

dbo.OrdersASIDocumentLink
ID, OrderID, ASIDocumentID

dbo.ASIDocuments
ID, DocumentContents

The OrderID from dbo.OrdersASIDocumentLink table corresponds to the ID in dbo.Orders.
The ASIDocumentID from dbo.OrdersASIDocumentLink table corresponds to the ID in dbo.ASIDocuments.

Hugo Alves

Hello,

I believe the query should be like this:

Select ord.OrderNumber, doc.*
FROM Orders ord, OrdersASIDocumentLink lnk, ASIDocuments doc
WHERE ord.ID = lnk.OrderID
AND lnk.ASIDocumentID = doc.ID
AND ord.OrderNumber = 22227

Open in new window

But you have an error your your example. None of the rows that you added to "OrdersASIDocumentLink" have a corresponding ID in "Orders". SO the result of that query will always be an empty set.

EDIT:
The values for "OrderID" in "OrdersASIDocumentLink" don't have a corresponding value for "ID" in "Orders". So an empty set is the correct result.

Thank you

Bill Prew

Based on that see my second query in my post above.

»bp

Jim Riddles

ASKER

Okay, I realize that I had the data mixed up. I have created a new SQL Fiddle here.
The query still does not work as expected, though.

@Bill
How would I specify the order number that I am searching for in your example?

Bill Prew

select d.DocumentContents
from ASIDocuments d, OrdersASIDocumentLink x, Orders o
Where d.Id = x.ASIDocumentID
And x.OrderId = o.Id
And o.OrderNumber = 1234;

Open in new window

»bp

Jim Riddles

ASKER

You are setting the handler before the item exists in the DOM. Move your script to the bottom of the page, or wrap it in a document ready function.

Hugo Alves

The query works perfectly

Select ord.OrderNumber, doc.*
FROM Orders ord, OrdersASIDocumentLink lnk, ASIDocuments doc
WHERE ord.ID = lnk.OrderID
AND lnk.ASIDocumentID = doc.ID
AND ord.OrderNumber = 21729

Open in new window

You just don't have any row in "OrdersASIDocumentLink" for the OrderNumber that you were trying to retrieve "22227". Try "21729".

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Riddles

ASKER

Here is the schema creation:

CREATE TABLE Orders ([ID] int, [OrderNumber] int);
CREATE TABLE OrdersASIDocumentLink ([ID] int, [OrderID] int, ASIDocumentID int);
CREATE TABLE ASIDocuments ([ID] int, [DocumentContents] varchar(5));

INSERT INTO Orders
  ([ID], [OrderNumber])
VALUES
  (1,21727), (2,21729), (3,21729), (1758,22227);
INSERT INTO OrdersASIDocumentLink
  ([ID], [OrderID], [ASIDocumentID])
VALUES
  (1,957,1), (2,960,2), (3,960,3), (1503,1758,2069), (1504,1758,2070), (1505,1758,2071), (1506,1758,2072);
INSERT INTO ASIDocuments
  ([ID], [DocumentContents])
VALUES
  (1,'L1'), (2,'L2'), (3,'L3'), (1503,'L4'), (1504,'L5'), (1505,'L6'), (1506,'L7');

Open in new window

You can see that in the OrdersASIDocumentLink table, there are four records at the end that have an OrderID of 1758. That is the ID in the Orders table for OrderNumber 22227.

I am still getting 0 results when I run the query here.

ASKER CERTIFIED SOLUTION

Hugo Alves

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Riddles

ASKER

Hugu...see my last post. I have set the ID for that record to 1758, not 4. The query should work with the data that I have.

Hugo Alves

Hello,

You are making a real mess.
Now where are the rows in "ASIDocuments" with any of the ASIDocumentID values that you have in "OrdersASIDocumentLink" (2069, 2070, 2071, 2072) for the OrderID of 1758?

Try this:

CREATE TABLE Orders ([ID] int, [OrderNumber] int);
CREATE TABLE OrdersASIDocumentLink ([ID] int, [OrderID] int, ASIDocumentID int);
CREATE TABLE ASIDocuments ([ID] int, [DocumentContents] varchar(5));

INSERT INTO Orders
  ([ID], [OrderNumber])
VALUES
  (1,21727), (2,21729), (3,21729), (4,22227);
INSERT INTO OrdersASIDocumentLink
  ([ID], [OrderID], [ASIDocumentID])
VALUES
  (1,1,1), (2,2,2), (3,2,3), (1503,3,2069), (1504,3,2070), (1505,3,2071), (1506,4,2072);
INSERT INTO ASIDocuments
  ([ID], [DocumentContents])
VALUES
  (1,'L1'), (2,'L2'), (3,'L3'), (1503,'L4'), (1504,'L5'), (1505,'L6'), (2072,'L7');

Open in new window

with the query:

Select ord.OrderNumber, doc.*
FROM Orders ord, OrdersASIDocumentLink lnk, ASIDocuments doc
WHERE ord.ID = lnk.OrderID
AND lnk.ASIDocumentID = doc.ID
AND ord.OrderNumber = 22227

Open in new window

Thank you

Bill Prew

Try this change, and then my earlier query (or any of the other identical ones). Notice I changed the fourth test data record.

INSERT INTO OrdersASIDocumentLink
  ([ID], [OrderID], [ASIDocumentID])
VALUES
  (1,957,1), (2,960,2), (3,960,3), (4,1758,1503), (1504,1758,2070), (1505,1758,2071), (1506,1758,2072);

Open in new window

»bp

Jim Riddles

ASKER

Ugh...I am just not with it today. Okay, so it was a flaw in my data. I was trying to put it together too quickly in the SQL Fiddle, and entered incorrect values for the ASIDocumentID. Hugo, your solution was working perfectly, as you pointed out several times. I apologize for my confusion. For posterity's sake, I am posting the updated schema here.

CREATE TABLE Orders ([ID] int, [OrderNumber] int);
CREATE TABLE OrdersASIDocumentLink ([ID] int, [OrderID] int, ASIDocumentID int);
CREATE TABLE ASIDocuments ([ID] int, [DocumentContents] varchar(5));

INSERT INTO Orders
  ([ID], [OrderNumber])
VALUES
  (1,21727), (2,21729), (3,21729), (1758,22227);
INSERT INTO OrdersASIDocumentLink
  ([ID], [OrderID], [ASIDocumentID])
VALUES
  (1,957,1), (2,960,2), (3,960,3), (1503,1758,2069), (1504,1758,2070), (1505,1758,2071), (1506,1758,2072);
INSERT INTO ASIDocuments
  ([ID], [DocumentContents])
VALUES
  (1,'L1'), (2,'L2'), (3,'L3'), (2069,'L4'), (2070,'L5'), (2071,'L6'), (2072,'L7');

Open in new window

Hugo Alves

Hello,

No problem. Glad I could help.

Thank you