Mysql only select records present in other table

Question

I've got two tables:

LOGGINGTYPES
id
name

LOGFILES
id
type_id (linked to loggintypes.id)
client_ip
time
logcontent

I am quite new to MYSQL, but I am looking for a query that only selects * from loggingtypes if there are logs linked to it.

With "SELECT * FROM loggingtypes ORDER BY name" I get everything, how do I get it to select only rows which have logs linked to it?

Is

SELECT 
DISTINCT logging_types.id, loggingtypes.name 
FROM logging, logging_types 
WHERE logging.type_id = loggingtypes.id

the best way to go?

score 0 · Answer 1 · answered Apr 18 '13 at 16:17

0

SELECT lt.*
FROM LOGGINGTYPES lt
INNER JOIN LOFGILES lf
ON lt.ID = lf.type_id

The INNER JOIN will restrict your LOGGINGTYPES listing to only records that have matches in LOGFILES

answered Apr 18 '13 at 16:17

Melanie

score 0 · Accepted Answer · answered Apr 18 '13 at 16:18

0

As far as I can tell, your query is right.

One alternative is to use Join:

select distinct 
    lt.id, lt.name
from 
    logging_types as lt
    inner join logging as l on lt.id = l.type_id

answered Apr 18 '13 at 16:18

Barranka

Is inner join faster or safer or something? Why use this over my query? – Mbrouwer88 Apr 18 '13 at 16:19
@Mbrouwer88 It's an alternative, just that. You may want to practice with `inner join` usage, because you may need to use `left join` or `right join` in the future. For a simple relation between two tables, `where` and `inner join` are mostly equivalent, but for more complex relations, I think `inner join` is a cleaner way to go – Barranka Apr 18 '13 at 16:21
Thanks for clearing that up. Will use inner join instead of WHERE from now on. – Mbrouwer88 Apr 22 '13 at 12:54

2 Answers2