how to implement joins to the existing querys

Question

0.00/5 (No votes)

See more:

Hi,I am very new to joins.. please suggest me how to use joins for the following query.

SQL

SELECT Line_No, isnull(Tool_id, ' ') Tool_id, QUAD_ID, Tool_grade, EARLY_WARNING_FLAG, Department, STATION,
CASE WHEN DATEDIFF(MI,ISNULL(NOK_TIME, TIMESTAMP2),GETDATE()) > 1440 THEN TIMESTAMP2 
ELSE ISNULL(NOK_TIME, TIMESTAMP2) END AS NOK_TIME, Ack_Time,
DATEDIFF(MI,TIMESTAMP2,GETDATE()) AS DOWNDURATION FROM TX_STATION_STATUS NOLOCK
WHERE LINE_NO LIKE '7%'
AND STATION LIKE'S%'
AND 
(TOOL_ID IS NOT NULL AND TOOL_ID IN (SELECT TESTER_ID FROM TX_CR_QTY_BUILD WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '%')) AND TESTER_ID = TOOL_ID AND BUILD_HOUR + 1 >= getdate())) 
OR (TOOL_ID IS NULL AND STATION IN (SELECT STATION FROM TX_CR_QTY_BUILD WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '')) AND STATION = STATION AND BUILD_HOUR + 1 >= getdate()) ) 
AND STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), Timestamp2, 126) IN ( 
SELECT STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), MAX(Timestamp2), 126)
FROM TX_STATION_STATUS WITH (NOLOCK)
WHERE LINE_NO LIKE '7%'
GROUP BY STATION, TOOL_ID
)

Thanks in advance,
kp.

Posted 28-Jul-13 22:26pm

KPrao82

Updated 28-Jul-13 23:11pm

berrymaria

v3

Add a Solution

Comments

Adarsh chauhan 29-Jul-13 4:35am

Please share your table structure, relation between them and your goal.
So that we can help you with an answer. without enough info. we can only hit and try on your query, which may or may not be correct answer for you.

KPrao82 29-Jul-13 5:07am

Hi its my table structure:
table1:
TX_STATION_STATUS: FIELDS.
STATION
DEPARTMENT
ACK_TIME
TECH_ID
QUAD_ID
NOK_TIME
TIMESTAMP2
TOOL_ID
TOOL_GRADE
EARLY_WARNING_FLAG
etc..

table2:
TX_CR_QTY_BUILD
FIELDS:
LINE_NO,
STATION,
BUILD_HOUR,
TIMESTAMP2,
TESTER_ID,
etc..

Need to get LINE_NO@TX_STATION_STATUS from TESTER_ID@TX_CR_QTY_BUILD..

remarks:
arrStationMain(LoopCounter) = Station Column

strShiftTime =
BUILD_HOUR >='2013-07-15 10:54:35.000'

many many thanks.

Adarsh chauhan 29-Jul-13 5:12am

Can you provide your tables in create mode?? it will save my time as well as there would not be mismatch in datatypes between yours and my tables.
also tell me that is the relationship between your tables is the station column??
and your "Need to get LINE_NO@TX_STATION_STATUS from TESTER_ID@TX_CR_QTY_BUILD.." is still not clear to me..

KPrao82 29-Jul-13 5:17am

Hi its my table structure:

CREATE TABLE [dbo].[TX_STATION_STATUS](
[STATION] [varchar](20) NOT NULL,
[LINE_NO] [varchar](6) NULL,
[QUAD_ID] [numeric](2, 0) NULL,
[OPERATOR] [varchar](10) NOT NULL,
[DEPARTMENT] [varchar](20) NOT NULL,
[ESCALATE_FLAG] [varchar](1) NULL,
[TIMESTAMP2] [datetime] NOT NULL,
[CREW] [varchar](1) NOT NULL,
[FAILURE_CODE] [varchar](20) NULL,
[ACK_TIME] [datetime] NULL,
[TECH_ID] [varchar](10) NULL,
[EARLY_WARNING_FLAG] [varchar](1) NULL,
[EARLY_WARNING_STRING] [varchar](50) NULL,
[TIMEOUT] [varchar](10) NULL,
[QC_ID] [varchar](10) NULL,
[Q_CODE] [varchar](40) NULL,
[SERIAL_NO] [varchar](13) NULL,
[TOOL_ID] [varchar](20) NULL,
[TOOL_GRADE] [varchar](10) NULL,
[NOK_TIME] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TX_STATION_STATUS] ADD CONSTRAINT [DF_TX_STATION_STATUS_ESCALATE_FLAG] DEFAULT ('0') FOR [ESCALATE_FLAG]
GO

ALTER TABLE [dbo].[TX_STATION_STATUS] ADD CONSTRAINT [DF_TX_STATION_STATUS_EARLY_WARNING_FLAG] DEFAULT ('0') FOR [EARLY_WARNING_FLAG]
GO

ALTER TABLE [dbo].[TX_STATION_STATUS] ADD CONSTRAINT [DF_TX_STATION_STATUS_EARLY_WARNING_STRING] DEFAULT ('0') FOR [EARLY_WARNING_STRING]
GO

----------------------------
CREATE TABLE [dbo].[TX_CR_QTY_BUILD](
[LINE_NO] [varchar](10) NOT NULL,
[STATION] [varchar](20) NOT NULL,
[BUILD_HOUR] [datetime] NOT NULL,
[QTY_BUILD] [numeric](18, 0) NOT NULL,
[TIMESTAMP2] [datetime] NOT NULL,
[QTY_FAIL] [numeric](18, 0) NULL,
[TESTER_ID] [varchar](20) NULL,
[QTY_FAIL_SKIPCHECK] [numeric](18, 0) NULL,
[FAILURE_CODE_DEF] [varchar](100) NULL,
[QTY_HEAD_BUILD] [numeric](18, 0) NULL,
[QTY_FAILURE_CODE1] [numeric](18, 0) NULL,
[QTY_FAILURE_CODE2] [numeric](18, 0) NULL,
[QTY_FAILURE_CODE3] [numeric](18, 0) NULL,
[QTY_FAILURE_CODE4] [numeric](18, 0) NULL,
[QTY_FAILURE_CODE5] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TX_CR_QTY_BUILD] ADD CONSTRAINT [DF_TX_CR_QTY_BUILD_QTY_BUILD] DEFAULT (0) FOR [QTY_BUILD]
GO

ALTER TABLE [dbo].[TX_CR_QTY_BUILD] ADD CONSTRAINT [DF_TX_CR_QTY_BUILD_QTY_FAIL] DEFAULT (0) FOR [QTY_FAIL]
GO

many many thanks.

3 solutions

Solution 1

visit following links for How to use joins..

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/[^]

http://blogs.msdn.com/b/spike/archive/2008/07/22/the-simplification-of-join-simple-example-of-how-t-sql-join-work.aspx[^]

http://blogs.msdn.com/b/blogdoezequiel/archive/2012/01/15/t-sql-misconceptions-join-on-vs-where.aspx#.UfYpwqxZKW5[^]

http://msdn.microsoft.com/en-us/library/ms191517%28v=sql.105%29.aspx[^]

SQL Joins[^]

Posted 28-Jul-13 22:46pm

Adarsh chauhan

Solution 2

Hi ,
Check this it will give you Idea try it

SQL

with TName as (
  SELECT Line_No, isnull(Tool_id, ' ') Tool_id, QUAD_ID, Tool_grade, EARLY_WARNING_FLAG, Department, STATION,
CASE WHEN DATEDIFF(MI,ISNULL(NOK_TIME, TIMESTAMP2),GETDATE()) > 1440 THEN TIMESTAMP2
ELSE ISNULL(NOK_TIME, TIMESTAMP2) END AS NOK_TIME, Ack_Time,
DATEDIFF(MI,TIMESTAMP2,GETDATE()) AS DOWNDURATION FROM TX_STATION_STATUS NOLOCK
WHERE LINE_NO LIKE '7%'
AND STATION LIKE'S%'
AND
(TOOL_ID IS NOT NULL AND TOOL_ID IN (SELECT TESTER_ID FROM TX_CR_QTY_BUILD  WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '%')) AND TESTER_ID = TOOL_ID AND BUILD_HOUR + 1 >= getdate()))
OR (TOOL_ID IS NULL AND STATION IN (SELECT STATION FROM TX_CR_QTY_BUILD WITH (NOLOCK) WHERE ((LINE_NO LIKE '7%') AND (LINE_NO LIKE '')) AND STATION = STATION AND BUILD_HOUR + 1 >= getdate()) )
AND STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), Timestamp2, 126) IN (
SELECT STATION+ISNULL(TOOL_ID,'')+CONVERT(VARCHAR(30), MAX(Timestamp2), 126)
      FROM TX_STATION_STATUS WITH (NOLOCK)
      WHERE LINE_NO LIKE '7%'
      GROUP BY STATION, TOOL_ID)
)
select col1,col2 ,TableName.Line_No from TName  inner join TableName
on
TableName.col1 = TName.Tool_id

Best Regards
M.Mitwalli

Posted 28-Jul-13 23:30pm

Mohamed Mitwalli

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Adarsh chauhan · Accepted Answer · 2013-07-28T23:42:00

I have made some changes in your query to show how to use join, because your goal is still not clear to me.
See if this is what you are asking for..

SQL

select TSS.Line_No, isnull(TSS.Tool_id, ' ') Tool_id, TSS.QUAD_ID, TSS.Tool_grade, TSS.EARLY_WARNING_FLAG
, TSS.Department, TSS.STATION,
CASE WHEN DATEDIFF(MI,ISNULL(TSS.NOK_TIME, TSS.TIMESTAMP2),GETDATE()) > 1440 THEN TSS.TIMESTAMP2
ELSE ISNULL(TSS.NOK_TIME, TSS.TIMESTAMP2) END AS NOK_TIME, TSS.Ack_Time,
DATEDIFF(MI,TSS.TIMESTAMP2,GETDATE()) AS DOWNDURATION
from TX_STATION_STATUS TSS (NOLOCK)
join TX_CR_QTY_BUILD TCQ (nolock)
on TSS.TOOL_ID =TCQ.TESTER_ID
or TSS.Station =TCQ.Station
WHERE TSS.LINE_NO LIKE '7%' AND TSS.STATION LIKE'S%'
and TCQ.BUILD_HOUR + 1 >= getdate()