Select just 1 row on primary table and multiple ro

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select just 1 row on primary table and multiple ro

Author

Topic

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-05 : 17:17:49

I am trying to select the just 1 row on the primary key table and multiple row on the joined tables which has the primary keys that has become the foriegn keys. Please help. Thanks. Below is my example

A table
-----------
pokey podate trantype Amt
123 1/01/04 101 10000.00

B table
----------
polinekey itemkey Amt pokey
001 ABC123 2500 123
002 DEF456 2500 123
003 GHI789 5000 123

so A table is joined with B table by the pokey so if you do equal it will give with 3 rows of pokeys,podate,trantype, and Amt from A table which I am trying to remove the duplicate from A. I need to do a select statement with a result format look like below because this is eventually going to be a text file: (column headings can be disregarded).Thanks

123 1/01/04 101 1000.00
001 ABC123 2500 123
002 DEF456 2500 123
003 GHI789 5000 123

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-05 : 17:26:11

You are trying to put two result sets together that don't match up with data types. You can't do that without converting them to VARCHAR or something like that. You can use a UNION if the data types are the same for the columns in each table.

SELECT a.pokey, podate, trantype, polinekey, itemkey, b.amt
FROM TableA a
INNER JOIN TableB b ON a.pokey = b.pokey
WHERE a.pokey = 123

You might consider Reporting Services for SQL Server 2000 to do this for you since it probably should be done on the presentation side. Or some other reporting tool like Crystal Reports.

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-05 : 17:34:49

well they are not the same columns in both tables because the first table is the primary tables and the second table is a secondary table (normalized table). Is it possible to insert into a temp table and get the result format I wanted? Do you have any ideas?Thanks.

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-05 : 17:40:49

I realize why they don't have the same column names. I was saying they don't have the same data types. So you have to explicitly CONVERT them in order to combine them into one result set.

You can combine them with a UNION as long as you CONVERT the columns to the same data type.

SELECT TOP 1 CONVERT(VARCHAR(50), pokey), CONVERT(VARCHAR(50), podate), CONVERT(VARCHAR(50), trantype), CONVERT(VARCHAR(50), amt)
FROM TableA
WHERE pokey = 123
UNION
SELECT CONVERT(VARCHAR(50), polinekey), CONVERT(VARCHAR(50), itemkey), CONVERT(VARCHAR(50), amt), CONVERT(VARCHAR(50), pokey)
FROM TableB
WHERE pokey = 123

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-05 : 18:19:31

this is the result that I got which is good but I need the last row(from the primary table) to become the first row. any ideas.Thanks.

001 4557 2500.00 123
002 5083 2500.00 123
003 4738 5000.00 123
123 Nov 19 2002 3:32PM 1101 10000.000

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-05 : 18:56:03

To order result sets, use ORDER BY.

Tara

fredong
Yak Posting Veteran

80 Posts

Posted - 2004-01-06 : 11:25:42

I meant how I can get the resutlt to look like this

123 Nov 19 2002 3:32PM 1101 10000.000
001 4557 2500.00 123
002 5083 2500.00 123
003 4738 5000.00 123

instead of this

001 4557 2500.00 123
002 5083 2500.00 123
003 4738 5000.00 123
123 Nov 19 2002 3:32PM 1101 10000.000

I don't understand how would you use order by in the Union operator. Please advise.Thanks.

k

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-06 : 12:06:21

When you have a UNION, you might need to use the ordinal positions of the columns since the column names are different.

For example,

ORDER BY 1

That says order by the first column of the SELECT statement, and it will do it for the entire result set and not just the first or second part. Your values could get messed up though since 123 might be smaller than your polinekey. If that is the case, then put the results of the individual selects in the temp table. Obviously do the primary table first. The temp table would have generic data types such as VARCHAR.

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-01-06 : 14:46:31

If you always want the results from one table to appear before the other then you can put a constant in both parts of the UNION to identify which table it came from and then order by that. So it would look something like

SELECT CONVERT......., 1 as SortOrder
...
UNION
...
SELECT CONVERT........, 2 as SortOrder
ORDER BY SortOrder

or if there are multiple "sets" then maybe
ORDER BY 4, 5, 1
to sort by pokey, table, keyfield

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources