Hi,
I appreciate if anyone can help me out, please. I have two tables;
TableA and TableB. When the user enters the account, I match it
against TableA.col1. Then I get the TableA.col2 from TableA and match
against TableB.col1. If not found, I still want the record to show.
My SQL only works if record is found on both tables. Here is my
current SQL:
select a.col1, a.col2, b.col2
from TableA a LEFT OUTER JOIN TableB b
where a.col1 = '456'
and a.col2 = b.col1
examples:
TableA
col1 col2
123 111
456 222
789 333
TableB
col1 col2
111 94.95
444 100.00
Desired result:
TableA.col1 TableA.col2 TableB.col2
456 222 0.00
Thanks in advance.
Teresa 2 1882
If a.col2 = b.col1 then try this Statement
select a.col1, a.col2,
(CASE WHEN b.col2 is null THEN 0 ELSE b.col2 END)
from TableA a LEFT OUTER JOIN TableB b On a.col2 = b.col1
where a.col1 = '456' and a.col2 = b.col1
--
Message posted via http://www.dbmonster.com tp****@pepco.com wrote: Hi,
Make that:
select a.col1, a.col2, b.col2
from TableA a LEFT OUTER JOIN TableB b
ON a.col1 = '456'
and a.col2 = b.col1;
With the comparisons in the WHERE clause instead of the ON clause they are
post-join conditions. The optimizer may work it all out, but you're asking
for a cartesian product then filtering the results. This way, with the
filter and join condition as pre-join conditions, the outer join only
creates the desired rows in the first place.
Art S. Kagel
I appreciate if anyone can help me out, please. I have two tables; TableA and TableB. When the user enters the account, I match it against TableA.col1. Then I get the TableA.col2 from TableA and match against TableB.col1. If not found, I still want the record to show. My SQL only works if record is found on both tables. Here is my current SQL:
select a.col1, a.col2, b.col2 from TableA a LEFT OUTER JOIN TableB b where a.col1 = '456' and a.col2 = b.col1
examples: TableA col1 col2 123 111 456 222 789 333
TableB col1 col2 111 94.95 444 100.00
Desired result: TableA.col1 TableA.col2 TableB.col2
456 222 0.00
Thanks in advance. Teresa This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Gerald Maher |
last post by:
Hi
I need to select 2 tables . The field 'p_description' is not always
aviable but i still need to print it to the screen
SELECT *
FROM task,p_description
WHERE ComponentIDLink = 34 OR...
|
by: David Logan |
last post by:
Hello,
I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the...
|
by: brett |
last post by:
Here is my SQL string:
"SELECT to_ordnum, to_orddate," _
& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *
(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ON...
|
by: dumbledad |
last post by:
Hi All,
I'm confused by how to replace a SELECT statement in a SQL statement
with a specific value. The table I'm working on is a list of words (a
column called "word") with an index int...
|
by: Anna Smith |
last post by:
I have an extremely long and complex select statement as
shown below and I need to split it onto two lines but not
quite sure how to do it because I've never needed to
before. I need to split it...
|
by: cedrik |
last post by:
Hi,
I've searched all over for this and not found that much useful
information for what I think to be a fairly obvious issue.
I am having to do some benchmark tests on a SELECT statement and...
|
by: Bob Stearns |
last post by:
I am getting duplicate rows back from a select distinct statement of the
form:
SELECT DISTINCT 'jhough', '000111', t0.bhid
FROM (SELECT lots of good stuff) t0
LEFT OUTER JOIN another_table ...
|
by: Jean-Claude |
last post by:
Hi,
which is the faster query ? (of course, in my case the real queries are more
complex)
1/
select *
from file1 a join file2 b on b.key=a.key
where b.data=123
and b.name='TEST'
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |