Help with building query

Hello,

This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have:

Two tables:

Table 1:
Orders
Some of the fields:

ID
PropID
WorkOrderNum
OrderDesc
DateCompleted

Table 2:
OrderDetail

ID
OrderID
TenantName

As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record.

Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc):

SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%'

Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out.

thanks.Acciording to your explanation,
1. PropertyLocations has 1:many relation to ORDERS,
2. Orders has 1:many relation to OrderDetail.

My first try would be like below for TenantName:
SELECT Distinct
PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID, Orders.PropID, Orders.WorkOrderNum,
Orders.OrderDesc, Orders.DateCompleted
FROM PropertyLocations INNER JOIN Orders
ON PropertyLocations.ID = Orders.PropID
INNER JOIN OrderDetail
ON Orders.ID = OrderDetail.OrderID
WHERE Orders.OrderDesc LIKE '%lds%'
AND OrderDetail.TenantName LIKE 'Steve%'|||That's almost it. It's pulling up all my orders that has the specified 'tenantname' (that is great!)...but it's showing duplicate orders.

For instance:
I have a record in my 'Orders' table of ID=2886. And in my 'OrderDetail' table, there are 3 records that have the characters 'dr.' in it that all have the value of 2886 in the 'OrderID' field which means that they all belong to the record in the 'Orders' table with ID of 2886 that I mentioned at the beginning of this paragraph. So it is showing me my data from the 'Orders' table 3 times (duplicates).

I also modified your statement slightly:

SELECT Distinct PropertyLocations.PropertyLocation, OrderDetail.TenantName, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderID WHERE OrderDetail.TenantName LIKE '%Dr.%' ORDER BY WorkOrderNum DESC

I'm only doing 1 'LIKE' at a time...but that's probably my fault...I wasn't real clear about it.

So how do I make it show me just a single instance of a record from the 'Orders' table even though that there may be several records in the 'OrderDetail' table that matches the search criteria?

P.S. Thank you for reminding me about trying to be SQL99 compliant. ;)

thanks.|||The reason that you do have duplicates is because of Orders.DateCompleted even if you have DISTINCT in the SELECT statement.
Therefore my next question would be
1. Do you need Orders.DateCompleted to display?
2. If YES, then duplicates are unavoidable.
3. If NO, then remove Orders.DateCompleted from the SELECT statement. - Problem solved.

In case of YES,
1. Do the dates of Orders.DateCompleted fall into the same date?
If YES, then use CAST(Orders.DateCompleted as varchar(11)) to pick up only the date portion that ignores time portion of the Orders.DateCompleted. - Problem solved.
If NO, then still duplicates are unavoidable.|||Well, I tried removing the Orders.DateCompleted field even though I really wanted it, but it still gave me the same results.

I included 2 screenshots now, one w/DateCompleted so everyone can see exactly what I'm getting, and my SQL statement, and then 1 after I removed the Orders.DateCompleted field.

Just to be sure that everyone knows what I mean when I say duplicates, I mean that the same exact Work Order (which is a row...or an order...or record) is showing up multiple times even though only 1 actually exists in the database. You can see this in the screenshots - there are 3 rows of the WO#6997 which is far from correct - there is only 1 record in the database with that Work Order Number. But there are 3 unique records in the OrderDetail table that contain the characters 'dr.' in it that belong to that WO#6997 work order. (And just to clarify, the WorkOrderNum is not my 'unique id' field in the 'Orders' table, nor is it the field that is 'linked' with the OrderDetail table)

If the above is what everyone already realized/knew, then cool, but if it wasn't, then maybe it will help clear things up for someone to help a little easier.

thanks.|||Let me clarify
1. You have one record in the Order table with WO#6997.
2. You have three records in the OrderDetail table with WO#6997 and TenantName like '%dr%'.
3. WO# in the Order table is not unique.
4. WO# in the OrderDetail is not unique.

OK.
First, my previous email regarding the Orders.DateCompleted was incorrect. Because it belongs to Orders table, it wouldn't generate duplicates. So please ignore my suggestion to remove the column Orders.DateCompleted.

Then please show me the TenantNames. Are they the same?
If they are different, obviously you will have 3 records.
Inside the SELECT statement, you have the following columns to display:

PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompleted

Among them, only TenantName comes from OrderDetail table.
Therefore if TenantName is the same it shouldn't display three records.
I am puzzled and very curious on the result for the TenantName.|||1. Yes
2. Kind of...The OrderDetail table doesn't actually have a WO# field in it. It has an OrderID field that corresponds to the ID field of the Orders table (which contains a WO# field)...but yes, there are 3 records that show up from the OrderDetail field with a TenantName like '%dr.%' that belong to the record that contains WO#6997 in the Orders table
3. Actually, WO# is unique (although there might be some records in the table ('Orders' table as I mentioned), that don't have a value for this field...but the records would be so old, it will never come up...so is it still considered 'unique'?), but isn't my identity field. I have an ID field for that.
4. As I mentioned, the OrderDetail table does not have a WO# field.

Ok, no problem.

Below are the 3 records that came up that matched the criteria:
The following data is from the TenantName field, and yes, as you can see, are unique. They all share the same OrderID of 2886...which is the ID of the Orders table. So I guess this is where the problem is? See, I don't really care that 3 records from the OrderDetail table matches my search criteria...all I need to know is that at least one record matches, and then I want to show the Order/Work Order from the Orders table that it corresponds to...1 time.

At Napa 9, 870 Napa Valley Corp. Dr.:
Work to be done at: [nl]870 Napa Valley Corp.Dr./Napa 9[nl]2511-2515 Napa Valley Corp. Dr./The Vines[nl]~+
At The Vines, 2511-2515 Napa Valley Corp. Dr.:

Yes, those are the columns I would like to display, and yes, TenantName is the only one that comes from the OrderDetail table.
As you see, the 3 records aren't the same that match the search criteria.|||Now I can see whole picture.

You have one record in the Order table with WO#6997.
You have three records in the OrderDetail table with WO#6997 and TenantName like '%dr%'.
ID is primary key in Order table.
OrderID in the OrderDetail is foreign key to Order table.

Inside the SELECT statement, you have the following columns to display:

PropertyLocations.PropertyLocation,
OrderDetail.TenantName,
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompleted

But OrderDetail.TenantName are not the same. Therefore you have to force it to be the same by using the criteria.

So
Select Distinct
PropertyLocations.PropertyLocation,
'Like ''%Dr.%''' as [TenantName Criteria],
Orders.ID,
Orders.PropID,
Orders.WorkOrderNum,
Orders.OrderDesc,
Orders.DateCompletedFROM PropertyLocations INNER JOIN Orders ON PropertyLocations.ID = Orders.PropID INNER JOIN OrderDetail ON Orders.ID = OrderDetail.OrderID WHERE OrderDetail.TenantName LIKE '%Dr.%' ORDER BY WorkOrderNum DESC

I hope this should satisfy your requirements.|||Cool, I'm sorry it took so long to get there.

1. Yes
2. Yes, in a way...see #2 from my previous post
3. Yes
4. Yes

Ok, this is where you're going to want to kill me. I just realized after re-reading your post...I don't actually need to display OrderDetail.TenantName. You can even see that I'm not showing that field/column in my screenshots...silly me.

So I tried one of your previous SELECT statements, and removed it (the OrderDetail.TenantName part) from the beginning so as to not display it, and it worked! I'm so sorry for all of the confusion and the extra steps.

But out of not wanting all your hard work to not be in vain, I tried your last statement, and it worked too! So now I know what to do if I have a similar situation. Although, I do have a question...I've never seen part of your statement before:

'Like ''%Dr.%''' as [TenantName Criteria],

What is that all about? Obviously it works, but it's new to me. I also don't really understand why it would've worked without showing the OrderDetail.TenantName column.

Thanks for sticking with me and all of your help, TerryP.|||I am glad that I could help you.

Your question:

'Like ''%Dr.%''' as [TenantName Criteria],
This is a constant column. Because the TenantName is not actually the name of the tenant but a memo or description, I tried to make a common string for TenantName which comes from the criteria in the WHERE clause. When it displayed, all three records would have 'Like ''%Dr.%'''. So I could eliminate multiple records of the same constant string to a single record by using DISTINCT keyword in SELECT clause.
I also don't really understand why it would've worked without showing the OrderDetail.TenantName column
You do not have to display all or any of the columns of tables used in FROM clause. But it would have returned three rows if DISTINCT had not been used. So the trick was in DISTINCT keyword.