CODEWITHSUNDEEP
mysql
Horák Jan
Horák Jan

Reputation: 51

mysql search over two tables with some kind of inner join

table estate_common -> common data for estate as title name etc. estate could differ in kind for example kind 1 - flat kind 2 - house etc.

id  | kind | title     | name
596 | 1    | title 596 | name 596
597 | 1    | title 597 | some 597
598 | 1    | title 598 | some 598
599 | 1    | title 599 | some 599
600 | 1    | title 600 | some 600
601 | 5    | title 607 | some 601

table estate_kind_1 -> specific data for differnet kind each estate_kind_# has differnet structure columns etc.

id | estate_common_id | floor | flat | shell
1  | 596              | 250   | 9b   | pvc
2  | 597              | 156   | 10c  | abc
3  | 598              | 126   | 12a  | csd
4  | 599              | 226   | 2a   | add
5  | 600              | 198   | 15o  | fdd

id from estate_common is equal estate_common_id from estate_kind_# wherer # is number of kind from table estate_common

before preparing query i know that i must combine data from table estate_common and estate_kind_1

for simple detail of estate with data from both tables it´s easy

SELECT `common`.*, `kind`.* FROM `estate_common` AS `common` INNER JOIN `estate_kind_1` AS `kind` ON common.id = kind.estate_common_id WHERE (common.id = '597')

but now i start doing some xml export and need to select data from both table means estate_common and estate_kind_1 upon selection of estate_common id´s

so query like this

SELECT `common`.*, `kind`.* FROM `estate_common` AS `common`, `estate_kind_1` AS `kind` WHERE (common.id IN ('596,597'))

but it gives me strange result

id  | kind | title     | name     | id | estate_common_id | floor | flat | shell
596 | 1    | title 596 | name 596 | 1  | 596              | 250   | 9b   | pvc
596 | 1    | title 596 | name 596 | 2  | 597              | 156   | 10c  | abc

data from table estate_kind_1 means with id 1 a 2 on right side are ok but left from estate_common are for both lines same

should be

id   | kind | title     | name   | id | estate_common_id | floor | flat | shell
596  | 1    | title 596 | name 596 | 1  | 596              | 250   | 9b   | pvc
597  | 1    | title 597 | name 597 | 2  | 597              | 156   | 10c  | abc

i tried group by distinct etc. but probably in wrong way will be glad for any help

thanks

Upvotes: 0

Views: 210

Answers (3)

Horák Jan
Horák Jan

Reputation: 51

solution is realy simple

SELECT common.*, kind.* FROM estate_common common, estate_kind_1 kind WHERE common.id = kind.estate_common_id AND (common.id IN (596,597))

Upvotes: 0

Degan
Degan

Reputation: 989

I must be missing something about your question. Is this what you want?

SELECT `common`.*, `kind`.*
FROM `estate_common` AS `common`
     INNER JOIN `estate_kind_1` AS `kind` ON common.id = kind.estate_common_id
WHERE common.id IN ('596,597')

Upvotes: 0

brian-brazil
brian-brazil

Reputation: 34112

Your latter query has no explicit JOIN so an INNER JOIN is being done. As there's also no ON clause, what's happening is a full cross product with a restriction on one table. The result you should be getting is that for every row of kind you will have two results - one for 596 of common and one for 597.

I assume you have cropped the result at 2 rows.

The query you want is:

SELECT `common`.*, `kind`.* 
  FROM `estate_common` AS `common`, `estate_kind_1` AS `kind` 
  ON common.id = kind.estate_common_id
  WHERE (common.id IN ('596,597'));

Upvotes: 1

Related Questions