Using alias tables within select statements

[eyorre]

I have a horrible long select statement and I need to use an alias table within it. I have not used alias tables much before and I am having problems getting my syntax correct. When I run my ASP page I get the message "Incorrect syntax near the keyword 'RIGHT'". I have bolded where my alias table is. It looks like it is the RIGHT join directly after this where the problem is happening. (If I replace my alias table in the statement with a 'real' table this error does not occur).


SELECT tblName.Surname, tblName.Firstname, tblName.ID AS NameID, tblCountry.Country, tblCountry.Region, tblCountry.ID AS CountryID, tblSection.SectionName, tblSection.ID AS SectionID, tblHeader.Heading, tblEntryValue.EntryValue
FROM (SELECT TOP 100 NameID FROM tblEntryValue AS tblALIAS WHERE HeaderID='" & SomeValue & "' ORDER BY EntryValue DESC)
RIGHT JOIN (tblSection INNER JOIN (tblHeader LEFT JOIN ((tblCountry RIGHT JOIN tblName ON tblCountry.ID = tblName.CountryID) RIGHT JOIN tblEntryValue ON tblName.ID = tblEntryValue.NameID) ON tblHeader.ID = tblEntryValue.HeaderID) ON tblSection.ID = tblHeader.SectionID) ON tblALIAS.NameID = tblEntryValue.NameID

Can anyone spot why my alias table is causing problems?

I could also do with adding another field to my alias table - one which would contain an autonumber or ranking order rather than just pulling in a field that already exists in a table. I would therefore be aiming for my alias table to be something like:

OrderID | NameID
1 | 90
2 | 23
3 | 45

 

[mwolf00]

When you put the parenthesis around the select, you are creating a derived table which needs an alias given after you close the parentsesis- is that what you are trying to do?

Derived Example
SELECT * FROM (SELECT fName FROM tblAuthors) as dtNames


Try removing the parenthesis...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[eyorre]

Thanks for your suggestion. I have tried without the parenthesis but it just gives the new error message of "Incorrect syntax near the keyword 'SELECT'".

I have been having a bit of a play with the brackets but to no avail so far.

 

[Tarwn]

As mwolf said above, you need to assign a name to that sub-query in order to use it in a JOIN. Right now your aliasing the table inside the sub-select, but not the actual results of the sub-select. Try doing the alias as mwolf suggested.

-T

 

[eyorre]

Thanks Tarwin. I think I am semi understanding you - so I need to assign a name to the:

"(SELECT TOP 100 NameID FROM tblEntryValue AS tblALIAS WHERE HeaderID='" & SomeValue & "' ORDER BY EntryValue DESC)"

bit before I should be using it in the join? Can I not just use the tblALIAS that I have set it to be?

How would I need to go about applying a name to this sub-query I am not sure how the syntax would work in my statement?:

"(SELECT TOP 100 NameID FROM tblEntryValue AS tblALIAS WHERE HeaderID='" & SomeValue & "' ORDER BY EntryValue DESC) AS MySubquery"?

and then use RIGHT JOIN MySubquery??

Sorry I have probably got this all in a muddle.

 

[mwolf00]

SELECT TOP 100
	tblName.Surname
	, tblName.Firstname
	, tblName.ID AS NameID
	, tblCountry.Country
	, tblCountry.Region
	, tblCountry.ID AS CountryID
	, tblSection.SectionName
	, tblSection.ID AS SectionID
	, tblHeader.Heading
	, tblEntryValue.EntryValue
FROM 
	tblEntryValue ev
JOIN tblName n ON ev.nameID = n.ID
JOIN tblCountry c ON n.countryID = c.ID
JOIN tblHeader h ON ev.headerID = h.ID
JOIN tblSection s ON h.sectionID = s.ID
WHERE ev.HeaderID='" & SomeValue & "'

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

NOt sure about the outer joins - seems like each table would have the relevant data - let me know which table may not join correctly (ie - relationship is not there) and I can update

SELECT TOP 100
	n.Surname
	, n.Firstname
	, n.ID AS NameID
	, c.Country
	, c.Region
	, c.ID AS CountryID
	, s.SectionName
	, s.ID AS SectionID
	, h.Heading
	, ev.EntryValue
FROM 
	tblEntryValue ev
JOIN tblName n ON ev.nameID = n.ID
JOIN tblCountry c ON n.countryID = c.ID
JOIN tblHeader h ON ev.headerID = h.ID
JOIN tblSection s ON h.sectionID = s.ID
WHERE ev.HeaderID='" & SomeValue & "'"

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[eyorre]

Thanks for all this mwolf. At a brief go it is certainly getting further than it did before - no errors. I am going to take a further look at it overnight and give another bash in the morning. Cheers for your help on this.

 

[eyorre]

wow mwolf, I really appreciate your help with this it has certainly sent me off in the right direction. I am extremely close to solving this now.

The following works fine but now I just need to pull in an incrementing number to my derived table.

SELECT
a.SortOrderID,
tblName.ID AS NameID,
tblName.Surname,
tblName.Firstname,
tblCountry.Country,
tblCountry.ID AS CountryID,
tblSection.SectionName,
tblSection.ID AS SectionID,
tblHeader.Heading,
tblEntryValue.EntryValue
FROM
(SELECT TOP 100 percent tblEntryValue.NameID, INCREMENT AS SortOrderID
FROM tblEntryValue
WHERE tblEntryValue.HeaderID='" & SortHeader & "'
ORDER BY tblEntryValue.EntryValue DESC) a
RIGHT JOIN (tblSection INNER JOIN
(tblHeader LEFT JOIN ((tblCountry RIGHT JOIN tblName ON tblCountry.ID = tblName.CountryID) RIGHT JOIN tblEntryValue ON tblName.ID = tblEntryValue.NameID) ON tblHeader.ID = tblEntryValue.HeaderID) ON tblSection.ID = tblHeader.SectionID) ON a.NameID = tblEntryValue.NameID

To get the increment I thought of using the COUNT function and so rewrote the derived part as:

(SELECT TOP 100 percent tblEntryValue.NameID, EntryValue.EntryValue, Count(NameID)
AS SortOrderID FROM tblEntryValue
WHERE tblEntryValue.HeaderID='" & SortHeader & "'
GROUP BY NameID, EntryValue
ORDER BY tblEntryValue.EntryValue DESC) a

The Count seems to stick at 1 though. What it needs to do is to number each row in the derived table as 1,2 etc as below

NameID | SortOrderID
29 | 1
67 | 2
15 | 3

I am sure there must be easy function I can use to create an increment?

 

[mwolf00]

Are you saying that you need to number your results incrementally? My query didn't use a derived table at all. I'm not sure why you need all of the nested joins and derived tables. It seems like they might not be needed.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[eyorre]

Unfortunately because of the way the record set is being used within a table I do need all the joins.

Basically I have a table which the user can select on what heading they want to sort. Unfortunately because of the nature of the table of results I cannot just change the ORDER BY clause in the record set. The sort needs to be determined by the order that NameId is retrieved from tblEntryValue when sorted by EntryValue and where HeaderId is that selected by the user.

Thus the need for the derived table so the increment can be used as the ordering of the recordset in the end.

The table is complex because rather than each loop of the recordset writing to one row of the table it only writes to one cell and the number of columns on view in the table is determined by the user.

I hope this makes my reasons a bit clearer but it does definitely need all those joins. I think I may have to start a new thread with this incremental problem now.

Thanks for your help though MWolf I would not have got this far without it.

 

[eyorre]

I have been a bit thick here. Got it sorted now though without need of the increment. May as well just set the EntryValue as SortOrderID!

(SELECT TOP 100 percent tblEntryValue.NameID, EntryValue.EntryValue AS SortOrderID FROM tblEntryValue
WHERE tblEntryValue.HeaderID='" & SortHeader & "'
GROUP BY NameID, EntryValue
ORDER BY tblEntryValue.EntryValue DESC) a