Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thom_sch
Starting Member
2 Posts |
 Posted - 2012-02-01 : 03:28:13
|
| Hello ! I'm new to SQL and have the following question : I have table T_A with field A1, A2, A3, ... which is joined with table T_B with B1, B2, B3 (A1 = B1, and A2 with B2) . My issue is with A2 = B2 join condition. If A2 = B2, I want to read the fields from this exact T_B table entry. If not, I want to look in T_B for an entry which meets the condition B2 = '000000'. What alternatives do I have for modeling ? Conditional where clause / select ? SQL script ? thanks for your help . -Tom |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-01 : 07:10:32
|
You can change the join condition to include the B2='000000' as in:WHERE A1=B1 AND (A2=B2 OR B2='000000') |
 |
|
|
thom_sch
Starting Member
2 Posts |
Posted - 2012-02-01 : 08:14:54
|
| Not sure ... it has to be ensured that A2=B2 is always processed before B2 = '000000'. Because I can have two entries in T_B, one where A2=B2 is true and one with B2 = '000000'. Then the entry where A2=B2 has priority and I do not want to select the one with B2 = '000000' . |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-01 : 08:35:35
|
| Since SELECT query is a set operation, the query itself does not have the concept of something being processed before another (for the most part). What you could do is add conditions in the WHERE clause to pick up the appropriate rows based on whether or not there are rows that satisfy the condition A2=B2.If you post the table structure, your query and sample data, I am sure that someone on the forum would be able to easily suggest the best way to accomplish this. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-01 : 08:48:11
|
SELECT * FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t2.A = t1.A AND t2.B = t1.BUNIONSELECT * FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t2.A = t1.A AND t2.B = '000000'
N 56°04'39.26"E 12°55'05.63" |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-01 : 10:04:36
|
quote:
Originally posted by SwePeso
SELECT * FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t2.A = t1.A AND t2.B = t1.BUNIONSELECT * FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t2.A = t1.A AND t2.B = '000000'
N 56°04'39.26"E 12°55'05.63"
Wouldn't that still produce two rows in the output if there were two identical rows in TableB, except for one with Column B = '000000' and another with Column B having a matching value in TableA?If I understood OP's request correctly, in such cases, they want to eliminate the one with '00000' from the result. But if there were no matching rows, then the row with the '00000' should be kept. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
|
|
|
|
|
Conditional select