SQL Select/Join Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Select/Join Help

Author

Topic

dslynch
Starting Member

8 Posts

Posted - 2008-06-22 : 20:20:06

Hi,

My SQL is a little rusty, and I need a little help for a client that I'm helping.

I'm simply trying to do create the following output:

HEADINGS: Sale Order | Customer Number | Customer Name | # of Pallets | (etc)
DATA: 456188 | 12355890 | Acme Customer | 4 | other stuff I have figured out that is irrelevant

This data comes from 3 tables:
1) SOE_HEADER
SALE_ORDER_NO | CUSTOMER_NO | (etc)
456188 | 12355890

2) CUST_NAME
CUSTOMER_NO | CUSTOMER_NAME | (etc)
456188 | Acme Customer

3) PALLETS_SALES_ORD
SALE_ORDER_NO | PALLET_ID | (etc)
456188 | 12345
456188 | 67890
456188 | 13579

I have 2 queries that independently pull the right data. 1 query joins the customer info to pull in the customer name, and the 2nd query calculates the # of pallets per sales order.

BUT I CAN'T GET THEM TO WORK WHEN I DO A JOIN!!! Can someone please help me? Here are the queries as I currently have them. This returns zero rows, but when I run my queries independently, the both return multiple rows with the correct data. PLEASE HELP ME!

SELECT
SOE.INSIDE_ROUTE,
SOE.SALESMAN_NO,
SOE.SALE_ORDER_NO,
CUST.CUST_NAME,
SOE.CUSTOMER_NO,
(SOE.SALES_AMT/100) AS SALES,
(SOE.COST_GOODS_SOLD/100) AS COGS,
(SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN,
COUNT(PALLET.SALE_ORDER_NO) AS NumOccurrences
FROM
SOE_HEADER SOE,
CUST_NAME CUST,
PALLET_SALES_ORD PALLET
WHERE
SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO
AND SOE.CUSTOMER_NO = CUST.CUSTOMER_NO
GROUP BY
PALLET_SALES_ORD.SALE_ORDER_NO
HAVING (COUNT(PALLET_SALES_ORD.SALE_ORDER_NO) > 1 )

THANKS. This will be a big help for the work I need to get done tomorrow, and I can't take it anymore. I've tweaked this all weekend, to no avail.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-22 : 21:31:03

try this

SELECT	SOE.INSIDE_ROUTE,
		SOE.SALESMAN_NO,
		SOE.SALE_ORDER_NO,
		CUST.CUST_NAME,
		SOE.CUSTOMER_NO,
		(SOE.SALES_AMT/100) 		AS SALES,
		(SOE.COST_GOODS_SOLD/100) 	AS COGS,
		(SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN,
		PALLET.NumOccurrences
FROM	
		SOE_HEADER SOE
INNER JOIN 	CUST_NAME CUST		ON	SOE.CUSTOMER_NO = CUST.CUSTOMER_NO
INNER JOIN 
		(
			SELECT	SALE_ORDER_NO, COUNT(SALE_ORDER_NO) AS NumOccurrences
			FROM	PALLET_SALES_ORD 
			GROUP BY SALE_ORDER_NO
			HAVING 	COUNT(SALE_ORDER_NO) > 1
		) PALLET		ON	SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO

EDIT : removed extra comma after "FROM SOE_HEADER SOE"

KH
[spoiler]Time is always against us[/spoiler]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-22 : 21:41:25

"try this

SELECT SOE.INSIDE_ROUTE,
SOE.SALESMAN_NO,
SOE.SALE_ORDER_NO,
CUST.CUST_NAME,
SOE.CUSTOMER_NO,
(SOE.SALES_AMT/100) AS SALES,
(SOE.COST_GOODS_SOLD/100) AS COGS,
(SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN,
PALLET.NumOccurrences
FROM
SOE_HEADER SOE ,
INNER JOIN CUST_NAME CUST ON SOE.CUSTOMER_NO = CUST.CUSTOMER_NO
INNER JOIN
(
SELECT SALE_ORDER_NO, COUNT(SALE_ORDER_NO) AS NumOccurrences
FROM PALLET_SALES_ORD
GROUP BY SALE_ORDER_NO
HAVING COUNT(SALE_ORDER_NO) > 1
) PALLET ON SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO"

--------------------------------------------------------------------------------
KH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-22 : 22:11:15

sodeep,

did i make any mistake there ? Can you highlight what have you changed ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-22 : 23:58:54

quote:
Originally posted by khtan

sodeep,

did i make any mistake there ? Can you highlight what have you changed ?

KH
[spoiler]Time is always against us[/spoiler]

Small typo, a , is remaining near FROM SOE_HEADER SOE,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 00:11:48

quote:
Originally posted by visakh16

quote:
Originally posted by khtan

sodeep,

did i make any mistake there ? Can you highlight what have you changed ?

KH
[spoiler]Time is always against us[/spoiler]

Small typo, a , is remaining near FROM SOE_HEADER SOE,

Thanks. Guess i need a bigger screen or bigger specs

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 00:39:37

quote:
Originally posted by khtan

quote:
Originally posted by visakh16

quote:
Originally posted by khtan

sodeep,

did i make any mistake there ? Can you highlight what have you changed ?

KH
[spoiler]Time is always against us[/spoiler]

Small typo, a , is remaining near FROM SOE_HEADER SOE,

Thanks. Guess i need a bigger screen or bigger specs

KH
[spoiler]Time is always against us[/spoiler]

yeah...its rather difficult to spot it out

dslynch
Starting Member

8 Posts

Posted - 2008-06-23 : 12:26:16

Thanks guys. Hopefully this works once I get on-site, and I will let you know.

Much, much appreciated.

dslynch
Starting Member

8 Posts

Posted - 2008-06-23 : 15:43:54

GRRRR. I've made progress, but it's still not working.

Here is my SQL. It's saying that I have a syntax error in the table list. In case it makes any difference, I'm using Microsoft Query via an ODBC connection to access HP3000 data. What the H am I doing wrong??? Thanks so much. I'm not trying to cop out here, but I'm just at my wits end.

SELECT
SOE.INSIDE_ROUTE,
SOE.SALESMAN_NO,
SOE.SALE_ORDER_NO,
CUST.CUST_NAME,
SOE.CUSTOMER_NO,
(SOE.SALES_AMT/100) AS SALES,
(SOE.COST_GOODS_SOLD/100) AS COGS,
(SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN,
PALLET.NumOccurrences
FROM
SOE_HEADER SOE
INNER JOIN
CUST_NAME CUST ON SOE.CUSTOMER_NO = CUST.CUSTOMER_NO
INNER JOIN
(SELECT
PALLET_SALES_ORD.SALE_ORDER_NO,
COUNT(PALLET_SALES_ORD.SALE_ORDER_NO) AS NumOccurrences
FROM
PALLET_SALES_ORD PALLET
GROUP BY PALLET_SALES_ORD.SALE_ORDER_NO)
PALLET_SALES_ORD ON SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 15:48:35

You need to alias the derived table. Alias it as PALLET_SALES_ORD PALLET to make it easier.

Example:
inner join (derived table) alias

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dslynch
Starting Member

8 Posts

Posted - 2008-06-23 : 15:58:56

Aren't I already doing that on the 3rd line from the bottom though?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 16:02:17

Nope. All derived tables are required to be aliased. You don't have an alias after the ending parenthesis of the derived table, hence the error.

Here's a more complete example of proper use of a derived table:


SELECT Column1
FROM
(
	SELECT Column1, Column2
	FROM SomeTable
) SomeAlias

You could choose to name your alias the same as the table being used inside the derived table. Imagine if you have a very complex query as your derived table though, one with unions and joins so it's hitting other tables and not just one.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dslynch
Starting Member

8 Posts

Posted - 2008-06-23 : 16:10:05

Ok, I get it. Thanks. Let me try to get that to work.

dslynch
Starting Member

8 Posts

Posted - 2008-06-23 : 16:15:15

Dang it. I'm still getting an error. Error 3kodbc, syntax error in table list (select).}

I've proofed this below to make sure I'm not doing something stupid (typo, etc.). Well, I'm probably doing something stupid, but I can't figure it out.

SELECT
SOE.INSIDE_ROUTE,
SOE.SALESMAN_NO,
SOE.SALE_ORDER_NO,
CUST.CUST_NAME,
SOE.CUSTOMER_NO,
(SOE.SALES_AMT/100) AS SALES,
(SOE.COST_GOODS_SOLD/100) AS COGS,
(SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN,
PALLET.NumOccurrences
FROM
SOE_HEADER SOE
INNER JOIN
CUST_NAME CUST ON SOE.CUSTOMER_NO = CUST.CUSTOMER_NO
INNER JOIN
(SELECT
PALLET_SALES_ORD.SALE_ORDER_NO,
COUNT(PALLET_SALES_ORD.SALE_ORDER_NO) AS NumOccurrences
FROM
PALLET_SALES_ORD
GROUP BY PALLET_SALES_ORD.SALE_ORDER_NO) PALLET
ON SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 20:00:06

quote:
Originally posted by dslynch

I'm using Microsoft Query via an ODBC connection to access HP3000 data.

What is the Database Server in HP3000 ? I don't think HP 3000 runs on MS Windows right and Microsoft SQL Sever only runs on MS Windows. So which means the Database Server in HP3000 is not MS SQL Server correct ?

In case you didn't notice yet, this is a Microsoft SQL Server forum.

KH
[spoiler]Time is always against us[/spoiler]

dslynch
Starting Member

8 Posts

Posted - 2008-06-23 : 21:50:25

It's an Image db. And yes, I did notice that this is a SQL server forum. I was just trying to find some place for some help. I thought that the SQL itself wouldn't differ.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 21:55:32

The basic SQL syntax might be the same but MS SQL code will not run on Oracle and vice versa.

Sorry, I can't help you much on ImageDB. This is the first time i know of the existence of such DB. You have to check the documentation of the ImageDB see if the syntax used in the above query is supported.

KH
[spoiler]Time is always against us[/spoiler]

dslynch
Starting Member

8 Posts

Posted - 2008-06-23 : 23:20:45

That's OK, I understand. As I'm sure you've experienced, the client has no documentation and the system is terribly out of date...I've kind of jimmy-rigged the query, in the sense that I'm pulling the data into 2 queries in Excel, then doing a vlookup.

Anyway, thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources