Need Help With SQL Query

[Focus12]

I really need some help. I have two different tables, but I wont to join the two tables together all in one select statement. I have provided in attachment, on how the table look now, in how I'm trying to get the tables.



SQL Table Example.txt


Table 1:
SELECT [SerialNumber]
,[In Stock]
,SerialNumber - [In Stock] as [Short Of]
FROM [phabsproduction].[dbo].[Stock_Inventory]
where
(SerialNumber - [In Stock])<0
order by
[Short Of] desc



Table 2:
SELECT PartNumber,Count(PartNumber) AS Total FROM Stock_Inventory WHERE [PartNumber] like '82480-%' or [PartNumber] like '83480-%' GROUP BY PartNumber


WHAT I WILL NEED FROM THE TABLE 1 ARE THE FOLLOWING; I WILL NEED THIS PART ADDED IN THE TABLE 2 QUERY;

,SerialNumber - [In Stock] as [Short Of]
FROM [phabsproduction].[dbo].[Stock_Inventory]
where
(SerialNumber - [In Stock])<0
order by
[Short Of] desc

[DataMiser]

Sounds like you need an Inner Join statement

Select table1.Field1, Table2.Field1 From Table1 Inner Join Table2 on Table2.KeyField=Table1.KeyField Where ......

[si_the_geek]

Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB, it is certainly not specific to VB)

[Focus12]

Is there a way, I dont have to have two tables. Becasue what im trying to prevent is making two tables. But have all this in a select statement. To prevent confusing, all is on one table so I just need one select query that can do all what i provided above.

[Focus12]

I have those two query for example what I need in one query, basically there's not 2 tables. Just one table, which i have a select statement to get things I need. but I nned both select statement in one select statement to get me the same results two select query would give me. If that make sense.

[smendoza]

Is there a way, I dont have to have two tables. Becasue what im trying to prevent is making two tables. But have all this in a select statement. To prevent confusing, all is on one table so I just need one select query that can do all what i provided above.

After reading your question again I think you're only query data from ONE data but you have two queries that you would like to rewrite as one? Is this correct?

[Focus12]

Both of theses query that are on the same table, but im trying to combine both query together. In get the same resulta on one query, as I was running in two query.

Query 1:
SELECT [SerialNumber]
,[In Stock]
,SerialNumber - [In Stock] as [Short Of]
FROM [production].[dbo].[Stock_Inventory]
where
(SerialNumber - [In Stock])<0
order by
[Short Of] desc



Query2:
SELECT PartNumber,Count(PartNumber) AS Total FROM Stock_Inventory WHERE [PartNumber] like '81-%' or [PartNumber] like '82-%' GROUP BY PartNumber;

[smendoza]

Off the top of my head id try this.

SELECT PartNumber, Count(PartNumber) AS Total
       , SerialNumber - [In Stock] as [Short Of]
 WHERE [PartNumber] like '82480-%' or [PartNumber] like '83480-%'
     AND (SerialNumber - [In Stock]) < 0
GROUP BY PartNumber

[smendoza]

if that doesn't work show us the schema of the table with two records or so.

[Focus12]

That works but it doest count the part number like it was; SELECT PartNumber, Count(PartNumber) AS Total
On that part I will need to collect all data that are the same on the table, it give the total. Just like I have below;

PartNumber Total
82480-EM 2
83480-EM 2

[smendoza]

Can you show us the table structure. It's hard to help (well for me) when you don't know what the table structure looks like. I think you will need to use both queries and a inner join.

[Focus12]

QUERY NEED HELP.doc





I need help, but I provided a attachment, that explain what i need help in.

EXAMPLE: PartNumber Total Limit Shorten
83480-EM 2 213 211
82470-EM 2 192 190

[smendoza]

Well in your doc you show you're query data from TWO different tables. So yes, like you said you need to use a JOIN, a INNER JOIN is what you want to use. I say give it a try it sounds like you know what you need to do.

[pallab.santra1987]

For SQL Server 2008:

What is the procedure to delete some data from Lock Table?
We can't use NOLOCK for Update, delete & Insert for that.

A table Name like Business_History which is in Transaction Lock.
Want to delete data where Proposal_Form_No='ABC'. If I Execute Select Operation With NOLOCK it shows 5 rows of data. But Can't delete them.

Please Help.