how to join two tables where second table has condtion

Question

I have two tables like

shopping_cart_table and customer_table

shopping_cart_table has fields shoppingcart_id | home_No|

customer_table has fields shoppingcart_id | status| customer_type|

I want to get the home_No from shopping_cart_table WHERE (customer_table.status is null OR customer_table.status='Y') AND customer_table.customer_type='X'

both table can join from shoppingcart_id

Sorry if this sounds harsh, but that is really, really basic SQL stuff. I'd suggest you grab a SQL tutorial and work through it. — Frank Schmitt, Dec 03 '14 at 08:27
I know its a basic one but it doesn't work for me. That's why I asked a help — user2771655, Dec 03 '14 at 08:33
Then please post a *complete* example next time. In your case, that would be your current SQL statement plus any error message you might get. — Frank Schmitt, Dec 03 '14 at 08:39
What does this return `SELECT s.home_No FROM shopping_cart_table AS s INNER JOIN customer_table AS c ON s.shoppingcart_id = c.shoppingcart_id` — Matt, Dec 03 '14 at 09:04
@Matt It returns some records, But if I put the remaining condition to customer_table, it doesn't return anything — user2771655, Dec 03 '14 at 09:07
Can you post up your data for each of the tables, `select * from shopping_cart_table` and `select * from customer_table ` — Matt, Dec 03 '14 at 09:15

score 1 · Answer 1 · edited Dec 03 '14 at 08:26

1

Actually this is just basic. You can use join & put where conditions.

Select a.home_No
  from shopping_cart_table as a
 inner join customer_table as b
    on a.shoppingcart_id = b.shoppingcart_id
 where nvl(b.status,'Y') = 'Y'
   and customer_type='X'

edited Dec 03 '14 at 08:26

crowne

8,336
2
34
48

answered Dec 03 '14 at 08:18

AK47

3,599
3
16
35

Shouldn't it be `b.status IS NOT NULL` rather than `b.status = 'Y'` - `Y` is not null... but `NOT NULL` is not `Y` – Captain Dec 03 '14 at 08:20
@AK47 This doesn't work. Also you missed one condtion. – user2771655 Dec 03 '14 at 08:21
Updated. Sorry It should be b.status is null – user2771655 Dec 03 '14 at 08:22
Edited , condition added! – AK47 Dec 03 '14 at 08:22
Oh! I also have changed my answer please take a look – AK47 Dec 03 '14 at 08:23
1

I like to see the use of ANSI joins – crowne Dec 03 '14 at 08:24
Doesn't return any record – user2771655 Dec 03 '14 at 08:34
Please share sample data – AK47 Dec 03 '14 at 09:33

score 0 · Answer 2 · edited Dec 03 '14 at 08:35

0

You can try this query:

SELECT sct.home_no 
FROM shopping_cart_table AS sct
, customer_table AS ct 
WHERE sct.shoppingcart_id = ct.shoppingcart_id 
AND (
  ct.status IS NOT NULL 
  OR ct.status = 'Y') 
AND ct.customer_type = 'X'

edited Dec 03 '14 at 08:35

Captain

1,998
13
13

answered Dec 03 '14 at 08:19

Thomas Van Driessche

85
6

Non ANSI syntax? that is outdated mate. Please take a look here http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax – AK47 Dec 03 '14 at 08:26
@Thomas Van Driessche this returns 0 rows. – user2771655 Dec 03 '14 at 08:31

score 0 · Answer 3 · answered Dec 03 '14 at 08:21

select home_No 
from shopping_cart_table, customer_table
WHERE shopping_cart_table.shoppingcart_id = customer_table.shoppingcart_id
AND(customer_table.status is not null OR customer_table.status='Y') AND       
customer_table.customer_type='X'

But this condition looks a bit strange:

(customer_table.status is not null OR customer_table.status='Y')

Maybe you'd want to change it for

nvl(customer_table.status,'Y')='Y'

aqssuming that 'not' was put there by a mistake

Hi, Yes you are correct. It should be is null. If I use your query it doesn't return any thing. — user2771655, Dec 03 '14 at 09:12

how to join two tables where second table has condtion

3 Answers3