equi-join with two tables?

Question

I would like to join two table using equi-join. I have this query so far:

SELECT CD_INVENTORY.CD_NAME, CD_INVENTORY.IN_STOCK, PERFORMERS.PERF_NAME,CD_INVENTORY.PERF_ID,PERFORMERS.PERF_ID, PERFORMERS.TYPE_ID
FROM PERFORMERS,CD_INVENTORY
WHERE CD_INVENTORY.PERF_ID = PERFORMERS.PERF_ID;

It keeps asking for a prompt to enter a parameter value for CD_INVENTORY.PERF_ID and its not supposed to do that if done correctly.

Anyone know how to do this correctly? I have been changing bits and pieces trying to figure out where I went wrong.

---

Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda. Blog - http://www.computerprofessions.co.nr

Answer 1

The query seems to be OK. I would write it differently, though:

SELECT Inv.CD_NAME, Inv.IN_STOCK, Perf.PERF_NAME,Inv.PERF_ID,Perf.PERF_ID, Perf.TYPE_ID
FROM PERFORMERS Perf INNER JOIN CD_INVENTORY Inv
ON Perf.PERF_ID = Inv.PERF_ID;

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I know that query will work because I did something similar but iam doing this for a database design class and the teacher wants a equi-join on the statement instead of inner join. 

note: iam using access for this assignment. 

---

Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda. Blog - http://www.computerprofessions.co.nr

Answer 3

For Access you may have better luck asking in Access forum. Let me know if you want your thread to be moved there. For now, using your original query try switching around your WHERE condition, e.g.

WHERE Performers.Perf_ID = CD_INVENTORY.Perf_ID

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

 but iam doing this for a database design class and the teacher wants a equi-join on the statement instead of inner join. 

That is an EQUI-JOIN what you have. Naomi's suggestion formatted:

SELECT Inv.cd_name, 
       Inv.in_stock, 
       Perf.perf_name, 
       Inv.perf_id, 
       Perf.perf_id, 
       Perf.type_id 
FROM   performers Perf 
       INNER JOIN cd_inventory Inv 
               ON Perf.perf_id = Inv.perf_id;  

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 5

I think the first answer about it being the correct query was fine because despite my problem I still received an A. I think maybe it might have been the relationship between my tables too. But whatever it was thanks for your help.

---

Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda. Blog - http://www.computerprofessions.co.nr