MySql multiple table query problem....

[tomasm]

hello everyone.. i'm a newby in mysql but i'm trying hard.

So i won't lose a minute, and i'll go straight into the heart of the problem.

I have the following mysql dumps:

DROP TABLE IF EXISTS location_tree;
CREATE TABLE location_tree (
id int(11) NOT NULL auto_increment,
pid int(11) NOT NULL default '0',
mid int(5) NOT NULL default '0',
user varchar(60) NOT NULL default '',
title_greek varchar(255) NOT NULL default '',
title_english varchar(255) NOT NULL default '',
tk int(5) NOT NULL default '0',
not_ok tinyint(1) NOT NULL default '1',
PRIMARY KEY (id)
) ENGINE=MyISAM;
INSERT INTO location_tree VALUES (1, 0, 0, '', '11111111', '11111111', 0, 0);
INSERT INTO location_tree VALUES (12, 1, 0, '', '222222', '222222', 35100, 0);
INSERT INTO location_tree VALUES (145, 12, 1, '', '333333', '333333', 35100, 0);

DROP TABLE IF EXISTS location_details;
CREATE TABLE location_details (
id int(11) NOT NULL auto_increment,
gid varchar(60) NOT NULL default '',
lid int(11) NOT NULL default '0',
cords varchar(60) NOT NULL default '',
title_greek varchar(255) NOT NULL default '',
title_english varchar(255) NOT NULL default '',
user varchar(60) NOT NULL default '',
not_ok tinyint(1) NOT NULL default '1',
PRIMARY KEY id (id)
) ENGINE=MyISAM;
INSERT INTO location_details VALUES (522, 'plateies', 145, '', '1111111a', '1111111a', '', 0);
INSERT INTO location_details VALUES (692, 'perioxes', 145, '', '22222a', '22222a', '', 0);
INSERT INTO location_details VALUES (30, 'dromosi', 145, '', '33333a', '33333a', '', 0);

DROP TABLE IF EXISTS cg_business;
CREATE TABLE cg_business (
id int(11) NOT NULL auto_increment,
cid int(11) NOT NULL default '0',
bustitle_greek varchar(255) NOT NULL default '',
bustitle_english varchar(255) NOT NULL default '',
address int(11) default NULL,
addressnum varchar(20) default NULL,
perioxi int(11) NOT NULL default '0',
location int(11) NOT NULL default '0',
PRIMARY KEY (id),
FULLTEXT KEY wholesite (bustitle_greek,bustitle_english)
) ENGINE=MyISAM;

INSERT INTO cg_business VALUES (1775,128,'jhgjhgjhg', 'jhgjhgjhg',522, '8', 0, 145);
INSERT INTO cg_business VALUES (496,128,'jhgjhgjhghh', 'jhgjhgjhghh',522, '8', 0, 145);
INSERT INTO cg_business VALUES (497,128,'jhgjhgjhghh', 'jhgjhgjhghh',522, '8', 0, 145);
INSERT INTO cg_business VALUES (498,128,'jhgjhgjhghh', 'jhgjhgjhghh',522, '8', 0, 145);
INSERT INTO cg_business VALUES (499,128,'jhgjhgjhghh', 'jhgjhgjhghh',522, '8', 692, 145);
INSERT INTO cg_business VALUES (500,128,'jhgjhgjhghh', 'jhgjhgjhghh',30, '8', 692, 145);
INSERT INTO cg_business VALUES (501,128,'jhgjhgjhghh', 'jhgjhgjhghh',30, '8', 692, 145);
INSERT INTO cg_business VALUES (502,128,'jhgjhgjhghh', 'jhgjhgjhghh',30, '8', 0, 145);
INSERT INTO cg_business VALUES (503,128,'jhgjhgjhghh', 'jhgjhgjhghh',30, '8', 692, 145);

And i want to make a query to get the data from all cg_business table, fields, and in this query result i want to have also the data from location_tree table, and location_details(i want twice the results from this table in the same query).

The problem is when i use the query which i will attach below, as u see it i have no errors but no records in result too, without the p. portions i get only one record in result, and without the a. portions i have all the records.

The query:

SELECT
id,
cid,
bustitle_greek,
address,
addressnum,
perioxi,
location,
a.cords as acords,
a.title_greek as addressname,
p.cords as pcords,
p.title_greek as perioxiname,
l.title_greek as locationname
FROM
cg_business,
location_details AS a,
location_details AS p,
location_tree AS l
WHERE
b.cid=128
AND address = a.id
AND perioxi = p.id
AND location = l.id
ORDER BY bustitle_greek ASC;

And the wanted resault as of the result fields (with the found records) is the following...

id | cid | bustitle_greek | address | addressnum | perioxi | location | locationname | acords | addressname | pcords | perioxiname | locationname

Any help will be very very wellcome....

thnx in advance for just reading this post!!!!

[Troy]

It appears that you have nothing that ties your tables together. I come to this assumption both from looking at your table structure and the query you posted.

Anytime you have a SQL statement with more than one table, you must have something that ties the tables together. Without this, you will always end up with a "cartesian product", which is almost never what you want.

I'm going to build you a simple 3 table example that will hopefully teach you how tables need to relate to each other. (This is why they call it an RDBMS or Relational Database Management System.)

============================================
Maker
============================================
maker_id   maker_name
--------------------------------------------
1	   Ford
2	   Chevy
3	   Dodge
--------------------------------------------

============================================
Model
============================================
model_id  model_name  maker_id
--------------------------------------------
1	  Mustang	 1
2	  Escort	  1
3	  Intrepid	3
4	  Caprice	 2
5	  Festiva	 1
--------------------------------------------

============================================
Car
============================================
car_id  model_id  year  color  mileage
--------------------------------------------
1	   4	  1990  white  145038
2	   2	  1992  red	115000
3	   5	  2000  green   71350 
4	   1	  2004  black   18790
5	   3	  1996  red	109482
6	   1	  1989  red	160000
--------------------------------------------

As you can see, rows in the Car table have a "model_id" that relates to the Model table. Also, rows in the Model table have a "maker_id" that relates to the Maker table. Therefore, if you want to select all Ford cars in descending order of year:

SELECT mak.maker_name
  ,mod.model_name
  ,car.year
  ,car.color
  ,car.mileage
FROM car
INNER JOIN model mod ON car.model_id = mod.model_id
INNER JOIN maker mak ON mod.maker_id = mak.maker_id
ORDER BY car.year DESC

If you want to select all red cars in ascending order by mileage, you could do this:

SELECT mak.maker_name
  ,mod.model_name
  ,car.year
  ,car.color
  ,car.mileage
FROM car
INNER JOIN model mod ON car.model_id = mod.model_id
INNER JOIN maker mak ON mod.maker_id = mak.maker_id
WHERE car.color = 'red'
ORDER BY car.mileage

[techniner]

ALWAYS ALWAYS ALWAYS have a unique identifer on your tables.. and then link the other tables back to each other so you can ref each piece of data in each table.

your missing your link thats the problem as stated above.

[tomasm]

Ok now with your example above.. i can clearly ask you if what i want can be done.

The table that links the other in your example
-----------
| model_id |
-----------
|
model_id.id

in my example
----------------------------------------------------------------
| address | pexioxi | location |
----------------------------------------------------------------
| | |
a.id (location_details) p.id (location_details) l.id (location_tree)

Questions:
-------------------------------
1. what i tried to do is to set the same table twice in a single query as a and p
Can that be done or is just science fiction?

2. in a single table i have 3 fields that i want based on their value to merge some fields from 3 relevant tables (let's say that a or p was set for another table, not as i wrote it above with the same table twice) ex. "where address=location_details.id"

So can i have more than one index field in a table to link the corresponding tables, or there must be only one link.field in each table for the corresponding table?!?!?
-------------------------------

Sorry if my question is stupid, i'm trying hard to understand thought...

thnx a lot for your time allready!!!!!!

[Troy]

Yes, you can use a single table more than once within a SQL statement, and you would do it as you think--by using a different table alias for each.

FROM car
INNER JOIN model mod1 ON car.model_id = mod1.model_id
INNER JOIN model mod2 ON car.model_id = mod2.model_id

The above is valid, and there are reasons to sometimes do this, although right now, I'm unable to think of one real-world example. The tables you posted do not appear to have anything that relates them to each other, and therefore, you have no way to join them -- meaning your results will be a cartesian product, and therefore, useless.

[tomasm]

SELECT
a.cords as acords,
a.title_greek as addressname,
p.cords as pcords,
p.title_greek as perioxiname,
l.title_greek as locationname
FROM
cg_business
INNER JOIN location_details a ON cg_business.address = a.id
INNER JOIN location_details p ON cg_business.perioxi = p.id
INNER JOIN location_tree l ON cg_business.location = l.id
WHERE
cid=128
ORDER BY bustitle_greek ASC;

I tried the above query to get only the fields from the related tables, and once again when i use the location_details twice as a and p (as it is written above) it does not give me anything. Allthought if i use it only once as a or as p I have all the records under the given cid with no records loss in the result.

thnx for your time... i'll try more.. :) :) :)

[Troy]

Try this:

SELECT a.cords as acords
  ,a.title_greek as addressname
  ,p.cords as pcords
  ,p.title_greek as perioxiname
  ,l.title_greek as locationname
FROM cg_business b
INNER JOIN location_details a ON (b.address = a.id
OR b.perioxi = a.id)
INNER JOIN location_tree l ON b.location = l.id
WHERE b.cid = 128
ORDER BY b.bustitle_greek ASC;

[tomasm]

hey i think that i found the problem..

it isn't the query as syntax... but the fields type.. i don't have result in that query cause the fields perioxi, address are int(11), not null with 0 value by default.

Is there a way to set that in the query syntax to display the records with the 0's in the specific fields, or i must change the field type for perioxi, address?!?!?!?!

thnx in advance!!!!! :cheesy: :cheesy: :cheesy: