CODEWITHSUNDEEP
phpmysql
zzwyb89
zzwyb89

Reputation: 470

adding another select

SELECT COUNT(a.aircraft) as total
    , a.aircraft
    , b.fullname AS aircraft_name 
FROM db_pireps AS a 
JOIN db_aircraft AS b ON a.aircraft = b.id 
WHERE pilotid = {$pilotid}
GROUP BY aircraft 
ORDER BY total DESC 
LIMIT 6

I have this query, however I am trying to add b.registration AS reg but my attempts seem to be failing, as I don't know how to put another SELECT within that query.

Upvotes: 0

Views: 52

Answers (4)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

you just need to add column b.registration and give it alias as reg. see below. Its the same as you have done count

SELECT COUNT(a.aircraft) as total, a.aircraft, b.fullname AS aircraft_name, b.registration   AS reg
FROM db_pireps AS a JOIN db_aircraft AS b ON a.aircraft = b.id 
WHERE pilotid = {$pilotid}
GROUP BY a.aircraft 
 ORDER BY total DESC LIMIT 6

Upvotes: 0

c4pone
c4pone

Reputation: 797

I don't really understand your Question, but I think this should work:

$aircraft_query = "SELECT COUNT(a.aircraft) as total, a.aircraft, b.fullname AS aircraft_name, b.registration AS reg 
                  FROM db_pireps AS a JOIN db_aircraft AS b ON a.aircraft = b.id 
                  WHERE pilotid = {$pilotid}
                  GROUP BY aircraft 
                  ORDER BY total DESC LIMIT 6";

Upvotes: 1

araknoid
araknoid

Reputation: 3125

SELECT COUNT(a.aircraft) as total, a.aircraft, b.fullname AS aircraft_name, b.registration AS reg
FROM db_pireps AS a JOIN db_aircraft AS b ON a.aircraft = b.id 
WHERE a.pilotid = {$pilotid}
GROUP BY a.aircraft 
ORDER BY total DESC LIMIT 6

Tip: To avoid problem with columns naming, if you use alias for table name, use the alias on all columns that you are using in the query.

Upvotes: 1

Sammitch
Sammitch

Reputation: 32252

Use a comma:

SELECT
  COUNT(a.aircraft) as total,
  a.aircraft,
  b.fullname AS aircraft_name,
  b.registration AS reg
FROM db_pireps AS a JOIN db_aircraft AS b 
  ON a.aircraft = b.id WHERE pilotid = {$pilotid}
GROUP BY aircraft ORDER BY total DESC LIMIT 6

Upvotes: 1

Related Questions