CODEWITHSUNDEEP
sqljoin
Steve G.
Steve G.

Reputation: 409

SQL: Joins with an OR?

I'm trying to pull data from two tables, and I've been reading up on JOINs both here and on Google, but nothing seems to be working properly.

What I need is to pull from a customer table and a customertype table. Here's the customer table layout:

CustomerID: int PRIMARY KEY UNIQUE
Customername: varstring
Customeraddress: varstring
Customertype: int
Customerdescription: text

Example:

CustomerID: 1
CustomernName: John Smith
Customeraddress: 1 Fake St., New York, New York, 11111
Customertype: 1
Customerdescription: Customer makes a monthly purchase of this and that.

Here's the customertype layout:

TypeID: int PRIMARY KEY UNIQUE
Typetitle: varstring
Typedescription: text

Example:

TypeID: 1
Typetitle: In good standing
Typedescription: These customers are in good standing and owe nothing.

The Type field in the customer table obviously should correspond with the Type field in the customertype table. So, if I need to know all John Smith's data, I want, in one query:

CustomerID: 1
Customername: John Smith
Customeraddress: 1 Fake St. New York, New York, 11111
Typetitle: In good standing
Customerdescription: Customer makes a monthly purchase of this and that.

The snag is, sometimes the Customertype is 0, which doesn't correspond to any record in the customertype table. Their type is undefined.

I've read about JOINs in various places, but I can't seem to make a query that will handle both where the CustomerID=the TypeID or where the CustomerID=0. Does anyone have any suggestions or know what the query would be?

Upvotes: 0

Views: 51

Answers (2)

isaace
isaace

Reputation: 3429

This should work:

SELECT c.CustomerID, c.Customername, c.Customeraddress, t.Typetitle, c.Customerdescription  
FROM Customer c 
LEFT JOIN customertype t ON c.Customertype = t.TypeID

A left join will not filter out those that don't have a match in the second table. You can add in the select which ever columns you want from either table.

Upvotes: 1

Matt Spinks
Matt Spinks

Reputation: 6700

You can use an OR expression in your join clause. In your case, you will need to do a left join. Something like...

SELECT * FROM Customer LEFT JOIN Type ON Customer.Customertype = Type.TypeID OR Customer.Customertype = 0

Upvotes: 0

Related Questions