Extending an old query

Question

0.00/5 (No votes)

See more:

I've ran in to yet another brick wall when it comes to SQL, this time I'd like to extend a query which I've inherited to add some more functionality.

I'm not sure if it's the possible to do this in the way I've tried to go about solving this but I've tried to add this new requested data as a sub query in the select statement.

Here is what I want to add to my query. From the id in the table fcontainer I've got to do this little walk to reach a product name.

SQL

select produkter.Produktnamn 
from FContainer INNER JOIN
                  Stämplingar ON FContainer.id = Stämplingar.ID INNER JOIN
                  Tempo ON Stämplingar.temponr = Tempo.temponr INNER JOIN
                  Produkter ON Tempo.produktnr = Produkter.Produktnr

The query which I wish to extend contains fcontainer but only in another sub query.

SQL

SELECT FContainerHylla.Name, FContainerPlats.HyllId,
       FContainerPlats.x, FContainerPlats.y,
       FContainerPlats.enable, FContainerHylla.Type,
       ISNULL((SELECT MIN(fcontainer.id) AS id
           FROM FContainer
           WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId
FROM FContainerPlats INNER JOIN FContainerHylla ON 
     FContainerPlats.HyllId = FContainerHylla.Id
WHERE (FContainerHylla.Type IN (1, 6, 10))
ORDER BY FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y

So far my best results is by adding the first query inside the select statement surrounded by ( ) to form a sub query but I get an error where I'm not allowed to have more than one value in a sub query.

I need to reduce the possible data by adding a where clause and I'd like to add

SQL

where fcontainer.id = ....

I need fcontainer.id to be compared to the value returned from the isnull statement and defined AS id but not certain how to do this.

Posted 9-Jun-15 23:08pm

Member 11683251

Add a Solution

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 · 2015-06-10T00:05:00

Solution 1

You can JOIN onto a sub query rather than having it in the select. I came up with this but I can't test it

SQL

SELECT FContainerHylla.Name, FContainerPlats.HyllId,
       FContainerPlats.x, FContainerPlats.y,
       FContainerPlats.enable, FContainerHylla.Type,
       ISNULL((SELECT MIN(fcontainer.id) AS id
           FROM FContainer
           WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId

		,SubQ.Produktnamn
		   
FROM FContainerPlats 
INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id

INNER JOIN (SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamn
			from FContainer INNER JOIN
                  Stämplingar ON FContainer.id = Stämplingar.ID INNER JOIN
                  Tempo ON Stämplingar.temponr = Tempo.temponr INNER JOIN
                  Produkter ON Tempo.produktnr = Produkter.Produktnr) SubQ ON SubQ.SubID = FContainerPlats.id

WHERE (FContainerHylla.Type IN (1, 6, 10))
ORDER BY FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y

I might have the key on the inner join to "SubQ" wrong.

Alternatively you could set them up as two Common Table Expressions and join them that way (again this is untested and watch out for the id I've used in the join)

;WITH CTE1 AS
(
	SELECT FContainerHylla.Name, FContainerPlats.HyllId,
		   FContainerPlats.x, FContainerPlats.y,
		   FContainerPlats.enable, FContainerHylla.Type,
		   ISNULL((SELECT MIN(fcontainer.id) AS id
			   FROM FContainer
			   WHERE (PlatsId = FContainerPlats.id)), 0) AS FcId

	FROM FContainerPlats 
	INNER JOIN FContainerHylla ON FContainerPlats.HyllId = FContainerHylla.Id
	WHERE (FContainerHylla.Type IN (1, 6, 10))
),
CTE2 AS 
(
	SELECT FContainer.id AS SubID, produkter.Produktnamn AS Produktnamn
	from FContainer 
		INNER JOIN Stämplingar ON FContainer.id = Stämplingar.ID 
		INNER JOIN Tempo ON Stämplingar.temponr = Tempo.temponr 
		INNER JOIN Produkter ON Tempo.produktnr = Produkter.Produktnr
) 
SELECT CTE1.*, CTE2.Produktnamn
FROM CTE1
INNER JOIN CTE2 ON CTE2.SubID = FContainerPlats.id
ORDER BY FContainerPlats.HyllId, FContainerPlats.x, FContainerPlats.y

Posted 10-Jun-15 0:05am

CHill60

v2

Comments

Member 11683251 10-Jun-15 6:38am

I was just trying to solve this using CTE as it seems to be such a wonderful tool as you showed a while back in answer to another question but didn't think of doing it on the new query too. Your solution works with the minor adjustment where you joined the two tables where it should be CTE2.subId = CTE1.fcid and the table names in the order by should be CTE1.

The first answer where you join on to sub queries is error free but doesn't return anything but I'll check through it too since that seems so closer to what I was trying to do.

I thank you for taking the time to write this helpful answer, going to bookmark this one too.

CHill60 10-Jun-15 7:10am

I've probably got the ON clause wrong or have picked up the wrong id in the sub-query.
I do like CTEs (you may have guessed!), but I do have a habit of becoming blinkered - it's often the first thing I reach for when sometimes a CASE in the order by clause will be more appropriate (for example) - not in this case but one to watch out for if you start to like CTE more and more :-)

Member 11683251 10-Jun-15 7:45am

The two solutions differ slightly in the results I get, the CTE one returns slightly more than the other but what I noticed is that both do is return a lot of duplicate records.

The original query returns about 2500 records while these between 1700-1800 and that is with duplicates.

Take the CTE for example, running just CTE1 returns a set of 2500 records and CTE2 6600. So far it's exactly as expected.

I cant wrap my head around how the part which joins those two tables can manage to give me so many fewer records and with duplicates, some row triplicate or more. The way I interpret it is that we are referencing CTE2 and if we find a match with the id's we basically append the name. Not really what's happening but how I picture it in my head.

Here are the changes I made.

SELECT CTE1.*, CTE2.Produktnamn
FROM CTE1
INNER JOIN CTE2 ON CTE2.SubID = CTE1.FcId
ORDER BY CTE1.HyllId, CTE1.x, CTE1.y

It feels like learning to program all over again trying to learn sql, or rather more than just the basics.

CHill60 10-Jun-15 8:16am

Try SELECT TOP 1 FContainer.id AS SubID, produkter.Produktnamn AS Produktnamn ... etc in the subquery or CTE2, I think I'm pulling back too much just to get the productname

Member 11683251 10-Jun-15 8:27am

But wouldn't that just result in a single record selected?

CHill60 10-Jun-15 8:31am

Yes - In theory a single record per product, which when joined to the other results should give the product name for each of those - make sure it's in the sub-query not the main query :)

Member 11683251 10-Jun-15 8:34am

Did it in the CTE version of the problem first but I also tried it in the sub query and the same result, it only returns one record for the entire query. That's certainly one way to assure there isn't any duplicates ^^

CHill60 10-Jun-15 8:36am

LOL! I can't see where I've gone wrong at the moment - I'll have another look. Let me know if you find it first!

Member 11683251 11-Jun-15 6:39am

I've gone over the CTE table several times now between other tasks and it really bugs me that is doesn't do what I think it should.

We have two tables, we do an inner join on them and ask to select a few things. If it was math it should be like an intersection of the two sets so why do I get such a weird result when they both check out fine if they are run simultaneously?

Tbh this has gone from a work task to something of a more private curiosity.

CHill60 11-Jun-15 7:35am

It's got me stumped. It's probably going to be something so obvious when one of us spots it.

Member 11683251 11-Jun-15 8:43am

Asked on DBA stackexchange and have gotten some interesting info. One suggestion to use outer join showed one thing I didn't consider and that's not all fcontainers got a produktnamn reachable using CTE2. Which explains why the joining produces so much fewer results.

Figuring out how to get those names shouldn't be to hard to do for me once I've cleared out the duplicates.

CHill60 11-Jun-15 9:05am

Yes - sorry I forgot about that difference and was focussing on the duplicates.
Try putting DISTINCT into CTE1 i.e. SELECT DISTINCT FContainerHylla.Name, FContainerPlats.HyllId,... or (possibly better) SELECT DISTINCT CTE1.*, CTE2.Produktnamn ... etc

Member 11683251 11-Jun-15 9:19am

Bullseye ;)

Select Distinct CTE1.* and duplicates are gone.

That combined with left join did the trick. Saw I wrote outer join in the last comment but was left join.

Thanks for hanging with me and solving it.

CHill60 11-Jun-15 9:23am

I'm pleased we got there!