CODEWITHSUNDEEP
mysql
Rounak
Rounak

Reputation: 181

Mysql select query does not return the exact result

I have two tables named User, Holiday. Now my objective is to search user. I need to search a user by the name from user table or city from the holiday table. If I search by name, then Name from user table and his city from holiday table should be display. And if I search by city then city from holiday table and his Name from user table should be displayed.

User table::

Holiday Table enter image description here

I have tried the following::

          SELECT * FROM `holiday`,`user` 
          WHERE holiday.UserID=user.UserID 
                and user.Name like '%Bre%' 
                or holiday.City like '%Bre%'

But :(

Upvotes: 1

Views: 191

Answers (4)

Maksym Polshcha
Maksym Polshcha

Reputation: 18358

Try:

      SELECT * FROM `user` left join `holiday` on holiday.UserID=user.UserID 
      WHERE user.Name like '%Bre%' 
            or holiday.City like '%Bre%'

Upvotes: 0

Akash KC
Akash KC

Reputation: 16310

I would like you to use LEFT JOIN,

  SELECT user.Name,holiday.City FROM  `user` LEFT JOIN `holiday`
                  ON user.UserID  =holiday.UserID
                        WHERE (user.Name like '%Bre%' 
                        or holiday.City like '%Bre%')

Upvotes: 2

JvdBerg
JvdBerg

Reputation: 21856

Try this:

SELECT 
  * 
FROM 
  `holiday` h
LEFT JOIN 
 `user` u
ON 
  h.UserID = u.UserID
WHERE
  u.Name like '%Bre%' or h.City like '%Bre%'

Notice, that given your tables no result is returned.

Upvotes: 0

lotusphp
lotusphp

Reputation: 227

You can not use select * because there are some columns have the same name in two tables, such as UserID.

Try this:

SELECT `user`.userID, `user`.Name, `holiday`.city 
FROM `holiday`,`user` 
WHERE holiday.UserID=user.UserID 
      and user.Name like '%Bre%' 
      or holiday.City like '%Bre%'

Upvotes: 0

Related Questions