CODEWITHSUNDEEP
mysql
Shree
Shree

Reputation: 795

MYSQL query with if-else statement

Hi I have below table structure shown in image

enter image description here

I am trying to write query in mysql to fetch

name,address,mono from customer table or vendortable depend on whoseid value from transportsticker table

I tried as 

 SELECT transportsticker.* ,AA.name,AA.address,AA.mono FROM transportsticker INNER JOIN (case when (transportsticker.whoseid='vendor') then (vendortable) else (customertable) end) AA   ON AA.id=transportsticker.vorcid AND transportsticker.id=1

But it is giving syntax error. can anybody help me...?

Upvotes: 3

Views: 77

Answers (2)

P. Weyrich
P. Weyrich

Reputation: 19

Try something like this.

SELECT name, address, mono FROM TransportSticker, CustomerTable, VendorTable WHERE (TransportSticker.whoseid = 'vendor' AND VendorTable.id = TransportSticker.vorcid) OR (TransportSticker.whoseid = 'customer' AND CustomerTable.id = TransportSticker.vorcid)

This selects the corresponding entries from the Tables VendorTable and CustomerTable to your entries in TransportSticker.

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

CASE in SQL is an expression and cannot be used to control flow of execution like in procedural languages.

You can use LEFT JOIN with COALESCE instead:

SELECT t.*, 
       COALESCE(c.name, v.name), 
       COALESCE(c.address, v.address),
       COALESCE(c.mono, v.mono) 
FROM transportsticker AS t
LEFT JOIN customertable AS c 
   ON t.whoseid='customer' AND c.id=t.vorcid
LEFT JOIN vendortable AS v
   ON t.whoseid='vendor' AND v.id=t.vorcid
WHERE t.id=1

Upvotes: 2

Related Questions