?
Solved

Need help with a query I have it 95% where I need it.

Posted on 2005-04-18
2
Medium Priority
?
275 Views
Last Modified: 2010-03-19
Hello all.  I am 95% where I need to be with this query from help last night from another EE member.   I had 2 queries where we got it down to 1 combined the 2.  The problem is that the CONTACT table in the 1 query I need to pull from both table that you will see from the 2 queries I originally had.  Take a look at the Contact joins and this is where I am having trouble pulling less records now and I need both the AVAILABLES Contact fields and the Reqs Contact fields.  One Available record will match to multiple Reqs records in which the COID from each table will link to a corresponding Contact Information.  Here is the 1 query and the 2 original.  Thanks all.


SELECT DISTINCT vwAllAvailables.PART_NO AS APART_NO
, vwAllAvailables.QTY AS AQTY
, vwAllAvailables.PRICE AS APRICE
, vwAllAvailables.InternalPartNo AS AInternalPartNo
, vwAllAvailables.DateOfferred AS ADateOfferred
, CONTACT.COMPANY AS COMPANY
, CONTACT.CONTACT AS CONTACT
, CONTACT.PHONE1 AS PHONE1
, contact.co_id  AS CO_ID
, REQS.PART_NO AS RPART_NO
, REQS.QTY AS RQTY
, REQS.PRICE AS RPRICE
 ,REQS.INTERNAL AS RINTERNAL
, REQS.DATEREQUESTED AS RDATEREQUESTED
, reqs.co_id AS RCO_ID
FROM vwAllAvailables
INNER JOIN REQS ON vwAllAvailables.part_no = REQS.Part_no
LEFT JOIN CONTACT ON vwAllAvailables.CO_ID = CONTACT.CO_ID
      AND REQS.CO_ID = CONTACT.CO_ID
WHERE
vwAllAvailables.CO_ID Like @COID AND
((@ADateRange = 'Yes' and vwAllAvailables.DateOfferred > convert(varchar(10),getdate()-@AD,101)) OR
(@AD = 1 and vwAllAvailables.DateOfferred = convert(varchar(10),getdate(),101)) OR
(@AD = 2 and vwAllAvailables.DateOfferred = convert(varchar(10),getdate()-1,101)))
and
((@RDateRange = 'Yes' and REQS.DateRequested > convert(varchar(10),getdate()-@RD,101)) OR
(@RD = 1 and REQS.DateRequested = convert(varchar(10),getdate(),101)) OR
(@RD = 2 and REQS.DateRequested = convert(varchar(10),getdate()-1,101)))
order by vwAllAvailables.dateofferred desc,vwAllAvailables.PART_NO, vwAllAvailables.QTY, vwAllAvailables.PRICE, CONTACT.CONTACT, CONTACT.PHONE1, vwAllAvailables.InternalPartNo

2 Original where you will see the CONTACT table join:

AVAILABLES:
SELECT DISTINCT  vwAllAvailables.PART_NO, vwAllAvailables.QTY, vwAllAvailables.PRICE,
 vwAllAvailables.InternalPartNo, vwAllAvailables.DateOfferred, CONTACT.COMPANY, CONTACT.CONTACT, CONTACT.PHONE1, contact.co_id
FROM vwAllAvailables
INNER JOIN REQS ON vwAllAvailables.part_no = REQS.Part_no
LEFT JOIN CONTACT ON vwAllAvailables.CO_ID = CONTACT.CO_ID
WHERE
vwAllAvailables.CO_ID Like @COID AND
((@ADateRange = 'Yes' and vwAllAvailables.DateOfferred > convert(varchar(10),getdate()-@AD,101)) OR
(@AD = 1 and vwAllAvailables.DateOfferred = convert(varchar(10),getdate(),101)) OR
(@AD = 2 and vwAllAvailables.DateOfferred = convert(varchar(10),getdate()-1,101)))
and
((@RDateRange = 'Yes' and REQS.DateRequested > convert(varchar(10),getdate()-@RD,101)) OR
(@RD = 1 and REQS.DateRequested = convert(varchar(10),getdate(),101)) OR
(@RD = 2 and REQS.DateRequested = convert(varchar(10),getdate()-1,101)))
order by vwAllAvailables.dateofferred desc,  vwAllAvailables.qty desc

REQS:
SELECT DISTINCT REQS.PART_NO,REQS.QTY, REQS.PRICE,REQS.INTERNAL,REQS.DATEREQUESTED,CONTACT.COMPANY,
CONTACT.PHONE1, CONTACT.CONTACT, reqs.co_id
FROM REQS LEFT JOIN CONTACT ON REQS.CO_ID = CONTACT.CO_ID
WHERE
REQS.PART_NO = @PartNo AND
((@RDateRange = 'Yes' and REQS.DateRequested > convert(varchar(10),getdate()-@RD,101)) OR
(@RD = 1 and REQS.DateRequested = convert(varchar(10),getdate(),101)) OR
(@RD = 2 and REQS.DateRequested = convert(varchar(10),getdate()-1,101)))
order by REQS.daterequested desc
GO
0
Comment
Question by:sbornstein2
2 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 400 total points
ID: 13808933
Was there a problem with this?

SELECT DISTINCT vwAllAvailables.PART_NO AS APART_NO
, vwAllAvailables.QTY AS AQTY
, vwAllAvailables.PRICE AS APRICE
, vwAllAvailables.InternalPartNo AS AInternalPartNo
, vwAllAvailables.DateOfferred AS ADateOfferred
, CONTACT.COMPANY AS COMPANY
, CONTACT.CONTACT AS CONTACT
, CONTACT.PHONE1 AS PHONE1
, contact.co_id  AS CO_ID
, REQS.PART_NO AS RPART_NO
, REQS.QTY AS RQTY
, REQS.PRICE AS RPRICE
 ,REQS.INTERNAL AS RINTERNAL
, REQS.DATEREQUESTED AS RDATEREQUESTED
, reqs.co_id AS RCO_ID
, CONTACT2.COMPANY AS COMPANY
, CONTACT2.CONTACT AS CONTACT
, CONTACT2.PHONE1 AS PHONE1
, contact2.co_id  AS CO_ID
FROM vwAllAvailables
INNER JOIN REQS ON vwAllAvailables.part_no = REQS.Part_no
LEFT JOIN CONTACT ON vwAllAvailables.CO_ID = CONTACT.CO_ID
LEFT JOIN CONTACT CONTACT2 on REQS.CO_ID = CONTACT.CO_ID
WHERE
vwAllAvailables.CO_ID Like @COID AND
((@ADateRange = 'Yes' and vwAllAvailables.DateOfferred > convert(varchar(10),getdate()-@AD,101)) OR
(@AD = 1 and vwAllAvailables.DateOfferred = convert(varchar(10),getdate(),101)) OR
(@AD = 2 and vwAllAvailables.DateOfferred = convert(varchar(10),getdate()-1,101)))
and
((@RDateRange = 'Yes' and REQS.DateRequested > convert(varchar(10),getdate()-@RD,101)) OR
(@RD = 1 and REQS.DateRequested = convert(varchar(10),getdate(),101)) OR
(@RD = 2 and REQS.DateRequested = convert(varchar(10),getdate()-1,101)))
order by vwAllAvailables.dateofferred desc,vwAllAvailables.PART_NO, vwAllAvailables.QTY, vwAllAvailables.PRICE, CONTACT.CONTACT, CONTACT.PHONE1, vwAllAvailables.InternalPartNo
0
 

Author Comment

by:sbornstein2
ID: 13809322
Hey there stranger :).  Yes the Contact info needs to pull from both tables and I need to write out the contact data for the Availables linked on COID but then the REQ will also have a COID that will match usually a different COID.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question