SQL Datagrid Query - Two Similar Tables

[tqeonline]

Hello All,

I am semi-new to SQL query programming.

I have two tables that have fields as follows:

tblManEntry:
-Stock_Num
-Description
-QTY
-Stock_PN

tblInStock
-Stock_Num
-Description
-QTY
-PN1
-PN2
-PN3

What I have so far is I have 2 datagrids that I can search both tables separately and it return results. That works great.

But I would like it to display on one datagrid as follows.

I would like to make the query merge the data where it displays all the Stock_Nums in one column, Description, QTY, etc.

Then all the Partnumbers I just want them in one column as well. i have this working on the second datagrid by doing something like

PN1 + ' ' + PN2 + ' ' + PN3 AS PN

I have tried Cross Join, Full Outer Join, etc and that all seem to just add the info from the tables to each other. Like if a PN in table 1 was 1234 and a PN in table 2 was 5678 then it'd merge them and it'd be 12345678 instead of two separate records.

This is what I currently have on two test tables

SELECT     Test1.Stock_Num + '' + Test2.Stock_Num AS Stock_Num, Test1.Description + '' + Test2.Description AS Description, Test1.QTY + '' + Test2.QTY AS QTY,
                       Test1.Stock_PN + '' + Test2.Stock_PN AS Stock_PN
FROM         Test2 CROSS JOIN
                      Test1

That just shows everything from each database all merged together.

Any ideas?

 

[tqeonline]

The reason it is two tables is because one is automatically updated and one is manually updated. The automatic one has multiple PNs where as the Manual will just have one.

I can't just make it one table because the automatic one is linked with another program, and the manual one is just overstock inventory that isn't kept in the active database.

 

[tqeonline]

SELECT     Stock_Num, Description, QTY, Stock_PN
FROM         Test1
UNION
SELECT     Stock_Num, Description, QTY, Stock_PN
FROM         Test2

I'm thinking this is the road I will have to take.

However How would I get PN1, PN2, PN3 to show up just under the PN column?

 

[tqeonline]

SELECT     Stock_Num, Description, QTY, Stock_PN
FROM         Test1
UNION
SELECT     Stock_Num, Description, QTY, Stock_PN1 + ' ' + Stock_PN2 + ' ' + Stock_PN3 AS Stock_PN
FROM         Test2

This works. Now I will try it on the actual Live Database. (Well a backup copy)

 

[gmmastros]

You'll almost certainly want to change that to UNION ALL. A UNION query will return distinct results, and UNION ALL will allow duplicates. If your data is such that there will not be any duplicates anyway, then you should use UNION ALL because it ill perform better.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

gmmastros

How would I go about finding the count of all the returned records?

I know about the rows.count and stuff but I have paging turned on because sometimes its like 250 results.

my gridview is gridview1 and my sqlsource is sqldatasource1.

Any help would be appreciated.