Query Problem

Question

Query Problem

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
December 12, 2012 at 9:18 am

#271830

Anyone see a problem with this query?
SELECT tblTransactions.PO_Number, tblTransactions.PO_Date, tblTransactions.Quantity,
tblTransactions.Software_Description, tblTransactions.Unit_Price,
tblTransactions.SoftwareShortName, tblTransactions.Transaction_Number,
tblTransactions.Transaction_Type, tblBulkPurchases.Quantity_Remaining, tblBulkPurchases.PO_Number
FROM tblTransactions, tblBulkPurchases
WHERE FROM tblTransactions.PO_Number = tblBulkPurchases.PO_Number
ORDER BY tblTransactions.PO_Number DESC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

briancampbellmcad (12/12/2012)
Anyone see a problem with this query?
SELECT tblTransactions.PO_Number, tblTransactions.PO_Date, tblTransactions.Quantity,
tblTransactions.Software_Description, tblTransactions.Unit_Price,
tblTransactions.SoftwareShortName, tblTransactions.Transaction_Number,
tblTransactions.Transaction_Type, tblBulkPurchases.Quantity_Remaining, tblBulkPurchases.PO_Number
FROM tblTransactions, tblBulkPurchases
WHERE FROM tblTransactions.PO_Number = tblBulkPurchases.PO_Number
ORDER BY tblTransactions.PO_Number DESC

Lots, but syntactically the only thing wrong is the extra "FROM" that you've written after "WHERE".

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

You have an extra from after the key word where. If you'll delete it, then there is a good chance that it will work. I would also specify how to join the tables in the from clause and not in the where clause.

SELECT tblTransactions.PO_Number, tblTransactions.PO_Date, tblTransactions.Quantity,

tblTransactions.Software_Description, tblTransactions.Unit_Price,

tblTransactions.SoftwareShortName, tblTransactions.Transaction_Number,

tblTransactions.Transaction_Type, tblBulkPurchases.Quantity_Remaining, tblBulkPurchases.PO_Number

FROM tblTransactions inner join tblBulkPurchases on tblTransactions.PO_Number = tblBulkPurchases.PO_Number

ORDER BY tblTransactions.PO_Number DESC

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 3

as suggested: changinbg old style join to new style ansi JOIN..ON:

SELECT

tblTransactions.PO_Number,

tblTransactions.PO_Date,

tblTransactions.Quantity,

tblTransactions.Software_Description,

tblTransactions.Unit_Price,

tblTransactions.SoftwareShortName,

tblTransactions.Transaction_Number,

tblTransactions.Transaction_Type,

tblBulkPurchases.Quantity_Remaining,

tblBulkPurchases.PO_Number

FROM tblTransactions

INNER JOIN tblBulkPurchases

ON tblTransactions.PO_Number = tblBulkPurchases.PO_Number

ORDER BY

tblTransactions.PO_Number DESC

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

CELKO (12/12/2012)
It is not a query; it is a cursor because it has an ORDER BY.

Odd. I always thought an ORDER BY was a sort, rather than a cursor by any other name.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

CELKO (12/12/2012)
Those silly 1960s BASIC “tbl-” prefixes violate ISO-11179 and basic data modeling.

That's a silly comment because 1960s basic wouldn't allow a "-" (minus) in a name anyway!

a-b was perfectly valid in basic meaning "a minus b", not a value/field/column named 'a-b'.

But I still strongly object to the "tbl-" prefix. Not because of ISO. But because (1) it puts extra meaning in the name (which is bad, mmmk), and (2) it's not possible to keep accurate.

Say a table changes to a view. Are you really going to rename it everywhere? Of course NOT. So don't name it that way to begin with! You don't want your object name "lying" to people down the road.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".