Need Help With SQL Query

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.



Attachment 90021


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

Sounds like you need an Inner Join statement

Code:

---

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

---

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)

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.

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.

Quote:

---

Originally Posted by 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.

---

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?

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;

Off the top of my head id try this.

Code:

---

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

---

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

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

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.

Attachment 90067





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

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.

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.