MySQL Join Query

MySQL

I have 4 tables in my MySQL database:

category with fields cat_id,category
nonadminusers with fields id, username,password,category,fid
ambulance with fields ambulance_id, ambulance_name,etc….
hospital with fields hospital_id, hospital_name….etc

The nonadminusers table has data like id=1,username=tes,password=pass,category=1,fid=2

The category has data like cat_id=1,category=Hospital, cat_id=2,category=Ambulance

I need to display data from nonadminusers joining data from category table linked with category foreign key and and fid foreign key either from hospital or ambulance depending on category id in table.

I am a newbie to MySQL and how is it possible using joins or otherwise in MySQL query?

Best Answer

Try the following SQL query:

Select * 
from nonadminusers as a 
inner join category as c 
    on a.category=c.cat_id
where category in ('Ambulance','Hospital');

Related Solutions

Thesql inner join producing more results than it should

Why are you using two syntaxes, old fashioned verbs, roles and then an explicit inner join? Makes the query very hard to read and since you don't have any where clause that defines the relationship between verbs and roles (this is one of the biggest problems with using old fashioned syntax), you essentially have the cartesian product of those first two tables, and this probably explains your row counts.

You need to define the join columns between verbs and roles and, preferably, put that into an ON clause with a proper INNER JOIN. E.g.:

SELECT r.name, v.name, p.enabled
FROM verbs AS v
INNER JOIN privileges AS p
  ON v.id = p.verb_id
INNER JOIN roles AS r
  ON p.role_id = r.id;

Mysql – Quantity of entries

 select a.occuId, a.occuDscr, COUNT(b.pstOccuId ) AS `amount` 
 from occupation_field a
 INNER JOIN job_post b ON (b.pstOccuId = a.occuId)
 GROUP BY a.occuId;

If you want to display categories with 0 posts, you need to use LEFT JOIN instead of INNER. Also, for other RDMS except mysql you have to GROUP BY a.occuId, a.occuDscr (Mysql lets you have just GROUP BY a.occuId

UPDATE by @RolandoMySQLDBA

The output of @a1ex07's query looks like this:

mysql> select a.occuId, a.occuDscr, COUNT(b.pstOccuId ) AS `amount`
    ->  from occupation_field a
    ->  INNER JOIN job_post b ON (b.pstOccuId = a.occuId)
    ->  GROUP BY a.occuId;
+--------+-------------------+--------+
| occuId | occuDscr          | amount |
+--------+-------------------+--------+
|      1 | Software Engineer |      2 |
|      5 | Plumbers          |      1 |
|      7 | Musicians         |      1 |
+--------+-------------------+--------+
3 rows in set (0.00 sec)

Best Answer

Related Solutions

Thesql inner join producing more results than it should

Mysql – Quantity of entries

Related Question