The used SELECT statements have a different number of columns?

[shany0786]

I am trying to fetch data from multiple table but i am getting this error The used SELECT statements have a different number of columns

4 tables will have different number of columns i am doing this because i have common edit profile page number filds may vary.So how can i select data from different table

this is my query

 SELECT * FROM tbl_usrs where user_id='{$id}' 
      UNION ALL
      SELECT * FROM nm_camping_personal where camp_user_id='{$id}'
      UNION ALL
      SELECT * FROM hotel_registration where hotel_user_id='{$id}'

plz help!!

[minitauros]

Why don't you use a JOIN? :)

[cereal]

And how would you know which result sets belongs to what? If you're expecting a row for each table, then you could add empty columns:

select id, name, lastname from users where id = 1
union all
select user_id, email, '' as dummy_3 from addresses where user_id = 1;

But in this case the join solution is better.

[minitauros]

JOIN works if tables have different column names.

Example:

SELECT table_a.name, table_b.username FROM table_a JOIN table_b ON table_a.user_id = table_b.user_id WHERE table_a.user_id = 1

In the ON part of the query you tell the query which column in the first table matches the which column in the second table.

[cereal]

what's that 'as dummy_3 ' ??

It's '' as dummy_3 i.e. empty space defined with an alias, it will return an empty column, for example:

select '' as dummy_3;
+---------+
| dummy_3 |
+---------+
|         |
+---------+
1 row in set (0.00 sec)

It can be used as workaround when quering tables with column mismatch.

UNION and UNION ALL statements should have individual select statements having the same number of columns. If you know the number of columns are different, you can add "dummy" columns as explained by cereal. You'd also want to declare which columns need to be returned by each select statement. Using * is a bit lazy and can cause problems, especially if the order of columns in your table changes, e.g. inserting or dropping columns at some future date, or just a simple re-ordering.

As your tables seem to be linked by a common field, it seems that you should be using a certain flavour of JOIN as pointed out by minitauros.

But there is some confusion as to the format required. Your UNION statement would list (I'm assuming):

A single record from users table, followed by
Single or multiple records from camping table for that user, followed
SIngle or multiple records for hotel bookings for that user.

It is difficult to see what common columns you would have for all three tables.
In addition, using a basic JOIN (or INNER JOIN) statement for all three tables would give you a mess - repeat data or cartesian product, for example:

SELECT u.username, c.campdata, h.hoteldata FROM users u JOIN camping c ON u.`id` = c.`user_id` JOIN hotels h ON u.`id` = h.`user_id` WHERE u.id = 1

The above is an approximation of your tables (users, camping, hotels). So it depends how you want your data returned. Would it be better to run two (or three) separate queries?

[shany0786]

+2 to all of you guys thank u very much