Will not show row with Line Type 2 1

[Cpreston]

HI

I have a query I am trying to fit together. The Works Order as 3 stages signified by linetype field which can be 1 ,2 or 3.

My first query brings in all 3 rows. S you can see ProductID does not have a ProductID

But however I need to add in product Information so in my second query I only get 2 lines. I need however the type 2 row to show even if as just NULLS. Any ideas what i can do to the code to make this row show.

SELECT 
                         dbo.WorksOrderLine.ProductID, 
						 dbo.Product.ProductCode, 
						 dbo.Product.Description, 
						 dbo.WorksOrderHeader.WorksOrderID, 
						 dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.WorksOrderHeader.DateTimeProcessed, 
						 dbo.Product.Thickness,
						 dbo.Product.Width, 
						 dbo.Product.Length, 
						 dbo.Product.DefaultPiecesPerPack,
						 dbo.WorksOrderLine.LineType
FROM            dbo.WorksOrderHeader INNER JOIN
                         dbo.WorksOrderLine ON dbo.WorksOrderHeader.WorksOrderID = dbo.WorksOrderLine.WorksOrderID INNER JOIN
                         dbo.Product ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID INNER JOIN
                         dbo.[148-vwWOSourceProductPlantCP1] ON dbo.WorksOrderHeader.WorksOrderID = dbo.[148-vwWOSourceProductPlantCP1].WorksOrderID
GROUP BY dbo.WorksOrderHeader.WorksOrderID, dbo.WorksOrderLine.LineType, dbo.WorksOrderLine.ProductID, dbo.WorksOrderHeader.WorksOrderNumber, dbo.Product.ProductCode, dbo.Product.Description, 
                         dbo.Product.Deleted, dbo.WorksOrderHeader.DateTimeProcessed, dbo.Product.DefaultPiecesPerPack, dbo.Product.Thickness, dbo.Product.Width, dbo.Product.Length
HAVING        (dbo.Product.Deleted = 0) AND (dbo.WorksOrderHeader.WorksOrderNumber = 299925)
ORDER BY dbo.Product.ProductCode, dbo.WorksOrderHeader.DateTimeProcessed DESC

 

[Andrzejek]

Why do you have GROUP BY and HAVING when you do not have any SUM, AVG, MIN, MAX, etc. in your Select?
[tt]
...
INNER JOIN dbo.Product ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID
...
[/tt]
You may consider a LEFT JOIN

BTW. You would benefit from using aliases for your tables.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

Thanks for the reply. I have tried the Joins but keep getting just the 2 rows unfortunately.

Not sure on the Grouping what you say makes sense there is no need I will alter that.

 

[Cpreston]

Code is now changed and I tired the LEFTY JOIN but same result, any more ideas please.

SELECT DISTINCT 
                         dbo.WorksOrderLine.ProductID, dbo.Product.ProductCode, dbo.Product.Description, dbo.WorksOrderHeader.WorksOrderID, dbo.WorksOrderHeader.WorksOrderNumber, 
                         dbo.WorksOrderHeader.DateTimeProcessed, dbo.Product.Thickness, dbo.Product.Width, dbo.Product.Length, dbo.Product.DefaultPiecesPerPack, dbo.WorksOrderLine.LineType
FROM            dbo.WorksOrderHeader INNER JOIN
                         dbo.WorksOrderLine ON dbo.WorksOrderHeader.WorksOrderID = dbo.WorksOrderLine.WorksOrderID LEFT JOIN
                         dbo.Product ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID FULL OUTER JOIN
                         dbo.[148-vwWOSourceProductPlantCP1] ON dbo.Product.ProductID = dbo.[148-vwWOSourceProductPlantCP1].ProductID AND 
                         dbo.WorksOrderHeader.WorksOrderID = dbo.[148-vwWOSourceProductPlantCP1].WorksOrderID
WHERE        (dbo.Product.Deleted = 0) AND (dbo.WorksOrderHeader.WorksOrderNumber = 299925)
ORDER BY dbo.Product.ProductCode, dbo.WorksOrderHeader.DateTimeProcessed DESC

 

[Andrzejek]

You are connecting your dbo.Product to dbo.WorksOrderLine by ProductID, but your LineType 2 does not have any ProductID and that's why LineType 2 does not show up in your output.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Yes I get that but is there no way of linking somehow, I doubt there is but I need the 2 as I will be adding in another table that requires line type 2.

 

[Andrzejek]

Is there any other way you can connect dbo.Product table to dbo.WorksOrderLine table? Other than by ProductID?
If not, you may need to create some sub-queries to accomplish what you need.

At this stage, I would just play with these 2 tables to get all required records, before you add any other tables to it.

Did you try:

...
From ... 
dbo.WorksOrderLine LEFT JOIN dbo.Product ON dbo.WorksOrderLine.ProductID  = dbo.Product.ProductID
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

Yes I tried the below but it gave the same result. I would not know where to start with sub querys unfortunately a bit beyond my sql skills. I have also looked through all the tables and cannot see anything else I can join it with so a bit stuck on this one.

dbo.WorksOrderLine LEFT JOIN dbo.Product ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID
...

Thanks

 

[Andrzejek]

Did you try just the 2 tables: dbo.WorksOrderLine and dbo.Product with the LEFT JOIN?

To quote SQL - LEFT JOINS:
The SQL LEFT JOIN returns all rows from the left table [dbo.WorksOrderLine], even if there are no matches in the right table [dbo.Product]. This means that if the ON clause matches 0 (zero) records in the right table]dbo.Product\; the join will still return a row in the result, but with NULL in each column from the right table[dbo.Product].

This means that a left join returns all the values from the left table [dbo.WorksOrderLine], plus matched values from the right table [dbo.Product] or NULL in case of no matching join predicate.

So, this should return all records from dbo.WorksOrderLine table, including Line2
[tt]
SELECT ...
FROM dbo.WorksOrderLine
LEFT JOIN dbo.Product
ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID;
[/tt]
---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

Yes tried with 2 tables per the code below but does not show LineType 2 at all which is the null ones

SELECT        dbo.WorksOrderLine.ProductID, dbo.Product.ProductCode, dbo.Product.Description, dbo.Product.Thickness, 
			  dbo.Product.Width, dbo.Product.Length, dbo.Product.DefaultPiecesPerPack, dbo.WorksOrderLine.LineType
FROM            dbo.WorksOrderLine LEFT OUTER JOIN dbo.Product ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID
WHERE        (dbo.Product.Deleted = 0) AND (dbo.WorksOrderLine.LineType = 2)
ORDER BY dbo.Product.ProductCode

 

[Andrzejek]

[tt]SELECT dbo.WorksOrderLine.*
FROM dbo.WorksOrderLine
WHERE WorksOrderdID = 314172[/tt]

This above SQL should give you 3 records, right? With the LineType 1, 2, and 3

So, this one below should also give you 3 records:

[tt]SELECT L.ProductID, P.ProductCode, P.Description, P.Thickness,
P.Width, P.Length, P.DefaultPiecesPerPack, L.LineType
FROM dbo.WorksOrderLine L LEFT JOIN dbo.Product P ON L.ProductID = P.ProductID
WHERE L.WorksOrderdID = 314172[/tt]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

Ok this brings back 51 rows but equal for both queries, both have 51.

Each Works Order as a

Source product = LineType 1 on the works order there is 21 rows this is correct
Process = LineType2 this information comes from worksorderschedule but your query is showing one line for linetype2 ( I will see if I can link this to the query and see if it gives me the information)
Finished product = LineType 3 on the works order there is 27 rows this is correct

 

[Cpreston]

I have joined in another table bu the W.LineText is coming in as NULL for all lines. Linetype 2 is still there but no information as mentioned.

SELECT        L.ProductID, P.ProductCode, P.Description, P.Thickness, P.Width, P.Length, P.DefaultPiecesPerPack, L.LineType, W.LineText
FROM                    dbo.WorksOrderScheduleLine as W LEFT JOIN
                         dbo.WorksOrderLine AS L ON w.WorksOrderID = L.WorksOrderID LEFT JOIN
                         dbo.Product AS P ON L.ProductID = P.ProductID
WHERE        (L.WorksOrderID = 314172)

 

[Andrzejek]

I guess you need to play attentions to the order in which you add new tables to your Select statement:

How about:
[pre]
SELECT L.ProductID, P.ProductCode, P.Description, P.Thickness, P.Width,
P.Length, P.DefaultPiecesPerPack, L.LineType, W.LineText
FROM dbo.WorksOrderLine AS L LEFT JOIN
dbo.Product AS P ON L.ProductID = P.ProductID [blue]LEFT JOIN
dbo.WorksOrderScheduleLine As W [/blue]ON W.WorksOrderID = L.WorksOrderID
WHERE L.WorksOrderID = 314172[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Sorry same result all are NULL

Thanks

 

[Andrzejek]

But you did have what you needed before you added WorksOrderScheduleLine, right?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Maybe a hint is in thread701-1814185 ...

That would mean, try some parenthesis:[pre]
SELECT L.ProductID, P.ProductCode, P.Description, P.Thickness, P.Width,
P.Length, P.DefaultPiecesPerPack, L.LineType, W.LineText
FROM [highlight #FCE94F]([/highlight]dbo.WorksOrderLine AS L LEFT JOIN
dbo.Product AS P ON L.ProductID = P.ProductID[highlight #FCE94F])[/highlight] LEFT JOIN
dbo.WorksOrderScheduleLine As W ON W.WorksOrderID = L.WorksOrderID
WHERE L.WorksOrderID = 314172 [/pre]

Just a guess here...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

First of all thanks for your replies

None of your last reply's worked so I thought I would back track a bit as the logic is all there but not working.

I created a simple query to see whats is in the WorksOrderScheduleLine.lintext fields

select * from WorksOrderScheduleLine where LineText is not null

But all of the rows have worksorderid as null. So everything with an entry in linetext is s null in worksorderid. So that tells me no wonder we are not getting the right data back.

Leave it with me for a while so I can back track and see what holds these tables together. Thanks again