MYSQL query with if-else statement

Question

Hi I have below table structure shown in image

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...?

I would go for a better table design. Combine the CustomerTable and VendorTable and keep the type there instead. — Kayaman
– Kayaman, Commented Jun 13, 2016 at 11:16
But I want data from CustomerTable or VendorTable based on whoseid column data from TransportSticker Table, then why should I combine them — Shree
– Shree, Commented Jun 13, 2016 at 11:17
Because your table design is wrong, making you attempt bad solutions like the CASE statement in a JOIN. — Kayaman
– Kayaman, Commented Jun 13, 2016 at 11:19

Giorgos Betsos · Accepted Answer · 2016-06-13 11:19:51Z

2

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

answered Jun 13, 2016 at 11:19

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

P. Weyrich · Accepted Answer · 2016-06-13 11:26:00Z

0

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.

answered Jun 13, 2016 at 11:26

P. Weyrich

193 bronze badges

Collectives™ on Stack Overflow

MYSQL query with if-else statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related