Same Column, Same Table - Query

[glennyboy]

good day.


sample database:

table1

col1 col2 col3
A item1 100
A item2 200
A item3 300
B item1 400
B item2 500
B item3 600


#################################

query result:

col1 col2 col3 col4 col5
item1 A 100 B 400
item2 A 200 B 500
item3 A 300 B 600


is this possible to do a query with this result.

thank you.

[Frinavale]

Glennyboy,

Is this a SQL Server question or an HTML question?
What technology are you using to implement your solution?

What are you trying to do?

[Bala Kumaran]

Yes It is possible getting from Sql. But, you need have use one of the script below to get result from sql.

PHP, ASP.NET, JSP

[glennyboy]

Frinavale,

thank you for the reply.

- This is for MS SQL Server.
- Im using SQL Server Management Studio Express to test the sql query, then i will use it in MS Excel to export data from sql server to MS Excel.

[ck9663]

Yes it's possible. Just JOIN the two tables by COL2

Happy Coding!!!


~~ CK

[glennyboy]

ck9663,

thanks for the reply.

its only 1 table.

[Rabbit]

You can join a table to itself

[glennyboy]

here is my query code

USE [SSI-HQ]
 
SELECT
    [SSI$Sales Price].[Sales Code]
    ,[SSI$Sales Price].[Item No_] AS [Item No]
    ,[SSI$Sales Price].[Variant Code]
    ,[SSI$Sales Price].[Unit of Measure Code] AS [Unit of Measure]
    ,[SSI$Sales Price].[Unit Price] AS SALES1
    ,[SSI$Sales Price].[Unit Price] AS SALES2
    ,SSI$Item.[Unit Cost]
    ,[SSI$Sales Price].[Starting Date]
    ,[SSI$Sales Price].[Ending Date]
 
 
FROM [SSI$Sales Price]
 
LEFT JOIN SSI$Item
    ON [SSI$Sales Price].[Item No_] = SSI$Item.No_
 
JOIN [SSI$Sales Price]
    ON [SSI$Sales Price].[Item No_] = [SSI$Sales Price].[Item No_] AND [SSI$Sales Price].[Variant Code] = [SSI$Sales Price].[Variant Code] 
 
WHERE
    [SSI$Sales Price].[Sales Code] = 'C00020' AND
    [SSI$Sales Price].[Sales Code] = 'SRP' AND
    [SSI$Sales Price].[Sales Type] = '0' 
 
 

[glennyboy]

error message

Msg 1013, Level 16, State 1, Line 3
The objects "SSI$Sales Price" and "SSI$Sales Price" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

[glennyboy]

here is revised query:

SELECT
    [SSI$Sales Price].[Sales Code] AS CUSTOMER
    ,[SSI$Sales Price].[Sales Code] AS SRP
    ,[SSI$Sales Price].[Item No_] AS [Item No]
    ,[SSI$Sales Price].[Variant Code]
    ,[SSI$Sales Price].[Unit of Measure Code] AS [Unit of Measure]
    ,[SSI$Sales Price].[Unit Price] AS SALES1
    ,[SSI$Sales Price].[Unit Price] AS SALES2
    ,SSI$Item.[Unit Cost]
    ,[SSI$Sales Price].[Starting Date]
    ,[SSI$Sales Price].[Ending Date]
 
 
FROM [SSI$Sales Price]
 
LEFT JOIN SSI$Item
    ON [SSI$Sales Price].[Item No_] = SSI$Item.No_
 
JOIN [SSI$Sales Price] AS CUSTOMER
    ON [SSI$Sales Price].[Item No_] = [SSI$Sales Price].[Item No_] AND [SSI$Sales Price].[Variant Code] = [SSI$Sales Price].[Variant Code] 
 
JOIN [SSI$Sales Price] AS SRP
    ON [SSI$Sales Price].[Item No_] = [SSI$Sales Price].[Item No_] AND [SSI$Sales Price].[Variant Code] = [SSI$Sales Price].[Variant Code] 
 
WHERE
    [SSI$Sales Price].[Sales Code] = 'C00020' AND
    [SSI$Sales Price].[Sales Code] = 'SRP' AND
    [SSI$Sales Price].[Sales Type] = '0'
 

after i run the query nothing happen, no error message

[Rabbit]

I'm not sure why you join to the same table 3 times. Your example only shows that you need to join it twice.

Also, you incorrectly reference the second instance of the table, you need to use the alias. When you use [SSI$Sales Price], that is referring to the first instance of the table. Therefore, your WHERE clause is impossible because Sales Code from the first table can never be equal to 2 different values at the same time.

[glennyboy]

Rabbit,

thanks for the reply.

i want to separate the column of [SSI$Sales Price].[Sales Code] into C00020 and SRP in the same table.

thanks.

[Rabbit]

I know, that's why you have to use the alias you gave to the second table.

[glennyboy]

here is my revised code, but im unable to separate column of sales code and unit price

SELECT
    [SSI$Sales Price].[Sales Code]
    ,[SSI$Sales Price].[Item No_]
    ,[SSI$Sales Price].[Variant Code]
    ,[SSI$Sales Price].[Unit of Measure Code]
    ,[SSI$Sales Price].[Unit Price]
    ,SSI$Item.[Unit Cost]
    ,[SSI$Sales Price].[Starting Date]
    ,[SSI$Sales Price].[Ending Date]
 
 
FROM [SSI$Sales Price]
 
LEFT JOIN SSI$Item
    ON [SSI$Sales Price].[Item No_] = SSI$Item.No_
 
WHERE
    [SSI$Sales Price].[Sales Code] IN ('C00020' , 'SRP') AND
    [SSI$Sales Price].[Sales Type] IN ('0', '1')
 
 

[Rabbit]

Your first attempt was closer. Now you only reference the table once. The first time you referenced the table 3 times. I said you needed to reference the table 2 times. Is there a reason you decided to reference it only once? You need to do something like this:

SELECT
   t1.field1,
   t2.field1,
   t1.field2,
   t2.field2
 
FROM
   table1 AS t1
 
   INNER JOIN table1 AS t2
   ON t1.field2 = t2.field2
 
WHERE
   t1.field1 = valueA AND
   t2.field2 = valueB

You need to reference the table twice, not once.

You need to join on the fields in the two references by their alias so it knows which reference you're referring to. Otherwise it has no idea which one you want to use.

And you need to reference the values you're looking for using the alias, you can't use the same reference for 2 different values because that's impossible.

[glennyboy]

thanks a lot Rabbit.