Join 2 tables with a where and return all

[timgerr]

Hello all, I have these 2 tables

Catalog:
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | 
| item1    | varchar(256) | NO   |     |         |                
| item2    | varchar(256) | NO   |     |         |                
| item3    | varchar(256) | NO   |     |         |                
| color    | varchar(256) | NO   |     |         |                
| amount   | varchar(25)  | NO   |     |         |                
| price    | varchar(25)  | NO   |     |         |               
| quantity | varchar(1)   | NO   |     |         |               
+----------+--------------+------+-----+---------+----------------+

Inventory:
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | 
| uid      | varchar(6)   | NO   | MUL |         |                
| unode    | varchar(256) | NO   | MUL |         |                
| catid    | varchar(5)   | NO   | MUL |         |                
| quantity | varchar(4)   | NO   |     |         |                
| date     | varchar(25)  | NO   |     |         |                
+----------+--------------+------+-----+---------+----------------+

I have 506 entries in catalog and only 1 test item in inventory. I have join an left join that looks like this

"select catalog.id, catalog.price, inventory.quantity  from catalog left  join inventory on catalog.id = inventory.catid "

and that will return all 506 rows including null or not null rows from inventory.quantity.

The problem is that I have to single out inventory.quantity where inventory.unide = '4'. So If I recreate the statement

select catalog.id, catalog.price, inventory.quantity  from catalog left  join inventory on catalog.id = inventory.catid   where inventory.unode = '4'"

then only one row is returned.
I need to return all 506 rows from catalog and join the catalog.id = inventory.catid where inventory.unode = 4 but still return all 506 rows.

Does this make sence???? can this be done??

Thanks,
timgerr






-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!

 

[r937]

SELECT catalog.id
     , catalog.price
     , inventory.quantity  
  FROM catalog 
LEFT OUTER
  JOIN inventory 
    ON inventory.catid = catalog.id
   [b][red]AND[/red][/b] inventory.unode = '4'

r937.com | rudy.ca

 

[timgerr]

Thanks for the response I have been out for personal reasons. I now need to modify this query to something like this (but it does not work)

SELECT catalog.id
     , catalog.price
     , inventory.quantity  
  FROM catalog
  where item3 = 'somthing'    <----- ERROR With This
LEFT OUTER
  JOIN inventory
    ON inventory.catid = catalog.id
   AND inventory.unode = '4'

I have to add a where to the query and I am getting errrors

Thanks for the help,
timgerr

-How important does a person have to be before they are considered assassinated instead of just murdered?
So there you go! You're the retarded offspring of five monkeys having butt sex with a fish-squirrel! Congratulations!

 

[r937]

o hai

the correct place for the WHERE clause, let me show u it

SELECT catalog.id
     , catalog.price
     , inventory.quantity  
  FROM catalog
LEFT OUTER
  JOIN inventory
    ON inventory.catid = catalog.id
   AND inventory.unode = '4' 
 WHERE catalog.item3 = 'somthing'

r937.com | rudy.ca