469,071 Members | 1,932 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,071 developers. It's quick & easy.

Same Column, Same Table - Query

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.
Oct 7 '14 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.    t1.field1,
  3.    t2.field1,
  4.    t1.field2,
  5.    t2.field2
  6.  
  7. FROM
  8.    table1 AS t1
  9.  
  10.    INNER JOIN table1 AS t2
  11.    ON t1.field2 = t2.field2
  12.  
  13. WHERE
  14.    t1.field1 = valueA AND
  15.    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.

15 1722
Frinavale
9,735 Expert Mod 8TB
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?
Oct 7 '14 #2
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
Oct 7 '14 #3
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.
Oct 8 '14 #4
ck9663
2,878 Expert 2GB
Yes it's possible. Just JOIN the two tables by COL2

Happy Coding!!!


~~ CK
Oct 8 '14 #5
ck9663,

thanks for the reply.

its only 1 table.
Oct 8 '14 #6
Rabbit
12,516 Expert Mod 8TB
You can join a table to itself
Oct 8 '14 #7
here is my query code
Expand|Select|Wrap|Line Numbers
  1. USE [SSI-HQ]
  2.  
  3. SELECT
  4.     [SSI$Sales Price].[Sales Code]
  5.     ,[SSI$Sales Price].[Item No_] AS [Item No]
  6.     ,[SSI$Sales Price].[Variant Code]
  7.     ,[SSI$Sales Price].[Unit of Measure Code] AS [Unit of Measure]
  8.     ,[SSI$Sales Price].[Unit Price] AS SALES1
  9.     ,[SSI$Sales Price].[Unit Price] AS SALES2
  10.     ,SSI$Item.[Unit Cost]
  11.     ,[SSI$Sales Price].[Starting Date]
  12.     ,[SSI$Sales Price].[Ending Date]
  13.  
  14.  
  15. FROM [SSI$Sales Price]
  16.  
  17. LEFT JOIN SSI$Item
  18.     ON [SSI$Sales Price].[Item No_] = SSI$Item.No_
  19.  
  20. JOIN [SSI$Sales Price]
  21.     ON [SSI$Sales Price].[Item No_] = [SSI$Sales Price].[Item No_] AND [SSI$Sales Price].[Variant Code] = [SSI$Sales Price].[Variant Code] 
  22.  
  23. WHERE
  24.     [SSI$Sales Price].[Sales Code] = 'C00020' AND
  25.     [SSI$Sales Price].[Sales Code] = 'SRP' AND
  26.     [SSI$Sales Price].[Sales Type] = '0' 
  27.  
  28.  
Oct 8 '14 #8
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.
Oct 8 '14 #9
here is revised query:

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     [SSI$Sales Price].[Sales Code] AS CUSTOMER
  3.     ,[SSI$Sales Price].[Sales Code] AS SRP
  4.     ,[SSI$Sales Price].[Item No_] AS [Item No]
  5.     ,[SSI$Sales Price].[Variant Code]
  6.     ,[SSI$Sales Price].[Unit of Measure Code] AS [Unit of Measure]
  7.     ,[SSI$Sales Price].[Unit Price] AS SALES1
  8.     ,[SSI$Sales Price].[Unit Price] AS SALES2
  9.     ,SSI$Item.[Unit Cost]
  10.     ,[SSI$Sales Price].[Starting Date]
  11.     ,[SSI$Sales Price].[Ending Date]
  12.  
  13.  
  14. FROM [SSI$Sales Price]
  15.  
  16. LEFT JOIN SSI$Item
  17.     ON [SSI$Sales Price].[Item No_] = SSI$Item.No_
  18.  
  19. JOIN [SSI$Sales Price] AS CUSTOMER
  20.     ON [SSI$Sales Price].[Item No_] = [SSI$Sales Price].[Item No_] AND [SSI$Sales Price].[Variant Code] = [SSI$Sales Price].[Variant Code] 
  21.  
  22. JOIN [SSI$Sales Price] AS SRP
  23.     ON [SSI$Sales Price].[Item No_] = [SSI$Sales Price].[Item No_] AND [SSI$Sales Price].[Variant Code] = [SSI$Sales Price].[Variant Code] 
  24.  
  25. WHERE
  26.     [SSI$Sales Price].[Sales Code] = 'C00020' AND
  27.     [SSI$Sales Price].[Sales Code] = 'SRP' AND
  28.     [SSI$Sales Price].[Sales Type] = '0'
  29.  
after i run the query nothing happen, no error message
Oct 8 '14 #10
Rabbit
12,516 Expert Mod 8TB
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.
Oct 8 '14 #11
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.
Oct 9 '14 #12
Rabbit
12,516 Expert Mod 8TB
I know, that's why you have to use the alias you gave to the second table.
Oct 9 '14 #13
here is my revised code, but im unable to separate column of sales code and unit price

Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     [SSI$Sales Price].[Sales Code]
  3.     ,[SSI$Sales Price].[Item No_]
  4.     ,[SSI$Sales Price].[Variant Code]
  5.     ,[SSI$Sales Price].[Unit of Measure Code]
  6.     ,[SSI$Sales Price].[Unit Price]
  7.     ,SSI$Item.[Unit Cost]
  8.     ,[SSI$Sales Price].[Starting Date]
  9.     ,[SSI$Sales Price].[Ending Date]
  10.  
  11.  
  12. FROM [SSI$Sales Price]
  13.  
  14. LEFT JOIN SSI$Item
  15.     ON [SSI$Sales Price].[Item No_] = SSI$Item.No_
  16.  
  17. WHERE
  18.     [SSI$Sales Price].[Sales Code] IN ('C00020' , 'SRP') AND
  19.     [SSI$Sales Price].[Sales Type] IN ('0', '1')
  20.  
  21.  
Oct 16 '14 #14
Rabbit
12,516 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.    t1.field1,
  3.    t2.field1,
  4.    t1.field2,
  5.    t2.field2
  6.  
  7. FROM
  8.    table1 AS t1
  9.  
  10.    INNER JOIN table1 AS t2
  11.    ON t1.field2 = t2.field2
  12.  
  13. WHERE
  14.    t1.field1 = valueA AND
  15.    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.
Oct 16 '14 #15
thanks a lot Rabbit.
Oct 20 '14 #16

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Heist | last post: by
2 posts views Thread by Volition | last post: by
4 posts views Thread by Captain Jack Sparrow | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.