0

i have problem with func "on". this is the Query :

Retrieving the names of dog lovers who have performed at least 3 babysitters in apartment type houses

CREATE TABLE DogLover (
DogLoverID INT PRIMARY KEY,
name VARCHAR(50),
DailyWage INT,
phone INT,
city VARCHAR(50));

CREATE TABLE House (
HouseID INT PRIMARY KEY,
HouseType VARCHAR(50),
no_of_dogs INT, 
max_cancel_notice INT);

CREATE TABLE DogLoverShift (
DogLoverID INT,
HouseID INT PRIMARY KEY,
StartDate DATE,
EndDate DATE,
FOREIGN KEY (DogLoverID) REFERENCES DogLover(DogLoverID),
FOREIGN KEY (HouseID) REFERENCES House(HouseID));

the query that i try is :

SELECT *
FROM DogLoverShift inner join DogLover AS newTable
ON doglover.DogLoverID = doglovershift.DogLoverID

the error :

Error Code: 1054. Unknown column 'doglover.DogLoverID' in 'on clause'

EDIT : after i see all the good help here, i change the query :


SELECT *
FROM DogLoverShift inner join DogLover inner join House AS newTable ON newtable.DogLoverID=DogLoverShift.DogLoverID AND newtable.HouseID=house.HouseID;

and the error :

Error Code: 1054. Unknown column 'newTable.DogLoverID' in 'on clause'

EDIT 2: (hope the last edit .. ) now i use this code ( thansk to 'Arulkumar' !)

SELECT * 
FROM DogLoverShift AS DLS 
INNER JOIN DogLover AS DG ON DG.DogLoverID = DLS.DogLoverID
INNER JOIN House AS HS ON HS.HouseID = DLS.HouseID

now how i check : "performed at least 3 babysitters in apartment type houses"? i know i need to group by, and make WHERE , but i dont know how :-(

idan
  • 69
  • 1
  • 7
  • 1
    most probably your server is a *NIX server and thus mysql is case sensitive (check the table names in the on clause). See [this](https://stackoverflow.com/questions/6134006/are-table-names-in-mysql-case-sensitive) for reference – Lelio Faieta May 23 '19 at 15:36
  • You're using pascal case in the create statement but all lower case in your `on` statement – stever May 23 '19 at 15:37
  • You aliased the table to newTable. So use newTable.DogLoverID – forpas May 23 '19 at 15:41
  • @LelioFaieta , whit sensitive you mean the Caps Look lettrs ? – idan May 23 '19 at 15:52
  • You alias your `DogLover` table as `newTable`, so of course `doglover.DogLoverID` will fail. – Eric May 23 '19 at 16:02
  • thanks you all, now its work !! – idan May 23 '19 at 17:03

2 Answers2

0

Your current syntax is off, because as soon as you aliased the DogLover table as newTable, you lost the ability to refer to DogLover again in the ON clause. But, your current query is very far off from what you need, so here is one correct way to approach your query:

SELECT
    dl.name
FROM DogLover dl
INNER JOIN DogLoverShift dls
    ON dl.DogLoverID = dls.DogLoverID
INNER JOIN House h
    ON dls.HouseID = h.HouseID
GROUP BY
    dl.name
HAVING
    SUM(h.HouseType = 'Apartment') >= 3;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • i know i fat away from the good syntax, i try to understand what i do here .. anyway, i copy your code, and return res : 0.. – idan May 23 '19 at 15:49
  • @idan Without seeing your exact data, everyone who answers here would be guessing to some degree. Can you tell us if the `HouseType` you want to match is actually `'houses'`? Or, is it some other value (e.g. `house`, in the singular) ? – Tim Biegeleisen May 23 '19 at 16:12
  • ok.. is google translate problem when i translate the question... sorry houses = Apartment ( in HouseType ) – idan May 23 '19 at 16:15
  • Please retry my query using `Apartment` as the house type you want to match. – Tim Biegeleisen May 23 '19 at 16:16
  • ok, get 0 row's now. but i will double check the database i have, i sure i have data for get some res – idan May 23 '19 at 16:24
  • Consider adding a demo to your question using a site like [DBFiddle](https://dbfiddle.uk). – Tim Biegeleisen May 23 '19 at 16:31
0

I think the issue with the table name newTable. You can use:

SELECT *
FROM DogLoverShift 
inner join DogLover AS newTable ON newTable.DogLoverID = DogLoverShift.DogLoverID

or directly with the table name

SELECT *
FROM DogLoverShift 
inner join DogLover ON DogLover.DogLoverID = DogLoverShift.DogLoverID

As per your comments, to join three tables, use the following query:

SELECT * 
FROM DogLoverShift AS DLS 
INNER JOIN DogLover AS DG ON DG.DogLoverID = DLS.DogLoverID
INNER JOIN House AS HS ON HS.HouseID = DLS.HouseID
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
  • if i need to do inned join to 3 tables "DogLover,House,DogLoverShift" what the syntax for this ? i try like this and get the same error (1054) : SELECT * FROM DogLoverShift inner join DogLover inner join House AS newTable ON newtable.DogLoverID=DogLoverShift.DogLoverID AND newtable.HouseID=house.HouseID – idan May 23 '19 at 15:51
  • @idan Also instead of `SELECT *`, mention the required column names such as `SELECT DLS.DogLoverID, DG.HouseID, ....` – Arulkumar May 23 '19 at 15:58
  • thanks you alot ! but, when i copy the code, same error : Error Code: 1054. Unknown column 'DG.HouseID' in 'on clause' – idan May 23 '19 at 16:00
  • but i want (for now, to know what my table show me) to see all the rows and coloms from the inner join, later when this table be done, i use the WHERE command and redo to SELECT* – idan May 23 '19 at 16:01
  • @idan There is a typo, instead of `DG.HouseID` use `DLS.HouseID` – Arulkumar May 23 '19 at 16:03
  • @idan You're Welcome. I'm glad I was able to help! – Arulkumar May 23 '19 at 17:04