CODEWITHSUNDEEP
sql
Poku
Poku

Reputation: 3178

SQL joining data from other tables

I have the following tables:

Item (Table) no name price description

Item-custom (Table) userid itemid description1 description2

If an item have a description in Item-custom table i want to show that description else i'm showing the description from item table.

I made a query where i inner join the item-custom table on item.no = item-custom.itemid. This works okay if the item have an description in the item-custom table. But if it doesn't have a description the query doesn't return any data.

How should i write this query so i always get a item record no matter if it have a description in item-custom table.

Here is what i have:

SELECT item.description, item-custom.description1, item-custom.description 
FROM item
INNER JOIN item-custom ON item.no = item-custom.itemid

Upvotes: 2

Views: 126

Answers (5)

capuzzo
capuzzo

Reputation: 1

SELECT CASE WHEN item-custom.description2 IS NOT NULL 
THEN item-custom.description2 
ELSE item.description END, ...

Upvotes: 0

Rob
Rob

Reputation: 6871

You can do this using a left join instead of an inner join. You can read more about left joins here An inner join only takes the records from both tables that has non nullable columns. So if description is empty (NULL) the record won't be shown. While using a left join it will.

SELECT item.description, item-custom.description1, item-custom.description 
FROM item
LEFT JOIN item-custom ON item.no = item-custom.itemid

Upvotes: 3

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

I think it fits more to your condition

Sql Server:

SELECT ISNULL(item.description, item-custom.description) as descriptin
FROM item
LEFT OUTER JOIN item-custom ON item.no = item-custom.itemid

MySql

SELECT COALESCE(item.description, item-custom.description) as descriptin
FROM item
LEFT OUTER JOIN item-custom ON item.no = item-custom.itemid

ISNULL or COALESCE Return the first non-NULL argument

Upvotes: 1

sshet
sshet

Reputation: 1160

Try with where clause instead

SELECT item.description, item-custom.description1, item-custom.description 
FROM item as item , item-custom as item-custom
WHERE  item.no = item-custom.itemid

Upvotes: 0

Ass3mbler
Ass3mbler

Reputation: 3915

SELECT item.description, item-custom.description1, item-custom.description 
FROM item
LEFT OUTER JOIN item-custom ON item.no = item-custom.itemid

Upvotes: 2

Related Questions