Select from all companies that have catalogues

Asked

Viewed 71 times

1

I’m having a problem at a select in two tables that are returning the value twice.
I have the table businesses and table catalogues which are linked by company id. and catalogues.id_company. When I make a select that has two or more catalogues of the same company, appears the company more than once.

CREATE TABLE IF NOT EXISTS `tbl_empresa` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `tbl_catalogos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tbl_empresa_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tbl_empresa_id` (`tbl_empresa_id`)
)

I need to select all companies that have catalogues.
How do I do this? What would the query look like?

mysql sql select

2 answers

1


To verify the businesses who possess catalogue you need to make a join with catalogues, but as you do not need to display all the catalogues, only the businesses, you have two alternatives: select only companies with distinct or select only companies using a group by in it.

With distinct:

select distinct e.* from tbl_empresa e 
join tbl_catalogos c on (e.id = c.tbl_empresa_id);

With group by:

select e.* from tbl_empresa e 
join tbl_catalogos c on (e.id = c.tbl_empresa_id)
group by e.id;

  • Thank you very much! I was trying to do it for a long time. I was using a very complicated query and the solution was very simple. I used the GROUP BY, it worked perfectly. Thank you again!

  • I recommend you take some time later to study the most common structures of SQL! Read about join, distinct, group by, order by... Everyone ends up needing to make more complicated queries and spends more time trying to solve a specific problem than the time needed to study the basics of SQL :)

  • I will. But my biggest difficulty at the time was a matter of logic because I was trying to pull everything only with Join and ect. The query had become huge and did not do what I needed. I think as a matter of practice. I will study and practice these questions more.

0

SELECT e.id
FROM tbl_empresa AS e
INNER JOIN tbl_catalogo AS c
ON e.id = c.tbl_empresa_id

Browser other questions tagged mysql sql select

You are not signed in. Login or sign up in order to post.