sql join question

Dev Shed Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

August 20th, 2003, 10:28 AM

bigdavejonnyt

lord of err; master of umm

Join Date: Mar 2003

Location: uk

Posts: 92

Time spent in forums: 6 h 47 m 40 sec
Reputation Power: 6

sql join question

hi,

i'm almost ashamed to ask, i should be able to get this going on my own, but here goes any way...apologies for using the php tags - the fixed width font makes layout easier !!

two tables - products and where orders orders.productId = products.Id

PHP Code:


		
			
 table 1 - products                  table 2 - orders

+--------------------------+        +---------------------------+             

| id | productName | price |        | OrderId | productId | qty |

+--------------------------+        +---------------------------+

| a1 | product1    | 23.45 |        |     002 | a1        |   2 |

+--------------------------+        +---------------------------+

| a2 | product2    | 34.56 |        |     002 | a2        |   1 |

+--------------------------+        +---------------------------+

| a3 | product3    | 45.67 |        |     002 | a3        |   3 |

+--------------------------+        +---------------------------+

                                    |     001 | a2        |   2 |

                                    +---------------------------+

                                    |     001 | a3        |   5 |

                                    +---------------------------+

i want to write a query which shows each product in the table once, and any order quantities against each line where orderId ='001' so the output looks like this.

PHP Code:


		
			
 query output

+------------------------------------------------------------------------------------+             

| products.id | products.productName | products.price | orders.OrderId |  orders.qty |

+------------------------------------------------------------------------------------+

|          a1 | product1             |          23.45 |                |             |

+------------------------------------------------------------------------------------+

|          a2 | product2             |          34.56 |            001 |           2 |

+------------------------------------------------------------------------------------+

|          a3 | product3             |          45.67 |            001 |           5 |

+------------------------------------------------------------------------------------+

i remain confident that your thoughts will prove invaluable, and thank you in advance for any time you spend on this.

__________________
rgds
bdjt

August 20th, 2003, 08:26 PM

unatratnag

Average Intelligence

Join Date: Apr 2003

Location: Ohio/Chicago

Posts: 678

Time spent in forums: 10 m 22 sec
Reputation Power: 6

You're right, you should have gotten this

Code:

select p.id, p.productName, p.price, ISNULL(o.OrderId,''), ISNULL(o.qty,'')
from products as p,orders as o
WHERE p.id *= o.productid
and o.orderid = '001'
order by p.id

If you just do a left outer join it still grabs the = row you want if the item exists in the left part of the join. easy enough ne?

Last edited by unatratnag : August 20th, 2003 at 08:32 PM.

August 21st, 2003, 03:32 AM